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

This article introduces the knowledge of "how to understand MySQL performance tuning". In the operation of practical cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Overview of MySQL performance tuning

The main factors affecting MySQL performance are environmental problems (CPU, disk Imax O, network performance, operating system contention) and MySQL configuration (database design, application performance, configuration variables).

The performance of MySQL is affected by the performance characteristics of the host. Various factors affect host performance: CPU speed and number, disk throughput and access time, network throughput, and competitive services on the operating system all have an impact on the performance of MySQL instances.

The contents of the database and its configuration also affect MySQL performance.

-databases that contain frequent minor updates will benefit from careful design and standardization.

-when you use the smallest applicable data type to store data, the database throughput increases.

-queries that request only a subset of table data will benefit from a well-designed index.

-applications that request only specific rows and columns will reduce the overhead of redundant requests.

-the shorter the transaction, the less likely it is to cause locking and delay in other transactions.

-properly tuned server variables optimize the allocation of MySQL buffers, caches, and other resources for specific workloads and datasets.

1.1. Performance monitoring

To adjust the performance of the server, you must understand its performance characteristics. To do this, you can benchmark the overall performance, and the MySQL installation provides the following benchmarking tools:

L mysqlslap is part of the standard MySQL distribution. This is a diagnostics that simulates the client load on the MySQL server instance and displays timing information for each phase.

L sql-bench, which is part of the MySQL source code distribution, is a series of Perl scripts that execute multiple statements and collect status timing data.

The following is an example of using mysqlslap to set the mode in a SQL script and run queries in other scripts:

Shell > mysqlslap-iterations=5000-concurrency=50-query=workload.sql-create=schema.sql-delimiter= ";"

In addition, MySQL provides a third-party benchmark suite.

You can also use slow query logs, general query logs, EXPLAIN and PROCEDURE ANALYSE to analyze events one by one; slow query logs are used to record statements that exceed the limits set by the long_query_time and min_examined_row_limit variables. Use mysqldumpslow to view the contents of the slow query log. The general query log is used to record all client connections and requests received by MySQL. Use this log to record all SQL statements received over a period of time, for example, when a workload is generated for use by mysqlslap or other benchmarking tools.

Use SHOW STATUS and mysqladmin extended-status to view database status, and you can use PERFORMANCE_SCHEMA to analyze events by group.

1.2. Performance mode

Performance Mode is a function that monitors the execution of MySQL servers at a lower level. This function is implemented using the PERFORMANCE_SCHEMA storage engine and performance_schema database. Performance mode is available in all binary versions of MySQL downloaded from Oracle. Performance mode is enabled by default and is controlled by the performance_schema variable when the server starts. Verify that performance mode is enabled using the following statement:

Mysql > SHOW VARIABLES LIKE 'performance_schema'

+-+ +

| | Variable_name | Value |

+-+ +

| | performance_schema | ON |

+-+ +

Performance mode allows you to monitor and check the performance characteristics of the code being detected in the MySQL server. Developers will detect functions and other coding events to collect timing information; the exposed performance data will be very helpful to MySQL code base contributors and plug-in developers, as well as to identify low-level performance bottlenecks, such as log file Imando O waiting or buffer pool mutual exclusion. The information exposed by the performance pattern can be used to identify low-level bottlenecks. Most of this information is low-level and can help developers of the MySQL server product family debug performance issues, or help system architects and performance consultants tune InnoDB data and log file storage hardware.

L detect (INSTRUMENT), instance (INSTANCE), event (EVENT) and consumer (CONSUMER)

The performance mode database contains configuration and event information:

-detect "is the point in the server code that raises the event to be monitored, which is configured in the setup_ statements table

-each detected object is an "instance" of the detection, recorded in a series of instance tables.

-when the thread executes the code in the detection instance, MySQL identifies the "event" that occurred and records it in the event and summary table.

-each "consumer" is the name of the table in performance mode, used to record and query events and summaries of events, and is configured in the SETUP_CONSUMERS table.

Detection in performance mode is a point in the server source code from which MySQL raises events. Detection has a hierarchical naming convention. For example, the following is a short list that contains some of the hundreds of tests in performance mode:

Stage/sql/statistics

Statement/com/Binlog Dump

Wait/io/file/innodb/innodb_data_file

Wait/io/file/sql/binlog

Wait/io/socket/sql/server_unix_socket

Each detection consists of its type, the module to which it belongs, and the variable or class for that particular detection. You can view all available tests by querying the performance_schema.setup_instruments table.

