Postgres process - high CPU load

The restarting of Postgres was not what resolved the issue, as I had restarted Postgres several times, with prior changes (making a change, restart Postgres, wait 10 or so minutes to see if the CPU spiked, when it did, revert the change, then try the next thing), prior to landing on this change. This change was probably the 5th or 6th thing tried, that finally worked. It is still working to this day. [H]

Matt,

for what it is worth - I think you were right. I was experiencing the exact same behavior with constant high CPU load caused by the progres process. I tried all suggested fixes of reducing data retention time etc. etc. but nothing worked. 

Multiple system restarts did also not do the trick (in case anybody still wants to hang on to that one).

After I applied your suggested 'effective_cache_size = 192MB' setting to postgresql.conf and then restarted the entire system the CPU usage went back to normal.

Interesting thing is that I did not have any issues with high CPU load until I upgraded to v8.301 and then to v8.302. The only other changes that coincided with these "firmware" upgrades was that I started using web proxy filters with active directory SSO (kerberos). Previously I did not use SSO.

Anyway - there is definitely something wrong with the progres setup. I run a small home use installation with two internet uplinks and a handful of family members as users - nothing big or complex, yet my CPU utilization for progres went up to 87%!

by the way - 

the actual file I ended up modifying is "postgresql.conf.large", located in "/var/storage/pgsql/cfg/". Reason being that at system start-up the postgresql config file gets replaced by the "pgconftune.sh" script, which in turn gets called from the postgresql init script only when the "start" parameter is passed to teh init script.

so - if you modify the postgresql.conf file directly, your changes will be undone the next time your system restarts...

Martijn.

Oh well - forget that. This does not solve the problem. I just found my system with the postgres proces eating up 85% of CPU solid...

I guess I am back to square 1.

Martijn.

I have a system where postgresql is absolutely swamping the CPU.  It is IO bound as shown by

vmstat 5 

which gives
 
procs -----------memory---------- ---swap-- -----io---- -system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0 31 1037380  58776  92792 3875176   14    3  1374  1666 10554 17072 23  5  2 70  0
 0 34 1037308 126872  91812 3807504  138    3  2112  2144 9746 15472 16  4  0 79  0
 0 26 1037248  59928  91888 3822320   11    0  1086  1045 8445 13287 11  4  2 82  0

Second last column is wa = wait, and the machine is spending 80% of its life waiting for IO to complete.  

Stopping postgresql with 

/etc/init.d/postgresql stop

Drops the waiting down to 5-10%.   (It also kills reporting stone dead).  Starting it takes a while before WA jumps back up, but it does eventually.  Still digging - will update.  


>>>>>
Inspired by the example of Matt Wagner earlier, and disgusted by posgresql consuming 4-5 times more CPU than FW+IPSec+IDS+AV+WebFiltering combined I searched and found the following very interesting article on optimising Postgresql performance
www.pgexperts.com/document.html?id=36 


>>>>>
To do optimisation of pgsql (in step 4. of above article) you need to look in 

/var/storage/pgsql/cfg

which contains

-rw-r--r-- 1 root root 2969 Aug 14  2012 postgresql.conf.large
-rw-r--r-- 1 root root 2968 Aug 14  2012 postgresql.conf.medium
-rw-r--r-- 1 root root 2967 Aug 14  2012 postgresql.conf.small

Stopping ... then starting posgresql copies one of these based on your RAM size.  If you have 6Gig or more then postgresql.conf.large gets copied to 

/var/storage/pgsql/data/postgresql.conf

This does not happen when you just restart the service which is why changes in the above file do not survive a reboot.  For performance experiments you need an additional file alongside the large, medium and small files above... you need 

/var/storage/pgsql/cfg/postgresql.conf.fixed

If present - then it will be selected in preference to the original three.  Therefore this is where to try optimisation experiments.  Then the output of the diff command - something like 

diff postgresql.conf.large postgresql.conf.fixed

tells me exactly what has been changed.


>>>>>>>>>>>>>>>>>>>>>>
My performance is much improved now.  Initial display of web reports down from 30 seconds to 3 seconds.  Also output of 

vmstat 5 

shows waiting for IO (WA column) is way down.  Reporting is now a servant of the firewall system and not its master:

procs -----------memory---------- ---swap-- -----io---- -system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0 940772  51380  20392 5336480    2    1     6    17   10    1 17  2 72 10  0
 1  0 943188  55924  18432 5285372    0  484     3  6774 3458 5481 22  1 70  7  0
 0  5 944604 276228  14828 5086768   61  287   752 13113 3256 5791 11  1 62 26  0
 

