Clemens Eisserer <linuxhippy@gmail.com> Thursday 24 February 2011 22:06:13
> 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
I thought about ACK, and all of this TCP stuff. Query execution should take no
more then ping*4 + processing time. 1sek for send 8k and receive 30k is to big
value. Execution of such simple query, with base types, works in same way like
for HTTP request, request and response. No itermediate talking.
Tomorrow I check exactly TCP dump.
What version of PG do you use?
Regards,
Radek
