Seems my Postgres 8.4 installation only allows four simultaneous queries - any queries above and beyond seem to be queued until the four previous are done.
This is regardless of which computer and/or user the queries come from.
All of my Postgres conf files are default. How can I further troubleshoot this or how can I remove this limitation?
* Dedicated DB server hosting only the DB discussed in this post * The queries run quickly, sub-second.
* They are all select queries, in many cases on pulling a single unsigned integer field from a single table, as an example. The largest table may have only a couple thousand records.
* Ubuntu 10 - Linux server 2.6.32-38-server #83-Ubuntu SMP Wed Jan 4 11:26:59 UTC 2012 x86_64 GNU/Linux
* postgresql.conf max_connections = 100
* server% grep -c proc /proc/cpuinfo ... 24
* Nothing in PG's log except for a bunch of fatal ident auth errors and a few entries for when I ran explain analyze
Output from select datname, query_start, current_query, waiting from pg_stat_activity...
datname query_start current_query waiting dev_db 2012-08-23 11:02:46.873037-04 SELECT * FROM events WHERE id='2878' FALSE template1 2012-08-23 11:02:46.873469-04 select datname,query_start,current_query,waiting from pg_stat_activity FALSE
Dev_db is the one in question. I've run the query against pg_stat_activity a number of times and sometimes will see IDLE in the current_query field for one of the items in the result. I don't ever seem to see a waiting of TRUE.
Output from select datname, backend_start, query_start, current_query, waiting from pg_stat_activity...
datname backend_start query_start current_query waiting dev_db 2012-08-23 11:20:40.256786-04 2012-08-23 11:21:55.530391-04 SELECT * FROM events WHERE id='6353' FALSE dev_db 2012-08-23 11:20:58.561484-04 2012-08-23 11:21:55.531009-04 SELECT * FROM events WHERE id='10497' FALSE template1 2012-08-23 11:21:55.477188-04 2012-08-23 11:21:55.530976-04 select datname, backend_start, query_start, current_query, waiting from pg_stat_activity FALSE
What stands out to me is the time difference between backend_start and query_start. The events table has 13300 rows, 6 fields (three int and three text - the text values are pretty small). I realize this has no bearing on the time diff I pointed out but figured somebody would ask at some point.