How do I disable the OPTIMIZE queries which take too long on my server?
When Matomo (Piwik) is setup to delete old logs, after the log deletion, we will run OPTIMIZE queries to claim the lost Mysql space on the database tables. These queries can be very slow to run and can cause problems on extremely large Matomo servers. If you see SQL queries and the server load going up out of control, with queries queueing because of an OPTIMIZE TABLE query, we recommend to disable all OPTIMIZE TABLE queries. To do so, edit config/config.ini.php and add:
[General]
enable_sql_optimize_queries=0
Note: Matomo will usually not OPTIMIZE tables which are Innodb tables (unless you are using MariaDB of version 10.1.1 or newer) but we will OPTIMIZE tables of the Myisam type.
Next FAQ: How do I improve the load time of the find visitor SQL query when visitors have many visits?
Previous FAQ: How do I get LOAD DATA INFILE to work on my server?