UXWizz
Search…
🛠️ Installation
📖
Guides
🔧
JavaScript API
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.
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.
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:
1
[mysqld]
2
3
# InnoDB
4
innodb_buffer_pool_size = 5500M # This should be around 70% of your total RAM
5
innodb_buffer_pool_instances = 6
6
innodb_stats_on_metadata = 0
7
innodb_log_file_size = 768M # Around 15-25% of buffer-pool-size
8
innodb_file_per_table = 1 # Don't fragment tables across files
9
innodb_flush_log_at_trx_commit = 2 # Increases tracking write speed
10
11
max_connections = 100
12
query_cache_type = 0 # Disable query cache, as app is write-intensive
13
query_cache_size = 0
14
max_allowed_packet = 16M
Copied!
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:
1
// Clear old heatmap data
2
DELETE FROM ust_movements ORDER BY ID ASC limit 50000;
3
DELETE FROM ust_clicks ORDER BY ID ASC limit 50000;
4
5
// Clear old record data
6
DELETE FROM ust_records ORDER BY ID asc LIMIT 10000;
7
DELETE FROM ust_partials ORDER BY ID asc LIMIT 10000;
Copied!
Example:
If you want to know how many rows of click heatmap data are stored in the database you can run this query:
1
SELECT COUNT(*) FROM ust_clicks
Copied!
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:
1
DELETE FROM ust_clicks ORDER BY ID ASC limit 50000;
Copied!
An 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:
1
cd /
2
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl -O mysqltuner.pl
3
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
4
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
5
perl mysqltuner.pl
Copied!
Last modified 1mo ago