LoadRunner MySQL performance optimization

Original: 49863787

I. Query and index optimization analysis

1.show command:

Show engines;

Displays status information for the storage engine. This statement is useful for checking whether a storage engine is supported, or for viewing what the default engine is.

Show index from table name
Display Index

1, table name.

2. Non_unique If the index cannot include a repeating word, it is 0, or 1 if it can.

3. Name of the Key_name index

4, seq_in_index the column sequence number in the index, starting from 1.

5, column_name column name.

6. The Collation column is stored in the index in any way. In MySQL, there is a value of ' A ' (ascending) or null (no classification).

7. An estimate of the number of unique values in the cardinality index. You can update by running analyze table or myisamchk-a. The cardinality is counted according to the statistics stored as integers, so even for small tables, this value is not necessarily accurate. The larger the cardinality, the greater the chance that MySQL will use the index when it is federated.

8, Sub_part If the column is only partially indexed, the number of characters that are indexed. Null if the entire column is indexed.

9. Packed indicates how keywords are compressed. Null if it is not compressed.

10, NULL if the column contains null, it contains Yes. If not, the column contains No.

11, Index_type used Index method (btree consecutive reading data, hash suitable for random reading data, rtree suitable for a data to find nearby data).

12. Commentary on Comment

Show Processlist;
Show which threads are running

Show variables like "%log%";
Show variables to get slow query log information

Show status; Provide server state information

Show triggers;

Lists the triggers that are currently defined by the MySQL server

Show columns information for columns displayed in a given table from a table name
2. Index: Advantages:

Creating indexes can greatly improve the performance of the system

Disadvantages:

First, it takes time to create indexes and maintain indexes, and this time increases as the amount of data increases.

Second, the index needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain physical space. If you are building a clustered index, you will need more space.

Thirdly, when the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.

What fields are appropriate for creating an index:

Indexes are built on top of some columns in a database table. Therefore, when you create an index, you should carefully consider which columns you can create an index on, and on which columns you cannot create an index.

In general, indexes should be created on these columns, for example:

First, in the frequently need to search the column, you can speed up the search;

Second, on the column that is the primary key, the uniqueness of the column is enforced, and the structure of the data in the organization table is arranged;

Third, in the frequently used in the connected column, these columns are mainly some foreign keys, you can speed up the connection;

IV. Create an index on a column that is often required to search by scope, because the index is sorted and its specified range is continuous;

Create an index on a column that is often ordered, because the index is sorted so that the query can use the sorting of the index to speed up the sorting query time;

The index is often used to create indexes on the columns in the WHERE clause, which speeds up the judgment of the condition.

Create an index

ALTER Table name Addindex index (column name); Example: ALTER TABLE ' book ' ADD INDEX y (card);

Delete Index drop the index on table name; Example: DROP INDEX y on Xiangxiang;

Display Index

Show index from table name

To view the types of indexes that exist in a table

Index redundancy 3. Slow query Log

Show variables like "%log%";

Slow query log path

4.explain Analysis Query

Usage: Explain +select statement

Obviously, type is all, which is the worst case scenario. The Extra also has a Using filesort, which is also the worst case scenario. Optimization is a must. Type all (worst case, full table scan) Extra using Filesort (no index will be used to sort the result using an external index instead of reading from the table to the relevant content by index order)

Start optimization:

Add an Index

ALTER TABLE ' Xiangxiang ' Addindex x (' category_id ', ' comments ', ' views ');

The type becomes range, which can be tolerated. However, the use of using Filesort in extra is still unacceptable. But we've built the index, so why not? This is because according to how the BTree index works, sort category_id first, if you encounter the same category_id then sort comments, and then sort views if you encounter the same comments. When the comments field is in the middle of the federated index, because the comments > 1 condition is a range value (so-called range), MySQL cannot use the index to retrieve the subsequent views section, i.e. the index after the range type query field is invalid.

Then we need to discard the comments and delete the old index:
Drop index x on Xiangxiang;

To create a new index:

Run explain:

Problem Solving!!!

The next example of a multi-table query:

Build three tables book, class, Phone (need to insert data separately, you can use DataFactory, need to be familiar with several concepts: Left connection, right connection, you can go to Baidu)

Operation of two tables first

Left JOIN connection:

Type all, we need to optimize, optimize right table, add index

Run Explain: Optimization complete

Right connection:

This time you need to add the left table index:

Run Explain: Optimization complete

For left and right connections:

The left JOIN condition is used to determine how to search for rows from the left table, and there must be one on the right, so it is important to have an index on the other side.

The right JOIN condition is used to determine how to search for rows from the left table, and there must be all of them, so the left is our key point and must be indexed.
Do three tables (you need to remove all indexes first):

