Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

EDIT 1
* 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

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

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

share|improve this question
    
Hmm.. are these queries long running? How many CPU cores do you have and is this a dedicated database? Also, can you post your postgresql.conf? –  Chida Aug 22 '12 at 19:56
    
What OS are you running (be specific)? How many Postgres connections do you allow (look in postgresql.conf)? What kind of queries are we talking about -- SELECTs? INSERTs? UPDATEs? Is there locking involved? Is there anything in your postgres log file? Detail is good - Useful detail is better... –  voretaq7 Aug 22 '12 at 20:44
    
@Chida What specific items out of postgresql.conf would you like to see? That file is around 500 lines out of the box -- no sense pasting the entire thing unless there's something that can't be answered by looking at specific excerpted bits. –  voretaq7 Aug 22 '12 at 20:46
    
Can you tell the result of select datname,query_start,current_query,waiting from pg_stat_activity when the problem occurs? –  Daniel Vérité Aug 23 '12 at 8:34
1  
If queries were queued, they should show up in pg_stat_activity, possibly with the waiting flag to true if they're stuck on a lock. that's clearly not the case here. Back to the 1st sentence of the question: what are you looking at to conclude that something limits to 4 concurrent queries? –  Daniel Vérité Aug 23 '12 at 16:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.