Re: Huge number of INSERTs

On Fri, Nov 18, 2011 at 9:25 AM, Steve Crawford
<scrawford(at)pinpointresearch(dot)com> wrote:

>> Database only? Or is it also your webserver?

It's my webserver and DB. Webserver is nginx, proxying all PHP
requests to apache in the backend.

> 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?

PG 9.0.5

CentOS 5 64 bit

OS tuning - lots of it since the beginning of time. What specifically
would you like to know? Please let me know and I can share info. Like
SHM Max and Min variables type of things?

RAID has the 3Com battery backed cache, yes. Not reporting any errors.

> 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?

Simple INSERTs. Into a table with 6 columns. Column 1 is a primary
key, column 5 is a date. There are two indexes on this table, on the
pkey (col1) and one on the date (col5).

SELECTs are simple straight selects, based on pkey with limit 1. No
joins, no sorting.

> What is
> the typical duration of your queries? Are lots of queries duplicated
> (caching candidates)?

The bulk of the big SELECTs are in "memcached". Much faster than PG.

It's INSERTs I don't know what to do with. Memcached is not a good
solution for INSERTs, which do need to go into a proper DB.

> What is the size of your database? Do you have any
> bandwidth bottleneck to the Internet?

Full DB: 32GB
The big table referenced above: 28 GB

It's inserts into this one that are taking time.

> 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?

How can I measure the time taken per web request? Nginx is super fast,
based on apache bench. Apache -- how do I test it? Don't want to do
fake inserts. With selects, apache bench uses memcached instead..

> 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.

If I make shared_buffers too high, (2GB....25% of my 8GB RAM), this
brings the server to its knees instantly. Probably because I have
apache, nginx, memcached running on the same server. Nginx and
memcached are negligible in terms of memory consumption.

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

Will pgbouncer or pgpool help with INSERTs?

> 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.

In addition to nginx proxying to apache, I am using CloudFlare. Is
this a problem?

Many thanks for the informative seeking of information. Hope the above
details shed more light?

I've currently disabled any INSERT functions on my website...but even
with disabled INSERTs and only SELECTs alive, I still see the "psql:
FATAL: sorry, too many clients already" message.

Btw, I don't see any PG logs. What could be the problem? The config
says that it should store it in the directory "pg_log", but this
directory is empty.

Also, here's the output of "vmstat 5 10"

> vmstat 5 10
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
11 3 17672 44860 24084 6559348 0 0 147 275 17 63 64
26 9 1 0
14 3 14376 48820 24208 6555968 438 0 24374 1287 1529 56176 73
26 1 0 0
13 2 14112 47320 24344 6555916 10 2 27350 1219 1523 57979 72
27 1 0 0
20 2 14100 46672 24468 6553420 2 3 28473 1172 1499 59492 71
27 1 0 0
17 3 10400 46284 24524 6548520 730 1 22237 1164 1482 59761 68
31 1 0 0
18 2 7984 45708 24712 6552308 478 0 26966 1164 1487 58218 69
30 1 0 0
12 2 7980 47636 24816 6549020 2 1 25210 1134 1486 57972 71
27 1 1 0
18 1 7924 44300 25108 6548836 1 0 25918 1310 1515 60067 70
28 1 1 0
18 2 7812 45444 25288 6543668 26 0 26474 1326 1465 62633 70
29 1 0 0
22 2 7800 46852 25488 6542360 0 0 25620 1258 1510 63181 69
29 1 1 0

> vmstat 5 10
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
10 4 7712 46420 27416 6449628 0 0 167 275 18 114 64
26 9 1 0
18 2 7704 47196 27580 6448252 4 0 22546 1146 1507 55693 68
26 3 4 0
20 2 7724 47616 27628 6444084 3 1 25419 1114 1424 58069 72
27 1 0 0
15 2 7840 47240 27852 6443056 0 0 22962 1145 2079 59501 71
27 1 1 0
17 3 7852 47400 28084 6442840 1 3 21262 1189 2038 58908 69
27 2 2 0
13 2 7864 47024 28220 6438784 0 2 21131 1030 1716 57518 69
30 1 0 0
18 0 7868 45948 28496 6442860 2 0 23282 1261 1479 57482 71
28 1 0 0
11 2 7904 45784 28708 6442748 0 1 25155 1239 1468 58439 72
27 1 0 0
13 2 7988 44616 28856 6443992 0 0 23411 1248 1435 58626 72
27 1 0 0
26 2 8024 44364 28848 6443120 0 0 22922 1229 1484 59022 71
27 1 0 0