MySQL Performance: How To Leverage MySQL Database Indexing

A Mysql Indexing Logo

Throughout this tutorial, we will cover some of the fundamentals of indexing. As part of the MySQL series, we will introduce capabilities of MySQL indexing and the role it plays in optimizing database performance. Liquid Web recommends consulting with a DBA before making any changes to your production level application.

What is Indexing?

Indexing is a powerful structure in MySQL which can be leveraged to get the fastest response times from common queries. MySQL queries achieve efficiency by generating a smaller table, called an index, from a specified column or set of columns. These columns, called a key, can be used to enforce uniqueness. Below is a simple visualization of an example index using two columns as a key.

+------+----------+----------+
| ROW | COLUMN_1 | COLUMN_2 |
+------+----------+----------+
| 1 | data1 | data2 |
+------+----------+----------+
| 2 | data1 | data1 |
+------+----------+----------+
| 3 | data1 | data1 |
+------+----------+----------+
| 4 | data1 | data1 |
+------+----------+----------+
| 5 | data1 | data1 |
+------+----------+----------+

Queries utilize indexes to identify and retrieve the targeted data, even if they are a combination of keys. Without an index, running that same query results in an inspection of every row for the needed data. Indexing produces a shortcut, with much faster query times on expansive tables. A textbooks analogy may provide another common way to visualize how indexes function.
This analogy compares MySQL indexing to indexing in the back of a book.

When to Enable Indexing?

Indexing is only advantageous for huge tables with regularly accessed information. For instance, to continue with our textbook analogy, it makes little sense to index a children’s storybook with only a dozen pages. It’s more efficient to simply read the book to find each occurrence of the word “turtle” than it would be to set up and maintain indexes, query for those indexes, and then review each page provided. In the computing world, those extra tasks surrounding indexing represent wasted resources which would be better purposed by not indexing.

Without indexes, when tables grow to enormous proportions, response times suffer from queries targeting those obtuse tables. Inefficient queries manifest into latency within application or website performance. We commonly identify this latency by using the MySQL slow query log feature. You can find more details about using the slow query log feature in the first article in this series: MySQL Performance: Identifying Long Queries.
Once a colossal table hits its tipping point, it reaches the potential for downtime for applications and websites. Conducting routine evaluations for growing database establishes optimal database performance and sidesteps long queries’ inherent interruptions.

MySQL Indexing Pros vs. Cons

There are benefits and downsides to using MySQL indexing, and we’ll discuss the significant pros and cons for your consideration. These aspects will guide you to decide whether indexing is an appropriate choice for your situation.

quick data transmissions and ideal for OLAP.

What Information Does One Index?

Selecting what to index is probably the most challenging part to indexing your databases. Determining what is important enough to index and what is benign enough to not index. Generally speaking, indexing works best on those columns that are the subject of the WHERE clauses in your commonly executed queries. Consider the following simplified table:

ID, TITLE, LAST_NAME, FIRST_NAME, MAIDEN_NAME, DOB, GENDER, AGE, DESCRIPTION, HISTORY, ETC...

If your queries rely on testing the WHERE clause using LAST_NAME and FIRST_NAME then indexing by these two columns would significantly increase query response times. Alternately, if your queries rely on a simple ID lookup, indexing by ID would be the better choice.

These examples are merely a rudimentary example, and there are several types of indexing structures built-in to MySQL. The following MySQL page discusses these types of indexes in greater detail, and a recommended read for anyone considering indexing: How MySQL Uses Indexes

What is a Unique Index?

Another point for consideration when evaluating which columns to serve as the key in your index is whether to use the UNIQUE constraint. Setting the UNIQUE constraint will enforce uniqueness based on the configured indexing key. As with any key, this can be a single column or a concatenation of multiple columns. The function of this constraint ensures that there are no duplicate entries in the table based on the configured key.

UNIQUE constraints increase write speeds, a taxation of implementation.

What is a Primary Key Index?

