1, 2, 3.. If your site still doesn’t load, it’s obviously not a good sign for your business..

Many server owners get servers with excellent specifications and wait for magic to happen. But server specs alone, cannot guarantee fast websites.

GBs of memory and GHz of CPU go wasted, if they are not optimized to suit your business purpose. 

In our role as Dedicated Server Engineers, we manage over 50,000 servers with around 7 million websites. Optimizing these servers for ideal performance, is a key task we perform.

Optimization includes fine-tuning the services in various ways to improve site loading speed. This optimization process will vary based on the control panel, server software, and the type of websites hosted.

cPanel MySQL optimization – Why you need it!

Most CMS websites in servers are database driven. That’s why MySQL optimization plays a major role in cPanel server management.

Here are the four major scenarios where optimization proves indispensable for your servers.

1. Database size

Over time, the databases grow in size. More and more records get added and this can lead to huge chunks of data being handled during MySQL processes.

Huge databases, if not truncated or optimized, can clog server memory to a large extent. This leads to degraded server performance and slow websites.

2. Resource shortage

Increasing memory and CPU helps improve server performance, but resource addition always has its limits. For cost efficiency, we need to ensure maximum utilisation of the available server specs.

If the services in the server are not configured for optimal resource usage, or if the server resources are not ideally distributed, a single service can hog all the resources, leading to server crash.

3. Traffic spike

A peak sale period, a new product launch, website attacks, abusive users, anything can lead to a spike in the MySQL processes in the server. An issue with one website can affect the entire server.

Apart from that, resource intensive processes such as backups and server updates can further add overhead to the I/O operations in the server, and further slow down the websites.

4. Slow queries

Each website has a unique personality. The data and queries can vary from one website to another. Many sites have custom code and queries to fulfil their purpose.

But if these queries are not optimized properly, it can lead to unwanted and complex sort and join operations, causing MySQL service to overload the server.

cPanel MySQL optimization – How we do it!

At Bobcares, we perform a 360 degree MySQL optimization process to ensure that the cPanel servers deliver their best performance. The major tasks involved are:

1. Database optimization

We perform periodic checks, repairs and optimization of databases in the cPanel servers we manage. Along with that, truncating unused records of huge DBs help us to further improve MySQL server performance.

Database updates often leave “holes” in the tables. These can cause longer data fetch times and we fix them with our periodic defragmentation process.

2. Fine-tuning MySQL server

MySQL server comes with a set of parameters, that we modify in an iterative manner to achieve ideal performance. Some of these are:

a. max_connections – In multi-user servers, this setting is used to prevent a single user hogging the entire server. In heavily loaded shared servers, this limit can be as low as 10, and in dedicated servers, it can be as high as 250.
b. innodb_buffer_pool_size – In MySQL databases enabled with InnoDB, query results are stored in a memory area called “buffer pool” for fast access. We set this value anywhere between 50-70% of available RAM for MySQL.
c. key_buffer_size – This setting determines the cache size for MyISAM tables. This is set approximately at 20% of available memory of MySQL.
d. query_cache_size – This is enabled only for single website servers, and is set to 10MB or less, depending on how slow the queries are at present.

The parameter values for MySQL server varies from server to server, and are configured based on the available memory. These values are periodically tweaked to ensure ongoing performance.

3. 24/7 service monitoring

Traffic spikes, backups, attacks, anything can lead to a high server load. These issues can happen any time of the day, and at unexpected hours.

That’s why we have a 24/7 expert monitoring system in place, for our customers’ servers. With strict triggers in place, we are able to pinpoint and fix an issue even before it affects any customer site.

4. Fixing inefficient queries

With the help of custom logs that track slow and inefficient queries, we detect the specific queries that cause high MySQL load, and take actions to fix them or fine-tune them.

5. Setting resource limits

In cPanel servers we manage, we optimize all major services like web server and MySQL server and allot resources among them in such a way that a single service cannot hog the entire server specs.

Configuring tools such as firewalls, integrating with CloudLinux utilities, etc. enable us to further limit resources per process and user, in the servers.

6. Periodic server audits

For the servers we manage, we conduct periodic server audits, which include security scans to service performance checks. This help us to detect and fix a deteriorating service, before it crashes the server.

Conclusion

Abusive users or improper resource management can lead to MySQL overhead. We’ve discussed the major scenarios of website slowness caused by MySQL in the cPanel/WHM servers managed by our Dedicated Hosting Engineers, and how we fix them.