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 .