Performance mode records each detection instance in the instance table. For example, the following query shows detection of events on the wait/io/file/sql/FRM record file instance / var/lib/mysql/mem/tags.frm.

Mysql > SELECT file_name, event_name FROM file_instances LIMIT 1\ G

* * 1. Row *

FILE_NAME: / var/lib/mysql/mem/tags.frm

EVENT_NAME: wait/io/file/sql/FRM

The following output shows the contents of the setup_consumers table:

Mysql > SELECT * FROM setup_consumers

+-+ +

| | NAME | ENABLED |

+-+ +

| | events_stages_current | NO |

| | events_stages_history | YES |

| | events_stages_history_long | NO |

| | events_statements_current | YES |

| | events_statements_history | NO |

| | events_statements_history_long | NO |

| | events_waits_current | YES |

| | events_waits_history | YES |

| | events_waits_history_long | NO |

| | global_instrumentation | YES |

| | thread_instrumentation | YES |

| | statements_digest | YES |

+-+ +

12 rows in set (0.00 sec)

The NAME for each consumer is the name of the table used to query events and summaries in performance mode. Disabled users do not record information, thus saving system resources.

When MySQL identifies the event that occurred in the detection instance, it records it in the event table.

-the main event table is events_waits_current, which stores the most recent events for each thread.

-events_waits_history stores the last 10 events for each thread.

-events_waits_history_long stores a total of 10000 recent events.

The events_waits_* tables all use the same schema. For structural information about the pattern, visit

Http://dev.mysql.com/doc/refman/5.6/en/events-waits-current-table.html

When using performance patterns to identify bottlenecks or other issues, do the following:

