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
