MySQL/MariaDB

Start with a supported database version and monitor memory, disk usage, query duration, and free space. Database configuration changes need server access and a verified backup.

Easy to implement, high impact:

1. Choose a supported database

UXWizz supports MySQL 8.0+ and MariaDB 10.7+. Do not install MariaDB over an existing MySQL data directory as a performance shortcut. Moving between database engines or major versions requires a supported migration and restore test.

2. Set correct MySQL configuration

Choose memory settings for the whole server. Leave RAM for PHP, Apache, the operating system, and concurrent database connections. A setting suited to a dedicated database server may exhaust memory on a shared application server.

Review the database's buffer pool and connection limits with your administrator. Change one setting at a time and compare the result under representative traffic.

Do not disable binary logging or weaken transaction durability just to reduce disk use. Binary logs may be required for replication or point-in-time recovery. Keep innodb_flush_log_at_trx_commit=1 unless the responsible administrator has accepted the recovery tradeoff of another value.

Use the documentation for your exact database version: MySQL InnoDB configuration or MariaDB optimization and tuning.

MariaDB configuration example

The original 8 GB example is useful for a server with enough RAM reserved for its other processes. On an 8 GB database-focused MariaDB server, these values are a starting point to measure, not a traffic guarantee:

[mysqld]
innodb_buffer_pool_size = 5600M
innodb_log_file_size = 1400M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
max_connections = 100
max_allowed_packet = 64M

For a server that also runs Apache/PHP, reduce the buffer pool to fit the measured total memory use. Back up the active configuration, edit the MariaDB server option file (commonly /etc/mysql/mariadb.conf.d/50-server.cnf on Ubuntu), then restart MariaDB during a maintenance window. Check the error log, memory use, and these effective values afterward:

SHOW VARIABLES WHERE Variable_name IN (
  'innodb_buffer_pool_size', 'innodb_log_file_size',
  'innodb_flush_log_at_trx_commit', 'max_connections', 'max_allowed_packet'
);

This example targets MariaDB. MySQL releases can use different redo-log settings; do not copy the MariaDB file unchanged to another engine. If startup fails, restore the saved option file and restart. Do not delete database or redo-log files to force startup.

Optional throughput tradeoffs: innodb_flush_log_at_trx_commit=2 can reduce disk writes but can lose recent committed transactions after an OS or power failure. skip-log-bin disables binary logging and removes the ability to use those logs for replication or point-in-time recovery. These original tuning options remain available only when the responsible administrator accepts those losses and confirms that the backup/replication setup does not depend on them. They are not required for UXWizz.

3. Delete unnecessary data more often

Use Scheduled Tasks to set separate retention periods for recordings, heatmaps, and sessions. Back up first and review the effect on historical reports.

Do not delete arbitrary rows from recording tables by ID: related payloads and sessions must remain consistent. A retention policy is easier to inspect and maintain than ad hoc cleanup queries.

To inspect the click-heatmap row count in the selected analytics database, you can run this read-only query:

SELECT COUNT(*) FROM ust_clicks;

Remove a bounded number of heatmap rows manually

If you need immediate cleanup and cannot use the dashboard, a database administrator can use the original row-limit method. Back up first and confirm the selected analytics database. This example permanently removes up to 50,000 rows from each heatmap table:

START TRANSACTION;
DELETE FROM ust_movements ORDER BY id ASC LIMIT 50000;
DELETE FROM ust_clicks ORDER BY id ASC LIMIT 50000;
COMMIT;

Lower IDs normally represent earlier collected rows; after imports they may not be the oldest by date. This operates across the selected database's domains. It removes heatmap detail, while keeping sessions and pageviews. InnoDB can reuse the freed space internally; the database file may not shrink on disk.

Do not apply separate row limits to ust_records, ust_partials, and ust_records_chunks: their IDs are independent, and doing so can leave incomplete recordings. Use the recording cleanup task, or the version-matched legacy cleanup script, which selects recordings through their pageviews.

For a server move, follow the migration guide. Do not delete data solely to make the first backup smaller.

A useful tool: MySQLTuner-perl

MySQLTuner can help an administrator review database settings. Follow its current installation instructions, inspect the downloaded tool, and run it with appropriate database access.

Treat its output as recommendations to investigate. Check compatibility with your database version and backup strategy before applying a change.

To keep the tool in a dedicated directory instead of downloading it into /:

mkdir mysqltuner
cd mysqltuner
curl -fSLO https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
curl -fSLO https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt
curl -fSLO https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv
less mysqltuner.pl
perl mysqltuner.pl

Use its interactive credential prompt or a protected client configuration. Do not put a database password in the command line.