Thread: Slow query execution over high latency network
Hello, When executing huge (10kb), hibernate-generated queries I noticed that when executed remotly over high-latency network (ping to server 200-400ms), the query takes a lot longer to complete. When the query is executed remotly (psql or jdbc) it takes 1800ms to execute, when I issue the query in an ssh terminal, I see the results almost immediatly. So although I should see the same latency over ssh , its way faster over ssh. The transmitted data is small (the wireshard-file has 22kb, attached), and even though the umts-network is high-latency its relativly high bandwith (~512kbit/s up, ~2mbit/s down). Any idea whats causing this? Maybe too small buffers somewhere? For me it poses problem, because I am working on a 2-Tier java application which should connect to postgres remotly - however with every more complex query taking 2s its almost unuseable over wireless networks (umts). Thank you in advance, Clemens
> > When executing huge (10kb), hibernate-generated queries I noticed that > when executed remotly over high-latency network (ping to server > 200-400ms), the query takes a lot longer to complete. > > When the query is executed remotly (psql or jdbc) it takes 1800ms to > execute, when I issue the query in an ssh terminal, I see the results > almost immediatly. > So although I should see the same latency over ssh , its way faster over > ssh. > The transmitted data is small (the wireshard-file has 22kb, attached), > and even though the umts-network is high-latency its relativly high > bandwith (~512kbit/s up, ~2mbit/s down). Well, if your upload bandwidth is really 512 kbits, uncompressed transmission of your query text should take about 0.2s, not too bad. SSH normally uses compression, so it should be a lor faster. Your attached file didn't come through. Anyway, there are several options : - different execution plan between your app and ssh+psql, which can happen if the planning uses/doesn't use your specific parameters, or if some wrong type bindings in your app force postgres not to use an index (there's been a few messages on that lately, check the archives). - dumb client versus smart client : smart client : use the protocol which sends the query text + parameters + prepare + execute in 1 TCP message, 1 ping, postgres works, 1 ping, get reply dumb client : - send prepare - wait for reply - send execute - wait for reply - send "gimme result" - wait for reply - etc > Any idea whats causing this? Maybe too small buffers somewhere? > For me it poses problem, because I am working on a 2-Tier java > application which should connect to postgres remotly - however with > every more complex query taking 2s its almost unuseable over wireless > networks (umts). If you want to ensure the fastest response time you need to ensure than one user action (click) needs one and only one roundtrip to the server before all the results are displayed. If said action needs 2 SQL queries, it ain't possible, unless (maybe) you use the asynchronous query protocol. You can also stuff multiple queries in stored procedures (but Hibernate won't be able to generate them obviously). One solution could be to put the database handling stuff inside an appserver, make your app communicate to it with a low-overhead RPC protocol (ie, not raw uncompressed XML) that minimizes the number of roudtrips, and compresses data thoroughly.
Hi Pierre, Thanks a lot for your reply. > Your attached file didn't come through. Hmm, ok. I uploaded the wireshark-log to: http://93.190.88.182/psql_large_query.bin > - different execution plan between your app and ssh+psql, which can happen > if the planning uses/doesn't use your specific parameters, Its both times (ssh and remote psql) exactly the same query - I copied the SQL generated by hibernate and executed it in psql. And although it has many columns (~210) the result-set is only about 5 rows and am sure not larger than a few kb. > - dumb client versus smart client : > smart client : use the protocol which sends the query text + parameters + > prepare + execute in 1 TCP message, 1 ping, postgres works, 1 ping, get > reply So are both psql and the jdbc driver dumb clients? Or are there only buffers somewhere too small and therefor data is sent in many smal batches. I thought one query would more or less equal to one roundtrip, right? Maybe I should ask on the pgsql-jdbc list. > If you want to ensure the fastest response time you need to ensure than one > user action (click) needs one and only one roundtrip to the server before > all the results are displayed > One solution could be to put the database handling stuff inside an > appserver, make your app communicate to it with a low-overhead RPC protocol > (ie, not raw uncompressed XML) that minimizes the number of roudtrips, and > compresses data thoroughly. I use well tuned hibernate fetch profiles to ensure fewest possible roundtrips, however I am not getting paid well enough to create an appserver tier ;) Thanks, Clemens
On 20 February 2011 02:05, Clemens Eisserer <linuxhippy@gmail.com> wrote: > I use well tuned hibernate fetch profiles to ensure fewest possible roundtrips, > however I am not getting paid well enough to create an appserver tier ;) I just had a brief glance over your tcpdump data ... are you sure hibernate isn't using a cursor to fetch each row individually? Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.georgedillon.com/web/html_email_is_evil.shtml
Hi Andrej, Thanks a lot for taking a loot at the tcpdump data. > I just had a brief glance over your tcpdump data ... are you sure > hibernate isn't using a cursor to fetch each row individually? Pretty sure, yes. I get the same performance when executing the hibernate-generated query using JDBC, even setting a large fetch-size doesn't improve the situation: > st.setFetchSize(100); > st.setFetchDirection(ResultSet.FETCH_FORWARD); Could it be jdbc driver struggles with the huge number of columns (~500)? Thanks, Clemens