I just deployed a small (1cpu, 600mb ram) db instance for Postgres.
With barely any load, db is very slow, average latency is 1,5s.
Ping for the DB IP is 190ms avg. I know it is high, but still it is a small amount of the total latency.
pgbench "sslmode=verify-ca sslrootcert=server-ca.pem sslcert=client-cert.pem sslkey=client-key.pem hostaddr={{IP}} port=5432 user={{user}} dbname=my-database"
Password:
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
latency average = 1576.916 ms
tps = 0.634149 (including connections establishing)
tps = 0.697559 (excluding connections establishing)
Is that latency normal? CPU and memory utilization is below 10%. Can you help me finding the remaining 1400ms ?
I tested the db with pgbench. I want to minimize the latency average.
I am developing django app, and latencies are noticable by the user.
2
Answers
After changing DB location, and reducing the ping from 200 to 20ms, overall latency decreased from 1600 to 200ms. I had no idea, it can cause lag that huge.
The default pgbench transaction include 7 sequential round-trips to the database. So the ping explains pretty much all of the observed latency. There are a variety of things you can do to reduce this, but unless you can do those same things to Django, it seems rather pointless to explore them.