1. Make sure that performance mode is enabled for a range of tests and consumers that apply to the type of problem you encounter. For example, if you are sure that the problem is with the Icano limit, use the wait/io/file/* test; if you are not sure of the root cause, use a broader test.

two。 Run the test case used to generate the problem.

3. Users such as querying the events_waits_* table, in particular, query events_waits_history_long using the applicable WHERE clause filter to further narrow the cause of the problem.

4. Disable tests that are used to evaluate problems that have been excluded.

5. Retry the test case.

1.3. General database optimization

1) Standardization

Standardizing data can eliminate redundant data, improve the performance of transactional workloads, provide flexible access to data, and minimize data inconsistencies.

Standardization is the act of removing redundancy and inappropriate dependencies from the database (to avoid storing the same data in multiple places and the risk of exceptions). Standardization usually results in fewer columns in many tables, lower overall storage requirements, lower Imax O requirements, and faster single insert, update, and delete operations. This improves the performance of transactional workloads that perform frequent small updates, but complicates queries that retrieve large amounts of data.

2) data type and size

Choosing the right data type and size can avoid NULL, improve performance, protect data, and use data compression where appropriate.

Choosing the right data type is a very important but often neglected part of table design, and the size of the data type may have a great impact on table operations. For example, choosing to store SMALLINT digits as INT doubles the space required for the column. In a table with a million rows, this decision will result in a waste of additional 2 MB storage space, slower disk operations, and more memory for buffers and caches. Use INSERT... COMPRESS (field_name) … And SELECT... UNCOMPRESS (column_name)... String data can be compressed and decompressed when it is stored and retrieved. Although you can also use CHAR or VARCHAR fields to do this, you can avoid character set conversion problems by using VARBINARY or BLOB columns to store compressed data.

3) efficient indexing

Creating the best index can improve the query throughput and reduce the Icano overhead.

If you query a specific row in a table by specifying a field in the WHERE clause, and the table does not create an index for that field, MySQL reads each row in the table to find each matching row. This will result in a lot of unnecessary disk access and significant performance degradation for large tables. An index is ordered grouped data, through which MySQL can more easily find the correct location of the query row. By default, InnoDB sorts the list in the order of the primary key; the ordered table is called the cluster index. Each additional or secondary index on the InnoDB table takes up additional space in the file system because the index contains an additional copy of the index field and a copy of the primary key. Each time you modify data using an INSERT, UPDATE, REPLACE, or DELETE operation, MySQL must also update all indexes that contain the modified fields. Therefore, adding multiple indexes to a table reduces the performance of data modification operations that affect the table. However, if the index is properly designed, queries that depend on index fields will benefit greatly in terms of performance. If the query cannot find a specific row using the index, a full table scan must be performed; that is, the entire table must be read to find the row. Queries that use indexes can read the corresponding rows directly without reading other rows, which greatly improves the performance of such queries.

1.4. PROCEDURE ANALYSE

PROCEDURE ANALYSE (), which can assist the reference analysis statement when optimizing the table structure. Using this statement, MySQL helps you analyze your fields and their actual data, and will give you some useful suggestions. However, these recommendations become useful only if there is actual data in the table, because data is needed as a basis for making some big decisions.

The syntax is as follows:

SELECT... FROM table_name WHERE... PROCEDURE ANALYSE ([max_elements, [max_memory]])

L max_elements (default of256) is the maximum number of different values that analyse notices in each column. Analyse uses this parameter to check whether the optimized column is of type ENUM.

L max_memory (default is 8192) is the maximum amount of memory that analyse allocates to each column when looking for all different values.

Example 1:

Taking the analysis in line 4 as an example, you can see that the br_Task.task_name field:

Column minimum: 121 new

List maximum: Han-soft-national-see silver medal

Minimum length: 3 bytes

Maximum length: 52 bytes

Average length: 24.1852

Optimization suggestion: the data type of the field is changed to VARCHAR (52) NOT NULL.

Example 2:

PROCEDURE ANALYSE analyzes the columns in a given query and provides tuning feedback for each field:

Mysql > SELECT CountryCode, District, Population

-> FROM City PROCEDURE ANALYSE (2505,024)\ G

The default setting usually recommends the use of the ENUM type to optimize the design of the table. If you decide that you do not want to use the ENUM value recommended by PROCEDURE ANALYSE () when parsing the column, use a non-default parameter.

The first parameter is the number of different elements to consider when analyzing the appropriateness of the ENUM value. The default value for this parameter is 256.

The second parameter is the maximum amount of memory used to collect different values for analysis. The default value for this parameter is 8192, which means 8 KB. If you set a value for this parameter, PROCEDURE ANALYSE () cannot check for different values to recommend using the ENUM type. If PROCEDURE ANALYSE () cannot store candidate ENUM values within an acceptable range (within the limits set by the parameter), the ENUM type is not recommended for this column.

This example recommends using the CHAR (3) type for the City.CountryCode column. On the other hand, if you use the default parameter, PROCEDURE ANALYSE () will suggest ENUM ('ABW','AFG',...,'ZMB','ZWE'), which is an ENUM type with more than 200 elements, with a different value for each corresponding CountryCode value.

1.5. EXPLAIN

The EXPLAIN command describes how MySQL intends to execute a specific SQL statement, returns no data from the dataset, and provides information about how MySQL intends to execute the statement

Use EXPLAIN to check the SELECT, INSERT, REPLACE, UPDATE, and DELETE statements. Put EXPLAIN in front of the statement, EXPLAIN SELECT..., EXPLAIN UPDATE...

EXPLAIN generates a row of output for each table used in the statement. The output contains the following:

-table: the table corresponding to the output row

-select_type: the type of selection used in the query. SIMPLE means that the query does not use UNION or subqueries.

-key: the index selected by the optimizer

-ref: the column compared to the index

-rows: the estimated number of rows checked by the optimizer

-Extra: additional information for each query provided by the optimizer

For a complete discussion of the output column, please visit:

Http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

Use EXPLAIN EXTENDED... You can view additional information provided by the optimizer. For a complete discussion, please visit:

Http://dev.mysql.com/doc/refman/5.6/en/explain-extended.html

For example, the following query joins the fields of two tables and performs aggregation:

Mysql > SELECT COUNT (*) as' Cities', SUM (Country.Population) AS Population

> Continent FROM Country JOIN City ON CountryCode = Code

> GROUP BY Continent ORDER BY Population DESC

+-+

| | Cities | Population | Continent | |

+-+

| | 1765 | 900934498400 | Asia |

| | 580 | 95052481000 | North America | |

| | 842 | 55127805400 | Europe |

| | 470 | 48533025000 | South America | |

| | 366 | 16179610000 | Africa |

| | 55 | 307500750 | Oceania |

+-+

6 rows in set (0.01 sec)

The following output shows the results of using EXPLAIN before the query:

Mysql > EXPLAIN SELECT COUNT (*) as' Cities', SUM (Country.Population) AS Population

> Continent FROM Country JOIN City ON CountryCode = Code

> GROUP BY Continent ORDER BY Population DESC\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: Country

Type: ALL

Possible_keys: PRIMARY

Key: NULL

Key_len: NULL

Ref: NULL

Rows: 239

Extra: Using temporary; Using filesort

* 2. Row * *

Id: 1

Select_type: SIMPLE

Table: City

Type: ref

Possible_keys: CountryCode

Key: CountryCode

Key_len: 3

Ref: world_innodb.Country.Code

Rows: 9

Extra: Using index

2 rows in set (0.00 sec)

EXPLAIN format

EXPLAIN output also provides other formats:

1) Visual EXPLAIN. Output in graphic format is provided in MySQL Workbench.

2) output in EXPLAIN FORMAT=JSON,JSON format, which is useful when passing EXPLAIN output to the program for further processing / analysis

JSON (JavaScript Object Notation,JavaScript object representation) is a simple data exchange format. The following output shows the results of using FORMAT=JSON in an EXPLAIN statement:

Mysql > EXPLAIN FORMAT=JSON SELECT COUNT (*) as' Cities', SUM (Country.Population) AS Population, Continent FROM Country JOIN City ON CountryCode = Code GROUP BY Continent ORDER BY Population DESC\ G

* * 1. Row *

EXPLAIN: {

"query_block": {

"select_id": 1

"ordering_operation": {

"using_filesort": true

"grouping_operation": {

"using_temporary_table": true

"using_filesort": false

...

1 row in set, 1 warning (0.00 sec)

1.6. Server statu

1) check the status of the server

MySQL provides several ways to view server status variables:

L View at the mysql prompt, command STATUS, SHOW STATUS

L View on the terminal:

Mysqladmin-login-path=login-path status

Mysqladmin-u user-p extended-status

MySQL provides short status messages through the mysql command STATUS and the mysqladmin command status. The long format state output displayed by the mysql command SHOW STATUS and the mysqladmin command extended-status contains the values of many system state variables, the most important of which are discussed later.

Using options for mysqladmin provides additional functionality. For example, the-- sleep (or-I) option specifies the number of seconds to wait between iterations and automatically re-executes the command after waiting for that time. The-- relative (or-r) option shows the difference of each variable since the last iteration, not the value of the variable. Use command-line tools such as grep to extend the use of mysqladmin. For example, use the following command to display only variables that contain the string cache_hits:

Shell > mysqladmin-- login-path=admin extended-status | grep cache_hits

| | Qcache_hits | 0 | |

| | Ssl_callback_cache_hits | 0 | |

| | Ssl_session_cache_hits | 0 | |

| | Table_open_cache_hits | 280 | |

2) main state variables

·Created_tmp_disk_tables: displays the number of internal temporary tables on disk; gets the number of temporary tables created by the server when the statement is executed. If the value is high, the server has created multiple temporary tables on disk rather than in memory, resulting in slow query execution.

·Handler_read_first: displays the number of reads for the first entry in the index, and if the value is high, the server has performed multiple full index scans to complete the query request.

Angular Innodb_buffer_pool_wait_free: displays the number of times the server waits for clean pages and waits for pages in the InnoDB buffer pool to refresh before completing the query request. If the value is high, the size of the InnoDB buffer pool is not set correctly and query performance is affected.

·Max_used_connections: displays the maximum number of concurrent connections since the server was started; this variable provides useful information to determine the number of concurrent connections that the server must support.

Angular Open_tables: displays the number of tables opened in a given time; comparing this variable with the server system variable table_cache provides useful information about how much memory should be reserved for the table cache. If the value of the Open_tables state variable is usually low, reduce the size of the server system variable table_cache. If the value is high (close to the server system variable table_cache), increase the amount of memory allocated to the table cache to reduce query response time.

·Select_full_join: shows the number of joins that perform a table scan instead of using an index, and if this value is not, the index of the table should be carefully checked.

Long_query_time Slow_queries: displays the number of queries that take longer than the number of seconds specified by the long_query_time system variable; this state variable depends on your understanding of the long_query_time variable (the default is 10 seconds). If the Slow_queries state variable is not, check the value of long_query_time and the slow query log, and improve the captured query.

·Sort_merge_passes: displays the number of merge passes performed by the sort algorithm; the sort operation requires a buffer in memory. This state variable calculates the number of passes through the sort buffer required by the sort operation. If the value is high, it may indicate that the sort buffer size is not large enough to perform a pass-through sort of the query; consider increasing the value of the sort_buffer_size system variable.

·Threads_connected: displays the number of connections currently open; periodically capturing this value provides useful information about when the server is most active. Use this variable to determine the best time to perform server maintenance, or to use it as a basis for allocating more resources to the server.

·Uptime: displays the number of seconds the server has been running; this value can provide useful information about the health of the server, such as how often the server needs to be restarted.

1.7. Brief introduction of system variable tuning

MySQL performance tuning should first adjust queries, patterns, and indexes, because each operation can yield more benefits than tuning variables; secondly, consider tuning for server size, such as memory and Imax O, and then for application configuration; for example, storage engine settings, providing 70% of physical RAM to the InnoDB buffer pool, minimizing MyISAM caches and buffers, etc. Finally, adjust the number of connections according to the type of server load (transaction server, report server), etc.

A common misunderstanding is that server variable configuration is the most important part of server tuning. In fact, in terms of effort spent, optimization patterns, common queries, and indexes in typical databases can gain more benefits than adjusting variables.

²default setting

MySQL engineers at Oracle choose the default settings to accommodate most production systems, which often handle frequent small transactions, many updates, and a few large slow queries (such as those used to generate reports). However, because MySQL is used on everything from small devices (such as point-of-sale systems and routers) to large Web servers with large memory and fast disk arrays, you may find that you can benefit from changing some of the server's default settings for your specific environment and workload.

²InnoDB Settings

For example, on a dedicated MySQL server that uses only the InnoDB user table, you can increase the value of innodb_buffer_pool_size to a large percentage of the server's total memory (70% of the server's total memory), keeping in mind the needs of the operating system, such as cron jobs, backups, virus scanning, and managing connections and tasks. If you have several GB RAM, you can also benefit from using multiple innodb_buffer_pool_instances, which enables multiple buffer pools to avoid contention.

²lower the MyISAM setting

On systems that do not use MyISAM as a user table, reduce the value of options that apply only to MyISAM (for example, reduce the value of key_buffer_size to a smaller value such as 16 MB), while keeping in mind that some internal MySQL operations will use MyISAM.

²reporting system

On servers that run a few large slow queries, such as those for business intelligence reports, use settings such as join_buffer_size and sort_buffer_size to increase the amount of memory dedicated to buffers. Although the default server settings are more appropriate for the transactional system, the default my.cnf file contains alternative values for these variables that apply to the report server.

²transaction system

On servers that support many fast concurrent transactions that are repeatedly disconnected and reconnected, set the value of thread_cache_size to large enough so that most new connections can use cached threads; this avoids the server overhead of creating and disconnecting each thread of connection.

On servers that support multiple write operations, improve log settings such as innodb_log_file_size and innodb_log_buffer_size, because the performance of data modification operations depends heavily on the performance of InnoDB logs. Consider changing the value of innodb_flush_log_at_trx_commit to improve performance per commit, but the risk is that some data may be lost if the server fails.

If your application executes the same query (or multiple identical queries) repeatedly, consider enabling query caching and resizing it based on the results of common queries by setting appropriate values for query_cache_type and query_cache_size.

²balanced memory usage

When you set large values for the cache and buffer for each query or connection, the available size of the buffer pool is reduced. Adjusting the server's configuration variables is a balanced process, starting with the default value, providing as much memory as possible to the buffer pool. then adjust the variables most closely related to the tuning target, the problems identified by checking the server status, and the bottlenecks identified by the query performance pattern.

1) main server system variables:

·innodb_buffer_pool_size: defines the size of the memory buffer (in bytes) that InnoDB uses to cache table data and indexes; for best performance, set this value to as large as possible, keeping in mind that too high a value will cause the operating system to swap pages, which can greatly degrade performance. If only the InnoDB user table is used on a dedicated database server, consider setting this variable to a value between 70% and 85% of the physical RAM.

·innodb_flush_log_at_trx_commit: defines how often InnoDB writes the log buffer to the log file and how often the log file is flushed to disk; there are three possible settings for this variable:

N: writes the log buffer to disk once per second.

N 1: the log is flushed to disk each time it is committed; if no commit occurs, it is refreshed every second.

N 2: flushes the log to the operating system cache and flushes to disk every innodb_flush_log_at_timeout second (default is one second).

·innodb_log_buffer_size: defines the size of the buffer (in bytes) that InnoDB uses to write to log files on disk; the default value for this variable is 8 MB. Transactions exceeding this size cause InnoDB to flush logs to disk before the transaction commits, which degrades performance. For applications that use a lot of BLOB or have large spikes in update activity, you can improve transaction performance by increasing this value.

·innodb_log_file_size: defines the size of each log file in the log group in bytes; for write-intensive workloads on large datasets, set this variable so that the maximum total size of all log files (set by innodb_log_files_in_group) is less than or equal to the size of the buffer pool. Large log files slow failure recovery, but you can improve overall performance by reducing checkpoint refresh activity.

·join_buffer_size: defines the minimum buffer size for joins that use table scans; for queries that contain joins that cannot use indexes, increase this value with the default value (256 KB) as the starting point. When running such a query, change the value of each session to avoid setting global settings so that queries that do not need such a large value waste memory.

·query_cache_size: defines the amount of memory allocated to cache query results; improves the performance of applications that issue repeated queries against data that rarely changes by using query caching. As a baseline, set this variable to a value between 32 MB and 512 MB based on the number of repeated queries and the size of the data returned. Please monitor the cache hit ratio to determine the validity of this variable and adjust its value according to your observation.

·sort_buffer_size: defines the maximum amount of memory allocated to the session that needs to be sorted; if the value of the Sort_merge_passes state variable is high, increase this value to improve the performance of ORDER BY and GROUP BY operations.

·table_open_cache: defines the number of tables opened by all threads; set this value to be greater than N * max_connections, where N is the maximum number of tables used in all queries in the application. This value is too high to cause the error "Too many open files". A high value of the Open_tables state variable indicates that MySQL frequently opens and closes the table, so table_open_cache should be increased.

·thread_cache_size: defines the number of threads that the server should cache for reuse; by default, this variable will automatically resize. Evaluate the Threads_created state variable to determine whether the value of thread_cache_size needs to be changed.

2) prepare to adjust

Tuning the database server can be compared to adjusting the instrument, selecting the value to change and setting a goal, adjusting the value up and down, and testing the detected behavior at the same time to determine the best setting.

To prepare the adjustment environment, copy the production system as much as possible; to reduce the impact of changed factors that are not related to the variables being adjusted, perform adjustments on the production server during downtime, or preferably on the replicated system.

Then set tuning goals, such as processing more transactions per second, generating complex reports faster, and improving performance through spikes in concurrent connections; set a goal before tuning. The tuning variable you choose depends on the target you set. The optimal setting of a report server with few connections is very different from that of a transactional application server that has many connections and handles hundreds of small transactions per second. Servers with a higher ratio of memory to database size have very different performance characteristics compared with servers with smaller memory but larger databases. The settings required for heavy write workloads are different from read-only systems. Select appropriate variables to adjust, such as buffer, cache, log settings, etc.

Finally, methods such as application code, general query logs, and so on, collect representative statements; in order to most accurately simulate the workload being adjusted against it, collect a set of representative statements. Select a sequence of statements with the correct proportion of query and modification operations from the application. Use the general query log to collect actual statements from the production server during the daily or weekly period that you want to optimize.

3) practice adjustment

A benchmark to find the best value for each variable, first set the variable to a setting lower than its default value, and then benchmark to measure relevant metrics, such as virtual memory usage, average time spent, and related state variables

Then, increase the value of the variable and repeat the benchmark and, if necessary, refresh the status variable.

Finally, the results are plotted, the points of decline in revenue and the peak of performance are found, and the final variable value is determined according to the best balance between resources and performance.

To view the value of the selected metric, use:

-mysqlslap or mysql to run workloads and get average execution time

-sql-bench to run a more general benchmark

-mysqladmin extended-status to get the value of the state variable before and after the workload

-operating system tools such as top or / proc file system to access process metrics

If you want to run a fine-tuning benchmark with multiple different values for a specific variable, or if you want to run the same benchmark repeatedly over a long period of time, consider using a scripting language to automate the steps used in the benchmark.

4) tuning example: sort buffer size

This example shows a series of test results for a database with a heavily sorted workload, where the sort_buffer_size variable was changed when the test was run.

The chart shows:

-when sort_buffer_size increases from 32 KB to 512 KB, the value of the Sort_merge_passes state variable (which can be viewed using mysqladmin extended_status-r) decreases sharply, and then slowly after that

-the average time spent testing the workload (which can be viewed using mysqlslap) decreased at 512 KB, peaked at 4 MB, then decreased at 8 MB, and finally reached the best performance at 32 MB

-the total virtual memory of the mysqld process (which can be viewed using top) is the smallest when the sort_buffer_size is 512 KB, and then increases steadily up to 16 MB, rising sharply at 32 MB

When the average query time is lowest, the sort_buffer_size is 32 MB, which uses a lot of memory that the buffer pool could have made better use of. In this example, the 512 KB setting provides the best balance between performance and memory used for the specific combination of workload, server, and database used in the test.

That's all for "how to understand MySQL performance tuning". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.