Also I have idle time back.  Woot! Woot! Woot!  (Boats leaving Portsmouth harbour).    


>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Current changes are:

diff postgresql.conf.large postgresql.conf.fixed
6c6
 shared_buffers = 1024MB  (I got this from another article suggesting 512-1500MB)
114c114
 work_mem = 1047MB   (tried smaller because too much work mem can cause swapping!!)
115a116
> effective_cache_size = 1500MB  (60% of available RAM)

based on suggestions from performance optimisation guides.  Not sure if one or all of these are required for the performance gains.  

>>>>>>>>>>>>>>>>>>>
NOTE: This kind of change voids warranty and support if done by end users. 

The supported end-user option for doing the same is to switch off reporting altogether.   

>>>>>>>>>>>>>>>>>>>
*****  Sadly all the above changes made no difference.   *****

After running overnight, CPU utilisation crept back up to 80%, all stuck in IO wait states.   Only diagnosis/option left is dump and rebuild the database - its equivalent of reinstall software using the ISO.   

All begs the rhetorical question of how and why we got here.   

BR A

Hi,

1. you didn't say what version # of ASG/UTM you're using

2. it's likely you have a corrupt reporting database; there are instructions in these forums for how to reset that.

3. other things in Astaro need RAM; the IPS and A/V and proxies need a lot. The articles you are reading are probably for DEDICATED database servers!

Barry

re Barry's #2:

/etc/init.d/postgresql rebuild

This deletes all existing data in Reporting.

Cheers - Bob

Hey Bob & Barry,

Thanks for your messages.  My reading of this board suggests that high CPU load is that the main indicator of pgsql DB corruption on Sophos UTM (still want to call it Astaro).  Off board reading (maintenance - PostgreSQL Database Data File Integrity Check - Database Administrators and Tracking Down Database Corruption With psql | End Point Blog) suggests it might be possible to pg_dump and pg_restore the database and catch corruption to metadata that way.  However, this would be a fools errand as the primary data is logs, and that log data is okay.  

So the diagnosis is the treatment, i.e. if I rebuild the database and the problem goes away, then it must have been a database corruption.   BTW Barry, I have an quad core Intel Xeon X3470 @ 2.93GHz with 8Gig RAM on a SATA disk.  So disk probably could, and should be upgraded to SAS with higher throughput.  More memory would also be good. Serves about 1500 users.   Database size is 8.3Gig, so definitely does not fit in memory.  

A final thought about reporting: There are only three database configurations; small, medium and large for (2, 4 and 8Gig memory configs respectively).   However given the many to many relationships of relational data of just for instance the user and the url, I would expect the database to grow by at least the square of usage.   So all the biggest changes in database size and performance requirements will be in the larger systems.   Non root tools to monitor size, throughput and cache hit ratio would be especially useful addition - the database equivalent of dumping a routing table in advanced tools.    

In three months when the database has repopulated I can tell you if the rebuild worked w.r.t. performance.   

Thanks again for this thread.

P.S. IMO, Postgresql should live on a ZFS filesystem to ensure disk data integrity.   See most interesting presentation http://hub.opensolaris.org/bin/download/Community+Group+zfs/docs/zfslast.pdf


>>>>>>>>>>>>>>>>>>>>>>>
Following rebuild of database database size has dropped from 8.3Gig, to 12Meg as shown by 

du -h /var/log/reporting/pgsql

However it is growing at 6M per busy hour.   So assuming 10 busy hours per day, 5 days per week, then in three months it should be about 4 Gig.... hmmm.   

Performance as shown by

vmstat 5

shows system very happy on performance side.  Little IO wait.  Idle time back in 90% range.  Good. 

procs -----------memory---------- ---swap-- -----io---- -system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0 1002920 2669720  71804 2271732    0    0     0   384 4594 6555  7  1 89  2  0
 1  0 1002920 2675748  71828 2262716    0    0     0   485 5040 7203 11  3 86  1  0
 0  0 1002920 2676220  71852 2264824    0    0     0   411 3391 6723  6  1 91  1  0
 0  0 1002912 2677316  71936 2264924    0    0     8   229 3321 6190  3  1 94  2  0

Adrien, have you considered adding another quad-core CPU to your device?  I'm thinking that, for 1500 users, a single one might be too little.  If you're not having a lot of swapping during the day, then RAM probably is good.

Cheers - Bob

Hi, if the database is getting too big and/or too slow (aside from due to corruption),

1. you can reduce the reporting history lengths

2. talk to your reseller and/or Sophos support


Barry