From: | Clemens Eisserer <linuxhippy(at)gmail(dot)com> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Slow query execution over WAN network |
Date: | 2011-02-24 21:06:13 |
Message-ID: | AANLkTinqXN=3ukU5sUUnA3OHptQsm7jep24zNAs5rSCK@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Hi,
I tried to analyze it further, this time the query executed over UMTS
- where it took 2000ms. (ping tp server 130ms, about 2mbit/s down):
http://93.190.88.182/wireshark_umts.tcpdump
0-490ms: Some TCP magic going on (tcp segment of a reassembled PDU??)
491 ms - Postgres query is sent
680ms - 1472ms - TCP ACKs
1642ms - Postgres - parse/bind completition
1873ms - Postgres - Row description
1893ms - First data Row
Row / ACK
2053ms - Last data Row
What makes me wonder is:
- Why does it take 500ms until the query is sent, what is that tcp
stuff happening before
- It seems until 1472ms the server is sending ACKs for the query
request, so does it take ~1000ms for the server to receive the query?
So is the query-upload the problem?
- Actual Row-Data transmission only takes 150ms.
If its indeed the query-data upload, are there any options to compress
the stream between the jdbc-driver and postgres-server?
Thanks, Clemens
> You truncated times in profiler window. May I ask what time was measured for
> socket.read(), and what kind of connection did you used for this (dsl, umts),
> connection speed and min/max/avg ping time to database server?
Sorry, I've deleted the profile - but about 70% were spent in socket.read().
Ping to the db-server was about ~30ms, it was a 8mbit ADSL line with
1M upstream.
Looking at the profile it seems reading back the data (~25kb/request)
is the problem.
> What other operations are performed inside benchamrkQuery(), commit/rollback,
> connection open?
Nothing, its just a:
> ResultSet rs = st.executeQuery(query);
> while (rs.next());
> Did fetch size wa,s when you was making this test, enaught to get all records
> at once?
Yes, I tried setting it higher a bit but nothing changed.
However the query only returned 12 rows - so I guess it should be
covered by default fetch size.
Thanks, Clemens
From | Date | Subject | |
---|---|---|---|
Next Message | Clemens Eisserer | 2011-02-24 21:07:43 | Re: Slow query execution over WAN network |
Previous Message | jerome.moliere | 2011-02-24 20:07:54 | Re: difference on execution time between prepared statement in pgAdminand through JDBC Stack |