Postgres process - high CPU load

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

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