Many websites run applications that are database driven. Fetching the data from relevant databases and delivering the contents dynamically to a web page, is what most of these applications do.

As the data content or traffic increases, we’ve seen that these applications tend to get slow over time. In our role as Outsourced Tech Support for web hosting companies, we speed up MySQL server for our customers who are web hosts.

Today we’ll see what are the different scenarios that slow down the MySQL server and how we get over them.

[ Use your time to build your business. We’ll take care of your customers. Hire Our Hosting Support Specialists at $9.99/hr. ]

What causes MySQL server to slow down

A server is only as good as its components and configuration. MySQL performance slows down due to memory, CPU or I/O overhead, due to any of these issues:

1. Poor hard disk performance

A slow hard disk or a degraded RAID array can lead to higher disk access time, and slow down the database operations. Bad sectors in the hard disk or too many failures can lead to poor performance.

2. Inadequate memory

The RAM size required by MySQL to perform its transactions would vary with the type of queries and the database size. If the RAM size is not enough, this can slow down MySQL by incurring too much wait to process queries.

3. Type of transactions

Write operations are usually slower than read operations. As a result, the type of database queries and the size of data involved, makes a huge impact on the performance and speed of MySQL transactions.

4. Improper MySQL configuration

Every MySQL server differ from each other, based on the type of transactions and applications supported by it. Custom configuration settings are therefore required to fine-tune and speed up each MySQL server.

5. Unoptimized DB queries

An online business showcases its identity via its website. Many websites use custom code in their web applications. If these queries are not optimized and contains complex joins, it can cause MySQL to slow down.

Now, we’ll see how our expert technicians speed up MySQL server for web hosts using various compile time and run time tweaks.

How we speed up MySQL server

Our expertise handling different web hosts ranging from web site owners to cloud hosting providers has bestowed upon us, a clear idea regarding the various settings required for each server type.

At Bobcares, our dedicated server specialists assists customers to choose the server specifications based on their hosting requirements, which helps them deliver the best performance to their customers.

But just adding memory or CPU is not sufficient to ensure a high performing MySQL server. We perform the following optimization methods to further speed up MySQL.

1. Compile-time options to speed up MySQL

For servers that require maximum performance for MySQL, we compile the server statically, without enabling the debugging feature, to avoid overhead and increase speed.

By enabling only the required character sets for MySQL, we bring in further improvement in speed. Choosing between MyISAM and InnoDB tables is another decision we make based on the type of connections required.

2. Run time options to speed up MySQL

Tweaking the my.cnf configuration file with optimal values for these key parameters, helps us to ensure maximum speed for MySQL during run time:

1. Enabling persistent connections helps to improve performance where there are a few applications that receive thousands of connections per hour, mostly read operations.
2. Controlling the frequency of InnoDB flushing helps us to reduce the I/O usage that happens due to the server writing the transactions to the disk.
3. Setting an ideal temporary table size based on the memory and number of connections reduced the I/O overhead for performing complex queries, which speeds up MySQL.
4. Increasing the buffer pool size is done to cache frequently accessed data in memory and to minimize the I/O. This helps to speed up MySQL reducing the amount of disk I/O needed during transactions. This is ideally set as 50-70% of available RAM.
5. Disabling name resolution prevents looking up a “caller ID” on whoever is connecting to the database and to speed up the transactions.
6. Limiting the maximum number of connections possible for MySQL at any instant of time, helps us to avoid a single user from overloading the server and thus speed up queries for all users.
7. Enabling query cache is done for web applications that involve more database read operations, such as blog. Caching the query results within a limit helps us to speed up frequent read transactions.

[ Running a hosting business doesn’t have to be hard, or costly. Get world class Hosting Support Specialists at $9.99/hour (bulk discounts available) ]

3. MySQL server management to prevent slow down

Our dedicated server specialists regularly performs database monitoring and performance optimization. We’ve seen several factors that slow down MySQL.

With our periodic database audit and MySQL process check, we are able to identify these issues proactively and correct them. Here are a few things we do:

1. Block abusive processes – With our database monitoring system, we track the number of connections to the MySQL server. When abuse or attacks cause the connections to go high, we jump in and block them.
2. Optimize database queries – Some web applications have complex queries that can take a long time to execute, and cause CPU load. With the output of “slow query log”, we identify such queries and initiate actions to sort them out.
3. Fix fragmented tables – As the database size grows, it can create gaps in the tables, known as fragments. These fragments are detected and corrected, before they cause a performance bottleneck.
4. Resource monitoring – Keeping an eye on the server resources such as CPU, I/O and memory usage 24/7, helps us to detect the overhead caused by MySQL on them and take corrective actions.
5. Security hardening – To protect the database server from any indirect attack that may crash it, we secure the server and the related web applications with firewalls.
6. Load balancing – When we notice that the traffic is high constantly and not within the server limits, we design and implement load balancing setup such as replication for MySQL, which speeds it up.
7. Using high performance alternatives – In cases where MySQL is unable to handle the CPU/memory bottlenecks, we switch to stabler alternatives such as MariaDB and Percona for better performance.

[ Want to know how we handle MySQL issues and other security incidents in lightning speed? Click here to know more.. ]

4. Hardware maintenance to enhance MySQL speed

Over time and usage, we’ve seen that the server hardware deteriorates and wears out. To prevent issues due to that, we follow a routine hardware maintenance process to speed up MySQL, which includes these steps:

1. SMART checks to inspect hard disk and fix the problems promptly
2. RAM upgrades in case of not having adequate memory
3. Setting up MySQL clusters or replication solutions for better performance
4. Using multi-disk RAID arrays instead of a single disk, for better load distribution
5. Upgrading the hard disk or moving to a faster server.

We’ve been able to prevent server incidents by up to 15% by using our proactive server checks (click here to see how we improve web hosting support).