Обсуждение: Slow query through ODBC
Hi all !
I have a query that runs very fast when executed through pgAdmin
(~2 seconds), but very slowly through the ODBC driver (~30
seconds). Both queries are executed from the same workstation on
the same server.
My workstation is a WinXP Pro, and I use the latest psqlODBC
driver available as an MSI package (version 8.01.0102).
The server is a PostGreSQL 8.1.2, running on a Win2000 server.
The query is very simple, it returns about 1500 lines, made of
only one text field, but this field can be very large (it is the
WKT representation of a spatial object from PostGIS).
I am connecting to the server in a VBA project with a simple
connectionstring with only default options (I have not found a
document explaining how to fine tune the odbc driver).
My connectionstring is :
"DRIVER={PostgreSQL
Unicode};SERVER=myserver;PORT=5432;DATABASE=mydatabase;UID=myuser;PWD=mypass"
I assume that the problem is with the very large field size that
is returned, but what should I change in my connectionstring to
make this query run faster ?
Thanks for your help !
--
Arnaud
Since it works with different speeds in different clients, it *probably* isn't on the server side. I'm far from sure on that though, there may be other paramters that are implicitly changed when ODBC is used. It's interesting to note that your problem is with a single large field. If you query for approximatly the same amount of data *but in several small fields*, do you get the same behaviour? Or does it happen only with large fields? > I posted a TCP/IP dump of the dialog between my workstation > and the postgresql server to a TCP/IP newsgroup. > I fixed the problem by changing a parameter in my TCP/IP > configuration (on my workstation, not on the server). > I have set the TcpAckFrequency key to 1, as suggested by > someone on the comp.protocols.tcp-ip NG. The procedure is > described in : > http://support.microsoft.com/?scid=kb%3Ben-us%3B328890&x=15&y=10 Interesting - I wouldn't have expected that to make a difference. <snip> > > The short answer: > > > > "TCP_NODELAY" should be enabled on the TCP connection to > the database > > on both the client and server side of the connection. > > (This is also known as "disabling the Nagle Algorithm"). > > (Based upon your capture I can almost guarantee that TCP_NODELAY is > > *not* enabled on your Database TCP connection on the server side). > > > > The use of TCP_NODELAY is a database option for a different > database > > server with which I'm familiar. I suggest you consult your > DBA with respect to PostgreSQL. > > (Also Google "database tcp_nodelay" & etc for information). We enable TCP_NODELAY on both server and client, when available. And it is available - I've double-checked and that code is indeed compiled into both libpq and the server on 8.1 at least. > > I have an area for you to explore: There's a method of socket > > programming in windows called "io completion ports" (or somesuch). > > Rather than streaming data into the socket UNIX style, the > application > > code allocates a buffer somewhere, fills it and then tells > the stack: > > "The buffer is here, please send it, then let me know when > I can have > > the buffer back." > > > > Some see an advantage to this type of coding because it > eliminates a > > copy of data from the application memory area into stack > memory area. We don't use this. We do, however, buffer it in an application side driver and then send it off with a single call to send(). On the server, that is. Perhaps it can have a similar effect. But I don't see how this can make a difference between pgadmin and odbc and psql, since they all use the same code! Might be interesting to write up a small test program that uses just libpq and see what is required to repro the problem there. //Magnus
Magnus Hagander a écrit : > Since it works with different speeds in different clients, it *probably* > isn't on the server side. I'm far from sure on that though, there may be > other paramters that are implicitly changed when ODBC is used. Yes, that was my first impression also. But the fact that the server waits for an ACK from the client before it goes on sending packet is quite strange, isn't it ? > It's interesting to note that your problem is with a single large field. > If you query for approximatly the same amount of data *but in several > small fields*, do you get the same behaviour? Or does it happen only > with large fields? I have splitted the data into 4 fields. The largest field was ~23.000 characters long, so I made 4 fields containing. The first one contains the first 6000, the second one from 6001 to 12000, etc... The query took about half the time to run ! > We enable TCP_NODELAY on both server and client, when available. And it > is available - I've double-checked and that code is indeed compiled into > both libpq and the server on 8.1 at least. I run 8.1.2, so I assume that it is enabled on my workstation and on the server. > But I don't see how this can make a difference between pgadmin and odbc > and psql, since they all use the same code! Yes, I really don't understand either ! But even though all use libpq.dll, they might not handle the data the same way. Mayboe ODBC needs to check for data type, field length, etc... and the other frontends do not need this kind of information ? Regards -- Arnaud
> Magnus Hagander a écrit : > > Since it works with different speeds in different clients, it > > *probably* isn't on the server side. I'm far from sure on > that though, > > there may be other paramters that are implicitly changed > when ODBC is used. > > Yes, that was my first impression also. But the fact that the > server waits for an ACK from the client before it goes on > sending packet is quite strange, isn't it ? Yes. Most definitly. > > It's interesting to note that your problem is with a single > large field. > > If you query for approximatly the same amount of data *but > in several > > small fields*, do you get the same behaviour? Or does it > happen only > > with large fields? > > I have splitted the data into 4 fields. > The largest field was ~23.000 characters long, so I made 4 > fields containing. The first one contains the first 6000, the > second one from 6001 to 12000, etc... > The query took about half the time to run ! Interesting. Did it make any difference to the timings in psql and/or pgadmin? > > We enable TCP_NODELAY on both server and client, when > available. And > > it is available - I've double-checked and that code is > indeed compiled > > into both libpq and the server on 8.1 at least. > > I run 8.1.2, so I assume that it is enabled on my workstation > and on the server. Yup. > > But I don't see how this can make a difference between pgadmin and > > odbc and psql, since they all use the same code! > > Yes, I really don't understand either ! > But even though all use libpq.dll, they might not handle the > data the same way. Mayboe ODBC needs to check for data type, > field length, etc... and the other frontends do not need this > kind of information ? Hm. That's an interesting observation. When you do a query with ODBC, does it do any "extra queries" automatically on thesystem tables? Enable statement logging on the server and see if something weird shows up. //Magnus
>> I have splitted the data into 4 fields. >> The largest field was ~23.000 characters long, so I made 4 >> fields containing. The first one contains the first 6000, the >> second one from 6001 to 12000, etc... >> The query took about half the time to run ! > > Interesting. Did it make any difference to the timings in psql and/or pgadmin? No, not at all. Actually, it even runs a tiny bit slower in pgAdmin (2.05 sec vs 1.95 for the "one large field" version). >> Yes, I really don't understand either ! >> But even though all use libpq.dll, they might not handle the >> data the same way. Mayboe ODBC needs to check for data type, >> field length, etc... and the other frontends do not need this >> kind of information ? > > Hm. That's an interesting observation. When you do a query with ODBC, does it do any "extra queries" automatically on thesystem tables? Enable statement logging on the server and see if something weird shows up. No, no query on the system tables. I am not very familiar with the log though, maybe Ludek could tell us more about that ? Regards -- Arnaud
> >>Yes, I really don't understand either ! > >>But even though all use libpq.dll, they might not handle the > >>data the same way. Mayboe ODBC needs to check for data type, > >>field length, etc... and the other frontends do not need this > >>kind of information ? > > > >Hm. That's an interesting observation. When you do a query with ODBC, does > >it do any "extra queries" automatically on the system tables? Enable > >statement logging on the server and see if something weird shows up. > > No, no query on the system tables. > I am not very familiar with the log though, maybe Ludek could tell > us more about that ? If I remember it right we measure the time problem in LIBPQ_send_query function in connection.c (I'm not sure with filename). Magnus, could you take a look at the code? Maybe you find the problem. I'm sorry I have no time for it today. BTW we change the code between 08.01.0102 and 08.01.0200 from PQexec to PQsendquery and PQgetresult (I write this from my head so it could be little different names). But I think Arnaud has problem even with 08.01.0102. Regards, Luf
> > >>Yes, I really don't understand either ! > > >>But even though all use libpq.dll, they might not handle the data > > >>the same way. Mayboe ODBC needs to check for data type, field > > >>length, etc... and the other frontends do not need this kind of > > >>information ? > > > > > >Hm. That's an interesting observation. When you do a query > with ODBC, > > >does it do any "extra queries" automatically on the system tables? > > >Enable statement logging on the server and see if > something weird shows up. > > > > No, no query on the system tables. > > I am not very familiar with the log though, maybe Ludek > could tell us > > more about that ? > > If I remember it right we measure the time problem in > LIBPQ_send_query function in connection.c (I'm not sure with > filename). > Magnus, could you take a look at the code? Maybe you find the problem. > I'm sorry I have no time for it today. > BTW we change the code between 08.01.0102 and 08.01.0200 from > PQexec to PQsendquery and PQgetresult (I write this from my > head so it could be little different names). But I think > Arnaud has problem even with 08.01.0102. That's very interestnig information. Because that was one thing that was different. Arnaud, any chance you can giev it a try with 8.01.0200? //Magnus
> > If I remember it right we measure the time problem in > > LIBPQ_send_query function in connection.c (I'm not sure with > > filename). > > Magnus, could you take a look at the code? Maybe you find the problem. > > I'm sorry I have no time for it today. > > BTW we change the code between 08.01.0102 and 08.01.0200 from > > PQexec to PQsendquery and PQgetresult (I write this from my > > head so it could be little different names). But I think > > Arnaud has problem even with 08.01.0102. > > That's very interestnig information. Because that was one thing that was > different. > > Arnaud, any chance you can giev it a try with 8.01.0200? Please try both, 08.01.0102 and 08.01.0200. Does it differ? Thanks, Luf
Ludek Finstrle a écrit : >> That's very interestnig information. Because that was one thing that was >> different. >> >> Arnaud, any chance you can giev it a try with 8.01.0200? > > Please try both, 08.01.0102 and 08.01.0200. Does it differ? I just tried both versions and there is absolutely no difference. I'll stick with .0200 of course ! Regards -- Arnaud
Arnaud Lesauvage <thewild@freesurf.fr> writes: > Magnus Hagander a écrit : >> Since it works with different speeds in different clients, it *probably* >> isn't on the server side. I'm far from sure on that though, there may be >> other paramters that are implicitly changed when ODBC is used. > > Yes, that was my first impression also. But the fact that the server > waits for an ACK from the client before it goes on sending packet is > quite strange, isn't it ? I don't think that's what happens. Those are two very different but unfortunately related things. 1) the Nagle algorithm (disabled by TCP_NO_DELAY) is an optimization when sending. It prevents sending many small packets, by waiting a little bit for them to become bigger. The price is a higher latency. 2) the half-ACK frequency is an similar optimization when receiving. It cuts the number of sent acknowledgements by two, by waiting a little bit to receive a 2nd packet, acknowledging the two packets using only one ACK. You can disable it with TcpAckFrequency in the registry. So far, those two have nothing in common. But the thing is, TCP _piggybacks_ acknowledgements packets of data flowing in one direction together with data packets flowing in the opposite direction. So, if you force TCP to send data more frequently with TCP_NO_DELAY, then it will also send ACKs (of the opposite communication) more frequently as a side effect. Once you decided to send a packet, then better put the maximum information in it! Same thing if you force TCP to send ACKs more frequently using TcpAckFrequency: then you'll get some TCP_NO_DELAY effect as a bonus. Once you decided to send an ACK, then better send the staging data in the same packet! So you see that TcpAckFrequency can (in some scenarios) play the role of TCP_NO_DELAY. And vice-versa. That's probably what you experienced.
Marc Herbert a écrit : > Same thing if you force TCP to send ACKs more frequently using > TcpAckFrequency: then you'll get some TCP_NO_DELAY effect as a > bonus. Once you decided to send an ACK, then better send the staging > data in the same packet! OK, but Magnus is affirmative that TCP_NO_DELAY is enabled on both the client and the server. So sending ACKs more frequently should not change the behaviour of the Nagle algorithm. Or am I still misunderstanding something ? Regards -- Arnaud
Arnaud Lesauvage <thewild@freesurf.fr> writes: > OK, but Magnus is affirmative that TCP_NO_DELAY is enabled on both > the client and the server. > So sending ACKs more frequently should not change the behaviour of > the Nagle algorithm. Yeah. The speculations quoted in http://archives.postgresql.org/pgsql-odbc/2006-02/msg00046.php as to the server behavior were completely wrong: we do use NODELAY and we don't have any weird zero-copy algorithm. However, it is true that we push out data in 8K bufferloads, which doesn't match well to typical TCP MTU sizes. I've occasionally wondered whether we should not try to make the bufferload a multiple of the MTU size when using a TCP connection, just so that no "short" packets get sent unnecessarily. (For a known MTU length, it'd be trivial to change the hard-wired buffer size on the server side just to experiment and see if there's any speedup.) None of this explains why odbc is slow when psql isn't, though, especially not if you are using one of the recent odbc releases that relies on libpq. The wire-level behavior certainly ought to be the same for anything using libpq ... regards, tom lane
Tom Lane a écrit : > None of this explains why odbc is slow when psql isn't, though, > especially not if you are using one of the recent odbc releases > that relies on libpq. The wire-level behavior certainly ought > to be the same for anything using libpq ... Yes. I am trying to figure out what the difference is between an ODBC call and a "direct" call, from another application... The issue might be at a higher level, but I have no clue right now... Regards -- Arnaud