Performance issue with postgres9.6

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

Responses

Browse pgsql-hackers by date

  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