How to optimize MySQL server _ MySQL

Today, developers are constantly developing and deploying applications that use the LAMP (Linux, Apache, MySQL, and PHPPerl) architecture. However, the server administrator often has no control over the application itself, because the application is compiled by others. This three-part series of articles will be discussed today, developers are constantly developing and deploying the use of 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 three-part series will discuss many server configurations that affect application performance. This article is the third and last part of this series. it will focus on optimizing the database layer for maximum efficiency.

MySQL optimization

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 ismysqld. Optimizing this process means allocating the memory appropriately and enablingmysqldUnderstand what type of load will be supported. 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). PairmysqldTuning 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 books have already discussed this topic), it will configuremysqldTo report queries that may require optimization.

Although these tasks have been assigned an order, pay attention to the hardware andmysqldTo optimize the query. The machine speed is too slow. I have seen a very fast machine that fails due to heavy load when running well-designed queries, becausemysqldIt is occupied by a large number of busy jobs and cannot be queried by the service.

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. When you need to search the entire table, it is calledTable 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 indexing efficiency is very low or indexes cannot be used at all, the query speed will be slowed down and the problem will become more significant as the server load and table size increase. Queries whose execution time exceeds the specified time range are calledSlow query.

You can configuremysqldRecord these slow queries to the appropriately 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 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 queries that have been executed for more than 5 seconds and that have not used an index. Please note that log-queries-not-using-indexesWarning: 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 it in my. cnf. log-slow-queries = /new/path/to/fileTo achieve this goal.

It is best to read slow query logs throughmysqldumpslowCommand. 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 ismysqldumpslowBefore comparing the results, any data specified by the user is deleted, so different calls to the same query are counted as one; this can help you find the query that requires the most workload.