large postgresql database becomes sluggish after 10-20 minutes ingestion

SOLVED
Highlighted
Adventurer

large postgresql database becomes sluggish after 10-20 minutes ingestion

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.

Archived Discussions


Effective March 31st, we will no longer engage on PostgresRocks.


How to engage with us further?


  • Thought Leadership: EDB Blogs

  • Tips and Tricks: Postgres Tutorials

  • Customer Support: Create a Case Please note: Only customers with an active EDB support subscription and support portal authorization can create support ticket

  • Engage on Stackoverflow While engaging on Stackoverflow tag the question with EDB or EnterpriseDB.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
EDB Team Member

Re: large postgresql database becomes sluggish after 10-20 minutes ingestion

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

View solution in original post

4 REPLIES 4
Highlighted
Level 2 Adventurer

Re: large postgresql database becomes sluggish after 10-20 minutes ingestion

Maybe you can check disk I/O activity.

 

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

Highlighted
EDB Team Member

Re: large postgresql database becomes sluggish after 10-20 minutes ingestion

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.

Highlighted
Adventurer

Re: large postgresql database becomes sluggish after 10-20 minutes ingestion

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?

Highlighted
EDB Team Member

Re: large postgresql database becomes sluggish after 10-20 minutes ingestion

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

View solution in original post

© 2019 EnterpriseDB Corporation. All rights reserved.   |   Privacy Policy   |  Terms of Use   |   Trademarks