At one time or the other, every website owner will face a “slow MySQL” issue.

Then they google “how to improve MySQL performance“.

And what do they find? A lot of tech jargon that analyzes threadbare query performance.

That stuff can make anyone’s head hurt.

Which is why we’ve put together a list of things we do here at Bobcares to quickly fix MySQL performance issues in our customer websites.

 

What causes MySQL performance issues?

Some people would have you believe that it’s all due to unoptimized queries and database designs in poorly coded apps.

Sure, there are such apps, BUT the majority of issues we’ve seen are due to resource bottlenecks, unoptimized MySQL settings, server config errors and more.

Today we’ll cover some of those:

 

1. Fix 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. Optimize MySQL 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. Fix other heavy users (Is it really MySQL?)

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. Look at hardware health

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. Fix MySQL bugs or file system issues

In some very rare cases, we’ve seen MySQL bugs (eg. Leap second bug) or file permissions causing MySQL queries to pile up and fail.

We investigate this possibility when MySQL queries take many seconds to execute or even fails to execute.

A quick check of the MySQL error logs should show an error of this nature.

The solution to it is really dependent on the specific kind of issue, and is best left to a specialist in the field to troubleshoot and fix.

 

None of these seem to be my issue

While we’ve today looked at the top 5 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.