A slow MySQL server can bring down the performance of your entire website, especially if you’re running WordPress or WooCommerce on a VPS. Even a single inefficient database query can cause high CPU usage, slow page loads, and random timeouts. Thankfully, Ubuntu and MariaDB include powerful built-in tools that help you identify exactly what’s slowing down your server. In this guide, you’ll learn how to check MySQL bottlenecks using the Slow Query Log, Performance Schema, and essential tuning techniques every VPS owner should know.
Table of Contents
What Causes MySQL Bottlenecks on Ubuntu?
MySQL bottlenecks happen when the database server cannot process queries efficiently. On a VPS, this usually comes from:
1. Inefficient SQL queries
Poorly written plugins or WooCommerce extensions often generate heavy queries.
2. Low RAM availability
If MySQL runs out of memory, it swaps — dramatically reducing performance.
(Great place for an internal link to your Swap Space guide.)
3. Default MySQL configuration
MariaDB’s default settings aren’t optimized for high-traffic WordPress sites.
4. Missing indexes or table locking issues
Large wp_postmeta, wp_wc_*, and wp_options tables can bottleneck instantly.
5. Too many concurrent connections
Traffic spikes can overwhelm MySQL’s thread handling.
To determine the exact cause, begin with the Slow Query Log.
Enable the MySQL Slow Query Log on Ubuntu
The Slow Query Log records queries that exceed a specific execution time. This is the most important tool for diagnosing MySQL bottlenecks.
Open MariaDB’s main configuration file:
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Inside the [mysqld] block, add or update:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
Restart MySQL:
sudo systemctl restart mariadb
Any query taking longer than 1 second is now recorded.
How to Read and Analyze Slow Queries
To watch slow queries in real time:
sudo tail -f /var/log/mysql/slow.log
To summarize the worst offenders:
sudo mysqldumpslow -s t /var/log/mysql/slow.log | less
Look for:
Repeating SQL patterns
Slow
SELECTqueriesWooCommerce postmeta lookups
Queries referencing
wp_options"Copying to tmp table"messages (index problems)
These patterns reveal exactly which plugin or operation is slowing down your server.
Use the MySQL Performance Schema for Live Diagnostics
Performance Schema provides real-time query performance insights.
Open MySQL:
sudo mysql -root -p
Then run:
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;
This shows:
Slowest SQL digest patterns
Execution frequency
Average execution time
Which types of queries hurt performance the most
This is especially valuable for WooCommerce and plugin-heavy WordPress sites.
Check MySQL Resource Usage at the System Level
To view CPU and RAM usage:
top -u mysql
If MySQL frequently spikes CPU or memory:
The buffer pool may be too small
Queries may be inefficient
Caching may be insufficient
The VPS may be undersized
Tune MariaDB/MySQL for Better VPS Performance
Once you identify the problems, tune MySQL accordingly.
Edit the config:
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Increase InnoDB Buffer Pool Size
For a 2GB VPS:
innodb_buffer_pool_size = 512M
For a 4GB VPS:
innodb_buffer_pool_size = 1G
Log Queries Not Using Indexes
log_queries_not_using_indexes = 1
Improve Table Cache
table_open_cache = 2000
Optimize Thread Handling
thread_cache_size = 100
Restart:
sudo systemctl restart mariadb
These changes significantly improve MySQL throughput under load.
Common MySQL Bottlenecks for WordPress Sites
1. Autoloaded wp_options bloat
Large autoloaded entries slow down every page load.
2. WooCommerce postmeta queries
Complex JOINs often require custom indexing.
3. Heavy plugins
Analytics, search, LMS, or CRM plugins often stress MySQL.
4. Lack of object caching
Redis can reduce MySQL load dramatically.
5. Cron tasks causing query spikes
wp-cron can overwhelm smaller VPS servers.
When to Consider Scaling or Offloading
You may need stronger MySQL resources when:
WooCommerce traffic increases
Database grows beyond 2–3GB
Slow queries persist after tuning
You hit connection limits during peak hours
In those situations, upgrading RAM, enabling Redis, or moving to a dedicated database server may be necessary.
Conclusion
MySQL bottlenecks are a leading cause of slow websites on Ubuntu-based VPS servers. With tools like the Slow Query Log, Performance Schema, and essential MariaDB tuning, you can quickly identify which queries are causing issues and optimize your database for steady performance. Whether you’re using Virtualmin, Nginx, or a manual LEMP setup, these steps will equip you to diagnose and resolve MySQL performance problems confidently.
