Shulou(Shulou.com)06/01 Report--

Please understand that the level of originality is limited.

Although I have learned these parameters before, they have not been proved at the source level, so they have not been written, but today there are

The friend asked, so first according to the official manual plus my own understanding to explain, in the future must be added at the source level

Use MYSQL version: 5.7.14

OS platform: CentOS release 6.5 (Final) 64bit

I. theoretical basis

First of all, to understand a few parameters, we must first know the following, note that the following is not in-depth, and only my own understanding

1. What is multithreading

In fact, multithreading in MYSQL is the set of POSIX, for example, what we call pthread family functions such as pthread_create, pthread_join, pthread_mutex_lock and so on. It is believed that there is a basis for multithreading programming.

The friend is no stranger, threads are also called lightweight processes (LWP) what are the advantages of so many threads, compared to the process, multi-threads share a lot of things, such as

1. File descriptor table

two。 The way each signal is processed.

3. Current working directory

4. User ID and group ID

5. Memory space other than stack

In fact, when we are programming, multithreaded communication is through non-stack memory processes, such as heap space, since threads can share so many resources, whether thread creation, context switching, and inter-thread communication.

It becomes convenient (note that sharing is convenient, but the management of critical areas needs to be implemented using locks such as mutex rwlock). Next we're going to talk about context switching between threads.

At the same time, keep in mind that threads are the smallest unit of CPU scheduling and processes are the smallest unit of resource allocation. With a picture.

2. Context switching of threads

We know that LINUX is a multi-channel and multi-user time-sharing operating system, which allows multiple tasks to enter memory at the same time. CPU is scheduled through time wheels. For example, if I have a 2-core CPU, but I currently have 4.

If two MYSQL threads of the same priority enter the ready queue, there are actually two MYSQL threads that can execute in parallel at the same time. What about the other two? Of course.

In the ready queue, wait for the CPU time to complete the work. When the 2 thread time wheels are used up, you need to keep the processor field. In fact, you need to save the value of the register to memory and then give up.

CPU, enter the ready state, at this time the two threads in the ready queue can enter the CPU to work, this kind of four threads execute concurrently, but only two threads get the time wheel parallel execution (get the CPU wheel) continuously.

You need to get the CPU wheel-- > > work-- > > Save the register value to memory-- > > in the way of abandoning the CPU wheel, we save the register value to memory, which is called thread context switching, which comes at a price, of course.

My understanding may be one-sided because I am not in the LINUX kernel after all. The more threads that need to execute concurrently, the more frequently this context switch, which is why we can observe when the LINUX load is high.

To more reasons for context switching (as can be seen by vmstat), we say that if you limit the number of threads that can be executed concurrently at the same time, context switching will be reduced, in a sense, long pain is better than short pain, instead of letting you keep

If you switch above, you might as well give up the right to use CPU while you are asleep.

Here is a brief account of the shortcomings of threads:

Thread instability (library function implementation)

Thread debugging is difficult (gdb support is not good)

It is very difficult to use the signal

3. the problem of thread hunger in small things

If too many threads have used MUTEX in programming, those who preempt and try the lock will not forget that after one thread releases MUTEX, other threads will get it in a preemptive way. Some threads may be unlucky and can't get it all the time.

Between the same priority threads, if the scheduling of OS is not balanced, then some threads with small tasks may not get CPU wheels, while large task threads will always get CPU wheels, which depends on the thread scheduling strategy of OS, so it is possible to form small

For the problem of task thread hunger, instead of relying on the scheduling strategy of OS, it is better to set a rule by itself, so that the thread that uses the wheel for a certain period of time is asleep first to give up the use of CPU.

Second, parameter interpretation

All right, with the above theoretical knowledge, we can explain these parameters.

In fact, these three parameters are used to solve the above problems.

1 、 innodb_thread_concurrency

The number of threads that can enter the innodb level and execute concurrently at the same time (note that concurrency is not parallel). If the number of CPU cores is exceeded, some threads may be in a ready state and do not get the CPU time wheel. If the thread in the SERVER layer is larger than this value, I am sorry for the redundant

The thread will be placed in a queue called wait queue, and will not be able to enter the INNODB layer, and of course will not be able to work without entering the innodb layer, let alone get the CPU. Since it is a queue, it must satisfy the principle of first in, first out. This is also said earlier that long-term pain is better than short-term pain. Instead of letting you keep switching above, it is better to give up the right to use CPU while you are asleep. The default value is 0, which means there is no limit.

2 、 innodb_concurrency_tickets

