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

On Fri, Nov 18, 2011 at 9:25 AM, Steve Crawford
<scrawford@pinpointresearch.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