PHP-MySQL websites like WordPress are ridiculously simple to use. Which is why they are so popular.

But they are also infamous for their performance issues. Apache, PHP & MySQL often cause high server load, and drag down the website.

Here at Bobcares, we help website owners, digital marketers and web hosts recover from and prevent website performance issues.

Today, we’ll go through the top 8 reasons we’ve seen for MySQL performance issues, and how we fix them.

1. Resource bottlenecks (I/O, Memory, CPU)

As part of our Server Management Services, we monitor our customers servers 24/7, and fix uptime, security or performance issues.

In these servers, a significant number of MySQL speed issues were caused by inadequate I/O, Memory or CPU.

This is more common in VPS and Cloud servers than in dedicated servers. Virtual servers are often provisioned with arbitrary limits placed on I/O, memory and CPU.

As site traffic grows, these limits prove inadequate for its proper functioning.

We rescue websites from this situation by using PHP OpCode cache, which reduces queries to the database, and thereby reduce MySQL induced I/O and CPU usage.

In many sites, caching resolved the resource bottlenecks. However in some other sites, as traffic continued to grow, resource upgrade was our only option.

If you need help in troubleshooting resource bottlenecks, click here to talk to our experts.

2. Unoptimized server settings

Now, there are cases where the server has enough memory and spare I/O, but MySQL somehow keeps taking up all the space.

That’s usually when the settings (like heap-size, buffer, sort-table, etc.) are not optimized for the available RAM.

Web hosting servers that run DNS service, Mail service, Web service and MySQL all in one server usually face this issue. Each of these service takes its share of memory, leaving MySQL with about 1/3rd or less of available memory.

But in its default settings, MySQL tries to use all of server’s memory, causing swap to be used; And swap usage leads to poor speed and response times.

When we find an unoptimized MySQL server, we run an audit and fix incorrect settings. Some common ones we tweak are:

• innodb_buffer_pool_size – The size of buffer (cache of old queries) in InnoDB databases. Usually set to 50-70% of available memory.
• key_buffer_size – The buffer size for MyISAM databases. Usually set to 20% of available memory.
• max_connections – Number of connections available to each user. Affects memory usage per user.
• query_cache_size – The total memory size used to hold old queries.

Of course, this is not an exhaustive list of settings, but if you feel your server has enough resources, or haven’t done a MySQL optimization in some time, click here to have an expert audit your MySQL settings.

3. Other programs (like backup)

Many times when customers complain about MySQL load, we’ve seen that the real culprit is not MySQL, but some other service running in the background.

It can be backups, log processors, statistic programs, or others.

What happens is that these long-running processes takes up a lot of memory and hogs the precious I/O bandwidth.

MySQL is then forced to run on a very low available memory and I/O, causing it to use the disk-based Swap memory, thereby slowing down the server.

Ideally MySQL should run in its own independent server. However, this is not economical. So, you might run related services together – such as Web, DNS and MySQL.

But if you run anything more than that, you need to carefully calculate the resource usage of all those extra processes, and schedule the run times accordingly.

For eg. if you have to run backup processes, schedule it at a time when the traffic to MySQL is low (eg. 2 am every night).

Here at Bobcares, we profile the resource usage of all services running in our customer servers, and arrange the running time so that high usage time of one service overlaps with the low usage time of another, thereby preventing a resource crunch.

If you need help in resource profiling, our server experts can help you in a few minutes. Click here to request a consultation.

4. Bad hardware

The hard disk is the most failure prone device in a server.

Sooner or later, you’ll start seeing errors, see server crashes or even data loss.

But first, you’ll see poor server performance. Although rare, we’ve seen high MySQL load due to I/O errors from a bad hard drive.

MySQL keeps trying to fetch data, but the hard disk keeps stalling, causing a pile up of processes, thereby leading to poor server speed.

So, when we see a MySQL load issue that does not fit a resource shortage issue, or a MySQL/server optimization issue, we look at the server logs for Disk errors.

If we see bad sectors, we use filesystem tools to mark & exclude them to rescue the server. Then we schedule a disk replacement as a permanent fix to the issue.

Similar issues can happen with the memory disk or network card.

If you feel your hardware might be acting up, we can help you backup your critical data and restore it with minimal downtime. Click here to consult our MySQL experts.

5. Unoptimized MySQL queries or table design

Not every software is created equal.

There are brilliant web apps that deliver complex functions with the least resource usage.

There are also poorly coded web apps and plugins that use unoptimized database design and long queries.

MySQL provides a way to find out poorly performing queries by using the slow_query_log method. It’ll log all queries that took more than 10 seconds to complete.

To enable it, enter these settings in MySQL config (/etc/my.cnf) and restart MySQL:

slow_query_log = 1 log-slow-queries = /var/log/slow-queries.log

When we detect slow queries in our customer’s servers, we resolve it by:

• Resource optimization – In many cases, slow queries are caused by resource limits or unoptimized MySQL settings. Our first action is to look for an fix such resource issues.
• Replacing bad app/plugin – When we determine that the issue is really due to poor application code, we work with the site owner to find an alternative app or plugin for the same function.
• Fixing the DB queries – If none of the above solutions work, we bring in our PHP developers and fix the query.

6. Database table corruption or fragmentation

Busy MySQL databases go through thousands of data insertions and deletions per week.

All this data deletion leaves “holes” in the database, which is called fragmentation.

When fragmentation in a MySQL table goes above 15%, it might affect the query speed.

But this usually becomes a problem only for databases that are more than 1 GB in size.

When we find a possible MySQL performance impact due to fragmentation, we use the Optimize function to fix it.

optimize table my_table;

An optimize command will stop all queries. So we perform such a fix only during off peak hours, and if needed by posting a maintenance schedule notice to prevent online business loss.

There are also some situations where queries are blocked due to corruption in session tables, cache tables, etc.

In these cases, we use MySQL repair tools such as MyISAMchk to fix the faulty tables.

7. System software bugs

On July 1st 2015, a leap second was added to standard UTC time.

In servers running old Linux kernel versions, and which uses time servers, we’ve seen this to cause MySQL high load.

A quick fix we’ve applied in these servers is to reset the time using the command date -s “$(date)”.

Although such bugs are very rare, it is beneficial to consider this as a possibility.

8. Permission or file system errors

MySQL uses temporary tables to perform sort and join operations. These tables are stored as files in the /tmp partition of Linux servers.

When there are file system errors, or if the permissions of /tmp are changed in some way, or if it is full, we’ve seen MySQL queries failing.

So, when we see intermittent MySQL errors along with queries taking a long time to complete, we look for and fix file system issues.

None of these seem to be my issue

While we’ve today looked at the top 8 reasons for MySQL performance issues, we haven’t covered all of it to keep this short.

There could be other issues like DoS attacks, comment spam, SQLi attacks, or bad app code that’s causing the MySQL load.

So, if you’re not sure what’s causing the performance issue, it’s best to talk to a MySQL expert.

At Bobcares, we help MySQL users like you recover from, and prevent database errors. Click here to request a consultation with our experts.

Conclusion

Database performance can be affected by resource bottlenecks, unoptimized servers, hardware issues, and more. Today we’ve seen the top issues affecting MySQL, and how we fix them.