Re: High CPU load on Postgres Server during Peak times!!!!

Hi

Thanks a lot for the reply.

*I see you are on a pretty old version of pg. Are you vacuuming regularly?*

Yes, Vaccuuming is done every day morning at 06 am

It is running perfectly fine.

*
*

*If you run a 'ps ax|grep post' do you see anything that says 'idle in
transaction'? (I hope that old of version will show it. my processes show up
as postgres not postmaster)*

Lots of requests shows as 'idle in transaction'.

Currently i am restarting the database using a cron job every 30 minutes
during offpeak time

and every 15 minutes during the peak time.

The top looks like you are cpu bound.

*Have you tried enabling logging slow queries? (again, I hope your version
supports that) It could be you have a query or two that are not using
indexes, and slowing everything down.*

Exactly right, thanks for the tip.

I indexed few tables frequently accessed which are not indexed. After
indexing the load has come down to 50 % during Peak time its between 10 and
20 and during offpeak its between 4 and 8 .

The PowerPC cpu is having some virtual layer that is shown in the Steal
value.

*Its weird, you have 6.1% idle and 3.0% waiting for disk and yet you have a
load of 13. Load usually means somebody is waiting for something. But you
have a little cpu idle time... and you have very low disk waits... you are
using very little swap. hum... odd...*

As per the concurrency of 300 to 400 users, the following parameters are
changed in

postgresql conf based on the calculation provided in the postgresql
documentation.

Max connections = 1800 ( Too much open connections will result in unwanted
memory wastage)

Shared Buffers = 375 000 ( 375000 * 8 * 1024 /100 = 3072 MB ) # proposed
value is 1/4 the actual memory

Effective Cache Size = 266000 ( 266000 * 8 * 1024 /100 = 2179 MB ) #
proposed value is 1/3 memory after OS Allocation

work_mem = 3000 ( 3000 * max connections * 1024 = 3000 * 1800 * 1024 = 5529
MB ( this is the working memory for postgres) )

max_fsm_pages = 20000 ( This has to be analyzed and can be increased to
40000, this can be done after one or two day observation)

Postgresql.conf

---------------

hba_file = '/var/lib/pgsql/data/pg_hba.conf'

listen_addresses = '*'

port = 5432

max_connections = 1800

shared_buffers = 300000

max_fsm_relations = 1000

effective_cache_size = 200000

log_destination = 'stderr'

redirect_stderr = on

log_rotation_age = 0

log_rotation_size = 10240

silent_mode = onlog_line_prefix = '%t %d %u '

autovacuum = on

datestyle = 'iso, dmy'

lc_messages = 'en_US.UTF-8'

lc_monetary = 'en_US.UTF-8'

lc_numeric = 'en_US.UTF-8'

lc_time = 'en_US.UTF-8'

Any modifications i have to do in this values ?

Regds

Shiva Raman .