Page 1 of 2 12 LastLast
Results 1 to 10 of 13
  1. #1
    Master Untangler dmor's Avatar
    Join Date
    Jun 2009
    Posts
    503

    Default Free disk space without dropping entire reports tables?

    Customer had an app on the network that didn't like SSL Inspection and was creating tons of sessions per minute. Filled up disk to a dangerously low level.

    Verified the daily reports.sessions tables are indeed the big consumers of the disk space (used https://wiki.untangle.com/index.php/...k_Usage_Report)

    Now that we've identified and rectified the root cause, we'd like to purge out *some* old report data to quickly free up more space. I don't want to simply purge *all* report data with this https://wiki.untangle.com/index.php/...r_Reports_Data

    If we change the report data retention setting from 45 to 30, is there a way we can manually tell Untangle to execute the process of purging that old data (instead of waiting for the process to occur overnight)?

    And after that, can we force Postgres to vacuum the DB to reclaim free space back to the OS?

    Thanks much for your help with this.

    -
    Doug

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

    Default

    If you're comfortable with SQL and can connect to the postgresql database, you can delete records from the table. However, this will not immediately free up disk space. The good news is it will mark the table pages as not used. Then, if you do a postgresql VACUUM the disk space can be reclaimed.
    Five time Microsoft ASP.Net MVP managing a Lenovo RD330 / E5-2420 / 8GB with Untangle 12.2 to protect 200Mbits for ~400 residential college students and associated staff and faculty

  3. #3
    Master Untangler dmor's Avatar
    Join Date
    Jun 2009
    Posts
    503

    Default

    Quote Originally Posted by jcoehoorn View Post
    If you're comfortable with SQL and can connect to the postgresql database, you can delete records from the table. However, this will not immediately free up disk space. The good news is it will mark the table pages as not used. Then, if you do a postgresql VACUUM the disk space can be reclaimed.
    Thanks. I'm not sure which records to purge and I think it would take much more time to determine the queries manually. Is there a way to invoke the nightly process that would otherwise happen anyway, in which Untangle purges out records that are older than the reports data retention threshold?

  4. #4
    Master Untangler dmor's Avatar
    Join Date
    Jun 2009
    Posts
    503

    Default

    Just got off the phone w/ support on this. They don't know how to manually purge out log data older than "X" number days. Additionally they are telling me that if I change the current retention setting from 45 days to 30 days, those older 15 days won't be purged/deleted tonight. This setting apparently only affects *newly* created log data. Is this correct? So in essence there is absolutely no way to delete log data older than "X" days, unless we manually go in and start dropping/deleting tables/records in psql?

  5. #5
    Master Untangler dmor's Avatar
    Join Date
    Jun 2009
    Posts
    503

    Default

    OK just found out about /etc/cron.daily/reports-cron

    And specifically that when we update the report data retention setting in Reports > Data, the reports-cron file gets updated. Specifically this line:
    /usr/share/untangle/bin/reports-clean-tables.py -d postgresql 33 | logger -t uvmreports

    The "33" above corresponds to the setting I configured for report data retention. Changing that value in the web interface, changes the number here from 33 [days] to whatever number of days I update it to.

    I just manually ran ./reports-cron

    But now I'm kind of wishing I would have just run only the scripts in that file that are needed. I think a lot of time/resources are used to create the daily reports (which I believe are also called by this script, but I don't need them right now).

    Currently standing by to see what happens.

  6. #6
    Master Untangler dmor's Avatar
    Join Date
    Jun 2009
    Posts
    503

    Default

    Well. That did it. Free space is now 30% instead of 8%.

    However, I'm concerned about the fact that reports-cron also calls /usr/share/untangle/bin/reports-generate-tables.py | logger -t uvmreports

    As the new tables were already created last night, I'm not sure what the result of running this script again in the same day is. No errors were logged to the console.

    But the following got logged to the postgresql log:
    Code:
    2017-07-31 13:37:24 MDT [63043-1] postgres@postgres ERROR:  role "untangle" already exists
    2017-07-31 13:37:24 MDT [63043-2] postgres@postgres STATEMENT:  CREATE ROLE untangle NOSUPERUSER CREATEDB NOCREATEROLE INHERIT LOGIN;
    2017-07-31 13:37:24 MDT [63046-1] postgres@postgres ERROR:  database "uvm" already exists
    2017-07-31 13:37:24 MDT [63046-2] postgres@postgres STATEMENT:  CREATE DATABASE uvm OWNER postgres;
    2017-07-31 13:38:17 MDT [63789-1] LOG:  automatic analyze of table "uvm.pg_catalog.pg_type" system usage: CPU 0.01s/0.13u sec elapsed 1.31 sec
    2017-07-31 13:38:20 MDT [63789-2] LOG:  automatic analyze of table "uvm.pg_catalog.pg_constraint" system usage: CPU 0.01s/1.74u sec elapsed 3.39 sec
    2017-07-31 13:38:22 MDT [63789-3] LOG:  automatic analyze of table "uvm.pg_catalog.pg_attrdef" system usage: CPU 0.00s/1.11u sec elapsed 1.42 sec
    2017-07-31 13:38:22 MDT [63789-4] LOG:  automatic analyze of table "uvm.pg_catalog.pg_inherits" system usage: CPU 0.00s/0.00u sec elapsed 0.04 sec
    2017-07-31 13:39:25 MDT [65789-1] LOG:  automatic vacuum of table "uvm.pg_catalog.pg_type": index scans: 1
            pages: 0 removed, 892 remain
            tuples: 18222 removed, 5892 remain, 0 are dead but not yet removable
            buffer usage: 3822 hits, 2799 misses, 2371 dirtied
            avg read rate: 2.399 MB/s, avg write rate: 2.032 MB/s
            system usage: CPU 0.11s/0.13u sec elapsed 9.11 sec
    2017-07-31 13:44:16 MDT [70704-1] LOG:  automatic analyze of table "uvm.reports.interface_stat_events_2017_07_31" system usage: CPU 0.00s/0.16u sec elapsed 0.58 sec
    I know this is in the realm of *unsupported*. But I really needed to free up this disk space, as it was dangerously low. And having the ability to purge the stuff older than the log retention setting on-demand, just makes sense.

    If anyone wants to chime in and tell me whether I should be worried about what I just did, I would greatly appreciate it.

  7. #7
    Master Untangler dmor's Avatar
    Join Date
    Jun 2009
    Posts
    503

    Default

    Looking in the postgresql logs for prior days, the errors about role "untangle" already existing and database "uvm" already existing actually happens every day anyway.

    Interesting other log files found here though (unrelated to this thread) are:
    Code:
    postgresql-9.4-main.log.7.gz:2017-06-18 05:41:00 MDT [93414-16] postgres@uvm ERROR:  new row for relation "session_minutes_2017_06_16" violates check constraint "session_minutes_2017_06_16_time_stamp_check"
    postgresql-9.4-main.log.7.gz:2017-06-18 16:52:00 MDT [93414-19] postgres@uvm ERROR:  new row for relation "session_minutes_2017_06_16" violates check constraint "session_minutes_2017_06_16_time_stamp_check"
    postgresql-9.4-main.log.7.gz:2017-06-18 20:57:00 MDT [93414-22] postgres@uvm ERROR:  new row for relation "session_minutes_2017_06_16" violates check constraint "session_minutes_2017_06_16_time_stamp_check"
    postgresql-9.4-main.log.7.gz:2017-06-19 01:14:00 MDT [93414-25] postgres@uvm ERROR:  new row for relation "session_minutes_2017_06_15" violates check constraint "session_minutes_2017_06_15_time_stamp_check"

  8. #8
    Newbie
    Join Date
    Apr 2017
    Posts
    10

    Default

    Thanks for your time today, dmor, and for your testing. It's a fine line of what we support in CLI and what we don't but; when we can provide information that you as the user puts to excellent use and testing it's a win all around. ~Chris C.

  9. #9
    Master Untangler dmor's Avatar
    Join Date
    Jun 2009
    Posts
    503

    Default

    Quote Originally Posted by Cods View Post
    Thanks for your time today, dmor, and for your testing. It's a fine line of what we support in CLI and what we don't but; when we can provide information that you as the user puts to excellent use and testing it's a win all around. ~Chris C.
    Thanks for your help too.

  10. #10
    Master Untangler dmor's Avatar
    Join Date
    Jun 2009
    Posts
    503

    Default

    I do recommend that you guys add to the GUI the ability to purge expired log data & vacuum the DB immediately. Would really be beneficial at times like this.
    wbennett77 likes this.

Page 1 of 2 12 LastLast

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