MySQL Server optimization solution 3

This article mainly introduces the actual operation scheme for optimizing the MySQL server. It is sometimes necessary to optimize the MySQL server, so today we have provided three different optimization solutions for this situation. I hope you will gain some benefits.

Today, developers are constantly developing and deploying LAMPLinux®Applications in the apache (the most popular WEB server platform on the Unix platform), MySQL (the best combination with PHP), and PHP/Perl architectures. 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. Optimization of MySQL (the best combination with PHP)

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

Replace faulty hardware. Optimize the settings of the MySQL (best combination with PHP) 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 increase the CPU speed of the central processor or the disk speed by 4 to 8 times.

The second method is to optimize the MySQL (the best combination with PHP) server, also known as MySQL (the best combination with PHP) d. Tuning this process means allocating memory appropriately and letting MySQL (the best combination with PHP) d 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, ensure that MySQL (the best combination with PHP) A proper process operation means that it spends more time on service queries than processing background tasks such as processing temporary disk tables or opening or closing files. Tuning MySQL (the best combination with PHP) d 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 by fully utilizing the MySQL (best combination with PHP) function. Although this article does not include the content of the query optimization, many of the works have already discussed this topic), but it will configure MySQL (the best combination with PHP) d To report the query that may require optimization.

Although the order has been assigned for these tasks, you still need to pay attention to the hardware and MySQL (the best combination with PHP) d settings to facilitate the appropriate tuning query. The machine speed is slow. I have seen a very fast machine fail due to heavy load when running well-designed queries, because MySQL (the best combination with PHP) d. It is occupied by a lot of busy work and cannot be queried by the Service.

Slow query of records

On an SQL Server, data tables are stored on disks. The Index provides the MySQL server with a way 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 MySQL server process query planner 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 called slow queries.

You can configure MySQL (the best combination with PHP) d to record these slow queries to the slow query log with the appropriate name. 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 (the best combination with PHP) Slow query log

 
 

  
  
[MySQL (the best combination with PHP) d];
  
  
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 (best combination with PHP)
  
  
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 (the best combination with PHP) Version 4.1 or later. Slow query logs are saved in the MySQL (best combination with PHP) 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 using the dumpslow command of MySQL (the best combination with PHP. 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 MySQL (the best combination with PHP) dumpslow deletes any user-specified data before comparing the results, therefore, different calls to the same query are counted as one; this can help you find the query that requires the most workload.

The above content is an introduction to MySQL Server tuning. I hope you will get some benefits.