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

What is the simple way to optimize the performance of MySQL? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

A simple way to optimize the performance of MySQL with hundreds of thousands of rows of tables (turn) [@ more@] database optimization is probably the most challenging in system management, because its requirements for the quality of personnel are almost all aspects, a good DBA requires a variety of comprehensive qualities. In addition to excluding the performance problems caused by the operating system and applications, the core of optimizing the database is actually the adjustment of configuration parameters. This article implements an example of group by optimization with hundreds of thousands of row tables through a simple parameter adjustment. Through this simple adjustment, database performance has been improved by leaps and bounds.

This example is adjusted for MySQL, unlike other commercial databases, MySQL does not have views, especially Oracle can use solidified views to improve query performance, there are no stored procedures, so performance adjustment can only be achieved by configuring appropriate parameters.

Specific steps for adjustment (example for pLog 0.3x blog system):

The most frequently found slow log is:

SELECT category_id, COUNT (*) AS 'count' FROM plog_articles WHERE blog_id = 2 AND status =' PUBLISHED' group by category_id

Generally, it is more than 20s, even 30s.

When blog_id=1 or others, the result can be selected quickly.

So I suspected that there was something wrong with the index and re-established the index, but to no avail. The EXPLAIN results are as follows:

Mysql > EXPLAIN SELECT category_id, COUNT (*) AS 'count' FROM plog_articles WHERE blog_id = 2 AND status =' PUBLISHED' group by category_id

+- -+

| | table | type | possible_keys | key | key_len | ref | rows | Extra |

+- -+

| | plog_articles | ref | idx_article_blog | idx_article_blog | 5 | const,const | 4064 | Using where; Using temporary; Using filesort |

+- -+

1 row in set (0.00 sec)

So I think that every time I check a blog with blog_id = 2, the system load increases and there is a higher swap. So looking at the information about temporary table, there is a saying like this:

If you create a lot of disk-based temporary tables, increase the size of tmp_table_size if you can do so safely. Keep in mind that setting the value too high may result in excessive swapping or MySQL running out of memory if too many threads attempt to allocate in-memory temporary tables at the same time. Otherwise, make sure that tmpdir points to a very fast disk that's not already doing lots of I/O.

Another problem that doesn't show up in the slow query log is an excessive use of disk-based temporary tables. In the output of EXPLAIN, you'll often see Using temporary. It indicates that MySQL must create a temporary table to complete the query. However, it doesn't tell you whether that temporary table will be in memory or on disk. That's controlled by the size of the table and MySQL's tmp_table_size variable.

If the space required to build the temporary table is less than or equal to tmp_table_size, MySQL keeps it in memory rather than incur the overhead and time required to write the data to disk and read it again. However, if the space required exceeds tmp_table_size, MySQL creates a disk-based table in its tmpdir directory (often / tmp on Unix systems.) The default tmp_table_size size is 32 MB.

To find out how often that happens, compare the relative sizes of the Created_tmp_tables and Created_tmp_disk_tables counters:

After adjusting the tmp_table_size to about 80m, the above statement can be solved in 14s.

This parameter is easily ignored by DBA.

In fact, not only the database, but also the operating system, is also greatly affected by tmp, such as installing software to d: disk, if the TMP environment variable points to c: disk, and c: is not enough space, it may still lead to installation failure.

So giving TMP enough space can be said to be a generally applicable principle in computer systems (the same is true of writing programs).

This is the answer to the question about how to optimize the performance of MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.