Add index of two right table

Run Explain: Optimization complete

The explain syntax in MYSQL can help us rewrite queries, optimize the structure of tables and set up indexes to maximize query efficiency. Of course, in large amounts of data, the cost of establishing and maintaining indexes is also very high, often requiring a long time and large space, and if you are indexing on different combinations of columns, the overhead of the space will be greater. Therefore, it is best to set the index in a field that needs to be queried frequently.

5.profiling Analysis Query

The slow log query lets you know which SQL statements are inefficient, and by explain we can learn how the SQL statements are executed, index usage, and so on, and you can also view the execution status with the show command.
If you feel that the explain information is not detailed enough, you can use the profiling command to get more accurate SQL execution consuming system resources information.

Specific operation:

Open: Set profiling = 1;

The time and id:show of the SQL statement being executed PROFILES\G

Details of the corresponding SQL statement execution: Show profile for query 4;

Show Profile Command format:

Show Profile All/block io/context switches/cpu/ipc/memory/page faults/source/swaps for query ID

By profiling resource consumption information, we can take targeted optimization measures.

OFF: Set profiling = 0;

Two. Here, or first to say some words, better to understand some of the details of the problem, less go some detours.

1. Types of indexes
1. Normal index: This is the most basic index type, no uniqueness and so on.
2. Uniqueness Index: Basically the same as a normal index, but all index column values remain unique.
3. Primary key: The primary key is a unique index, but must be specified as "PRIMARY key".
4. Full-Text indexing: MySQL supports full-text indexing and full-text retrieval starting from 3.23.23. In MySQL, the index type of the full-text index is fulltext. A full-text index can be created on a varchar or text-type column.
Most MySQL indexes (PRIMARY KEY, UNIQUE, index, and fulltext) are stored using the B-tree. Index of Spatial column type use R-Tree, memory table supports hash index.

2. Single-row and multi-column indexes (composite index)
The index can be a single-column index or a multicolumn index. Using indexes on related columns is one of the best ways to improve the performance of select operations.
Multi-column index:
MySQL can create indexes for multiple columns. An index can consist of 15 columns. For some column types, the left prefix of the column can be indexed, and the order of the columns is important.
A multicolumn index can be treated as an array of sorts that contains values created by connecting the values of indexed columns. In general, even the most restrictive single-column indexes can have a far less restrictive ability than multi-column indexes.

3. Leftmost prefix
A multi-column index has one feature, the leftmost prefix (leftmost prefixing). If there is a multicolumn index of key (FirstName LastName age), MySQL will use that multicolumn index when the search condition is the combination and order of the following columns:
Firstname,lastname,age
Firstname,lastname
FirstName
In other words, the equivalent of the key (FirstName LastName) and Key (FirstName) is established.

4. The index is primarily used for the following operations:
1. Quickly find the row that matches a WHERE clause.
2. Delete the row. When a join is performed, rows are retrieved from other tables.
3. Find the Max () or min () value for the column key_col that are indexed. is optimized by the preprocessor to check whether all keyword elements that occur before key_col in the index are used where key_part_# = constant. In this case, MySQL performs a keyword lookup for each min () or max () expression, replacing it with a constant. If all expressions are replaced with constants, the query returns immediately. For example:
SELECT MIN (Key2), MAX (Key2) from TB WHERE key1=10;
4. If the leftmost prefix of a usable keyword is sorted or grouped (for example, order by key_part_1,key_part_2), sort or group a table. If all keyword elements are followed by DESC, the keyword is read in reverse order.
5. In some cases, a query can be optimized so that values can be retrieved without querying the data rows. If the query uses only numeric types from a table and is the leftmost column that makes up some of the keywords, you can retrieve the values from the index tree for faster.
SELECT key_part3 from TB WHERE key_part1=1
Sometimes MySQL does not use indexes, even if there is an index available. One scenario is when the optimizer estimates that using an index will require MySQL to access most of the rows in the table. (In this case, the table scan might be faster). However, if such a query uses limit to search only a subset of rows, MySQL uses the index because it can find several rows more quickly and return in the results

5. Recommendations for proper indexing:
1. Smaller data types are usually better: smaller data types typically require less space in disk, memory, and CPU caches, and are processed faster.
2. Simple data types are better: integer data is less expensive to handle than characters, because string comparisons are more complex. In MySQL, you should use a built-in date and time data type instead of a string to store the time, and an integer data type to store the IP address.
3. Avoid null: The column should be specified as NOT NULL unless you want to store null. In MySQL, columns with null values are difficult to query optimization because they complicate indexing, index statistics, and comparison operations. You should use 0, a special value, or an empty string instead of a null value.

