Re: Huge number of INSERTs - Mailing list pgsql-general

On 11/17/2011 04:44 PM, Phoenix Kiula wrote:
> Hi. I have a massive traffic website.
"Massive" = what, exactly?
> I keep getting "FATAL: Sorry, too many clients already" problems.
>
> It's a Quad core machine with dual servers, 4 SCSI disks with RAID 10,
> with RAM of 8GB.
Database only? Or is it also your webserver?
> 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.
>
> My conf file is below. My vmstat + top are below too.
>
> What else can I do?
Provide more info.

What version of PostgreSQL? What OS? What OS tuning, if any, have you
done? (Have you increased readahead? Changed swappiness, turned off
atime on your mounts, made syslogging asynchronous, etc?). Does your
RAID have battery-backed cache? What are the cache settings?

What is the nature of the queries? Single record inserts or bulk? Same
for the selects. Have you run analyze on them and optimized the queries?
What is the typical duration of your queries? Are lots of queries
duplicated (caching candidates)? What is the size of your database? Do
you have any bandwidth bottleneck to the Internet?

Is this your database server only or is it running web and/or other
processes? How long does a typical web-request take to handle?

At first blush, and shooting in the dark, I'll guess there are lots of
things you can do. Your shared_buffers seems a bit low - a rough
starting point would be closer to 25% of your available RAM.

You are a prime candidate for using a connection pooler. I have had good
luck with pgbouncer but there are others.

If you have lots of repeated queries, you could benefit from memcached
or similar.

If your typical web request involves a database hit, there is not really
a benefit to having so many web processes that you exhaust your database
connections. At least until you fix the underlying issues, you might
want to decrease the maximum number of allowed web connections. (If you
server lots of static content, you may want to adjust your process count
accordingly).

Note: bandwidth bottlenecks can screw everything up. Your web processes
stay alive dribbling the data to the client and, even though they don't
have much work to do, they are still holding database connections, using
memory, etc. Such cases can often benefit from a reverse proxy.

Provide more data and we can provide more assistance.

Cheers,
Steve