As commonly invoked as the UNIQUE constraint the PRIMARY KEY is used to optimize indexes. This constraint ensures that the designated PRIMARY KEY cannot be of a null value. As a result, a performance boost occurs when running on an InnoDB storage engine for the table in question. This boost is due to how InnoDB physically stores data, placing null valued rows in the key out of contiguous sequence with rows that have values. Enabling this constraint ensures the rows of the table are kept in contiguous order for quicker responses.

Primary Key Index is absolutely necessary for large tables.

Managing Indexes

Now we will cover some of the basics of manipulating indexes using MySQL syntax. In examples, we will include the creation, deletion, and listing of indexes.Keywords for Managing Indexes: dbName, tableName, indexName Keep in mind, these examples have placeholder entries for the specific keywords. These keywords are self-explanatory by nature for easy reading, and below is an outline of them.

Instead of tableName you can use dbName.tableName.

Listing/Showing Indexes

Tables can have multiple indexes. Managing indexes will inevitably require being able to list the existing indexes on a table. The syntax for viewing an index is below.

SHOW INDEX FROM tableName;

SHOW INDEX FROM tableName; shows all indexes.

Indexing are present on 3 different columns.

Creating Indexes

Index creation has a simple syntax. The difficulty is in determining what columns need indexing and whether enforcing uniqueness is necessary. Below we will illustrate how to create indexes with and without a PRIMARY KEY and UNIQUE constraints.

As previously mentioned, tables can have multiple indexes. Multiple indexing is useful for creating indexes attuned to the queries required by your application or website. The default settings allow for up to 16 indexes per table, increase this number but is generally more than is necessary. Indexes can be created during a table’s creation or added on to the table as additional indexes later on. We will go over both methods below.

Creating too many indexes can add latency, but if you must then increase buffers in MySQL config.

Example: Create a Table with a Standard Index

CREATE TABLE tableName (
ID int,
LName varchar(255),
FName varchar(255),
DOB varchar(255),
LOC varchar(255),
INDEX ( ID )
);
You can create an index for several columns, using ID as the index.

Example: Create a Table with Unique Index & Primary Key

CREATE TABLE tableName (
ID int,
LName varchar(255),
FName varchar(255),
DOB varchar(255),
LOC varchar(255),
PRIMARY KEY (ID),
UNIQUE INDEX ( ID )
);
You can create an Primary Key and UNIQUE constraint over several columns.

Example: Add an Index to Existing Table

CREATE INDEX indexName ON tableName (ID, LName, FName, LOC);CREATE INDEX statement creates an index and names it.

Example: Add an Index to Existing Table with Primary Key

CREATE UNIQUE INDEX indexName ON tableName (ID, LName, FName, LOC);the CREATE UNIQUE command can add an index to a table ensuring no duplicate data.

Deleting Indexes

While managing indexes, you may find it necessary to remove some. Deleting indexes is also a very simple process, see the example below:

DROP INDEX indexName ON tableName;The DROP INDEX command lets us drop indexes on particular column.

There are many ways to optimize your database for true efficiency. If you would like to learn more or convert the search engines types available in MySQL read through our MyISAM vs. InnoDB tutorial.  Or if you are need of high functioning databases check out our MySQL product page to view different options.

Apache Performance Tuning: Configuring MPM Directives

 

Our previous article in this series focused on defining and fitting MPM to match your environment.  Building off of our last tutorial we will be discussing specific details on how to adjust the previously mentioned Apache configuration directives on the various types of Liquid Web servers.  

 

 

Core-managed CentOS 6/7 Servers

On CentOS servers, Apache configuration files are located in /etc/httpd/.

  1. Log in to the server over SSH or FTP.
  2. First, create an optimization file. It’s necessary for the optimization file to be loaded last so that it will override all other previous settings. We suggest naming the file z-optimize.conf.touch /etc/httpd/conf.d/z-optimize.conf
  3. Open file for editing with your favorite editor:vim /etc/httpd/conf.d/z-optimize.conf
  4. Input necessary directive change, using IfModule statements for compatibility.Configuration example for Centos 6/7 servers.
  5. Save the file.
  6. Reload Apache.service httpd restart

