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

This article mainly shows you "what is the impact of Sort_Buffer_Size settings on server performance in mysql", which is easy to understand and well-organized, hoping to help you solve your doubts. Let me lead you to study and learn about the impact of Sort_Buffer_Size settings on server performance in mysql.

The influence of Sort_Buffer_Size setting on server performance

Basics:

one. Sort_Buffer_Size is a connection-level parameter that allocates the set memory at once when each connection needs to use this buffer for the first time.

two. Sort_Buffer_Size is not the bigger the better, because it is a connection-level parameter, too large settings + high concurrency may deplete system memory resources.

three. The document says "On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation"

This paper mainly tests on the third point:

It is said that when Sort_Buffer_Size exceeds 2KB, mmap () instead of malloc () is used for memory allocation, resulting in inefficiency. Environment:

In order to reflect the performance gap, the Fedora virtual machine with 1GB memory is used for testing.

Test table structure:

Table structure of 1w rows

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (10) unsigned | NO | PRI | NULL | auto_increment |

| | k | int (10) unsigned | NO | MUL | 0 | |

| | c | char (120) | NO | |

| | pad | char (60) | NO | |

+-+ +

Test statement:

Set the Sort_Buffer_Size to 250K, 512K, 3m, respectively, and then run the following statement to see the run time.

1. Sql_no_cache prevents query cache from taking effect.

2. Limit 1 in order to reduce the proportion of sorting in execution time, more reflect the impact of memory allocation

3. The result of the statement explain is filesort to ensure that sort_buffer is used

Mysqlslap-uroot-h227.0.0.1-Q 'select sql_no_cache * from sbtest order by pad limit 1'-c 100-- create-schema=test-I 10

Copy the code

Test results:

Execution time

250K: 1.318s

512K: 1.944s

3M: 2.412s250 K

[root@localhost tmp] # mysqlslap-uroot-h227.0.0.1-Q 'select sql_no_cache * from sbtest order by pad limit 1'-c 100-- create-schema=test-I 10

Benchmark

Average number of seconds to run all queries: 1.318 seconds

Minimum number of seconds to run all queries: 1.285 seconds

Maximum number of seconds to run all queries: 1.378 seconds

Number of clients running queries: 100

Average number of queries per client: 1

512 K

[root@localhost tmp] # mysqlslap-uroot-h227.0.0.1-Q 'select sql_no_cache * from sbtest order by pad limit 1'-c 100-- create-schema=test-I 10

Benchmark

Average number of seconds to run all queries: 1.944 seconds

Minimum number of seconds to run all queries: 1.362 seconds

Maximum number of seconds to run all queries: 4.406 seconds

Number of clients running queries: 100

Average number of queries per client: 1

3M

[root@localhost tmp] # mysqlslap-uroot-h227.0.0.1-Q 'select sql_no_cache * from sbtest order by pad limit 1'-c 100-- create-schema=test-I 10

Benchmark

Average number of seconds to run all queries: 2.412 seconds

Minimum number of seconds to run all queries: 2.376 seconds

Maximum number of seconds to run all queries: 2.527 seconds

Number of clients running queries: 100

Average number of queries per client: 1 above are all the contents of this article entitled "what is the impact of Sort_Buffer_Size settings on server performance in mysql?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.