How to Replace MySQL with Percona on Plesk CentOS 7

This article outlines the procedure for replacing the native MySQL®️ or MariaDB®️ service that is preinstalled on any typical Plesk Onyx 11 CentOS 7 server. The procedure outlines removal of the existing MySQL related binaries and replaces them with an adequate version of the Percona binaries. Once these Percona binaries are in place, a typical multistage MySQL Incremental version upgrade is processed to bring the existing databases and Percona binaries to the desired Percona 5.7 version.

Maintenance Expectations: Length & Downtime

Suggested Maintenance Duration:  90 minutes
Minimum Expected Downtime: ~30 minutes of service flapping

The expected MySQL service downtime for this procedure is less than 30 minutes of MySQL service availability going up and down repeatedly (flapping). This figure assumes that all goes smoothly and no unforeseen errors are encountered. The suggested maintenance length allows time for troubleshooting should error arise as well as an extra 30 minutes in the unlikely event that all changes need to be reverted and the maintenance plan aborted.

 

Maintenance Stages Overview

The plan is broken up into preparation tasks and stages. Preparation tasks should be completed prior to the start time of your maintenance window. Stages are conducted throughout the maintenance event window and walk through the entire procedure for accomplishing each important stage of the outline.

Preparation – Pre Maintenance Tasks

These two important tasks must be completed prior to the scheduled start time of your maintenance window:

  • Copy existing MySQL config file
  • Stage backup of MySQL database data

We use stage zero in this plan to help expedite the backup process of the MySQL data. Since the amount of data MySQL will hold varies wildly per server, it is a good practice to run an incremental backup solution, like rsync, prior to the maintenance event window. Rsync will backup the majority of static data while MySQL is still running and then will backup the incremental difference once MySQL is shut down for maintenance.

Copy existing MySQL config file:
1) Make a backup copy of the existing myself config file.

cp -pv /etc/my.cnf{,.pre-percona.bak}

Stage backup of MySQL database data:

2) Run the following rsync to clone the MySQL data. [IMPORTANT: trailing slash required]

rsync -vaH /var/lib/mysql{,.pre-percona}/

Note:
Very Large databases will take considerably longer to complete so plan accordingly.

3) Wait for step 1 to complete before starting Stage 1.

 

Stage 1 – Shutdown MySQL & Rerun Rsync Final Sync

The purpose of stage one is to ensure a pristine backup of the MySQL data is in place before proceeding. To accomplish this, it is necessary to completely stop MySQL services so all buffers are written and no additional changes are being written to the database. Once stopped, rsync is executed again to copy the differences between the now stopped pristine data and the target backup data.

1) Shutdown MySQL/MariaDB Service

systemctl stop mysql

2) Rerun the following rsync to finalize the cloned data. [IMPORTANT: trailing slash required]

rsync -vaH /var/lib/mysql{,.pre-percona}/

3) Wait for rsync to complete before moving on to Stage 3.

 

Stage 2 – Replace MariaDB 5.5 Binaries With Percona 5.5

NOTE:
MariaDB 5.5 is the version installed with Onyx 11 CentOS 7, so this is the starting point.

Replacing the existing binaries involves removing the necessary binary packages from the system using the RPM command line tool instead of yum. This allows the packages to be removed without touching any of the dependencies linking to other system packages. Since Percona, like MariaDB, is a drop-in replacement for MySQL, once the new binaries are in place the linked dependencies won’t know the difference and will function normally.

1) Remove MariaDB packages without dependencies:

rpm -e --nodeps mariadb mariadb-libs mariadb-server

2) Remove mariadb-devel package (do this as a separate command from above to avoid removal failure do to missing package)

rpm -e --nodeps mariadb-devel

3) Install Percona repository:

yum install https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-4.noarch.rpm

4) Install Percona DB server:

yum install Percona-Server-client-55 Percona-Server-server-55 Percona-Server-shared-55

5) Execute the following commands (allows InnoDB plugin to load correctly)

