Hi, there's a pretty wiki page about tuning PostgreSQL databases: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server On 18 Listopad 2011, 1:44, Phoenix Kiula wrote: > Hi. I have a massive traffic website. > > I keep getting "FATAL: Sorry, too many clients already" problems. That has nothing to do with the inserts, it means the number of connection requests exceeds the max_connections. You've set it to 350, and that seems too high - the processes are going to struggle for resources (CPU, I/O and memory) and the scheduling gets expensive too. A good starting point is usually 2*(number of cores + number of drives) which is 16 or 24 (not sure what a "dual server" is - probably dual CPU). You may increase that if the database more or less fits into memory (so less I/O is needed). But go step by step - by 10 connections or something like that. The problem is that each connection can allocate memory (work_mem), and if you have too many connections doing that at the same time you'll get OOM or a swapping system (and that's not what you want). And you should give the sessions enough memory, because otherwise they're going to do on-disk sort. So you have to keep in mind these "rules" (1) give each session enough memory to perform the operations in RAM (enough work_mem to sort in memory etc.), but not more (2) don't use too many connections - watch the I/O utilization and don't overload it (you won't get higher throughput, just higher latencies) BTW the same rule holds for the number of Apache workers - how many are you using? Is that on the same machine or on a dedicated one? The fact that you're receiving "too many clients" suggests that you have MaxClients higher than 350. Have you actually tested this to see if it gives better performance than 50? If the clients actually need to connect / query the database, there's probably no point in having more than max_connections of them. > It's a Quad core machine with dual servers, 4 SCSI disks with RAID 10, > with RAM of 8GB. > > Server is Nginx backed by Apache for the php. > > Postgresql just has to do about 1000 SELECTs a minute, and about 200 > INSERTs a minute. Maybe 10-20 UPDATEs. That's completely information-less description. Those SELECTs may be a simple "fetch by PK" queries or complex queries aggregating data from 20 tables. So the fact that you need to execute 1000 of them is useless. The same for UPDATEs and INSERTs. Post an example of the queries with EXPLAIN ANALYZE for each of them (use explain.depesz.com to post it). > My conf file is below. My vmstat + top are below too. A static (single line) of vmstat is not very useful - we need a few lines of "vmstat 1" (say 30) collected when the application is in use. > What else can I do? 1) Decrease the number of connections to a reasonable value. 2) Use a connection pooler. You may also use persistent connections in PHP too, but you have to set MaxClients in apache config to the same value (otherwise you'll get "too many clients"). The connection pooler can handle this for you - it will wait until a connection is available. And the most important thing - prepare a simple stress script (a few HTTP requests, performed by a typical client) and use it to stress test the application. Start with low max_connections / MaxClients (say 20), increase them gradually and watch the performance (throughput). The usual behavior is that at the beginning the throughput scales linearly (2 clients give you 2x the throughput of 1 client, with the same latency). Then this growth stops and the throughput does not grow anymore - adding more clients just increases the latency. Then the throughput usually goes down. Tomas -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
