We use cookies to understand how you interact with our site, to personalize and streamline your experience, and to tailor advertising. By continuing to use our site, you accept our use of cookies and accept our Privacy Policy.

Your Digital Commerce Experts
Nexcess Logo

Tuning Your MySQL Database for Performance

February 15, 2022



Avoid a slow MySQL database and avoid a slow website! Delight your visitors with the performance! Reduce your server costs with MySQL database efficiency!

Nexcess & My SQL: Tuning Your MySQL Database for Performance

About MySQL Database Tuning

MySQL is the most popular open-source relational database management system (RDBMS) that generates meaningful structure and accessibility for large amounts of data. However, with a large amount of data and poorly designed SQL queries, we will face MySQL database performance issues. Also, slow database performance can seriously affect all your applications and users.


MySQL database performance tuning tools will help us to identify possible tuning tips to improve the database performance quickly. By following those tuning tips, MySQL database can respond to queries faster, leading to improved site performance.

The Advantages of MySQL Database Performance Tuning


The significant benefit of identifying the performance enhancement factor for a MySQL database is that it helps you to avoid over-provisioning and reduce cost by choosing the right size of your servers. It also provides you the awareness of whether moving data storage or adding server capacity will help to improve in performance or not and estimate the cost for the same. Once we have tuned the MySQL database correctly, it will deliver optimized performance results and functionality. This will lower unwanted task load and optimize the MySQL database for faster data retrieval.

How to Improve MySQL Database Performance


There are two types of MySQL database tunings available. System MySQL Performance Tuning and Software MySQL Performance Tuning. Adjusting hardware and software options at the system level is called System MySQL Performance Tuning. By tweaking the MySQL database configuration files, structuring the MySQL database to retrieve data more efficiently, and writing more efficient MySQL database queries will help us to maximize query speeds on a MySQL database at the software level with Software MySQL Performance Tuning.

System MySQL Performance Tuning Part 1: Monitor the Primary Hardware Resources 


To make your MySQL database function properly, you will need the four main hardware resources: storage, memory, processor, and network. If any of these hardware resources don’t function correctly, it will affect the MySQL database server, leading to poor MySQL database performance. If you focus on choosing the proper hardware and troubleshoot problems with the hardware, you will be able to understand their ideal setup and configuration.

Storage


If the server uses HDD (hard disk drives) for storage, you can upgrade to SSD (solid-state drives) to improve server performanceTo monitor your disk input/output rates, you can use a monitor tool like iotop or sar from the sysstat package. First, let's keep an eye on how much disk space MySQL is using compared to other resources. If the disk usage is much higher than other resources, you can consider adding more storage or upgrading the disk to faster storage.


Memory


The memory represents the total RAM in the MySQL database storage server. Adjusting or improving the memory will enhance the MySQL server's total RAM, which helps to improve performance. You can find the buffer pool size command for optimizing memory in the server configuration section. You can also adjust the memory cache to improve performance. If the server is continuously running out of memory, you can upgrade by adding more. If the server runs short of memory, the server will cache data storage (like a hard drive) to act as memory. Please note that database caching slows down the server performance.


Processor


The processor’s speed shows how fast the server is. The top command will provide detailed information on the CPU and memory usage per process or how the resources are used. When using MySQL, we will need to monitor that particular process as a usage percentage. If it is too high and the CPU is a bottleneck, and it needs to be upgraded. 


Network


It is essential to monitor network traffic to make sure the server has sufficient infrastructure to manage the load. If the network is overloading, it will cause latency, dropped packets, and even server outages. So, it is vital to make sure the server has enough network bandwidth to accommodate the normal levels of database traffic.


Software MySQL Performance Tuning Part 2: Prefer InnoDB Over MyISAM  


The InnoDB supports more advanced features and has in-built optimization mechanics. MyISAM uses an older database design, so its database design efficiency is less ideal. InnoDB uses clustered index, and it will keep data in pages, which are stored in consecutive physical blocks. If its value is very large for a page, InnoDB migrates it to another location, then indexes it. This feature helps to keep important data in the same location on the storage device, and therefore it takes the hard drive minimum time to access the data. 


Software MySQL Performance Tuning Part 3: Upgrade the MySQL Database to the Latest Version


