pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedODBC driver over network very slow
4+ messages / 4 participants
[nested] [flat]
* ODBC driver over network very slow
@ 2005-06-30 10:28 Milan Sekanina <milan@sde.cz>
2005-06-30 13:48 ` Re: ODBC driver over network very slow Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Milan Sekanina @ 2005-06-30 10:28 UTC (permalink / raw)
To: pgsql-performance
We are running an application that uses psqlodbc driver on Windows XP to
connect to a server and for some reason the download of data from the
server is very slow. We have created a very simple test application that
inserts a larger amount of data into the database and uses a simple
"SELECT * from test" to download it back. The INSERT of 10MB takes about
4 seconds, while the SELECT takes almost 5 minutes (with basically
nothing else running on both the network and the two computers). If we
run the PostgreSQL server on the local machine so that the network is
not used, both actions are very fast.
Do you have any idea what could be the cause of this behavior? Are there
any driver settings/other drivers we should use? We are currently using
psqlodbc version 7.03.02.00, but it seems that other versions we tried
show the same behavior. We have tweaked the various driver settings but
the times remain basically unchanged.
Any ideas or hints are warmly welcome.
regards
Milan
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ODBC driver over network very slow
2005-06-30 10:28 ODBC driver over network very slow Milan Sekanina <milan@sde.cz>
@ 2005-06-30 13:48 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-06-30 13:48 UTC (permalink / raw)
To: Milan Sekanina <milan@sde.cz>; +Cc: pgsql-performance
Milan Sekanina <milan@sde.cz> writes:
> We are running an application that uses psqlodbc driver on Windows XP to
> connect to a server and for some reason the download of data from the
> server is very slow. We have created a very simple test application that
> inserts a larger amount of data into the database and uses a simple
> "SELECT * from test" to download it back. The INSERT of 10MB takes about
> 4 seconds, while the SELECT takes almost 5 minutes (with basically
> nothing else running on both the network and the two computers). If we
> run the PostgreSQL server on the local machine so that the network is
> not used, both actions are very fast.
I seem to recall having seen similar reports not involving ODBC at all.
Try searching the mailing-list archives, but I think the cases we solved
involved getting rid of third-party add-ons to the Windows TCP stack.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ODBC driver over network very slow
@ 2005-06-30 14:10 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 4+ messages in thread
From: Merlin Moncure @ 2005-06-30 14:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance; Milan Sekanina <milan@sde.cz>
> Milan Sekanina <milan@sde.cz> writes:
> > We are running an application that uses psqlodbc driver on Windows
XP to
> > connect to a server and for some reason the download of data from
the
> > server is very slow. We have created a very simple test application
that
> > inserts a larger amount of data into the database and uses a simple
> > "SELECT * from test" to download it back. The INSERT of 10MB takes
about
> > 4 seconds, while the SELECT takes almost 5 minutes (with basically
> > nothing else running on both the network and the two computers). If
we
> > run the PostgreSQL server on the local machine so that the network
is
> > not used, both actions are very fast.
>
> I seem to recall having seen similar reports not involving ODBC at
all.
> Try searching the mailing-list archives, but I think the cases we
solved
> involved getting rid of third-party add-ons to the Windows TCP stack.
IIRC there was a TCP related fix in the odbc driver related to
performance with large buffers. I'd suggest trying a newer odbc driver
first.
Merlin
dave page wrote ([odbc] 500 times slower)
>
> My collegue spent some time to dig the following case and it
> looks like
> Nagle algorithm and delayed ACKs related problem.
> In psqlodbc.h
> #define SOCK_BUFFER_SIZE 4096
>
> I changed that value to 8192 and driver works fine for me.
> I am not sure why this change helps.
Err, no neither am I. Why do you think it's got something to do with
Nagle/delayed ACKs?
The only thing that instantly rings bells for me is that the max size of
a text field is 8190 bytes at present (which really should be increased,
if not removed altogether), which won't fit in the default buffer. But
then, I wouldn't expect to see the performance drop you describe with a
4096 byte buffer, only one much smaller.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ODBC driver over network very slow
@ 2005-06-30 14:47 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 4+ messages in thread
From: Kevin Grittner @ 2005-06-30 14:47 UTC (permalink / raw)
To: merlin.moncure@rcsonline.com; tgl@sss.pgh.pa.us; +Cc: pgsql-performance; milan@sde.cz
I was hesitant to jump in on this because I am new to PostgreSQL and
haven't seen this problem with _it_, but I have seen this with the
Sybase database products. You can configure Sybase to disable the Nagle
algorithm. If you don't, any query which returns rows too big to fit in
their network buffer will be painfully slow. Increasing the buffer size
can help with an individual query, but it just reduces the scope of the
problem. What you really want to do is make sure that TCP_NODELAY is
set for the connection, to disable the Nagle algorithm; it just doesn't
seem to be appropriate for returning query results.
How this issue comes into play in PostgreSQL is beyond my ken, but
hopefully this observation is helpful to someone.
-Kevin
>>> "Merlin Moncure" <merlin.moncure@rcsonline.com> 06/30/05 9:10 AM >>>
> My collegue spent some time to dig the following case and it
> looks like
> Nagle algorithm and delayed ACKs related problem.
> In psqlodbc.h
> #define SOCK_BUFFER_SIZE 4096
>
> I changed that value to 8192 and driver works fine for me.
> I am not sure why this change helps.
Err, no neither am I. Why do you think it's got something to do with
Nagle/delayed ACKs?
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-06-30 14:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-30 10:28 ODBC driver over network very slow Milan Sekanina <milan@sde.cz>
2005-06-30 13:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-30 14:10 Re: ODBC driver over network very slow Merlin Moncure <merlin.moncure@rcsonline.com>
2005-06-30 14:47 Re: ODBC driver over network very slow Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox