Data base doesn’t respond due to too much data

If we have more that 15 millions of registers in data main table and it isn’t well configured, we could have problems.
You should have a secondary database to store the information with data from 30 days or similar and then you could delete the information from the main one with more that 31 days old.

The “quicker” solution to this problem is to do a DDBB rebuilding and to apply some configuration tweaks in the DDBB

1. Do a dump of the complete DB with mysqldump (data and structure).
2. Stop all.
3. Delete the DB and stop MySQL.
4. Modify the following parameters of the my.cnf file:

innodb_flush_log_at_trx_commit = 0
key_buffer = 400M
innodb_file_per_table

5. Start the MySQL.
6. Enter in the MySQL console:

create database pandora;
use pandora;
import

This way you would have rebuilt the DB using a data file for each table, making the most of the access and activating the writing cache. All this optimize a lot the access to the disk

It’s very important that you remember to purge the DDBB and to modify the deletion limits as to the main DDBB contains data with a 1 month old or less.