Three methods to speed up MySQL server running _ MySQL-mysql tutorial

Source: Internet
Author: User
Three methods to speed up MySQL server running:

Today, society is concerned with speed and efficiency in everything. of course, MySQL servers must also be efficient. 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:

The first method is to replace the 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 books 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.

The third method is to record slow queries.

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 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. 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

Reference content is as follows:

[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.

You can use the mysqldumphotoshop/target = _ blank class = infotextkey> pslow command to read slow query logs. 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 mysqldumphotoshop/target = _ blank class = infotextkey> pslow 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.