current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

Looking at the postgres server log, I see that the exact same query on the same postgres server takes much longer (about 10x longer) when invoked from a Linux client or from a Windows client.

The queries come from a Django application running on a Linux machine with 4GB RAM and on a Windows machine with 8GB RAM. Both pyhon environments have psycopg2 library version 2.4.4 to send requests to the same postgres server.

Below are the postgres server logs

The windows query (with time):

2013-06-11 12:12:19 EEST [unknown] 10.1.3.152(56895) mferreiraLOG:  duration: 3207.195 ms  statement: SELECT "autotests_tracerperformance"."id", "autotests_tracerperformance"."date", "autotests_tracerperformance"."video_id", "autotests_tracerperformance"."revision_id", "autotests_tracerperformance"."computer_id", "autotests_tracerperformance"."probe", "autotests_tracerperformance"."time_tostart", "autotests_tracerperformance"."hang_atstart", "autotests_tracerperformance"."time_tohang", "autotests_tracerperformance"."hang", "autotests_tracerperformance"."crash", "autotests_tracerperformance"."stacktrace", "autotests_tracerperformance"."framemax", "autotests_tracerperformance"."maxtime", "autotests_tracerperformance"."avgtime" FROM "autotests_tracerperformance" INNER JOIN "revisions" ON ("autotests_tracerperformance"."revision_id" = "revisions"."id") WHERE ("autotests_tracerperformance"."computer_id" = 61  AND "revisions"."repo" = 'Trunk' )

The linux query (much longer):

2013-06-11 12:12:56 EEST [unknown] 10.1.3.154(35325) mferreiraLOG:  duration: 22191.773 ms  statement: SELECT "autotests_tracerperformance"."id", "autotests_tracerperformance"."date", "autotests_tracerperformance"."video_id", "autotests_tracerperformance"."revision_id", "autotests_tracerperformance"."computer_id", "autotests_tracerperformance"."probe", "autotests_tracerperformance"."time_tostart", "autotests_tracerperformance"."hang_atstart", "autotests_tracerperformance"."time_tohang", "autotests_tracerperformance"."hang", "autotests_tracerperformance"."crash", "autotests_tracerperformance"."stacktrace", "autotests_tracerperformance"."framemax", "autotests_tracerperformance"."maxtime", "autotests_tracerperformance"."avgtime" FROM "autotests_tracerperformance" INNER JOIN "revisions" ON ("autotests_tracerperformance"."revision_id" = "revisions"."id") WHERE ("autotests_tracerperformance"."computer_id" = 61  AND "revisions"."repo" = 'Trunk' )

executing straight from psql (the fastest):

2013-06-11 12:19:06 EEST psql [local] mferreiraLOG:  duration: 1332.902 ms  statement: SELECT "autotests_tracerperformance"."id", "autotests_tracerperformance"."date", "autotests_tracerperformance"."video_id", "autotests_tracerperformance"."revision_id", "autotests_tracerperformance"."computer_id", "autotests_tracerperformance"."probe", "autotests_tracerperformance"."time_tostart", "autotests_tracerperformance"."hang_atstart", "autotests_tracerperformance"."time_tohang", "autotests_tracerperformance"."hang", "autotests_tracerperformance"."crash", "autotests_tracerperformance"."stacktrace", "autotests_tracerperformance"."framemax", "autotests_tracerperformance"."maxtime", "autotests_tracerperformance"."avgtime" FROM "autotests_tracerperformance" INNER JOIN "revisions" ON ("autotests_tracerperformance"."revision_id" = "revisions"."id") WHERE ("autotests_tracerperformance"."computer_id" = 61  AND "revisions"."repo" = 'Trunk' );

Other queries which do not need to load so many items from the database are performing almost the same.

Why so big time differences between clients for this query?

Note: Transmission times are not relevant, since all machines are in the same intranet. Also, the slower times are seen when the client request comes from the same Linux machine where the postgresql server is running.

Note2: Psycopg2 was installed differently in Windows and Linux. Whereas in Windows I installed it from a pre-packaged binary, in Linux I ran 'pip install psycopg2' which relies on a postgresql installation available on the system. Could this result in different values for parameters affecting performance on the client side (e.g. 'work_mem' parameter) ?

share|improve this question
    
Just a shot in the dark: Maybe it's a PostgreSQL internal caching issue? Did you try to submit the SELECT statement multiple times from Linux, and also multiple times from Windows? I would imagine that the average time should be the same then. –  mawimawi Jun 11 '13 at 9:39
    
to mawimawi: No these times are consistent, I started debugging this as my production django app was much slower than in the development (windows) machine. The times are the same if you run multiple times. –  mpaf Jun 11 '13 at 9:46
1  
It could be related to network lags. Especially if you're transmitting large amounts of data from a server to the next. Log the query at the server level, to see how much time is actually spent in Postgres. Oh, it could also be execution time differences in python, too, e.g. creating objects etc. –  Denis Jun 11 '13 at 9:47
    
To Dennis: not really, I tested running the django app in the same machine where postgresql server is - it takes the same 10x more than from a django app running on a different machine with Windows client. It seems to be related to client configuration. All servers are part of the same local intranet, shouldn't really matter - and I think the postgresql log shows time running in the postgres server and doesn't take transmission of data into account (?) –  mpaf Jun 11 '13 at 9:49
    
I agree with Denis, it looks like network issue - or Linux station network card issue. –  Pavel Stehule Jun 11 '13 at 9:49

1 Answer 1

up vote 3 down vote accepted

You may want to check if the slow client does SSL encryption or not. It happens by default when it's set up on the server and the client has been compiled with SSL support.

For queries that retrieve large amounts of data, the time difference is significant. Also some Linux distributions like Debian/Ubuntu have SSL on by default, even for TCP connections through localhost.

As an example, here's the time difference for a query retrieving 1,5M rows weighing a total of 64Mbytes, with a warm cache.

Without encryption:

$ psql "host=localhost dbname=mlists sslmode=disable"
Password: 
psql (9.1.7, server 9.1.9)
Type "help" for help.

mlists=> \timing
Timing is on.
mlists=> \o /dev/null
mlists=> select subject from mail;
Time: 1672.258 ms

With encryption:

$ psql "host=localhost dbname=mlists"
Password: 
psql (9.1.7, server 9.1.9)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

mlists=> \o /dev/null
mlists=> \timing
Timing is on.
mlists=> select subject from mail;
Time: 7017.935 ms

To turn it off globally, one might set SSL=off in postgresql.conf.

To turn it off for specific ranges of client addresses, add entries in pg_hba.conf with hostnossl in the first field before the more generic host entries.

To turn if off client-side, it depends on how the driver exposes the sslmode connection parameter. If it doesn't, the PGSSLMODE environment variable may be used if the driver is implemented on top of libpq.

As for connections through Unix domain sockets (local), SSL is never used with them.

share|improve this answer
    
Great answer! I tested and you were right, SSL was impacting the performance. Both for Windows and Linux actually, when I disabled SSL in postgresql.conf, Windows time went down from 3s to 1.7s and Linux time down from 22s to 1.5s! So it seems both were going through SSL but Linux more affected by it? Thanks a lot for your answer, good insight. –  mpaf Jun 12 '13 at 8:49
    
@mpaf: glad it helps. As for Linux being more affected, it's hard to explain without more investigation. It might be that a stronger cipher gets selected on Linux, but that's just speculation. –  Daniel Vérité Jun 12 '13 at 19:08

Your Answer

 
discard

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

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