In this article by Altaf Hussain, author of the book Learning PHP 7 High Performance we will see how databases play a key role in dynamic websites. All incoming and outgoing data is stored in databases. So if the database for a PHP application is not well-designed and optimized, then it will affect the application performance tremendously. In this article, we will be looking into the ways to optimize our PHP application database.

(For more resources related to this topic, see here.)

MySQL

MySQL is the most used Relational Database Management System (RDMS) for the web. It is open source and has a free community version. It provides all those features, which can be provided by an enterprise-level database.

The default settings provided with the MySQL installation may not be so good for performance, and there are always ways to fine-tune settings to get an increased performance. Also, remember that your database design also plays a role in performance. A poorly designed database will have an effect on overall performance.

In this article, we will discuss how to improve the MySQL database performance.

We will be modifying the MySQL configuration my.cnf file. This file is located in different places in different OSes. Also, if you are using XAMPP, WAMP, and so on, on Windows, this file will be located in those respective folders. Whenever my.cnf is mentioned, it is assumed that the file is open no matter which OS is used.

Query Caching

Query Caching is an important performance feature of MySQL. It caches SELECT queries along with the resulting dataset. When an identical SELECT query occurs, MySQL fetches the data from memory; hence, the query is executed faster. Thus, this reduces the load on the database.

To check whether query cache is enabled on a MySQL server or not, issue the following command in your MySQL command line:

SHOW VARIABLES LIKE 'have_query_cache';

This command will display an output, as follows:

This result set shows that query cache is enabled. If query cache is disabled, the value will be NO.

To enable query caching, open up the my.cnf file and add the following lines. If these lines are present, just uncomment them if they are commented:

query_cache_type = 1
query_cache_size = 128MB
query_cache_limit = 1MB

Save the my.cnf file and restart the MySQL server. Let’s discuss what these three configurations mean.

query_cache_size

The query_cache_size parameter means how much memory will be allocated. Some will think that the more memory used, the better this is; but this is just a misunderstanding. It all depends on the size of the database, the types of queries, and ratios between read and writes, hardware and database traffic, and so on. A good value for query_cache_size is in between 100 MB and 200 MB. Then, monitor the performance and the other previously mentioned variables on which the query cache depends, and adjust the size. We have used 128 MB for a medium range traffic magento website, and it is working perfectly. Set this value to 0 to disable the query cache.

query_cache_limit

This defines the maximum size of a query dataset to be cached. If the size of a query dataset is larger than this value, it won’t be cached. The value of this configuration can be guessed by finding out the largest select query and the size of its returned dataset.

query_cache_type

The query_cache_type parameter plays a weird role. If query_cache_type is set to 1, then the following may occur:

• If query_cache_size is 0, then no memory is allocated and query cache is disabled
• If query_cache_size is greater than 0, then query cache is enabled, memory is allocated, and all queries that do not exceed query_cache_limit and use the SQL_NO_CACHE option will be cached

If query_cache_type value is 0, then the following occurs:

• If query_cache_size is 0, then no memory is allocated and the cache is disabled
• If query_cache_size is greater than 0, then the memory is allocated, but nothing is cached, that is, the cache is disabled

Storage Engines

Storage Engines (or Table Types) are a part of core MySQL and are responsible for handling operations on tables. MySQL provides several storage engines, and the two most widely-used are MyISAM and InnoDB. Both storage engines have their own pros and cons, but InnoDB is always prioritized. MySQL started to use InnoDB as its default storage engine starting from version 5.5.

MySQL provides some other storage engines, which have their own purposes. During the database design process, which table should use which storage engine can be decided. A complete list of storage engines for MySQL 5.6 can be found at http://dev.mysql.com/doc/refman/5.6/en/storage-engines.html.

Storage engine can be set at database level, which will be then used as default storage engine for each newly created table. Note that the storage engine is table-based and different tables can have different storage engines in a single database. What if we have a table already created and we want to change its storage engine? This is easy. Let’s say our table name is pkt_users and its storage engine is MyISAM and we want to change it to InnoDB, then we will use the following MySQL command:

ALTER TABLE pkt_users ENGINE=INNODB;

This will change the storage engine of the table to InnoDB.

Now, let’s discuss the difference between the two most widely-used storage engines MyISAM and InnoDB.

MyISAM

A brief list of features that are or are not supported by MyISAM is as follows:

• MyISAM is designed for speed, which plays best with SELECT statement.
• If a table is more static, that is, the data in that table is less frequently updated or deleted and mostly the data is only fetched, then MyISAM is best for this table.
• MyISAM supports table-level locking. If a specific operation needs to be performed on data in a table, then the complete table can be locked. During this lock, no operation can be performed on this table. This can cause performance degradation if the table is more dynamic, that is, the data is frequently changing in the table.
• MyISAM does not have support for Foreign Keys (FK).
• MyISAM supports fulltext search.
• MyISAM does not support transactions. So, there is no support for commit and rollback. If a query on a table is executed, it is executed and there is no coming back.
• Data compression, Replication, Query Cache, and Data encryption is supported.
• Cluster database is not supported.

