Not the answer you need?
Register and ask your own question!

MySQL performance good after restart, than it drops

FrankFrank Current User Role Contributor

We are running a MySQL community server version 5.7.27-1 on a debian system. Every time we restart the MySQL server our data load is done in 3.5 hours, the second run is most of the time at the same speed, than the performance drops and it takes up to 8 hours to process the same data until we restart the MySQL server.

What could cause such a significant performance degradation?

Answers

  • vadimtkvadimtk Contributor Percona Staff Role
    If I am to guess most likely your innodb_buffer_pool gets filled during initial fast load, and later InnoDB has to write data to disk in order to free up pages for the new incoming data, this is what slower things down. When you restart, InnoDB starts with empty buffer pool and you can load data fast again. But to confirm this theory I would install PMM and check InnoDB charts to see what's happening inside InnoDB subsystems
  • FrankFrank Current User Role Contributor

    Installing PMM helped a lot identifying the issue. We have a lot of very long running queries. The transaction history was not purged fast enough and that slowed down the selects until we restarted the server. We decided to change the isolation level to commited read which fixed our performance issues.

  • PeterPeter Percona CEO Percona Moderator Role

    Nice! Thanks for sharing

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.