6. This section is a few trivial suggestions and points for attention when it comes to indexing and writing SQL statements:
1. Use limit 1 When the result set has only one row of data
2. Avoid select *, always specify the columns you need
The more data is read from the table, the more slowly the query becomes. He increases the time it takes for the disk to operate, or if the database server is separate from the Web server. You will experience a very long network delay, simply because the data is not required to be transferred between servers.
3. Use connection (join) instead of subquery (sub-queries)
Connect (Join): It is more efficient because MySQL does not need to create a temporary table in memory to complete this logical two-step query effort.
4. Using enum, CHAR instead of varchar, use reasonable field property length
5. Use not NULL where possible
6. Fixed-length tables are faster
7. Splitting a large delete or INSERT statement
8. The smaller the query column, the faster

7.Where conditions
In the query, the Where condition is also a relatively important factor, as little as possible and is reasonable where condition is very important, as far as possible in multiple conditions, will extract as little data as possible in front of the conditions, reduce the next where the query time of the condition.
Some where conditions cause the index to be invalid:
? There are in the query condition of the WHERE clause! =,mysql will not be able to use the index.
? When the WHERE clause uses the MySQL function, the index will be invalid, for example: SELECT * from TB where left (name, 4) = ' xxx '
? When searching for matches using like, the index is valid: the SELECT * from TBL1 where name is ' xxx% ', and the index is invalid if the '%xxx% '

Three. Configuration optimization

After installing MySQL, the configuration file my.cnf in the/mysql installation directory/share/mysql directory, which also contains a number of configuration files for reference, there are my-large.cnf, MY-HUGE.CNF, MY-MEDIUM.CNF,MY-SMALL.CNF, respectively, corresponding to the configuration of large and small database applications. The. ini file in the MySQL installation directory is present in the win environment.

The following is a list of major variables that have a large impact on performance optimization, which are divided into connection request variables and buffer variables.

Variables for connection requests:

1.max_connections

The maximum number of MySQL connections, increasing this value increases the number of file descriptors required by the mysqld. If the server has a large number of concurrent connection requests, it is recommended that this value be increased to increase the amount of concurrent connections, of course, this is based on the machine can support the case, because if the number of connections between MySQL will provide a connection buffer for each connection, it will cost more memory, so to adjust the value appropriately, can not blindly increase the value.
The value is too small will often appear error 1040:too many connections errors, can pass the ' conn% ' wildcard to see the current state of the number of connections to decide the size of the values

Max_used_connections/max_connections * 100% (ideal value ≈85%)
If the max_used_connections is the same as max_connections, then it is max_connections set too low or exceed the server load limit, less than 10% is set too large.

2.back_log

The number of connections that can be staged by MySQL. This works when the primary MySQL thread gets very many connection requests in a very short period of time. If the MySQL connection data reaches max_connections, the new request will be present in the stack, waiting for a connection to release the resource, the number of that stack is back_log, and if the number of waiting connections exceeds back_log, the connection resource will not be granted.

The Back_log value indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops answering a new request. Only if you expect to have a lot of connections in a short period of time, you need to increase it, in other words, the size of the listening queue for incoming TCP/IP connections.
When observing your Host process list (mysql> show full processlist), discover a lot of 264084 | Unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | Login | NULL to connect the process, it is necessary to increase the value of Back_log.
The default value is 50, which can be adjusted to 128, for Linux systems with an integer range of less than 512.

3.interactive_timeout

The number of seconds an interactive connection waits for an action before being shut down by the server. An interactive customer is defined as a customer who uses the client_interactive option for Mysql_real_connect ().
The default value is 28800, which is adjustable to 7200.

Buffer variables:

Global variables:

1.key_buffer_size

KEY_BUFFER_SIZE Specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reads. By checking the status values key_read_requests and Key_reads, you can see if the key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above status values can be obtained using the show status like ' key_read% ').
Key_buffer_size only works on MyISAM tables. Even if you do not use the MyISAM table, the internal temporary disk table is the MyISAM table and this value is used. You can use the Check status value created_tmp_disk_tables to learn more.
Examples are as follows:
Mysql> Show variables like ' key_buffer_size ';
——————-———— +

Variable_name

Value

——————————— +

Key_buffer_size

536870912

———— ———-———— +
Key_buffer_size is 512MB, let's look at the usage of key_buffer_size:
Mysql> show global status like ' key_read% ';
————————————-+

Variable_name

Value

————————————-+

Key_read_requests	27813678764
Key_reads	6798830

