Обсуждение: Result Set over Network Question
Hi,
We have recently switch our product from MS SQL 2000 to Postgresql 9.0.7. We have tuned the searches and indexes so that they are very close (often better) to what sql2k was giving us. We are noticing some differences now in the time it takes for the result set to make it back to the client and would like some help finding out why.
What we see on the PG side is that if we run:
Select SomeInt32 from someTable where something Limit 1
It consistently returns the results "instantaneously" after the fetch time. If we run the same select but ask for more data the fetch time stays the same but the row takes longer to come back. Bringing back 400 bytes takes 1-2 s but bringing back 866 bytes takes 9 - 11 s.
We went to the SQL2k server (On the same hardware) and ran the selects again. When bringing back on an int32 PG was faster with the fetch and the row coming back in 1-5 ms and SQL2k coming back in 500-700 ms. This tells me that the problem is not related to PG index or Disk. When bringing back 400 bytes PG fetch time would be 1-2 ms but the results would take 2-3 s but SQL2k would it bring back in 700-900 ms. Working with 866 bytes, PG fetch time is 1-3 ms with the results coming back in 9 - 11 s and SQL2k bringing the results back in 2-3 s.
The head to head test was run in Aqua Data Studio 10.0.8 and ODBC driver 9.0.3.10. The same slow down happens when use PGadminIII. The differnces in time to not occure when running on the pg/sql server computer so I think there is a network component to this.
I know that as you bring back more data it takes longer but why is there such a difference in the time it takes PG to send the data compared to the time it takes sql2k to send it?
Any thoughts and suggestions are very much appreciated
Thanks
Ron
We have recently switch our product from MS SQL 2000 to Postgresql 9.0.7. We have tuned the searches and indexes so that they are very close (often better) to what sql2k was giving us. We are noticing some differences now in the time it takes for the result set to make it back to the client and would like some help finding out why.
What we see on the PG side is that if we run:
Select SomeInt32 from someTable where something Limit 1
It consistently returns the results "instantaneously" after the fetch time. If we run the same select but ask for more data the fetch time stays the same but the row takes longer to come back. Bringing back 400 bytes takes 1-2 s but bringing back 866 bytes takes 9 - 11 s.
We went to the SQL2k server (On the same hardware) and ran the selects again. When bringing back on an int32 PG was faster with the fetch and the row coming back in 1-5 ms and SQL2k coming back in 500-700 ms. This tells me that the problem is not related to PG index or Disk. When bringing back 400 bytes PG fetch time would be 1-2 ms but the results would take 2-3 s but SQL2k would it bring back in 700-900 ms. Working with 866 bytes, PG fetch time is 1-3 ms with the results coming back in 9 - 11 s and SQL2k bringing the results back in 2-3 s.
The head to head test was run in Aqua Data Studio 10.0.8 and ODBC driver 9.0.3.10. The same slow down happens when use PGadminIII. The differnces in time to not occure when running on the pg/sql server computer so I think there is a network component to this.
I know that as you bring back more data it takes longer but why is there such a difference in the time it takes PG to send the data compared to the time it takes sql2k to send it?
Any thoughts and suggestions are very much appreciated
Thanks
Ron
--
Ronald Hahn , CCP, CIPS Member
DOCFOCUS INC.
Suite 103, 17505 - 107 Avenue,
Edmonton, Alberta T5S 1E5
Phone: 780.444.5407
Toll Free: 800.661.2727 (ext 6)
Fax: 780.444.5409
Email: rhahn@docfocus.ca
Support:dfisupport@docfocus.ca
DOCFOCUS.ca
There are 2 kinds of people in the world.
Those who can extrapolate from incomplete data
On Mon, Apr 30, 2012 at 3:32 PM, Ronald Hahn, DOCFOCUS INC. <rhahn@docfocus.ca> wrote: > We went to the SQL2k server (On the same hardware) and ran the selects > again. When bringing back on an int32 PG was faster with the fetch and the > row coming back in 1-5 ms and SQL2k coming back in 500-700 ms. This tells me > that the problem is not related to PG index or Disk. When bringing back 400 > bytes PG fetch time would be 1-2 ms but the results would take 2-3 s but > SQL2k would it bring back in 700-900 ms. Working with 866 bytes, PG fetch > time is 1-3 ms with the results coming back in 9 - 11 s and SQL2k bringing > the results back in 2-3 s. I think the opposite. I'm thinking it's quite probable that it's disk access the one killing you. Remember, two different database systems means two different access patterns. To figure it out, you have to provide a lot more information on your system and your query. Check out how to post "Slow Query Questions" [0]. Only after getting all that information the people of the list will be able to have a clue as to what your problem is. [0] http://wiki.postgresql.org/wiki/SlowQueryQuestions
On Mon, Apr 30, 2012 at 1:32 PM, Ronald Hahn, DOCFOCUS INC. <rhahn@docfocus.ca> wrote: > Hi, > We have recently switch our product from MS SQL 2000 to Postgresql > 9.0.7. We have tuned the searches and indexes so that they are very close > (often better) to what sql2k was giving us. We are noticing some > differences now in the time it takes for the result set to make it back to > the client and would like some help finding out why. > > What we see on the PG side is that if we run: > Select SomeInt32 from someTable where something Limit 1 > It consistently returns the results "instantaneously" after the fetch > time. If we run the same select but ask for more data the fetch time stays > the same but the row takes longer to come back. Bringing back 400 bytes > takes 1-2 s but bringing back 866 bytes takes 9 - 11 s. > > We went to the SQL2k server (On the same hardware) and ran the selects > again. When bringing back on an int32 PG was faster with the fetch and the > row coming back in 1-5 ms and SQL2k coming back in 500-700 ms. This tells me > that the problem is not related to PG index or Disk. When bringing back 400 > bytes PG fetch time would be 1-2 ms but the results would take 2-3 s but > SQL2k would it bring back in 700-900 ms. Working with 866 bytes, PG fetch > time is 1-3 ms with the results coming back in 9 - 11 s and SQL2k bringing > the results back in 2-3 s. > > The head to head test was run in Aqua Data Studio 10.0.8 and ODBC driver > 9.0.3.10. The same slow down happens when use PGadminIII. The differnces in > time to not occure when running on the pg/sql server computer so I think > there is a network component to this. to rule out network just do: create temp table scratch as select <your query>... if it's a lot faster, then you have a probable network issue. merlin
After some testing using wiershark (poor mans profiler) to see what was going on with the network I found that it was the tools I've been using. Both Aqua and PGadminIII have a large overhead per column to get the meta data. MSSQL sends that data upfront so the impact isn't as bad. I'm not sure if it's a limitation of the pgsql protocol vs tds or a limitation of Aqua or a combination of both. At any rate it turns out not to be part of the problem I'm having with my software stalling out so I'm back to Square one with my problem.
Thanks,
Ron
Thanks,
Ron
On Thu, May 3, 2012 at 10:28 AM, Ronald Hahn, DOCFOCUS INC. <rhahn@docfocus.ca> wrote: > After some testing using wiershark (poor mans profiler) to see what was > going on with the network I found that it was the tools I've been using. > Both Aqua and PGadminIII have a large overhead per column to get the meta > data. MSSQL sends that data upfront so the impact isn't as bad. I'm not sure > if it's a limitation of the pgsql protocol vs tds or a limitation of Aqua or > a combination of both. At any rate it turns out not to be part of the > problem I'm having with my software stalling out so I'm back to Square one > with my problem. ok, let's figure out what the issue is then. first, let's make sure it isn't the server that's stalling: configure log_min_duration_statement with an appropriate value so you start catching queries that are taking longer then you think the should be. also some client side logging directly wrapping the SQL invocation couldn't hurt. is your application jdbc? merlin
On Thu, May 3, 2012 at 5:40 PM, Merlin Moncure <mmoncure@gmail.com> wrote: > On Thu, May 3, 2012 at 10:28 AM, Ronald Hahn, DOCFOCUS INC. > <rhahn@docfocus.ca> wrote: >> After some testing using wiershark (poor mans profiler) to see what was >> going on with the network I found that it was the tools I've been using. >> Both Aqua and PGadminIII have a large overhead per column to get the meta >> data. MSSQL sends that data upfront so the impact isn't as bad. I'm not sure >> if it's a limitation of the pgsql protocol vs tds or a limitation of Aqua or >> a combination of both. At any rate it turns out not to be part of the >> problem I'm having with my software stalling out so I'm back to Square one >> with my problem. So, Ronald, are you saying the different approach to meta data transfer is _not_ the issue? > ok, let's figure out what the issue is then. first, let's make sure > it isn't the server that's stalling: configure > log_min_duration_statement with an appropriate value so you start > catching queries that are taking longer then you think the should be. > also some client side logging directly wrapping the SQL invocation > couldn't hurt. is your application jdbc? Ronald said ODBC in his first posting. But since ADS seems to support JDBC as well trying that might be a good test to get another data point. Alternative tools for JDBC tests: http://squirrel-sql.sourceforge.net/ http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html Using the PG client remotely with "\timing on" might be an even better idea. Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
Robert Klemme, 07.05.2012 14:03: > Alternative tools for JDBC tests: > > http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html SQL Developer does not support PostgreSQL This page: http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools also lists several JDBC based tools. Thomas
On Mon, May 7, 2012 at 2:11 PM, Thomas Kellerer <spam_eater@gmx.net> wrote: > Robert Klemme, 07.05.2012 14:03: >> >> Alternative tools for JDBC tests: >> >> http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html > > SQL Developer does not support PostgreSQL Last time I checked (quite a while ago) you could use arbitrary JDBC drivers. There's also http://docs.oracle.com/cd/E25259_01/appdev.31/e24285/intro.htm#sthref306 And this seems to indicate that it's still the case: "[...] or another third-party driver. [...] JDBC URL (Other Third Party Driver): URL for connecting directly from Java to the database; overrides any other connection type specification." http://docs.oracle.com/cd/E25259_01/appdev.31/e24285/dialogs.htm#BACDGCIA I assume Oracle is not interested in aggressively advertizing this feature though. Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
Robert Klemme, 07.05.2012 15:44: >>> http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html >> >> SQL Developer does not support PostgreSQL > > Last time I checked (quite a while ago) you could use arbitrary JDBC > drivers. There's also > http://docs.oracle.com/cd/E25259_01/appdev.31/e24285/intro.htm#sthref306 > > And this seems to indicate that it's still the case: "[...] or another > third-party driver. [...] > JDBC URL (Other Third Party Driver): URL for connecting directly from > Java to the database; overrides any other connection type > specification." > http://docs.oracle.com/cd/E25259_01/appdev.31/e24285/dialogs.htm#BACDGCIA > > I assume Oracle is not interested in aggressively advertizing this > feature though. That seems to be a documentation bug. I tried it, and it definitely does not work (or I am missing something). Their release notes at: http://www.oracle.com/technetwork/developer-tools/sql-developer/sqldev31-ea-relnotes-487612.html state: Third Party Databases SQL Developer supports IBM DB2 UDB LUW , Microsoft SQL Server and Microsoft Access, MySQL, Sybase Adaptive Server andTeradata. See Supported Platforms for details on all third party database releases supported. Regards Thomas
On Mon, May 7, 2012 at 4:25 PM, Thomas Kellerer <spam_eater@gmx.net> wrote: > That seems to be a documentation bug. > I tried it, and it definitely does not work (or I am missing something). Apparently I am the one who is missing something. :-) > Their release notes at: > http://www.oracle.com/technetwork/developer-tools/sql-developer/sqldev31-ea-relnotes-487612.html > > state: > > Third Party Databases > > SQL Developer supports IBM DB2 UDB LUW , Microsoft SQL Server and > Microsoft Access, MySQL, Sybase Adaptive Server and Teradata. > See Supported Platforms for details on all third party database releases > supported. Right you are, Thomas! Thank you! Sorry for the confusion. Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/