Core-managed Ubuntu 14.04/16.04 LTS Servers

On Ubuntu servers, Apache configuration files are located in /etc/apache2/.

  1. Backup existing apache2.conf file:cp -p /etc/apache2/apache2.conf{,.bak.$(date +%F_%H%M%S)}
    ls -lah /etc/apache2/apache2.conf*
  2. Open file for editing with your favorite editor:vim /etc/apache2/apache2.conf
  3. Append the necessary directive changes to the very bottom of the config file. Configuration examples for Centos 6/7, Ubuntu 14.04/16.04 servers.
  4. Save the file.
  5. Reload Apache.apache2ctl reload

Fully-managed CentOS 6/7 cPanel Servers

Out of the box, our Fully-Managed cPanel servers come with general optimization suitable for most small and mid-range sites. cPanel servers use a sophisticated template system for managing Apache configurations. cPanel typically handles all configurations seamlessly by using the WHM and cPanel interfaces. However, it is still quite simple to set up an optimization configuration file. You have the choice to compose an optimized configuration file via command line over SSH/FTP or within the WHM interface.

Command Line Method (SSH/FTP)

The Apache configuration files on cPanel servers are stored in: /usr/local/apache/conf/includes/

You can use several included files for optimization. It’s necessary for the optimization file to be loaded last so that it will override all other previous settings. For this reason, the post_ include files work best, specifically the post_virtualhost_global.conf file.

 

  1. Log in to the server with SSH or FTP.
  2. Open the post_virtualhost_global.conf file in your favorite editor. (This file is typically empty and maybe missing entirely. This is okay and not unexpected.)vim /usr/local/apache/conf/includes/post_virtualhost_global.conf
  3. Input necessary directive change, using IfModule statements for compatibility. Configuration examples for Centos 6/7, Ubuntu 14.04/16.04 servers.
  4. Save the file.
  5. Reload Apache./scripts/restartsrv_apache
  6. Reload Apache PHP FPM servers./scripts/restartsrv_apache_php_fpm


WMH Method

  1. Log in to Webhost Manager (WHM) on the necessary server.
  2. Type apache in the quick find box.
  3. Click on Apache Configuration in the Service Configuration section.
  4. Click on Include Editor.
  5. Scroll down to Post VirtualHost Include.
  6. Select All Versions from the drop down.
  7. In the box input the necessary directives for optimization. Configuration examples for Centos 6/7, Ubuntu 14.04/16.04 servers.
  8. Click the Update button when finished to save the change.
  9. On the left-hand navigation pane in the Restart Services section at the bottom click on HTTP Server(Apache).
  10. Click on the Yes button.
  11. Back to the left-hand navigation pane in the Restart Services section at the bottom click on PHP-FPM services for Apache.
  12. Click on the Yes button to complete the configuration.

Fully-managed CentOS 7 Plesk Onyx 17 Linux Servers

Out of the box, our Fully-Managed Linux Plesk servers come with general optimization suitable for most small and mid-range sites. Plesk uses mostly a standard CentOS based Apache2 installation so that it can be modified in the same manner as our Core-managed CentOS 6/7 servers:

 

On CentOS servers, Apache configuration files are located in /etc/httpd/.

 

  1. Log in to the server over SSH or FTP.
  2. First, create an optimization file. It’s necessary for the optimization file to be loaded last so that it will override all other previous settings. Suggested name: z-optimize.conf.touch /etc/httpd/conf.d/z-optimize.conf
  3. Open file for editing with your favorite editor:vim /etc/httpd/conf.d/z-optimize.conf
  4. Input necessary directive change, using IfModule statements for compatibility.Configuration examples for Centos 6/7, Ubuntu 14.04/16.04 servers.
  5. Save the file.
  6. Reload Apache.service httpd restart