mv /var/lib/mysql/ibdata1 /var/lib/mysql/ibdata1.bak
cp -a /var/lib/mysql/ibdata1.bak /var/lib/mysql/ibdata1
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
cp -a /var/lib/mysql/ib_logfile0.bak /var/lib/mysql/ib_logfile0
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak
cp -a /var/lib/mysql/ib_logfile1.bak /var/lib/mysql/ib_logfile1

6) Make sure that there are no processes listening on port 3306 and kill the process if any.

netstat -tulpn | grep 3306

7) Start MySQL (now Percona 5.5)

service mysql start

8) Run mysql_upgrade script

mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`

9) [Optional]  Enable Percona XtraDB Cluster UDF from Percona Toolkit:

plesk db -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
plesk db -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
plesk db -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

10) Restart MySQL to complete upgrade procedure

systemctl start mysql

 

Stage 3 – Switch Plesk MySQL Package over to the Plesk MySQL Community package

Running Percona on Plesk is an unsupported configuration. For this reason, to satisfy dependency connections between the Plesk MySQL package and the new Percona binaries, it is necessary to switch the Plesk MySQL release over to using the Plesk MySQL Community release instead. The change is necessary to facilitate upgrading and downgrading Percona versions.

1) Create the necessary repo config file:

printf '[PLESK_17_8_11-dist] %s\nname=PLESK_17_5_3 dist %s\nbaseurl=http://autoinstall.plesk.com/PSA_17.8.11/dist-rpm-CentOS-7-x86_64/ %s\nenabled=1 %s\ngpgcheck=1 %s\n' > /etc/yum.repos.d/plesk178.repo

2) Install “plesk-mysql-server-community” package from the new repository:

yum install plesk-mysql-server-community

3) Disable /etc/yum.repos.d/plesk178.repo file as it’s no longer needed:

mv /etc/yum.repos.d/plesk178.repo{,.disabled}

4) Remove ”plesk-mysql-server” package without touching dependencies.

rpm -e --nodeps plesk-mysql-server

 

Percona should now be installed and working on the base 5.5 version. The next stages are identical to the typical MySQL upgrade process where binaries are upgraded, and then the schema updates are made manually using the mysql_upgrade tool.

Upgrade Percona 5.5 to 5.6

1) Stop MySQL service:

service mysql stop

2) Remove Percona 55 packages without dependencies:

rpm -e --nodeps Percona-Server-shared-55 Percona-Server-client-55 Percona-Server-server-55

3) Install Percona 56 packages

yum install Percona-Server-shared-56 Percona-Server-client-56 Percona-Server-server-56

4) Make sure that there are no processes listening on port 3306 and kill the process if any.

netstat -tulpn | grep 3306

5) Start the MySQL service:

service mysql start

6) Upgrade MySQL tables:

mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`

 

Stage 4 – Upgrade Percona 5.6 to 5.7

1) Stop MySQL service:

service mysql stop

2) Remove Percona 56 packages without dependencies:

rpm -e --nodeps Percona-Server-shared-56 Percona-Server-client-56 Percona-Server-server-56

3) Install Percona 57 packages

yum install Percona-Server-shared-57 Percona-Server-client-57 Percona-Server-server-57

4)  Make sure that there are no processes listening on port 3306 and kill the process if any.

netstat -tulpn | grep 3306

5) Start the MySQL service:

service mysql start

6) Upgrade MySQL tables:

mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`

 

Stage 5 – Finalizing Upgrade

The system should now be running Percona 5.7 and be fully accessible by applications. At this stage, any sites or application using a database should be tested and any errors investigated.

Install PowerDNS and MariaDB on an Ubuntu 16.04 Server

What is PowerDNS?

PowerDNS (pdns) is a DNS server written in C++. It originally started as proprietary software but was released in 2002 under the open-source GPL license. It is widely used for DNS hosting and is one of the most popular DNS servers for hosting domains using DNSSEC. It can be installed on most UNIX type operating systems. It features support for a large number of backends, including but not limited to MySQL, SQLite3, PostgreSQL, Oracle, Sybase, LDAP, Microsoft SQL Server, and BIND like plain text files. It also has a built-in web server for use with their JSON/REST API. PowerDNS can be the authoritative name server for your domains but can also be used as a recursive DNS resolver. The PowerDNS Authoritative Server and the PowerDNS Recursor are separate programs. Continue reading “Install PowerDNS and MariaDB on an Ubuntu 16.04 Server”

Upgrading MariaDB 10.0 to 10.3.9 on Ubuntu 16.04

MariaDB is quickly becoming the de facto open-source database software to use in development, production, and even enterprise environments. Our very own Cloud Sites product uses the newest in MariaDB as it’s mostly known for being a fork and drop-in replacement to MySQL, which is created and maintained by the original MySQL developers.

Continue reading “Upgrading MariaDB 10.0 to 10.3.9 on Ubuntu 16.04”

MySQL Performance: InnoDB Buffers & Directives

As discussed earlier in our MySQL Performance series, the InnoDB storage engine is designed to be a high-performance database for very large datasets. The row-locking technique it uses allows for many read and write requests to occur on a single table concurrently. This is a vast improvement in speed over traditional table-locking of the MyISAM engine. This part of our MySQL Performance series will focus on configuring InnoDB tables for maximum concurrency with minimal disk input/output (I/O).

Disk I/O & Temporary Tables

Disk I/O & Temporary Tables.

A simple and effective change that can provide a noticeable performance boost immediately is the practice of forcing all temporary table writes into memory instead of writing to a hard disk. Writing temp tables to RAM reduces the impact MySQL has on overall system disk I/O, which greatly improves performance. This change is done by leveraging the /dev/shm tmpfs partition in Linux. This special device gives server processes the ability to write files directly into RAM as if it were a standard disk drive.

 

InnoDB Log File Size

InnoDB Log File Size

The InnoDB log file is not your traditional log file. It’s a special binary redo log, which InnoDB uses to store redo activity for every write request handled by the server. Every write query executed gets a redo entry in the log file so that change can be recovered in the event of a crash. Because of this reason, the InnoDB log file size plays a critical role in MySQL write query performance, particularly on write-heavy databases.

Rule of Thumb: innodb_log_file_size
MySQL®️ Recommends: “The larger the value, the less checkpoint flush activity is required in the buffer pool, saving disk I/O.” A log file size of 1GB is sufficient for most situation. This may need to be raised when database growth exceeded several dozen GB in size.
Notice
MySQL®️ Warns: “Larger log files also make crash recovery slower, although improvements to recovery performance make log file size less of a consideration than it was in earlier versions of MySQL. “

Due to the special nature of the InnoDB Redo Logs, this setting cannot be changed without first deleting the existing InnoDB log files. The following procedure for adjusting the innodb_log_file_size directive must be followed before MySQL will start again.

Changing innodb_log_file_size Procedure:

  1. Stop MySQL Serviceservice mysql stop
  1. Update innodb_log_file_size within [mysqld] header in /etc/my.cnf file.innodb_log_file_size=1G
  1. Move the existing log files out of the mysql directorymv /var/lib/mysql/ib_logfile* /backup
  1. Start MySQL Serviceservice mysql start

 

InnoDB Buffer Pool (IBP)

The InnoDB Buffer Pool plays a critical role in MySQL Performance. The IBP is a reserved portion of system memory where InnoDB table and index data are cached. This allows frequently accessed data to be returned quickly, without the need of spinning up a physical hard drive. The more InnoDB tablespace that is cached in memory, the less often MySQL will access physical disks, which manifests as faster query response times and improved overall system performance.

There are multiple directives which control IBP behavior. Tuning these to match the servers existing tablespace data, with some room to grow, will optimize the performance of any queries using InnoDB tables. Since InnoDB is the recommended engine for very large tables, optimizing the IBP gives major performance gains.

 

InnoDB Buffer Pool Size

InnoDB Buffer Pool Size

The larger the IBP, the more InnoDB functions as a high-performance in-memory database. However, setting the size too large can be detrimental to overall system performance. A balance must be struck between the IBP size and needed memory for other system services. In an ideal configuration, the IBP should be as large as possible, without causing excessive swapping (i.e., Thrashing) to occur. There are some simple rules to follow when making these determinations.

Rule of Thumb: innodb_buffer_pool_size (Dedicated Servers)

When MySQL is the only major service on a server.

The recommended configuration by MySQL is for innodb_buffer_pool_size to use as much as 80% of the system’s total physical memory.

Calculate innodb_buffer_pool_size Script: (Dedicated Server)

awk '
/MemTotal/{
$3="GB"
$2=sprintf("%.0f",$2/1048576)
print
$1="  Mem80%:"
$2=sprintf("%.0f",$2*.8)
print
}' /proc/meminfo
Example Output:

MemTotal: 15 GB
Mem80%: 12 GB

Rule of Thumb: innodb_buffer_pool_size (Shared Servers)

When MySQL runs alongside other major services like Web, Email, etc... (e.g. cPanel, Plesk)

Due to their varied resource requirements, there is no one-size-fits-all calculation for shared servers. It becomes necessary to calculate the memory requirement needs of all other critical services on the server and subtract those from total system memory to find a proper amount of available memory which can be assigned to innodb_buffer_pool_size.

A simplified method is to use a generic calculation. A conservative starting point is assigning between 30 and 80 percent of available system memory, instead of total physical memory. However, determining the exact setting may require some guesswork and testing.

The following awk script reads MemAvail from /proc/meminfo and provides a selection of percentage based calculations to choose for the server.

Calculate innodb_buffer_pool_size Script: (Shared Server)

awk '
/MemAvail/{
$3="G";_=$2
$2=sprintf("% 3.0f",_/1048576)
print
for (i=80;i>=25;i-=10) {
$1="MemAvail_"i"%:"
$2=sprintf("% 3.0f",_*(i/100)/1048576)
$4=sprintf("| %.0f M",_*(i/100)/1024)
print
}
}' /proc/meminfo

Example Output:

MemAvailable:  10 G
MemAvail_80%:   8 G | 8405 M
MemAvail_70%:   7 G | 7354 M
MemAvail_60%:   6 G | 6304 M
MemAvail_50%:   5 G | 5253 M
MemAvail_40%:   4 G | 4203 M
MemAvail_30%:   3 G | 3152 M

 

InnoDB Buffer Pool Instances

InnoDB Buffer Pool Instances

The innodb_buffer_pool_instances directive controls the number of memory pages Innodb creates. MySQL ignores this directive unless the innodb_buffer_pool_size is greater than 1G/1024M. When larger than 1G, the buffer pool is divided up into a number of equal sized memory pages specified by this directive.

Rule of Thumb: innodb_buffer_pool_instances
MySQL®️ Recommends: “For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1GB.”

  ‣ Rule Exception: innodb_buffer_pool_instances should not exceed Total_IO_Threads

Total_IO_Threads=(innodb_read_io_threads + innodb_write_io_threads)

Preference a 1:1 Ratio for: Total_Instances:Total_IO_Threads

 

InnoDB I/O Threads

InnoDB I/O ThreadsInnoDB I/O Threads

Input/Output threads are sub-processes of MySQL which directly access the IBP memory pages. There are two types of I/O threads: innodb_read_io_threads and innodb_write_io_threads. These threads read and write, respectively, to the individual memory pages created by innodb_buffer_pool_instances.

The default value for both of these thread types is 4, making a total of 8 threads running concurrently. This pairs up with the default number of memory pages created when innodb_buffer_pool_size is larger than 1G (x8). This synergy is a key factor when improving InnoDB performance on very large database servers with large volumes of RAM and CPU cores. When increasing innodb_buffer_pool_instances past the default of 8, it becomes optimal to increase the number of threads to handle the extra memory pages concurrently. The goal being, to keep the synergy of total I/O threads equal to total memory pages:

( innodb_read_io_threads + innodb_write_io_threads ) = innodb_buffer_pool_instances

When to change InnoDB I/O Threads

Two conditions should be satisfied before adjusting I/O threads for a server.

  1. When innodb_buffer_pool_size is larger than 8 Gigabytes, this means there are more memory pages than I/O threads to handle them concurrently.
  2. When the server has more than 8 CPU cores to devote to the MySQL Service.

It is not enough to merely divide the innodb_buffer_pool_instances in half to get equal numbers of read and write threads. The goal is to increase the number of threads primarily used by the specific server’s query workload. For instance, if your server reads more data than it writes, increasing write threads would be counter-productive. The same holds true in reverse.

How to Calculate MySQL Read:Write Ratio

The global statistics kept by a MySQL server can be leveraged to determine a systems Read:Write Ratio. The following MySQL queries can be used to calculate the Total_Reads and Total_Writes of a server.

Total_Reads = Com_select

SHOW GLOBAL STATUS LIKE 'Com_select';

Total_Writes = Com_delete + Com_insert + Com_replace + Com_update

SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_insert', 'Com_update', 'Com_replace', 'Com_delete');

  • If the Total_Reads is greater than Total_Writes, the server is considered Read_Heavy.
  • If the Total_Writes is greater than Total_Reads, the server is considered Write_Heavy.

The following awk script will print the necessary statistics variables from MySQL and automatically calculate a simplified Read:Write Ratio to help determine whether the system in question is Read_Heavy or Write_Heavy.

Calculate Read:Write Ratio Script:

mysql -e 'SHOW GLOBAL STATUS;'|\
awk '
$1~/Com_(delete|insert|update|replace)$/{
w += $2
printf $0 "\t + Total_Writes = " w "\n"
}
$1~/Com_(select)/{
r += $2
printf $0 "\t + Total_Reads = " r "\n"
}
END {
printf "\nRead:Write Ratio:\n\t" r ":" w " "
if (r >= w) {
R=sprintf("%.0f",r/w)
print R ":1"
} else {
W=sprintf("%.0f",w/r)
print "1:" W
}
}'

Example Output:

Com_delete    14916     + Total_Writes = 14916
Com_insert    87413     + Total_Writes = 102329
Com_replace   0         + Total_Writes = 102329
Com_select    675528    + Total_Reads = 675528
Com_update    18976     + Total_Writes = 121305
Read:Write Ratio:
675528:121305 6:1

Note:
Adjust the first line’s mysql statement as needed to connect to the appropriate server. e.g.,

mysql -h localhost -u root -p -e 'SHOW GLOBAL STATUS;' |\

Hardware CPU Core Considerations

When adjusting innodb_read_io_threads or innodb_write_io_threads, keep the total threads between the two equal to the number of CPU cores available to MySQL. This ensures maximum concurrency as each memory page can be accessed simultaneously by each individual CPU core.

Rule of Thumb: InnoDB I/O Threads (High-Performance) ‣innodb_read_io_threads + innodb_write_io_threads
The total number of InnoDB I/O Threads should not surpass the total number of CPU cores available to MySQL.

When increasing Innodb I/O Threads, increase only the read or write threads depending on whether the server is Read_Heavy or Write_Heavy.

Other Directives

There are several more advanced techniques for fine tuning the InnoDB Buffer Pool and its behavior. These are beyond the scope of this article. However, you can find more details about these techniques on the MySQL website here: 15.5.1 Buffer Pool.

 

MySQL Performance: Converting MySQL to MariaDB

As we explored in our previous article of our MySQL Perfomance Series: MySQL vs. MariaDB there are very few downsides to using MariaDB over standard MySQL. Our high-availbility MariaDBs have proven itself to be a worthy successor with easily migitated drawbacks.  As the last article in our series we will focus on upgrading to various MySQL and MariaDB version on the following servers:

CentOS 6/7

Ubuntu 14.04/16.04

Continue reading “MySQL Performance: Converting MySQL to MariaDB”

MySQL Performance: MySQL vs. MariaDB

MariaDB and MySQL logos.

We continue our series on MySQL Optimization by focusing on the differences and similarities between MariaDB and MySQL. MariaDB is a popular alternative to using traditional MySQL for database hosting. The free market of hosting allows additional actors to compete with the long-standing database solution, MySQL. MariaDB is the most prominent alternative to MySQL and is a favorite among industry giants such as Google, Craigslist, Wikipedia, Arch Linux, RedHat, CentOS, Fedora, and cPanel. In a direct performance comparison, MariaDB leaps to the head of the pack through performance optimizations, easy of conversion and compatibility.

Why Switch to MariaDB?

MariaDB is mostly a clone of MySQL, but it also provides several improvements over running the standard MySQL instance. MariaDB aims for a drop-in replacement to MySQL, which makes the switch painless and straightforward. Combine the simplicity of switching with a long list of improvements, MariaDB brings both performance and cutting-edge features to the table. Below are some of its selling points over standard MySQL.

More Storage Engines Options: There is 12 new storage engine built into MariaDB. Among these are CONNECT, Spider, and SphinxSE. Visit their Storage Engines page for a complete list of these engines, how they function, and ways to leverage them to optimize your database.

Speed improvements: MariaDB sports many new speed improvements compared directly with standard MySQL. This improved performance makes MariaDB stand apart from the baseline performance of traditional MySQL servers. Like MySQL, MariaDB has dozens of features for speed optimization including disk access, JOIN and EXPLAIN improvements, subquery, derived tables/views, execution control, and optimizer control.

Faster Indexes/Cache: When using the MEMORY storage engine, MariaDB can complete INSERT statements up to 24% faster than traditional MySQL servers, along with CHECKSUM TABLE and MyISAM Segment Key Cache being 4x faster.

Speedier and Larger Connection Pool: MariaDB benefits from an improved pool of threads that run faster and support up to 200,000+ connections where standard MySQL falls short.

Improved Replication: MariaDB sports faster and safer replication with updates being up to 2x faster than with traditional MySQL Replication setups. Now possible, parallel replication allows the existence of Active/Active or Master/Master configurations. MariaDB replication is backward compatible with MySQL servers, so migrating your cluster to MariaDB is possible by utilizing one node at a time.

New Extensions/Features: There are several new extension and features, to name a few, the WITH, JSON and KILL statements. DECIMAL sees an increased from 30 to 38 decimals while KILL ALL queries for a specified user.

List of Features: Available MariaDB’s website is a comprehensive list of improvements and features, located here: MariaDB versus MySQL-Features.

 

What is the Downside to Using MariaDB?

The MariaDB project is entirely open-source and free, unlike MySQL which uses dual licensing to keep its Enterprise edition specific features proprietary. The MariaDB developers actively update and enhance with cutting-edge features that standard MySQL does not carry.

Missing Features: MySQL uses some proprietary code in its Enterprise Edition. MariaDB does not have access to this propriety content and is a closed source, meaning the features are only available to MySQL Enterprise users.

Mitigation: MariaDB has addressed this concern through allowing viable open-source alternative plugins for MariaDB, providing the same functionality that the MySQL Enterprise Edition offers. This allows the vast majority of standard MySQL and MySQL Enterprise Edition users the option to switch to MariaDB, taking advantage of its robust performance enhancing features.

Delayed MySQL Compatibility: MariaDB originally forked from MySQL 5.5, so this is the base starting point for the MariaDB source code. Meaning that newer features and bug fixes developed for standard MySQL after version 5.5 are not part of the existing MariaDB source code.

Mitigation: MariaDB conducts monthly merges of the standard MySQL source code to ensure both compatibility and feature/bug-fix adoption. This kind of regular code merging, allows MariaDB to maintain its allure as a drop-in replacement for practically any existing versions of standard MySQL. However, there will always be a delay causing MariaDB to lag behind MySQL’s newer features and bug patches.

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.

MySQL Performance: Identifying Long Queries

Every MySQL backed application can benefit from a finely tuned database server. The Liquid Web Heroic Support team has encountered numerous situations over the years where some minor adjustments have made a world of difference in website and application performance. In this series of articles, we have outlined some of the more common recommendations that have had the largest impact on performance.

Preflight Check

This article applies to most Linux based MySQL servers. This includes, but is not limited to, both Traditional Dedicated and Cloud VPS servers running a variety of common Linux distributions. The article can be used with the following Liquid Web system types:

  • Core-managed CentOS 6x/7x
  • Core-managed Ubuntu 14.04/16.04
  • Fully-managed CentOS 6/7 cPanel
  • Fully-managed CentOS 7 Plesk Onyx 17
  • Self-managed Linux servers
Note
Self-managed systems, which have opted out of direct support can take advantage of the techniques discussed here, however, the Liquid Web Heroic Support Team cannot offer direct aid on these server types.

This series of articles assumes familiarity with the following basic system administration concepts:

 

What is MySQL Optimization?

There is no clearly defined definition for the term MySQL Optimization. It can mean something different depending on the person,  administrator, group or company. For the sake of this series of articles on MySQL Optimization, we will define MySQL Optimization as:  The configuration of a MySQL or MariaDB server which has been configured to avoid commonly encountered bottlenecks discussed in this series of articles.

What is a bottleneck?

Very similar to the neck on a soda bottle, a bottleneck as a technical term is a point in an application or server configuration where a small amount of traffic or data can pass through without issue. However, a larger volume of the same type of traffic or data is hindered or blocked and cannot operate successfully as-is. See the following example of a configuration bottleneck:

Visual Difference between Optimized and Non-Optimized DatabaseIn this example, the server is capable of handling 10 connections simultaneously. However, the configuration only accepts 5 connections. This issue would not manifest so long as there were 5 or less connections at one time. However, when traffic ramps up to 10 connections, half of them start to fail due to unused resources in the server configuration. The above examples illustrates the bottleneck shape where it derives its name versus an optimized configuration which corrects the bottleneck.

When Should I Optimize My MySQL database?

Ideally, database performance tuning should occur regularly and before productivity is affected. It is best practice behavior to conduct weekly or monthly audits of database performance to prevent issues from adversely affecting  applications. The most obvious symptoms of performance problems are:

  • Queries stack up and never completing in the MySQL process table.
  • Applications or websites using the database become sluggish.
  • Connection timeouts errors, especially during peak hours.

While it is normal for there to be several concurrent queries running at one time on a busy system, it becomes a problem when these queries are taking too long to finish on a regular basis. Although the specific threshold varies per system and per application, average query times exceeding several seconds will manifest as a slowdown within attached websites and applications. These slowdowns can sometimes start out small and go unnoticed until a large traffic surge hits a particular bottleneck.

Identifying Performance Issues

Knowing how to examine the MySQL process table is vital for diagnosing the specific bottleneck being encountered. There is a number of ways to view the process table depending on your particular server and preference. For the sake of brevity this series will focus on the most common methods used via Secure Shell (SSH) access:

 

Using The MySQL Process Table: Method 1

Use the ‘mysqladmin’ command line tool with the flag ‘processlist’ or ‘proc’ for short. (Adding the flag ‘statistics’ or ‘stat’ for short will show running statistics for queries since MySQL’s last restart.)

Command:

mysqladmin proc stat

Output:

 +-------+------+-----------+-----------+---------+------+-------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+------+-----------+-----------+---------+------+-------+--------------------+----------+
| 77255 | root | localhost | employees | Query | 150 | | call While_Loop2() | 0.000 |
| 77285 | root | localhost | | Query | 0 | init | show processlist | 0.000 |
+-------+------+-----------+-----------+---------+------+-------+--------------------+----------+
Uptime: 861755 Threads: 2 Questions: 20961045 Slow queries: 0 Opens: 2976 Flush tables: 1 Open tables: 1011 Queries per second avg: 24.323

Pro: Used on the shell interface, this makes piping output to other scripts and tools very easy.
Con: The process table’s info column is always truncated so does not provide the full query on longer queries.

Using The MySQL Process Table: Method 2

Run the ‘show processlist;’ query from within MySQL interactive mode prompt. (Adding the ‘full’  modifier to the command disables truncation of the Info column. This is necessary when viewing long queries.)

 

Command:

show processlist;

Output:
MariaDB [(none)]> show full processlist;
+-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+
| 77006 | root | localhost | employees | Query | 151 | NULL | call While_Loop2() | 0.000 |
| 77021 | root | localhost | NULL | Query | 0 | init | show full processlist | 0.000 |
+-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+

Pro: Using the full modifier allows for seeing the full query on longer queries.
Con: MySQL Interactive mode cannot access scripts and tools available in the shell interface.

Using The slow query log

Another valuable tool in  MySQL is the included slow query logging feature. This feature is the preferred method for finding long running queries on a regular basis. There are several directives available to adjust this feature. However, the most commonly needed settings are:

 

slow_query_logenable/disable the slow query log
slow_query_log_filename and path of the slow query log file
long_query_timetime in seconds/microseconds defining a slow query

These directives are set within the [mysqld] section of the MySQL configuration file located at /etc/my.cnf and will require a MySQL service restart before they will take affect. See the example below for formatting:

Caution
There is a large disk space concern with the slow query log file, which needs to be attended to continually until the slow query log feature is disabled. Keep in mind, the lower your long_query_time directive the faster the slow query log fills up a disk partition.
[mysqld]
log-error=/var/lib/mysql/mysql.err
innodb_file_per_table=1
default-storage-engine=innodb
innodb_buffer_pool_size=128M
innodb_log_file_size=128M
max_connections=300
key_buffer_size = 8M
slow_query_log=1
slow_query_log_file=/var/lib/mysql/slowquery.log
long_query_time=5

Once the slow query log is enabled you will need to periodically follow-up with it to review unruly queries that need to be adjusted for better performance. To analyze the slow query log file, you can parse it directly to review its contents. The following example shows the statistics for the sample query which ran longer that the configured 5 seconds:

Caution
There is a performance hit taken by enabling the slow query log feature. This is due to the additional routines needed to analyze each query as well as the I/O needed to write the necessary queries to the log file. Because of this, it is considered best practice on production systems to disable the slow query log. The slow query log should only remain enabled for a specific duration when actively looking for troublesome queries that may be impacting the application or website.
# Time: 180717 0:23:28
# User@Host: root[root] @ localhost [] # Thread_id: 32 Schema: employees QC_hit: No
# Query_time: 627.163085 Lock_time: 0.000021 Rows_sent: 0 Rows_examined: 0
# Rows_affected: 0
use employees;
SET timestamp=1531801408;
call While_Loop2();

Optionally, you can use the mysqldumpslow command line tool, which parses the slow query log file and groups like queries together except values of number and string data:
~ $ mysqldumpslow -a /var/lib/mysql/slowquery.log
Reading mysql slow query log from /var/lib/mysql/slowquery.log
Count: 2 Time=316.67s (633s) Lock=0.00s (0s) Rows_sent=0.5 (1), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost
call While_Loop2()
(For usage information visit MySQL documentation here: mysqldumpslow – Summarize Slow Query Log Files)

So concludes the first part of our Database Optimization series and gives us a solid basis to refer back to for benchmark purposes. Though database issues can be complicated, our series will break down these concepts to provide means to optimize your database through database conversion, table conversion, and indexing.

 

Choosing Your Cloud Sites Technology Setup

Behind Cloud Sites, racks full of both Linux and Windows servers power over 100,000 sites and applications. Every Windows-based page is served from clusters built and optimized especially for Windows, and every Linux-based page is served from clusters built and optimized especially for Linux. We use advanced load balancing technologies to automatically detect the type of technology you are running and route each request to the proper pool of servers.

This is a great example of the power of cloud computing, since you no longer have to make a hosting choice between Linux and Windows. Both PHP and .NET are included, allowing you to choose the technology you need site by site.
Continue reading “Choosing Your Cloud Sites Technology Setup”