————————————-+
A total of 27,813,678,764 index read requests, with 6,798,830 requests not found in memory directly from the hard disk to read the index, calculate the probability of index misses cache:
Key_cache_miss_rate =key_reads/key_read_requests * 100%, set at about 1/1000 better
The default configuration value is 8388600 (8M), the host has 4GB memory and can be tuned to 268435456 (256MB).

Using query buffering, MySQL stores the query results in a buffer and will read the results directly from the buffer in the future for the same SELECT statement (case sensitive).
By checking the status value qcache_*, you can know whether the Query_cache_size setting is reasonable (the above status values can be obtained using the show status like ' qcache% '). If the value of Qcache_lowmem_prunes is very large, it indicates that there is often insufficient buffering, if the value of Qcache_hits is also very large, it indicates that the query buffer is used very frequently, the buffer size needs to be increased, and if the value of qcache_hits is small, Indicates that your query repetition rate is very low, in which case the use of query buffering will affect efficiency, then you can consider not querying the buffer. In addition, adding sql_no_cache in the SELECT statement can make it clear that query buffering is not used.

The parameters related to query buffering are Query_cache_type, Query_cache_limit, Query_cache_min_res_unit.

QUERY_CACHE_TYPE Specifies whether to use query buffering, which can be set to 0, 1, 2, which is a variable at the session level.
QUERY_CACHE_LIMIT Specifies the buffer size that can be used by a single query, which defaults to 1M.
Query_cache_min_res_unit was introduced after the 4.1 release, which specifies the smallest unit of allocation buffer space, which defaults to 4K. Checking the status value qcache_free_blocks, if the value is very large, indicates that there is a lot of fragmentation in the buffer, which indicates that the query results are relatively small and you need to reduce query_cache_min_res_unit.
Examples are as follows:
Mysql> show global status like ' qcache% ';
——————————-————— –+

Variable_name

Value

——————————-————— –+

Qcache_free_blocks	22756
Qcache_free_memory	76764704
Qcache_hits	213028692
Qcache_inserts	208894227
Qcache_lowmem_prunes	4010916
Qcache_not_cached	13385031
Qcache_queries_in_cache	43560
Qcache_total_blocks	111212

——————————-————— –+
Mysql> Show variables like ' query_cache% ';
———————————— – ———— –+

Variable_name

Value

———————————— – ——— –+

Query_cache_limit	2097152
Query_cache_min_res_unit	4096
Query_cache_size	203423744
Query_cache_type	On
Query_cache_wlock_invalidate	OFF

———————————— – ————— +
Query Cache Fragmentation Rate = Qcache_free_blocks/qcache_total_blocks * 100%
If the query cache fragmentation rate exceeds 20%, you can use flush query cache to defragment the cache, or try to reduce query_cache_min_res_unit if your queries are small data volumes.
Query Cache utilization = (query_cache_size–qcache_free_memory)/query_cache_size * 100%
Query cache utilization below 25% indicates that the query_cache_size setting is too large to be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 query_cache_ Size may be a little bit small, or too much fragmentation.
Query Cache Hit Ratio = (qcache_hits–qcache_inserts)/qcache_hits * 100%
Sample server query cache fragmentation rate =20.46%, query cache utilization =62.26%, query cache hit ratio =1.94%, the hit ratio is poor, may write more frequently, and may be some fragments.

Buffering for each connection:

6.record_buffer_size

Each thread that makes a sequential scan allocates a buffer of that size for each table it scans. If you do a lot of sequential scans, you may want to increase the value. The default value is 131072 (128K) and can be changed to 16773120 (16M) 7.read_rnd_buffer_size

The random read buffer size. When rows are read in any order (for example, in sort order), a random read buffer is allocated. When you sort a query, MySQL scans the buffer first to avoid disk searches, improve query speed, and, if you need to sort large amounts of data, raise the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriately as possible to avoid excessive memory overhead.
Can generally be set to 16M

8.sort_buffer_size

Each thread that needs to be sorted allocates a buffer of that size. Increase this value to accelerate the order by or group by operation.
The default value is 2097144 (2M) and can be changed to 16777208 (16M).

9.join_buffer_size

The size of the buffer that can be used by the Federated query operation
Record_buffer_size,read_rnd_buffer_size,sort_buffer_size,join_buffer_size is exclusive for each thread, that is, if there are 100 threads connected, it is occupied as 16m*100

10.table_cache

The size of the table cache. Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and put into it, which allows for faster access to the table contents. By checking the status values of peak time open_tables and Opened_tables, you can determine whether you need to increase the value of Table_cache. If you find that open_tables equals Table_cache, and opened_tables is growing, you need to increase the value of Table_cache (the above status values can use Show status like ' Open%tables ' obtained). Note that you cannot blindly set the Table_cache to a very large value. If set too high, it may cause insufficient file descriptors, resulting in performance instability or connection failures.
1G memory machine, the recommended value is 128-256. Server with memory around 4GB This parameter can be set to 256M or 384M.