Upgrading MySQL to the latest version is not always possible for older and legacy databases. But you will need to check the version of MySQL in use and upgrade to the latest if possible. Newer versions have improvements in performance by default. So, it helps to save time to find the solutions to typical performance tuning for common MySQL issues. In general, it’s always recommended to use default or native MySQL performance enhancement over scripting and configuration files.


Software MySQL Performance Tuning Part 1: Use MySQL Optimization Tool


The MySQL Tuner is one of the most useful MySQL optimization tools. It is an open-source Perl script that will evaluate the MySQL performance and provide the information and recommendations on which variables to enhance the MySQL server performance.


You can install the MySQL Tuner by following the below steps. 


To download MySQL tuning file, use this command: 


wget http://mysqltuner.pl/ -O mysqltuner.pl 


To change the permission of the MySQL tuning file, use this command:

chmod +x mysqltuner.pl


To run MySQL Tuner, use this command:

./mysqltuner.pl


You will be prompted to enter the MySQL administrative login and the MySQL administrative password. Enter the details and continue. The script will display statistics and recommendations for improving MySQL database performance.

Sample output: 




Software MySQL Performance Tuning Part 2: Use MySQL Query Optimization Guidelines to Optimize Queries

If the database has some function predefined in the column, it doesn’t use an index. So, we can avoid using a function in the predicate of a query. For example: 

SELECT * FROM TABLE1 WHERE UPPER(COL1)='XYZ'Copy


The UPPER notation will create a function, and the function will operate during the SELECT operation. This will double the work the query is doing. So, we should avoid it if possible. If you don't want to avoid that function in SQL, you will need to create a new function-based index or generate custom columns in the database to improve performance.

The wildcard usage will reduce database performance because the predicate like '%xyz' will cause a full table scan, which is resource intensive:


SELECT * FROM TABLE1 WHERE COL1 LIKE '%XYZ’'Copy


So, we can avoid using a wildcard (%) at the beginning of a query's predicate:

  • Instead, always specify columns in the SELECT clause instead of using ‘SELECT *’ to improve MySQL performance as unnecessary columns will cause additional load on the MySQL database and slow down its performance.

  • Using an EXPLAIN expression to start a query will read and evaluate the query. The EXPLAIN will help us to find if there are confusing structures or inefficient expressions. To avoid unintentional table scans or other performance hits, you can also adjust the phrasing of your query.

  • You can use an inner join instead of an outer join in a query and use outer join only if necessary. Unwanted usage of outer join will limit the database performance and MySQL query optimization options. So, we will face the slower execution of SQL statements.

Software MySQL Performance Tuning Part 3: Optimize Queries


A query helps us search the database for data that matches a specific value. Some query operators take a long time to run. The MySQL database performance tuning will help us to detect the queries with poor execution time and optimize queries for better run times. Some of the SQL queries can also become expensive fast, impacting the entire operation. Frequently executed queries on large datasets are slow and occupy databases. Therefore, the tables are unavailable for any other tasks.


First, we will need to optimize the queries to focus on indexing. In the help operations, we will use indexes to find data more quickly in database tables stored in the database. To make the index more efficient, we can index all the predicates by JOIN, GROUP BY, ORDER BY, and WHERE clauses. When someone wants to find information, the indexes to be moved through more quickly.

Software MySQL Performance Tuning Part 4: MySQL Query Cache


Caching the content is one of the most critical features of measuring performance. MySQL query caching will help us to improve performance by permitting data to be stored for faster access. MySQL allows database query caching, which caches the SELECT statement text and the retrieved result. If a MySQL cache query is stored and an identical query is received in the future, it will return much faster results. So, we can maximize the MySQL cache optimization process using the method.


To set up MySQL query cache, we can follow the below steps from MySQL prompt. 

Use the following command to check if MySQL query cache is available or not:

mysql> SHOW VARIABLES LIKE 'have_query_cache';


You will get the output like the example below, which means the MySQL query cache is available. 

+------------------+---------------------+
| Variable_name          | Value |
+------------------+---------------------+
| have_query_cache   | YES   |
+------------------+---------------------+


Use the following command to set up the MySQL query_cache_size:


mysql> SET GLOBAL query_cache_size = 50000;


Please note that the minimum default size is 40 KB, and the maximum size can be 32 MB


Query cache type will help us to determine the behavior of all connections.

