large postgresql database becomes sluggish after 10-20 minutes ingestion

[jwoodsford]

I have a large postgresql advanced server database (v11.2), 9+ Tb, OLTP system on RHEL 6 which injest 1M+ rows of inserts/updates per minute which becomes sluggish after about 10-20 minutes of connecting to injesting application reducing injestion to maybe 300K per minute. We have tried to modify vacuuming times, checkpoint times, and adjusting other parameters in the postgresql.conf file but always get the same result. Our recent attempt we modified some of the OS kernal paging which no luck. This database schema has 15K tables with about half paritions tables with parent tables. I have tried the same load with non-partitioned tables but still get the same sluggish outcome in about the same time frame.

Any input would be welcome.

[alexc168]

Maybe you can check disk I/O activity.

I guess that because DB is busying writing data to the storage.

[saurabhs]

Hi jwoodsford,

By looking at your reported query, there might be multiple reasons which can slow down the database performance. Also, as per mentioned by "alexc168", I/O could be one of the reasons for slowness.

However, we would recommend the generic area from the troubleshooting point of view.

• Monitor the disk I/O as mentioned by "alexc168".
• Execute the statements in transaction batches. (i.e BEGIN.....COMMIT)
• Verify there is no blocking issue with parallel UPDATE statements.
• wal_buffers, checkpoint, bg_writer are tuned enough.
• If possible replace "INSERT" with "COPY" as it increases the performance. 
• - Refer the following link for the usage of "COPY" statement.
  https://www.postgresql.org/docs/10/populate.html
• 6. Network latency.

Hope this helps.

[jwoodsford]

Thanks for the post saurabhs. We have tried or applied all that in past testing. I think some of the OS kernal parameters may need some modification.

Any suggestions on OS kernal?

[slonkar · Accepted solution]

Hi @jwoodsford,

You can check the below suggestion for the write performance :

1) Check edb_dynatune and other memory-related parameters are tuned properly to use the available memory.

2) Generate the pgbadger analysis report and check which queries are slower and check statistics, indexes for them.

3) For kernel parameter tuning, please refer below link :

https://www.enterprisedb.com/docs/en/10/pg/kernel-resources.html

4) Keep the data in the tablespaces on the SSD drives.

5) As suggested earlier, please check disk I/O.

6) Check whether you are using the partitioned column for the query filter, in case of partitioned tables.

Regards,

Sudhir