InnoDB

A brief list of features that are or are not supported by InnoDB is as follows:

• InnoDB is designed for high reliability and high performance when processing a high volume of data.
• InnoDB supports row-level locking. It is a good feature and is great for performance. Instead of locking the complete table like MyISAM, it locks only the specific rows for SELECT, DELETE, or UPDATE operations; and during these operations, other data in this table can be manipulated.
• InnoDB supports Foreign Keys and support forcing Foreign Keys Constraints.
• Transactions are supported. Commits and rollbacks are possible; hence, data can be recovered from a specific transaction.
• Data Compression, Replication, Query Cache, and Data encryption is supported.
• InnoDB can be used in a cluster environment, but it does not have full support. However, the InnoDB tables can be converted to an NDB storage engine, which is used in a MySQL cluster by changing the table engine to NDB.

In the following sections, we will discuss some more performance features that are related to InnoDB. Values for the following configuration are set in the my.cnf file.

InnoDB_buffer_pool_size

This setting defines how much memory should be used for InnoDB data and indexes loaded into memory. For a dedicated MySQL server, the recommended value is 50-80% of the installed memory on the sever. If this value is set to a high value, then there will be no memory left for the operating system and other subsystems of MySQL, such as transaction logs. So, let’s open our my.cnf file, search for innodb_buffer_pool_size, and set the value in between the recommended value (50-80%) of our RAM.

Innoddb_buffer_pool_instances

This feature is not that widely-used. This feature enables multiple buffer pool instances to work together to reduce the chances of memory contentions on 64 bits’ system and with a large value for innodb_buffer_pool_size.

There are different choices on which the value for innodb_buffer_pool_instances should be calculated. One way is to use one instance per GB of innodb_buffer_pool_size. So, if the value of innodb_bufer_pool_size is 16 GB, we will set innodb_buffer_pool_instances to 16.

InnoDB_log_file_size

Inno_db_log_file_size is the the size of the log file that stores every query information that has been executed. For a dedicated server, a value up to 4 GB is safe, but the time of crash recovery may increase if the log file size is too big. So, in best practices, it should be kept in between 1 GB to 4 GB.

Percona server

According to Percona website, “Percona server is a free, fully compatible, enhanced, open source drop-in replacement for MySQL that provides superior performance, scalability, and instrumentation.”

Percona is a fork of MySQL with enhanced features for performance. All the features available in MySQL are available in Percona. Percona uses an enhanced storage engine, which is called XtraDB. According to the Percona website: “Percona XtraDB is an enhanced version of the InnoDB storage engine for MySQL, which has more features, faster performance, and better scalability on modern hardware. Percona XtraDB uses memory more efficiently in high-load environments.”

As mentioned previously, XtraDB is a fork of InnoDB, so all features available with InnoDB are available in XtraDB.

Installation

Percona is only available for Linux systems. It is not available for Windows as of now. In this book, we will install the Percona server on Debian 8. The process is the same for both Ubuntu and Debian.

To install the Percona server on other Linux flavors, check out the Percona Installation manual at https://www.percona.com/doc/percona-server/5.5/installation.html. As of now, they provide instructions for Debian, Ubuntu, CentOS, and RHEL. They also provide instructions to install the Percona server from sources and Git.

Now, let’s install Percona server using the following steps:

1. Open your sources list file using the following command in your terminal:

   sudo nano /etc/apt/sources.list 

2. If prompted for a password, enter your Debian password. The file will be opened. Now, place the following repository information at the end of the sources.list file:

   deb http://repo.percona.com/apt jessie main
   deb-src http://repo.percona.com/apt jessie main

3. Save the file by clicking on CTRL + O and close the file by clicking on CTRL + X.
4. Update your system using the following command in terminal:

   sudo apt-get update

5. Start the installation by issuing the following command in terminal:

   sudo apt-get install percona-server-server-5.5

6. The installation will start. The process is the same as the MySQL server installation. During installation, the root password for the Percona server will be asked. You just need to enter it. When the installation is completed, you are ready to use the Percona server in the same way as you would use MySQL.
7. Configure the Percona server and optimize it as discussed in the previous sections.

Summary

In this article, we studied the MySQL and Percona servers with Query Caching and other MySQL configuration options for performance. We also compared different storage engines and Percona XtraDB. We saw MySQL Workbench Performance monitoring tools as well.

Resources for Article:

---

Further resources on this subject:

• Building a Web Application with PHP and MariaDB – Introduction to caching [article]
• PHP Magic Features [article]
• Understanding PHP basics [article]

---