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 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 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.
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.
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.
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_type value is 0, then the following occurs:
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.
A brief list of features that are or are not supported by MyISAM is as follows:
A brief list of features that are or are not supported by InnoDB is as follows:
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.
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.
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.
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.
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.
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:
sudo nano /etc/apt/sources.list
deb http://repo.percona.com/apt jessie main
deb-src http://repo.percona.com/apt jessie main
sudo apt-get update
sudo apt-get install percona-server-server-5.5
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.
Further resources on this subject:
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…