From: | Prakash Itnal <prakash074(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Performance issue with postgres9.6 |
Date: | 2017-04-07 10:16:33 |
Message-ID: | CAHC5u7_C4HapMoS2wNC_VS02_vmgS1DAUcgZCjYRP-jg=QJD2Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hello,
We currently use psotgres 9.3 in our products. Recently we upgraded to
postgres 9.6. But with 9.6 we have seen a drastic reduction in throughput.
After analyzing carefully I found that "planner time" in 9.6 is very high.
Below are the details:
Scenario:
1 Create a table with 100000 rows.
2 Execute simple query: select * from subscriber where s_id = 100;
3 No update/delete/insert; tried vacuum, full vacuum; by default we enable
auto-vacuum
9.3: Avg of "Total runtime" : *0.24ms* [actual throughput: *650 TPS*]
9.6: Avg of Total time: *0.56ms* (Avg of "Planning time" : 0.38ms + Avg of
"Execution time" : 0.18ms) [actual throughput: *80 TPS*]
Check the attachments for more details.
Below is the configuration setting. Full configuration can be found in
attachment.
shared_buffers = 128MB
effective_cache_size = 256MB
Note that we use master-slave (one master - one slave) setup. I could see
no difference even when I take out slave.
I tried all possibilities of increasing shared memory, maitenance_work,
asynchronous commit etc. but, nothing showed any major improvements. Kindly
help to identify what is missing!
PS: We use postgres for small scale so the values are less. The size of the
DB is also just around 180MB.
--
Cheers,
Prakash
Attachment | Content-Type | Size |
---|---|---|
out93.txt | text/plain | 4.8 KB |
out96.txt | text/plain | 5.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tatsuo Ishii | 2017-04-07 10:20:26 | pg_export_snapshot doc |
Previous Message | Amit Langote | 2017-04-07 10:05:53 | ExecPrepareExprList and per-query context |