I have this server, Dual Intel Xeon 2.83GHz w 4gb ram. My shared buffers were set to 1756 MB.
We have a high traffic e-commerce site, yesterday like 80000 uniques. We cache most of our dynamic pages to disk so its wierd we are experience really slow response times with postgresql.
Usually the number of simultaneous connections i monitor is around 8 to 10, but yesterday night, maybe there was a spike in the traffic, it was going up to 100, so the pages were taking like 15 seconds to render.
The wierd thing is that server is fine, i mean the load on the server itself is not that high, but postgresql for some reason was slowing bad.
We upgraded the ram to 8 GB ram today, replicated some of the read-only tables to another database to split the load.
Is there anything i can check to see whats causing this really? Im checking pg_stat_activity, all processes are idle, yet the timeout is set low on the configuration, i dont know why they sit there.
With 8 GB ram, im thinking of incresing the shared_buffer to 3. Whats a good value for work_mem and temp memory?
Any advise appreciated, server running on RHEL 2.6.18-128.7.1.el5 x86_64