Results 1 to 8 of 8
  1. #1
    Untangler
    Join Date
    Nov 2019
    Posts
    43

    Question What activity is causing DB Table sizes ?

    So my home edition FW running 16.3.2 is moaning about disk space every other day.

    I;ve turned off session logging as per a tuning guide i found on here:

    firefox_EwKdg0r1b0.png

    I've ran the disk usage script and can see the reports.sessions table are likely to be the main culprits:

    = Disk Free Status =
    Filesystem Size Used Avail Use% Mounted on
    udev 3.9G 0 3.9G 0% /dev
    tmpfs 790M 82M 709M 11% /run
    /dev/sda1 53G 46G 4.2G 92% /
    tmpfs 3.9G 40K 3.9G 1% /dev/shm
    tmpfs 5.0M 0 5.0M 0% /run/lock
    tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
    tmpfs 790M 0 790M 0% /run/user/10000
    tmpfs 790M 0 790M 0% /run/user/0


    = Postgres: Disk Use of postgres db files =
    41G /var/lib/postgresql/11


    = Postgres: Top 30 Tables/Relations =
    relation | size
    -----------------------------------------------------------------+---------
    reports.sessions_2021_09_06 | 2529 MB
    reports.sessions_2021_09_05 | 2478 MB
    reports.sessions_2021_09_04 | 2414 MB
    reports.session_minutes_2021_09_06 | 1330 MB
    reports.session_minutes_2021_09_05 | 1289 MB
    reports.session_minutes_2021_09_04 | 1242 MB
    reports.sessions_2021_09_03 | 861 MB
    reports.sessions_2021_09_06_hostname_idx | 527 MB
    reports.sessions_2021_09_05_hostname_idx | 513 MB
    reports.sessions_2021_09_04_hostname_idx | 503 MB
    reports.sessions_2021_09_06_application_control_application_idx | 434 MB
    reports.session_minutes_2021_09_03 | 425 MB
    reports.sessions_2021_09_05_application_control_application_idx | 422 MB
    reports.sessions_2021_09_06_c_client_addr_idx | 415 MB
    reports.sessions_2021_09_04_application_control_application_idx | 412 MB
    reports.sessions_2021_09_06_time_stamp_idx | 410 MB
    reports.sessions_2021_09_05_c_client_addr_idx | 405 MB
    reports.sessions_2021_09_05_time_stamp_idx | 402 MB
    reports.sessions_2021_09_04_c_client_addr_idx | 395 MB
    reports.sessions_2021_09_06_application_control_blocked_idx | 394 MB
    reports.sessions_2021_09_06_s_server_addr_idx | 394 MB
    reports.sessions_2021_09_06_threat_prevention_blocked_idx | 394 MB
    reports.sessions_2021_09_06_server_intf_idx | 394 MB
    reports.sessions_2021_09_04_time_stamp_idx | 394 MB
    reports.sessions_2021_09_06_application_control_flagged_idx | 394 MB
    reports.sessions_2021_09_06_policy_id_idx | 394 MB
    reports.sessions_2021_09_06_client_intf_idx | 394 MB
    reports.sessions_2021_09_06_threat_prevention_flagged_idx | 394 MB
    reports.sessions_2021_09_06_firewall_blocked_idx | 394 MB
    reports.sessions_2021_09_06_username_idx | 393 MB
    (30 rows)

    What can i do to identify whats causing this usage ? i have other deployments running on the the same hardware that use a fraction of the available disk.

    Thanks

  2. #2
    Untangler
    Join Date
    Aug 2019
    Posts
    53

    Default

    It seems like you've already identified what is causing the problem.

    https://support.untangle.com/hc/en-u...onsumption-low

  3. #3
    Untangler
    Join Date
    Nov 2019
    Posts
    43

    Default

    Quote Originally Posted by sperman View Post
    It seems like you've already identified what is causing the problem.

    https://support.untangle.com/hc/en-u...onsumption-low
    How so?

    From the link:

    1. Lower the Data Retention period in Apps > Reports > Data - Already done
    2. Disable extraneous logging options in Config > Network > Advanced - Already done
    3. Limit the number of alerts being logged - not the solution
    4. Reduce the amount of logging being done by applications - I agree, my post was asking for help to discover which module is generating so much logging
    5. Bypass some traffic - I have a couple of devices already being bypassed

  4. #4
    Untangle Ninja sky-knight's Avatar
    Join Date
    Apr 2008
    Location
    Phoenix, AZ
    Posts
    26,163

    Default

    1.) is and always has been the only viable option.

    The problem is... figuring out what you need to reduce retention to is the problem!

    In the reports module next to the retention setting is a button that will delete all reports. Pressing that will certainly solve your storage issue. However... it doesn't tell you where to put your retention! Which is why I ASKED FOR a button that would simply tell reports to dump all data beyond the currently configured retention value.

    Sadly... in response to this request we got the nuke all button instead... Which while helpful, is about as useful to an admin as a screen door on a submarine in these circumstances. YOU LISTENING TO ME UNTANGLE DEVS STOP BEING DOORNOBS! *ahem* The button is only useful in an emergency save me now situation. Again, we need to figure out what to set retention to so this stops. And that means making the data you're generating fit within the storage available.

    Now... if you're willing to open up SSH on your Untangle, you can run a script built into Untangle... and written by the Untangle devs themselves. Because of course they wrote it... anyway...

    Code:
    /usr/share/untangle/bin/reports-clean-tables.py -d "postgresql" 7
    The above command dumps all report data older than 7 days. Change that 7 to match the number of days you have in retention and run it. It'll nuke all things beyond that value. Check your dashboard to see if the drive space cleared up. If it didn't... you need to REDUCE YOUR RETENTION MORE! And run the above again with the new value. You'll immediately see storage clear as you reduce that value, and the storage volume you have is going to be about where it stays from now on. Because you're be at a nice steady state.

    If that means you have to take retention below what you want... oh well. If you want more time, you need more storage. And once you've found a steady state, you can divide the storage you're using by the number of days it's working at to determine your storage amount / day and use that calculation to know how much more drive you need.
    Last edited by sky-knight; 09-07-2021 at 12:52 PM.
    Rob Sandling, BS:SWE, MCP
    NexgenAppliances.com
    Phone: 866-794-8879 x201
    Email: support@nexgenappliances.com

  5. #5
    Untangle Ninja jcoehoorn's Avatar
    Join Date
    Mar 2010
    Location
    York, NE
    Posts
    1,866

    Default

    Quote Originally Posted by sky-knight View Post
    The problem is... figuring out what you need to reduce retention to is the problem!
    You can do this by deleting everything, noting the disk use, and then coming back at the exact same time the next day to check the difference. This will give you one day's sample to use as a starting estimate.

    Obviously some days will be heavier use days, but some days might also be lighter. This at least gives you a place to begin making an educated guess about what your per-day needs are, and from there how many days you can afford given the space you have.

    Of course, this requires you to throw out any logs already gathered, which isn't ideal. Being able to back off one day at a time until you see disk drop would be better.
    Five time Microsoft ASP.Net MVP managing a Lenovo RD330 / E5-2420 / 16GB with Untangle 16.2 to protect 500Mbits for ~450 residential college students and associated staff and faculty

  6. #6
    Untangle Ninja sky-knight's Avatar
    Join Date
    Apr 2008
    Location
    Phoenix, AZ
    Posts
    26,163

    Default

    Quote Originally Posted by jcoehoorn View Post
    You can do this by deleting everything, noting the disk use, and then coming back at the exact same time the next day to check the difference. This will give you one day's sample to use as a starting estimate.

    Obviously some days will be heavier use days, but some days might also be lighter. This at least gives you a place to begin making an educated guess about what your per-day needs are, and from there how many days you can afford given the space you have.

    Of course, this requires you to throw out any logs already gathered, which isn't ideal. Being able to back off one day at a time until you see disk drop would be better.
    So let's see... we can wait until tomorrow... or we can run a script and know NOW.

    I can tell you which process is more MSP friendly... Which is how we determine sales. So we're right back to doornob status...

    Also, an average over multiple days is a far more accurate estimate. So you need a quick way to get to a known steady state... that's the script above, decreasing the day count until you see the storage needle move. Then you know how many days to divide by to get a decent number.

    Nuke it from orbit isn't an acceptable management technique... This is the "reboot fixed it" approach to managing report data volume! It's insane!
    Rob Sandling, BS:SWE, MCP
    NexgenAppliances.com
    Phone: 866-794-8879 x201
    Email: support@nexgenappliances.com

  7. #7
    Untangler
    Join Date
    Nov 2019
    Posts
    43

    Default

    Quote Originally Posted by sky-knight View Post
    1.) is and always has been the only viable option.

    The problem is... figuring out what you need to reduce retention to is the problem!
    Thanks for the info, going to have a play around with numbers to see if i can improve things at all.

  8. #8
    Newbie
    Join Date
    Sep 2021
    Posts
    1

    Default

    I think u already figure out your problem.

    Tempdb is used (and can grow) when users explicitly create objects like temp tables, variables, cursors, or stored procedures. Tempdb is also used when the database engine creates work tables for sorts or spooling, and row versioning operations.

    Thanks!!
    Last edited by jcoffin; 09-08-2021 at 09:00 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

SEO by vBSEO 3.6.0 PL2