At the beginning of this year we lunched a lot of massive data deletions (millions of rows per week) in our production database. Also, there is an automatic integration of new data (insert/updates) of 10 to 25 .CSV files per day with a global/average size of 400MB.

We noticed that reading performance on data even with full using of index became too slow. We have about 122GB of daya with 60GB of RAM and I configured 48GB for the Innodb buffer pool (please check details in images).

Is the deletion affect buffer pool performance ? What is the best/fast way to resolve that issue ?

Thank you for your help.






It's not really a fragmentation issue, I added a 3rd image for more information.image3


It is not indexes (the use is about 90% - 100%) but the bad way the app is using the db model made is too bad.

  • How often do you optimize table and analyze table? – mustaccio Mar 15 at 15:51
  • not really a fragmentation issue (I think), I added a 3rd image for more information. – Yassine LAADIDI Mar 15 at 16:21
  • There are still too many unknowns. How did you do the Delete? There are many ways; some will impact the buffer_pool. – Rick James Mar 15 at 18:00
  • You have a huge number of indexes -- probably most are useless. Please provide SHOW CREATE TABLE. – Rick James Mar 15 at 18:00
  • For the future, consider changing the DELETE technique: mysql.rjweb.org/doc.php/deletebig – Rick James Mar 15 at 18:01

I restarted yesterday the server with making some changes in the configuration file :

innodb_buffer_pool_size = 48
innodb_buffer_pool_instances = 32
innodb_read_io_threads  = 16
innodb_write_io_threads 8

The buffer pool is used, no more slowness noticed until now (after some refresh). However, still not understand the reason of the impact of deletion on the buffer pool. Thus I believe that the bad way the app is using queries via ORM is the source of this issue.

  • monitor and graph your innodb_buffer_pool global status. Notable *_reads vs *read_requests. – danblack Mar 22 at 3:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.