Software MySQL Performance Tuning Part 5: Don’t Use MySQL as a Queue 

If you use MySQL as a queue, where you set up tasks to be carried out one after the other, it will slow down in any conditions where operations could have run in parallel. It will also prevent the completion of tasks in parallel. They often result in a table that contains work in process and historical data from already completed jobs. This will make the database inefficient.

Conclusion

Nexcess is already using an optimized MySQL database as per the recommendations in all the servers. If you are planning to move our cloud servers, you can contact us anytime. 


At Liquid Web, there are offering for Dedicated Server and Managed VPS Hosting options. In addition, our skilled team provides 24/7/365 security and monitoring services so that you can focus on your business. Contact our team today to learn more. 


Consider Hosting with Nexcess

Hosting optimized for the industry's leading platforms, including Managed WordPress Hosting, Managed WooCommerce Hosting, and Managed Magento Hosting:

Build Better Sites and Stores With Fully Managed Hosting from Nexcess 

Faster Speeds, Stronger Security, Inherent Scalability and Trusted Support.

Our range of hosting plans caters to any business scale. We do all the heavy lifting for you to focus on growing your business. In addition, we automatically update ore components and plugins to the latest version and ensure your hosting environment is properly optimized, secured, and updated.


Nexcess infrastructure was specially designed to keep up the best speed and performance in the industry. No traffic surge can threaten your website to go down thanks to our autoscaling technology which adds more resources to your server to handle the load. In addition, we offer always-on security monitoring and support from web hosting experts 24/7/365 and a built-in CDN with 22 locations and advanced caching for ultra-fast loading.


All hosting plans include The Nexcess 30-Day Web Hosting Money Back Guarantee.


Need a No-Code Website Building Solution? 


Are you on an accelerated schedule and already ready to move forward? If you need to get started with your Nexcess StoreBuilder Solution sooner rather than later, check out these resources:


Next Steps with a Nexcess Cloud Hosting Solution?

Read more about the Fully Managed Cloud Environment by Nexcess and its benefits for your business.

A Cloud Hosting Solution That Lets You Do Business Your Way 

We believe in the promise of cloud: scalability, security, performance, and ease of use. Together with our team, clients, and partners, we’ve built something better.

Choose From Multiple Applications 

Whether you’re a small business or a high-traffic eCommerce store, our cloud hosting solutions are designed around your needs: auto scaling, PCI compliance, and comprehensive development tools provide you with maximum dynamic flexibility in a fully managed cloud environment.


We make applications easy with environment optimizations for:

And there are many more!


We also have a variety of Nexcess support articles about how best to implement our product offerings housed in the Nexcess Knowledge Baseincluding how to get your site going with a number of different configuration options. These resources include a great article on setting this up for Migrating to Nexcess with managed WordPress and managed WooCommerce hosting

24-Hour Assistance

If you need any assistance with the above-mentioned, don't hesitate to reach out. For 24-hour assistance any day of the year, Nexcess customers can contact our support team by email or through your Client Portal.

Why Choose Nexcess?

Because we're different! Chris Lema captures "the why" in his passionate and stirring recount of a Nexcess support-related story.


Useful YouTube > Nexcess Channel Links

Resources for More Information

Need more help? The Applications, WooCommerceand WordPress sections within the Nexcess Knowledge Base are important resources for those seeking additional knowledge. 

The Applications section also contains valuable insights for those seeking additional knowledge about our other various hosted applications and platforms. Check out our related video playlists and articles below:

New Customers: Fully Managed Hosting Solutions

Not a Nexcess customer yet? Check out our fully managed hosting solutions. The option to chat with an expert is also available.

Related Articles

Paul Stubblefield
Paul Stubblefield


Nexcess Knowledge Base Owner
Content Marketing in Nexcess Digital Marketing
Nexcess, A Liquid Web Brand

Paul Stubblefield
— Technical Writer & Knowledge Management Professional

"Delivering the next generation of life-enhancing technology platforms, software solutions, and mobile-ready applications for technology pioneers, thought leaders, and market innovators in a robustly connected world."

Paul lives in Bonita Springs, Florida, USA. He is an aficionado of art, coffee, good-natured humor, lifelong learning, music, nature, pets, technology, world cultures, and his Zen Patio Garden project.