How can I automatically stop long running database queries?

On large datasets with complicated custom reports or when the database server is under load, there is a chance that database queries may become stuck or run for too long.

Archiving queries

By default, archiving queries will be stopped after two hours.

To change this behaviour, add the following setting to the config/config.ini.php file under the [General] section:

[General]
archiving_query_max_execution_time = 3600

The value is the number of seconds the query will be allowed to run. The example above will stop archiving queries after one hour.

If an archiving query for a report is automatically stopped then data for that report might not be created. There is currently no notification when an archiving query is stopped.

Live queries

By default, live data queries (for example visitor profile and visits log) will never be stopped automatically. To stop live data queries after a certain length of time, add the following setting to the config/config.ini.php file.

[General]
live_query_max_execution_time = 60

Note: These settings require a recent MySQL version (5.7 or newer) and the PDO\MYSQL extension must be used. Some MySQL forks like MariaDB might not support these settings. These settings will not work when the PHP MySQLi extension is used.

Advertising Conversion Export

By default conversion export queries will never be stopped automatically. To stop conversion export queries after a certain length of time, add the following settings to the config/config.ini.php file.

[AdvertisingConversionExport]
advertising_conversion_export_max_execution_time = 60
advertising_conversion_export_query_limit = 5000

To disable all exports without deleting them, add the following setting to the config/config.ini.php file.

[AdvertisingConversionExport]
enable_export = 0

Note: This settings requires plugin version > 5.0.7.

Previous FAQ: How do I get LOAD DATA INFILE to work on my server?