How to optimize the MySQL server

Nowadays, developers are constantly developing and deploying LAMP (Linux? , Apache, MySQL, and PHPPerl) architecture applications. However, the server administrator often has no control over the application itself, because the application is compiled by others. This article focuses on optimizing the database layer for maximum efficiency. There are three ways to speed up My

Nowadays, developers are constantly developing and deploying LAMP (Linux? , Apache, MySQL, PHP/Perl) architecture applications. However, the server administrator often has no control over the application itself, because the application is compiled by others. This article focuses on optimizing the database layer for maximum efficiency. There are three ways to speed up My

Nowadays, developers are constantly developing and deploying LAMP (Linux? , Apache, MySQL, PHP/Perl) architecture applications. However, the server administrator often has no control over the application itself, because the application is compiled by others. This article focuses on optimizing the database layer for maximum efficiency.

There are three ways to speed up the running of the MySQL server:

Replace faulty hardware. Optimize the settings of the MySQL process. Optimize the query.

Replacing faulty hardware is usually our first consideration, mainly because the database occupies a large amount of resources. However, this solution is limited to this. In fact, you can usually double the speed of the central processor (CPU) or disk, or increase the memory by 4 to 8 times.

The second method is to optimize the MySQL server (also called mysqld. Tuning this process means allocating memory appropriately and letting mysqld know what type of load it will bear. Speed up disk operation is not as fast as reducing the number of disk accesses required. Similarly, ensuring that the MySQL process operates correctly means that it spends more time on service queries than processing background tasks (such as processing temporary disk tables or opening and closing files). Tuning mysqld is the focus of this article.

The best way is to make sure that the query has been optimized. This means that an appropriate index is applied to the table, and the query is written in a way that fully utilizes the MySQL function. Although this article does not include the content of the query optimization (many of the works have already discussed this topic), it will configure mysqld to report the query that may require optimization.

Although the order has been assigned for these tasks, you should still pay attention to the hardware and mysqld settings to facilitate proper query optimization. The machine speed is too slow. I have seen very fast Machines fail due to heavy load when running well-designed queries, because mysqld is occupied by a lot of busy work and cannot be used for service queries.

Slow query of records

On an SQL Server, data tables are stored on disks. Indexes provide a method for the server to search for specific data rows in a table instead of the entire table. To search for the entire table, it is called a table scan. Generally, you may only want to obtain a subset of the data in the table. Therefore, full table scan will waste a lot of disk I/O, and thus a lot of time. When data must be connected, this problem is more complicated, because multi-row data at both ends of the connection must be compared.

Of course, table scanning does not always cause problems. Sometimes reading the entire table is more effective than selecting a part of the data from it (the query planner in the server process is used to make these decisions ). If the index usage efficiency is very low, or the index cannot be used at all, the query speed will be slowed down. As the server load and table size increase ,, this problem will become more significant. A query whose execution time exceeds the specified time range is called a slow query.

You can configure mysqld to record these slow queries to the named slow query log. The Administrator then checks the log to help them determine which parts of the application need further investigation. Listing 1 shows the configuration that needs to be done in my. cnf to enable slow query logs.

Listing 1. Enabling MySQL slow query logs

[Mysqld]; enable the slow query log, default 10 secondslog-slow-queries; log queries taking longer than 5 secondslong_query_time = 5; log queries that don't use indexes even if they take less than long_query_time; MySQL 4.1 and newer onlylog-queries-not-using-indexes

These three settings are used together to record queries that have been executed for more than 5 seconds and that have not used an index. Note the warning about log-queries-not-using-indexes: You must use MySQL 4.1 or later. Slow query logs are saved 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 achieve this goal.

Read slow query logs by using the mysqldumpslow command. Specify the log file path to view the sorted list of slow queries, and display the number of times they appear in the log file. A very useful feature is that mysqldumpslow deletes data specified by any user before comparing results. Therefore, different calls to the same query are counted as one; this helps you find the query that requires the most workload.