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 .