Our Heroic Support™ team is equipped with talented and knowledgeable techs who can discuss ways to enhance your environment.  After reading through our series if you still have questions our techs can walk you through the outlined steps. For our customer with Fully Managed servers, we are happy to pick up the torch and take the lead by directly implementing the changes in this article.  We are just a phone call, chat or ticket away from aiding you through the process.

Apache Performance Tuning: MPM Modules

The keystone for understanding Apache server performance is by far the MultiProcessing Modules (MPMs). These modules determine the basis for how Apache addresses multiprocessing. Multiprocessing means running multiple operations simultaneously in a system with multiple central processing units (CPU Cores).

There are many MPMs to choose; however, this article focuses on the most commonly used modules found in Liquid Web Linux based servers. These modules are:

The self-regulating MPM Prefork derives its namesake from how it forks or copies itself into new identical processes preemptively to wait for incoming requests. A non-threaded process-based approach at multiprocessing, MPM Prefork runs Apache in a single master parent server process. This parent is responsible for managing any additional child servers that make up its serverpool. While using MPM Prefork, each child server handles only a single request. This focus provides complete isolation from other requests dealt with on the server. MPM Prefork is typically used for compatibility when non-threaded libraries/software, like mod_php (DSO), are required. From an optimization standpoint, MPM Prefork can be sorely lacking when compared to multi-threaded solutions, requiring vastly more resources to reach similar traffic levels as a threaded MPM. It is resource intensive due to its need to spawn full copies of Apache for every request.

MPM Prefork

Rule-of-Thumb:
Avoid using MPM Prefork whenever possible. It’s inability to scale well with increased traffic will quickly outpace the available hardware on most system configurations.

 

A hybrid pre-forking, multithreaded, multiprocessing web server. In the same fashion as MPM Prefork, MPM Worker uses the same approach with a single master parent process governing all children within its serverpool. However, unlike MPM Prefork, these children are multi-threaded processes that can handle dozens of threads (requests) simultaneously. MPM Worker has set the foundation for multithreaded multiprocessing in Apache servers which became stable in Apache 2.2. The threaded configuration allows Apache to service hundreds of requests with ease while retaining only a dozen or so child processes in memory. The MPM Worker make for both a high capacity and low resource solution for web service.

MPM Worker

Note
The KeepAliveTimeOut directive currently defines the amount of time Apache will wait for requests. When utilizing KeepAlive with MPM Worker use the smallest KeepAliveTimeout as possible (1 second preferably).

Based off the MPM Worker source code, MPM Event shares configuration directives with MPM Worker. It works nearly identical to MPM Worker except when it comes to handling KeepAlive requests. MPM Event uses a dedicated Listener thread in each child process. This Listening thread is responsible for directing incoming requests to an available worker thread. The Listening thread solves the issue encountered by MPM Worker which locks entire threads into waiting for the KeepAliveTimeout. The Listener approach of MPM Event ensures worker threads are not “stuck” waiting for KeepAliveTimeout to expire. This method keeps the maximum amount of worker threads handling as many requests as possible.


MPM EventMP

Tip:
MPM Event is stable in Apache 2.4, older versions can use MPM Worker as an alternative.

There is an assortment of additional MPMs available. These are typically part of Apache’s integration into Operating Systems other than Unix based systems. These have specific MPMs which are requirements or utilizing Apache on their respective system types. These types of MPMs are beyond the purview of this article. You can find more information on specific MPM in the MPM Defaults section of the official Apache Documentation.

MPM EventMP

Tip:
We recommend staying away from experimental and unstable MPMs. The unreliable nature of these types of software renders them unsupportable.

 

When considering optimization, it is essential to understand there is no such thing as a one-size-fits-all Apache configuration. Correctly choosing an MPM requires analysis of many moving variables like traffic, site code, server type, PHP Handler and available hardware. Every server is unique making the best MPM an entirely subjective choice.

