> For the complete documentation index, see [llms.txt](https://docs.uxwizz.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.uxwizz.com/installation/optimization-tips/mysql-mariadb.md). # MySQL/MariaDB Start with a supported [database version](/installation/requirements.md) 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. {% hint style="warning" %} 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. {% endhint %} Use the documentation for your exact database version: [MySQL InnoDB configuration](https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html) or [MariaDB optimization and tuning](https://mariadb.com/kb/en/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: ```ini [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: ```sql 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](/installation/optimization-tips/auto-delete-old-data-cron-jobs.md) 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: ```sql 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: ```sql 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](/installation/optimization-tips/auto-delete-old-data-cron-jobs.md#built-in-example-scripts), which selects recordings through their pageviews. For a server move, follow the [migration guide](/guides/migrating-to-a-new-server.md). Do not delete data solely to make the first backup smaller. ## A useful tool: MySQLTuner-perl [MySQLTuner](https://github.com/major/MySQLTuner-perl) 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 `/`: ```bash 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.