This parameter is set to a tickets, and the default is 5000. I don't know exactly how long it represents. From the official documents, it is related to the number of lines of transaction. Naturally, there are more rows of big things to deal with, and of course there are fewer small things. At least we can think of it as the time to get CPU, and it will continue to decrease during work. If it is reduced to 0, the thread will be put into the innodb level and enter the waiting queue mentioned earlier. Of course, it is at the end of the queue. Suppose that a small thing is queuing up to enter the innodb layer, or that it has entered the innodb layer without getting the CPU time wheel, and suddenly a big thing tickets is exhausted and put forward the innodb layer, then this small thing can naturally get the CPU wheel to work, and the small thing is executed very quickly, after the execution is completed.

Other things can get CPU work as soon as possible, will not be due to OS thread scheduling uneven problem caused by the small thing hunger problem, which is easy to understand. That is to say, instead of relying on the scheduling strategy of OS, it is better to set a rule for threads that use a certain amount of time to abandon the use of CPU in a sleeping state.

3 、 innodb_thread_sleep_delay

According to the official manual, this parameter represents the time it takes to sleep after things are kicked out of the innodb level. After waiting for sleep to finish and re-enter the wait que queue 5.6.3, you can set innodb_adaptive_max_sleep_delay to automatically adjust innodb_thread_sleep_delay, which is more convenient, because it is difficult to say how much is appropriate, and its unit is microseconds. In a sense, this value aggravates the execution time of big things. It is easier for small things to enter INNODB.

The hierarchy gets CPU time to work.

With regard to these values, once innodb_thread_concurrency is set to 0, the settings of other values have no effect, which is easy to understand. Set to 0.

Later means there is no limit, if there is no limit, there will be no waiting queue, there is no point in not waiting for how long the queue sleeps into the waiting queue.

If set to 0, the lower face value of show engine status is always 0

0 queries inside InnoDB, 0 queries in queue

Here is a picture that you have drawn according to your understanding:

Here is an official recommended setting for innodb_thread_concurrency:

? If the number of concurrent user threads for a workload is less than 64, set

Innodb_thread_concurrency=0.

? If your workload is consistently heavy or occasionally spikes, start by setting

Innodb_thread_concurrency=128, and lowering the value to 96, 80, 64, and so on, until you

Find the number of threads that provides the best performance. For example, suppose your system

Typically has 40 to 50 users, but periodically the number increases to 60, 70, or even 200. You find that

Performance is stable at 80 concurrent users but starts to show a regression above this number. In

This case, you would set innodb_thread_concurrency=80 to avoid impacting performance.

? If you do not want InnoDBto use more than a certain number of vCPUs for user threads (20 vCPUs

For example), set innodb_thread_concurrency to this number (or possibly lower, depending

On performance results). If your goal is to isolate MySQL from other applications, you may consider

Binding the mysqldprocess exclusively to the vCPUs. Be aware, however, that exclusive binding

Could result in non-optimal hardware usage if the mysqldprocess is not consistently busy. In this

Case, you might bind the mysqldprocess to the vCPUs but also allow other applications to use some

Or all of the vCPUs.

At least we know that if you want to set innodb_thread_concurrency should not be higher than the number of CPU cores, for example, we can set 1.5 times the number of * CPU cores.

For this section, you can also refer to the MYSQL official manual.

Section 15.4.6, "Configuring Thread Concurrency for InnoDB".

Third, how to observe

Now we know that the main ways of observation are show engine innodb status and innodb_trx, and the state of things will be

Sleeping before entering InnoDB

For better observation, my settings here are as follows:

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_thread_concurrency | 1 | |

+-+ +

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_concurrency_tickets | 10 | |

+-+ +

The implication is that I set the number of threads that can enter innodb at the same time to 1, while tickets is set to 10 to observe this continuous entry into innodb as much as possible.

Hierarchical, and then tickets to be proposed innodb hierarchical phenomenon, and then I did two big things

Okay, I can observe at show engine innodb status as follows:

-TRANSACTION 162307, ACTIVE 133sec sleeping before entering InnoDB

Mysql tables in use 2, locked 2

767 lock struct (s), heap size 106968, 212591 row lock (s), undo log entries 15451

MySQL thread id 14, OS thread handle 140736751912704, query id 1077 localhost root Sending data

Insert into testui select * from testui

-TRANSACTION 162302, ACTIVE 320sec, thread declared inside InnoDB 1

Mysql tables in use 2, locked 2

2477 lock struct (s), heap size 336344, 609049 row lock (s), undo log entries 83582

MySQL thread id 13, OS thread handle 140737153779456, query id 1050 localhost root Sending data

Insert into testti3 select * from testti3

-

Notice the sleeping before entering InnoDB here.

And then you can observe

1 queries inside InnoDB, 1 queries in queue

It is also obvious that one thread is in the innodb and the other is waiting in the queue.

It can be observed in innodb_trx:

Time A:

Mysql > select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx\ G

* * 1. Row *

Trx_id: 162612

Trx_state: RUNNING

Trx_query: insert into testti3 select * from testti3

Trx_operation_state: sleeping before entering InnoDB

