Performance tuning of lamp system MySQL server tuning

Today, developers continue to develop and deploy applications that use lamp (linux®, Apache, MySQL, and Php/perl) architectures. However, server administrators often have little control over the application itself, because the application is written by someone else. This article focuses on tuning the database layer to achieve maximum efficiency.

About MySQL Tuning

There are 3 ways to speed up the running of the MySQL server, from low to high efficiency:

Replace the problematic hardware.

Tuning the MySQL process settings.

Optimize the query.

Replacing the problematic hardware is usually our first consideration, mainly because the database consumes a lot of resources. But this is only the solution. In fact, you can usually double the central processing unit (CPU) or disk speed, or you can increase memory by 4 to 8 times times.

The second approach is to tune the MySQL server (also known as MYSQLD). Tuning this process means allocating memory appropriately and letting mysqld know what kind of load it will withstand. Speed up disk operation less than you need to reduce the number of disk accesses. Similarly, ensuring that the MySQL process is working correctly means that it spends more time on the service query than it spends on processing background tasks, such as processing temporary disk tables or opening and closing files. The emphasis of this paper is to adjust the mysqld.

The best way is to make sure that the query has been optimized. This means that the appropriate index is applied to the table, and the query is written in a way that makes full use of the MySQL functionality. Although this article does not contain content for query tuning (which is discussed in many books), it configures MYSQLD to report queries that might require tuning.

Although orders have been assigned to these tasks, you still need to be aware of the hardware and mysqld settings to facilitate appropriate tuning of the query. The speed of the machine is just too slow, I've seen a machine that's fast. A well-designed query failed because it was overloaded because the mysqld was occupied by a lot of busy work and could not service the query.

Log Slow queries

In a SQL Server, the data tables are stored on disk. The index provides a way for the server to find a specific row of data in a table without searching the entire table. When you have to search the entire table, it is called a table scan. In general, you may want to get only a subset of the data in a table, so a full table scan wastes a lot of disk I/O and therefore wastes a lot of time. This problem is even more complicated when data must be connected, because it is necessary to compare the multiple rows of data at both ends of the connection.

Of course, table scans do not always cause problems, and sometimes reading the entire table is more efficient than picking up a subset of the data from it (the Query planner in the server process is used to make these decisions). If the index is inefficient or cannot be used at all, the query slows down, and the problem becomes more pronounced as the load and table size increase on the server. Queries that run longer than a given time range are called slow queries.

You can configure MYSQLD to record these slow queries in the appropriately named slow query log. The administrator then looks at the log to help them determine which parts of the application need to be investigated further. Listing 1 shows the configuration that needs to be done in my.cnf to enable the slow query log.

Listing 1. Enable MySQL slow query log[mysqld]
; enable the slow query log, default 10 seconds
log-slow-queries
; log queries taking longer than 5 seconds
long_query_time = 5
; log queries that don't use indexes even if they take less than long_query_time
; MySQL 4.1 and newer only
log-queries-not-using-indexes

These three settings are used together to record the execution time of more than 5 seconds and queries that do not use the index. Please note the warning about log-queries-not-using-indexes: You must use MySQL 4.1 or later. The slow query log is stored in the MySQL data directory, named Hostname-slow.log. If you want to use a different name or path, you can use log-slow-queries =/new/path/to/file in my.cnf to accomplish this.

Reading a slow query log is best done through the Mysqldumpslow command. Specify the path to the log file to see a sorted list of a slow query, and also show how many times they appear in the log file. A very useful feature is that mysqldumpslow deletes any user-specified data before comparing the results, so different calls to the same query are counted once; this helps to identify the queries that require the most work.