If your application code does not support multi-threading, then your choice will inevitably be MPM Prefork purely on a compatibility basis. MPM Prefork includes software modules like mod_php (DSO). MPM Worker without KeepAlive performs very well if your application is a high-performance load balanced API system. The scalability and flexibility of MPM Event is a solid choice for hosting multiple small to medium sites in a shared hosting configuration.

Most simple servers setups operate well under the self-governing default configuration of MPM Event, making it an ideal starting point for optimization tuning. Once chosen, an MPM can then move onto Configuration Directives to review which settings pertain to server performance and optimization. Or check out our previous article in this series, Apache Performance Tuning: Swap Memory.

Install Nginx on Ubuntu 16.04

Nginx is an open source Linux web server that accelerates content while utilizing low resources. Known for its performance and stability Nginx has many other uses such as load balancing, reverse proxy, mail proxy and HTTP cache. With all these qualities it makes a definite competitor for Apache. To install Nginx follow our straightforward tutorial.

Pre-Flight Check

  • Logged into an as root and are working on an Ubuntu 16.04 LTS server powered by Liquid Web! If using a different user with admin privileges use sudo before each command.

Step 1: Update Apt-Get

As always, we update and upgrade our package manager.

apt-get update && apt-get upgrade

Step 2: Install Nginx

One simple command to install Nginx is all that is needed:

apt-get -y install nginx

Step 3: Verify Nginx Installation

When correctly installed Nginx’s default file will appear in /var/www/html as index.nginx-debian.html . If you see the Apache default page rename index.html file. Much like Apache, by default, the port for Nginx is port 80, which means that if you already have your A record set for your server’s hostname you can visit the IP to verify the installation of Nginx. Run the following command to get the IP of your server if you don’t have it at hand.

ip addr show eth0 | grep inet | awk '{ print $2; }' | sed 's/\/.*$//'

Take the IP given by the previous command and visit via HTTP. (http://xxx.xxx.xxx.xxx) You will be greeted with a similar screen, verifying the installation of Nginx!

Nginx Default Page

Note
Nginx, by default, does not execute PHP scripts and must be configured to do so.

If you already have Apache established to port 80, you may find the Apache default page when visiting your host IP, but you can change this port to make way for Nginx to take over port 80. Change Apache’s port by visiting the Apache port configuration file:

vim /etc/apache2/ports.conf

Change “Listen 80” to any other open port number, for our example we will use port 8090.

Listen 8090

Restart Apache for the changes to be recognized:

service apache2 restart

All things Apache can now be seen using your IP in the replacement of the x’s. For example http://xxx.xxx.xxx.xxx:8090

WordPress Tutorial 4: Recommended WordPress Plugins

This is part 4 in an ongoing series on WordPress. Please see Part 1: WordPress Tutorial 1: Installation Setup and Part 2: WordPress Tutorial 2: Terminology and Part 3: WordPress Tutorial 3: How to Install a New Plugin, Theme, or Widget.

__________

Now that you have WordPress installed, understand the interface, and know how to install new parts, let’s take a look at our recommended plugins.
Continue reading “WordPress Tutorial 4: Recommended WordPress Plugins”

How and Why: Enabling Apache’s Piped Logging

Apache by default logs data directly to log files. While this isn’t a bad thing, it is not your only option. Both Apache 1.x and Apache 2.x bring with them the option of enabling something called “Piped Logging”, though cPanel will only allow you to enable it for version 2.x.

Continue reading “How and Why: Enabling Apache’s Piped Logging”

Apache Configuration File Tips

The default Apache settings that cPanel sets upon install are definitely something that can be improved on. With a few small tweaks, the efficiency with which Apache runs with can be greatly improved.

Please noted: This article assumes that you are using a Linux server running Apache and cPanel or Plesk, and that you are familiar with editing files from the command line.

Continue reading “Apache Configuration File Tips”