Trx_concurrency_tickets: 0

* 2. Row * *

Trx_id: 422212176322720

Trx_state: RUNNING

Trx_query: insert into testui select * from testui

Trx_operation_state: fetching rows

Trx_concurrency_tickets: 2

2 rows in set (0.01sec)

Time B:

Mysql > select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx\ G

* * 1. Row *

Trx_id: 162612

Trx_state: RUNNING

Trx_query: insert into testti3 select * from testti3

Trx_operation_state: NULL

Trx_concurrency_tickets: 10

* 2. Row * *

Trx_id: 422212176322720

Trx_state: RUNNING

Trx_query: insert into testui select * from testui

Trx_operation_state: sleeping before entering InnoDB

Trx_concurrency_tickets: 0

2 rows in set (0.32 sec)

From the trx_operation_state, we can see that they are constantly rotating into the innodb level, at the same time, we can also see

Active things trx_concurrency_tickets this tickets continues to decrease, while in the sleeping before entering InnoDB

The trx_concurrency_tickets of the thing is 0.

4. Things wait to enter the innodb layer stack

Although there is no research on the source code, the stack is still typed out for later study.

# 0 0x0000003ca620ef3d in nanosleep () from / lib64/libpthread.so.0

# 1 0x0000000001a80c73 in os_thread_sleep (tm=1026) at / root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/os/os0thread.cc:278

# 2 0x0000000001b74e81 in srv_conc_enter_innodb_with_atomics (trx=0x7fffeeca15d0) at / root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0conc.cc:214

# 3 0x0000000001b74fcb in srv_conc_enter_innodb (prebuilt=0x7fffb41b7110) at / root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0conc.cc:259

# 4 0x000000000199c8c8 in innobase_srv_conc_enter_innodb (prebuilt=0x7fffb41b7110)

At / root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:1671

# 5 0x00000000019a856d in ha_innobase::write_row (this=0x7fffb41b6b60, record=0x7fffb41af0d0 "\ 375\ 001")

At / root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:7920

# 6 0x0000000000f72e73 in handler::ha_write_row (this=0x7fffb41b6b60, buf=0x7fffb41af0d0 "\ 375\ 001") at / root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:8228

# 7 0x00000000017d0c10 in write_record (thd=0x7fffb402eb20, table=0x7fffb41b61b0, info=0x7fffb40283f0, update=0x7fffb4028468)

At / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_insert.cc:1864

# 8 0x00000000017d2117 in Query_result_insert::send_data (this=0x7fffb40283a8, values=...) At / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_insert.cc:2262

# 9 0x000000000155f954 in end_send (join=0x7fffb40286d0, qep_tab=0x7fffb41e4948, end_of_records=false)

At / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:2919

# 10 0x000000000155c515 in evaluate_join_record (join=0x7fffb40286d0, qep_tab=0x7fffb41e47d0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1639

# 11 0x00000000015646b7 in QEP_tmp_table::end_send (this=0x7fffb4028ad0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:4710

# 12 0x000000000155b508 in sub_select_op (join=0x7fffb40286d0, qep_tab=0x7fffb41e47d0, end_of_records=true)

At / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1063

# 13 0x000000000155b640 in sub_select (join=0x7fffb40286d0, qep_tab=0x7fffb41e4658, end_of_records=true)

At / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:1220

# 14 0x000000000155b1ba in do_select (join=0x7fffb40286d0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:946

# 15 0x0000000001559060 in JOIN::exec (this=0x7fffb40286d0) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:199

# 16 0x00000000015f932a in handle_query (thd=0x7fffb402eb20, lex=0x7fffb4031100, result=0x7fffb40283a8, added_options=1342177280, removed_options=0)

At / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:184

# 17 0x00000000017d4d5f in Sql_cmd_insert_select::execute (this=0x7fffb4028330, thd=0x7fffb402eb20) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_insert.cc:3199

# 18 0x00000000015a6bea in mysql_execute_command (thd=0x7fffb402eb20, first_level=true) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:3719

# 19 0x00000000015ad15a in mysql_parse (thd=0x7fffb402eb20, parser_state=0x7fffec12c600) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836

# 20 0x00000000015a1019 in dispatch_command (thd=0x7fffb402eb20, com_data=0x7fffec12cd70, command=COM_QUERY)

At / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447

# 21 0x000000000159fe4a in do_command (thd=0x7fffb402eb20) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010

# 22 0x00000000016e1d9c in handle_connection (arg=0x3a06b60) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312

# 23 0x0000000001d72180 in pfs_spawn_thread (arg=0x413d3d0) at / root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188

# 24 0x0000003ca62079d1 in start_thread () from / lib64/libpthread.so.0

# 25 0x0000003ca5ee8b6d in clone () from / lib64/libc.so.6

Author Wechat:

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.