Hardware Secrets
Home | Camera | Case | CE | Cooling | CPU | Input | Memory | Mobile | Motherboard | Networking | Power | Storage | Video | Other
First Look
Gabriel’s Blog
Main Menu
About Us
Awarded Products
Manufacturer Finder
RSS Feed
Test Your Skills
Subscribe today!
High Performance MySQL: Optimization, Backups, and Replication
High Performance MySQL: Optimization, Backups, and Replication, by Vadim Tkachenko (O'Reilly Media), starting at $20.98
Home » Networking
How to Optimize a MySQL Server
Author: Gabriel Torres 64,842 views
Type: Tutorials Last Updated: March 13, 2013
Page: 1 of 7

If you have a MySQL server, chances are that it is not running at the top of the performance it can deliver. In this tutorial, we will teach you how to optimize a MySQL server, so you can make your application (i.e., your website) run as fast as possible.

Before talking specifically about the MySQL server, we need to discuss three topics often overlooked when setting up a MySQL server: the hardware configuration of the server, the networking setup of the server, and the optimization of the source code of the application that will access the MySQL server.

In our explanations, we are assuming that you have a dedicated MySQL server.

When selecting the hardware configuration of the MySQL server, you should focus on RAM. The more, the better, since MySQL can use RAM to store frequently-accessed data (i.e., caching), which improves performance. Of course, you will need to correctly enable and adjust the caching mechanism of the MySQL server, and this is one of the goals of this tutorial.

Usually MySQL servers don’t require a lot of processing power, meaning that the CPU load will usually stay very low, usually close to zero (the exception is when you are running InnoDB tables; more on this ahead).

If the CPU load is above “1” (which means the server is queuing processing jobs), this either means that the MySQL server is using InnoDB tables or that the server ran out of usable RAM and it is using virtual memory, i.e., using a swap file in the hard drive to simulate more RAM.

If you already have a MySQL server up and running, we recommend you run the utility top to check the server load, the amount of RAM available, and whether the server is using the swap file.

The top utility
click to enlarge
Figure 1: The top utility

In Figure 1, which represents the MySQL server of a very busy website, you can see that the server load is very low. We have 16 GiB of RAM installed, and the swap file is not in use. This means the amount of RAM for this server seems to be “perfect.” However, by enabling and adjusting MySQL’s parameters, you actually may need more RAM even if the swap file is not currently in use; we will talk about this later.

If you see that the server is using the swap file, this means you need to add more RAM to the server – urgently. In this case, if the CPU load is above “1”, it will drop below “1” as soon as you add more RAM.

If the CPU load is above “1” and the server is not using the swap file, this probably means you are using InnoDB tables, which require more processing power. In this case, there are two options: to configure the database tables to use the MyISAM engine or to replace the server with a model with more processing power. We will give you a more in-depth explanation of this issue on the MyISAM vs. InnoDB page.

Print Version | Send to Friend | Bookmark Article Page 1 of 7  | Next »

Related Content
  • BIOS Setup
  • How to Build a Web Server with PHP 5 and MySQL 5 Support
  • Supermicro X9DRL-EF Motherboard
  • Migrating to Apache 2.4
  • System Mechanic Review

  • RSSLatest Content
    ASRock FM2A88X-ITX+ Motherboard
    April 27, 2015 - 2:40 AM
    GeForce GTX TITAN X Video Card Review
    April 22, 2015 - 4:00 AM
    A10-7800 CPU Review
    April 6, 2015 - 2:50 AM
    Samsung Galaxy A5 Smartphone Review
    March 31, 2015 - 2:47 AM
    A10-6800K vs. Core i3-4150 CPU Review
    March 25, 2015 - 3:15 AM

    2004-15 Clube do Hardware, all rights reserved.
    Advertising | Legal Information | Privacy Policy
    All times are Pacific Standard Time (PST, GMT -08:00)