MySQL/MariaDB

Easy to implement, high impact:

Some of those optimizations might only be possible on VPS or dedicated servers, not on shared hosting with cPanel.

1. Replace MySQL with MariaDB

MariaDB is an open-source fork of MySQL that provides better performance.

The nice part is that MariaDB is a "drop-in replacement" for MySQL, meaning that you can just install it over the current MySQL installation and it should work.

MariaDB also has a bunch of good articles on database optimization: https://mariadb.com/kb/en/optimization-and-tuning/

2. Set correct MySQL configuration

By default, MySQL won't use all the server resources. You have to tell MySQL explicitly how much memory it can use.

It is important to change your the innodb-buffer-pool-size based on the available RAM size.

Here is a recommended MySQL config for an 8GB/4vCPU server:

[mysqld]

# InnoDB
innodb_buffer_pool_size = 5500M # This should be around 70% of your total RAM
innodb_stats_on_metadata = 0
innodb_log_file_size = 768M # Around 15-25% of buffer-pool-size
innodb_file_per_table = 1 # Don't fragment tables across files
innodb_flush_log_at_trx_commit = 2 # Increases tracking write speed

max_connections = 100
query_cache_type = 0 # Disable query cache, as app is write-intensive
query_cache_size = 0
max_allowed_packet = 16M

An easy way to find the correct values for your MySQLTuner tool. When ran, the MySQLTuner will check your current configuration and server specifications and suggest some reasonable values to use.

3. Delete unnecessary data more often

The highest database usage in UXWizz are usually the heatmaps and session recordings. By default, UXWizz keeps this data until the user associated with it is also removed.

If your database size grew too big, or you want to reduce the size before a server migration, you can safely delete old heatmaps and session recordings.

For example, if you only want to delete the oldest 50K heatmap entries and oldest 10K recordings, you can run those MySQL queries:

// Clear old heatmap data
DELETE FROM ust_movements ORDER BY ID ASC limit 50000;
DELETE FROM ust_clicks ORDER BY ID ASC limit 50000;

// Clear old record data
DELETE FROM ust_records ORDER BY ID asc LIMIT 10000;
DELETE FROM ust_partials ORDER BY ID asc LIMIT 10000;

Example:

If you want to know how many rows of click heatmap data are stored in the database you can run this query:

SELECT COUNT(*) FROM ust_clicks

If it shows that you have 124000 rows and you only want to keep the latest 24000, you could then run the query described above to delete 100k rows:

DELETE FROM ust_clicks ORDER BY ID ASC limit 100000;

A useful tool: MySQLTuner-perl

The optimal configuration settings depend on the database size and system specs. There are tools that can automatically suggest the best configuration for your specific setup. One such tools is MySQLTuner-perl

Please check their repo for the latest installation instructions.

To install on use it, simply download the Perl file and executed it:

cd /
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
perl mysqltuner.pl