Hardware Secrets
Home | Camera | Case | CE | Cooling | CPU | Input | Memory | Mobile | Motherboard | Networking | Power | Storage | Video | Other
Content
Articles
Editorial
First Look
Gabriel’s Blog
News
Reviews
Tutorials
Main Menu
About Us
Awarded Products
Datasheets
Dictionary
Download
Drivers
Facebook
Links
Manufacturer Finder
Newsletter
RSS Feed
Test Your Skills
Twitter
Newsletter
Subscribe today!
Search
Recommended
High Performance MySQL: Optimization, Backups, and Replication
High Performance MySQL: Optimization, Backups, and Replication, by Vadim Tkachenko (O'Reilly Media), starting at $20.80


Home » Networking
How to Optimize a MySQL Server
Author: Gabriel Torres 46,420 views
Type: Tutorials Last Updated: March 13, 2013
Page: 1 of 7
Introduction

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
    ASUS ZenFone 5 Smartphone Review
    October 15, 2014 - 7:00 PM
    ASUS AM1M-A Motherboard
    October 15, 2014 - 4:30 AM
    ASRock X99 Extreme4 Motherboard
    October 14, 2014 - 4:10 AM
    Cooler Master Elite 130 Case Review
    October 9, 2014 - 2:46 AM
    ASUS RAMPAGE V EXTREME Motherboard
    October 7, 2014 - 2:50 AM
    ASRock Fatal1ty X99M Killer Motherboard
    October 6, 2014 - 5:40 AM
    ASUS X99-DELUXE Motherboard
    September 30, 2014 - 1:07 AM
    MSI GT70 2PE Dominator Pro Laptop Review
    September 25, 2014 - 1:15 AM







    2004-14, Hardware Secrets, LLC. All rights reserved.
    Advertising | Legal Information | Privacy Policy
    All times are Pacific Standard Time (PST, GMT -08:00)