6

Seeing query performance which is not satisfactory on our Python application, which runs several processes that use SQLAlchemy core to access a PostgreSQL 9.2 database. We may have around 100 - 200 or more concurrent processes executing queries against the database.

We have no connection pooling systems like pgpoolII or pgbounce currently installed.

I have tested a query which can take in the order of 1-10 seconds from our application, which when run over psql will take at most 20 ms.

We believe this issue is related to concurrent connections particularly because of the behavior described above and because we see these performance hits when accessing different tables in the system. However, we are not sure of this and could very readily accept alternate suggestions or solutions.

What is the usual way of handling concurrent connections from multiple processes against a single database server, in a way that will enable queries to execute quickly?

Improve this question
6
  • 1
    I would be hesitant to assume that the number of connections is the issue without any evidence. It sounds like this is a hunch, and that's fine for a starting point of investigation, but it seems like you have already concluded that your hunch is correct without bothering to check. Do you see this same behavior when you reduce the number of processes? Do you know for a fact that this time is being spent in the PostgreSQL server and not in your application? Have you collected I/O statistics from the server process? (Maybe it's just a slow disk!)
    – cdhowie
    Commented Mar 4, 2013 at 22:10
  • 3
    100 to 200 concurrent connections is quite a lot and a pooler will probably help here. Very often coping with concurrency problems is done by reducing the number of concurrent processes.
    – user1822
    Commented Mar 4, 2013 at 22:21
  • 2
    If the same query (???) through your application is 100 times slower, there is something wrong with your application. Is there a network in between? Is your timing measurement the "wall time" (with a stopwatch) ? Did you check the execution time in the postgres logs ? Is the 20ms measurement done in isolation, or simultaniously with the app-clients?
    – wildplasser
    Commented Mar 4, 2013 at 22:21
  • I'd recommend doing some profiling to determine if there's any in-Python bottlenecks as well, see stackoverflow.com/questions/1171166/…
    – zzzeek
    Commented Mar 7, 2013 at 17:47
  • @zzzeek Thanks for this suggestion. It definitely looks like something we should implement. Thanks!
    – Juan Carlos Coto
    Commented Mar 13, 2013 at 17:17

1 Answer 1

Reset to default
3

You sort of answer your own question when you say you have no pooling but...

This is not an answer out of the box, with all client/db stuff you may need to do some work to determine exactly what is amiss

backup postgresql.conf changing

log_min_duration_statement to 0 
log_destination = 'csvlog'              # Valid values are combinations of      
logging_collector = on                # Enable capturing of stderr and csvlog 
log_directory = 'pg_log'                # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,        
debug_print_parse = on
debug_print_rewritten = on
debug_print_plan output = on
log_min_messages = info (debug1 for all server versions prior to 8.4)

Stop and restart your database server ( reload may not pick up the changes ) Reproduce your tests ensuring that the server time and client times match and that you record the start times etc.

copy the log file off an import into editor of your choice (excel or another spreadsheet can be useful for getting advance manipulation for sql & plans etc)

now examine the timings from the server side and note:

is the sql reported on the server the same in each case

if the same you should have the same timings

is the client generating a cursor rather than passing sql

is the query arriving on the server when you believe it should do

is one driver doing a lot of casting/converting between character sets or implicit converting of other types such as dates or timestamps.

and so on

The plan data will be included for completeness, this may inform if there are gross differences in the SQL submitted by the clients.

Improve this answer
1
  • Thanks very much for your answer. I'll look into all of this. Thanks!
    – Juan Carlos Coto
    Commented Apr 24, 2013 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.