11.max_heap_table_size

The size of the memory table that the user can create. This value is used to calculate the maximum row value for the memory table. This variable supports dynamic change, that is, set @max_heap_table_size =#
This variable, together with Tmp_table_size, limits the size of the internal memory table. If an internal heap (stacked) table is larger than Tmp_table_size,mysql, you can automatically change the in-memory heap table to the hard disk-based MyISAM table as needed.

12.tmp_table_size

Increase the size of a temporary table by setting the Tmp_table_size option, such as a temporary table generated by the advanced group by operation. If this value is raised, MySQL will also increase the size of the heap table to improve the speed of the join query, it is recommended to optimize the query as far as possible, to ensure that the temporary table generated during the query in memory, to avoid the temporary table is too large to generate a hard disk-based MyISAM table.
Mysql> show global status like ' created_tmp% ';
—————————— – ——— +

Variable_name

Value

———————————-——— +

Created_tmp_disk_tables	21197
Created_tmp_files	58
Created_tmp_tables	1771587

—————————— – ——— –+
Each time a temporary table is created, created_tmp_tables increases, and if the temporary table size exceeds tmp_table_size, the temporary table is created on disk, Created_tmp_disk_tables also increases, CREATED_TMP_ Files represent the number of temporary file files created by the MySQL service, and the ideal configuration is:
Created_tmp_disk_tables/created_tmp_tables * 100% <= 25% such as the server above Created_tmp_disk_tables/created_tmp_tables * 100% =1.20%, it should be pretty good.
Default is 16M, adjustable to 64-256 best, thread exclusive, too large may not have enough memory I/O blocking

13.thread_cache_size

The number of threads that can be reused for saving in. If there is, a new thread is obtained from the cache, and if there is space when disconnected, the customer's line is placed in the cache. If there are many new threads, the value of this variable can be increased in order to improve performance.
By comparing the variables of the connections and threads_created states, you can see the effect of this variable.
The default value is 110, which is adjustable to 80.

14.thread_concurrency

The recommended setting is twice times the number of server CPU cores, such as a dual-core CPU, then the thread_concurrency should be 4, and 2 dual-core CPUs, thread_concurrency should be a value of 8. Default is 8

15.wait_timeout

Specifies the maximum connection time for a request, which can be set to 5-10 for a server with about 4GB of memory.

Configure several variables for InnoDB:

1.innodb_buffer_pool_size
For the InnoDB table, the role of innodb_buffer_pool_size is equivalent to key_buffer_size for the MyISAM table. InnoDB uses this parameter to specify the size of memory to buffer data and indexes. For a separate MySQL database server, the maximum value can be set to 80% of physical memory.
According to the MySQL manual, the recommended value for 2G memory machines is 1G (50%).

2.innodb_flush_log_at_trx_commit
The main control is InnoDB to write the data in log buffer and flush the disk at a point in time, with values of 0, 1, and 23 respectively. 0, that is, when the transaction commits, do not do the log write operation, but every second writes the data in the log buffer to the logfile and flush the disk once, 1, in every second or every time the commit will cause the log file write, flush disk operations, to ensure that the transaction acid; set to 2, Each transaction commit causes the action to be written to the log file, but the flush disk operation is completed once per second.
The actual test found that this value has a very large effect on the speed at which the data is inserted, that it takes only 2 seconds to insert 10,000 records at 2 o'clock, 0 for 1 seconds, and 1 seconds when set to 229. Therefore, the MySQL manual also recommends merging inserts into one transaction as much as possible, which can greatly increase the speed.
Depending on the MySQL manual, you can set the value to 0 or 2 if the risk of losing the most recent transaction is allowed.

3.innodb_log_buffer_size
The log cache size, typically 1-8m, defaults to 1M, and for larger transactions, you can increase the cache size.
Can be set to 4M or 8M.

4.innodb_additional_mem_pool_size
This parameter specifies the size of the memory pool used by InnoDB to store data dictionaries and other internal structures. The default value is 1M. Usually not too big, as long as enough on the line, should be related to the complexity of the table structure. If not enough, MySQL writes a warning message to the error log.
According to the MySQL manual, for 2G memory machines, the recommended value is 20M, can be appropriately increased.

5.innodb_thread_concurrency=8
Recommended setting is Numcpus+numdisks, which is typically 8 by default

LoadRunner MySQL performance optimization