Shulou(Shulou.com)05/31 Report--

This article focuses on "how to tune the MySQL server", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to tune the MySQL server.

There are three ways to speed up the MySQL server, from low efficiency to high efficiency:

The first method is to replace the problematic hardware. Tune the settings for the MySQL process. Optimize the query.

Replacing problematic hardware is usually our first consideration, mainly because the database takes up a lot of resources. But that's the end of the solution. In fact, you can usually double the speed of the central processing unit (CPU) or disk, or you can increase the memory by four to eight times.

The second method is to tune the MySQL server (also known as mysqld).

Tuning this process means allocating memory appropriately and letting mysqld know what type of load it will bear. It is better to speed up the disk operation than to reduce the number of disk accesses required. Similarly, ensuring that the MySQL process operates correctly means that it spends more time on service queries than on 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 the appropriate index is applied to the table, and the query is written in a way that takes full advantage of MySQL capabilities. Although this article does not cover query tuning (which has been discussed in many books), it will configure mysqld to report queries that may need to be tuned.

Although the order has been assigned to these tasks, it is important to pay attention to the hardware and mysqld settings to facilitate proper tuning of the query. It's just that the machine is slow. I've seen fast machines fail to run well-designed queries because they are overloaded because mysqld is too busy to serve queries.

How to tune the MySQL server

The third method is to record slow queries.

In a SQL server, data tables are stored on disk. The index provides the server with a way to find specific rows 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 only want to get a subset of the data in the table, so a full table scan wastes a lot of disk I and O, and therefore a lot of time. This problem is even more complicated when the data must be connected, because multiple rows of data at both ends of the connection must be compared.

Of course, table scans don't always cause problems; sometimes it's more efficient to read the entire table than to pick out some data from it (the query planner in the server process is used to make these decisions). If the use of the index is inefficient, or if the index cannot be used at all, the query speed will be slowed down, and the problem will become more significant as the load and table size on the server increases. A query whose execution time exceeds a given time range is called a slow query.

You can configure mysqld to log these slow queries to appropriately named slow query logs. The administrator then looks at the log to help them determine which parts of the application need further investigation. Listing 1 shows the configuration you need to do in my.cnf to enable slow query logging.

Listing 1. Enable MySQL slow query log

The following is the referenced content:

[mysqld]; enabletheslowquerylog,default10secondslog-slow-queries;logqueriestakinglongerthan5secondslong_query_time=5;logqueriesthatdon'tuseindexeseveniftheytakelessthanlong_query_time;MySQL4.1andneweronlylog-queries-not-using-indexes

Using these three settings together, you can record queries that take more than 5 seconds to execute and that do not use indexes. Note the warning about log-queries-not-using-indexes: you must use MySQL4.1 or later. The slow query logs are saved in the MySQL data directory, called hostname-slow.log. If you want to use a different name or path, you can do this using log-slow-queries=/new/path/to/file in my.cnf.

Reading the slow query log is best done through the mysqldumpslow command. Specify the path to the log file and you will see a sorted list of slow queries and the number of 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 find the query that requires the most effort.

At this point, I believe you have a deeper understanding of "how to tune the MySQL server". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.