UXWizz
WebsitePricingDemoTwitter (X)
  • Introduction
  • 🛠️ Installation
    • Requirements
      • Limitations
      • Server specs (CPU, RAM)
    • Installation guide
      • Uploading the script
      • Creating a MySQL database
      • Running the installer
    • Install on a new server
      • Ubuntu 20.04 (or higher)
      • DigitalOcean
    • Docker
      • Via Docker Compose
      • Standalone Docker image
    • Adding the tracking code
      • Automatic SPA pageview tracking
      • Add tracker to Next.js App
      • Add tracker to React Router App
    • Optimization tips
      • MySQL/MariaDB
      • Auto-delete old data (cron jobs)
      • Apache
    • Frequently Asked Questions
  • 🔧JavaScript API
    • Tags
    • Events
    • Hooks/callbacks
    • Other API functions
    • Session Recording
      • Ignore specific elements
  • 📖Guides and features
    • Goals (NEW!)
    • Ask AI (NEW!)
    • Basic usage
    • A/B testing
    • Usage tips
    • Dashboard user access level
    • Resetting the admin password
    • Database querying
    • Technical details
      • IP Geolocation
    • Troubleshooting
      • Agency
        • MultiDB
      • Dashboard
        • Refreshing dashboard sub-page leads to 404 error
        • Website iframe not loading (x-frame-options)
        • License says "invalid"
        • Updating Fails
      • Tracking
        • No data is being recorded
        • Can't include tracker via Google Tag Manager
        • The A/B test JS file is missing
      • WordPress
        • Cloudways 403 Forbidden screen on WordPress
        • NGINX 403 Forbidden screen on WordPress
    • Extending the dashboard
    • Support
    • Migrating to a new server
  • 🎓Useful Examples
    • Feedback form (polls)
    • Tracking 404 Pages
    • Tracking UTM parameters
    • Tracking Google Ads GCLID
    • Storing user device types
    • Track video playback
  • 📜About
    • Changelog
    • Personal Data Information
    • Privacy Policy (uxwizz.com)
    • Licenses and pricing
    • [Deprecated] License Subscriptions
Powered by GitBook
On this page
  • Easy to implement, high impact:
  • 1. Replace MySQL with MariaDB
  • 2. Set correct MySQL configuration
  • 3. Delete unnecessary data more often
  • A useful tool: MySQLTuner-perl

Was this helpful?

  1. 🛠️ Installation
  2. Optimization tips

MySQL/MariaDB

PreviousOptimization tipsNextAuto-delete old data (cron jobs)

Last updated 11 months ago

Was this helpful?

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 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:

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 = 5600M # This should be around 70% of your total RAM
innodb_log_file_size = 1400M # 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
max_allowed_packet = 16M

# Disable bin log - more efficient, but less robust:
#  this drastically reduces storage size and improves performance
#  but you lose the replication and restore capabilities
skip-log-bin 

3. Delete unnecessary data more often

Checkout the cron jobs guide to enable automatic data deletion.Auto-delete old data (cron jobs)

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

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

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

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

MariaDB
https://mariadb.com/kb/en/optimization-and-tuning/
MySQLTuner tool
MySQLTuner-perl