Thread: Low performance between datacenters
hi All,
I am new on the list.
I hope someone can give me an adequate answer or good advice about my problem.
I have a client (normally a web service, for testing the psql client) in GCP. There is a PSQL server in another DC. The ping response time is 20ms.
I measured the bandwidth via scp and it is more than 1Gb/s which is more than enough IMO.
The psql connection between the DCs for me was unexpectedly slow.
I would expect a bit slower query without data ('select now()') due to the increased latency and somewhat similar speed of data transfer.
What I see is that
select now() increased from 0.7ms to 20ms which is OK.
And 'select *' on a table with 3082 rows (so it's a small table) increased from 10ms to 800ms.
Is this normal? Can I improve it somehow?
Thank you,
El dom, 7 jul 2024 a la(s) 3:17 p.m., Tamás PAPP (tomposmiko@gmail.com) escribió:
hi All,I am new on the list.I hope someone can give me an adequate answer or good advice about my problem.I have a client (normally a web service, for testing the psql client) in GCP. There is a PSQL server in another DC. The ping response time is 20ms.I measured the bandwidth via scp and it is more than 1Gb/s which is more than enough IMO.
Hi Tamás,
Bandwidth and Latency are two different concepts. Latency is the round-trip-time of a data-packet travelling over the network while bandwidth is the amount of data you can move concurrently. Latency will add up time over each conversation between the client and the server. For example, establishing a connection has a bit of back and forth dialogue going on. Each of these communications bits will be affected by latency as either client or server needs to wait for the other party to receive the message and then respond to it.
Latency will have an impact even when transmitting data like in a plain SELECT * FROM table. Bear in mind that psql uses TCP which sends an ACK packet at regular intervals to assert the integrity of the transmitted data.
The psql connection between the DCs for me was unexpectedly slow.I would expect a bit slower query without data ('select now()') due to the increased latency and somewhat similar speed of data transfer.What I see is thatselect now() increased from 0.7ms to 20ms which is OK.And 'select *' on a table with 3082 rows (so it's a small table) increased from 10ms to 800ms.
Since you aren't providing much evidence, I can speculate a lot might be going on to explain this kind of increase in the query delay: busy database, busy network, locks, latency, network unreliability, etc.
Is this normal? Can I improve it somehow?
Move your web service physically as close as possible to the database server.
Often it happens because of the low batch size for fetching data. This makes client wait unnecessarily while reading rows. I am not sure which client are you using, but in java this can be controlled on per-statement level, see
I believe there is also a connection parameter to set the default value, but I don’t remember out of top of my head. You can definitely set it on connection, see
нд, 7 лип. 2024 р. о 10:17 Tamás PAPP <tomposmiko@gmail.com> пише:
hi All,I am new on the list.I hope someone can give me an adequate answer or good advice about my problem.I have a client (normally a web service, for testing the psql client) in GCP. There is a PSQL server in another DC. The ping response time is 20ms.I measured the bandwidth via scp and it is more than 1Gb/s which is more than enough IMO.The psql connection between the DCs for me was unexpectedly slow.I would expect a bit slower query without data ('select now()') due to the increased latency and somewhat similar speed of data transfer.What I see is thatselect now() increased from 0.7ms to 20ms which is OK.And 'select *' on a table with 3082 rows (so it's a small table) increased from 10ms to 800ms.Is this normal? Can I improve it somehow?Thank you,
On 8/7/2024 01:17, Tamás PAPP wrote:
> hi All,
>
> I am new on the list.
> I hope someone can give me an adequate answer or good advice about my
> problem.
>
> I have a client (normally a web service, for testing the psql client) in
> GCP. There is a PSQL server in another DC. The ping response time is 20ms.
> I measured the bandwidth via scp and it is more than 1Gb/s which is more
> than enough IMO.
>
>
> The psql connection between the DCs for me was unexpectedly slow.
> I would expect a bit slower query without data ('select now()') due to
> the increased latency and somewhat similar speed of data transfer.
> What I see is that
>
> select now() increased from 0.7ms to 20ms which is OK.
> And 'select *' on a table with 3082 rows (so it's a small table)
> increased from 10ms to 800ms.
>
>
> Is this normal? Can I improve it somehow?
This subject is totally new for me, but maybe you have different
connection settings for internal and external connections? I mean - from
different networks?
--
regards, Andrei Lepikhov