SEMAPHORES - Semaphores have to do with waiting for internal thread locks in the OS
---------------------
OS WAIT ARRAY INFO: reservation count 90528934
OS WAIT ARRAY INFO: signal count 210610453
OS WAIT ARRAY INFO: reservation count 13569, signal count 11421
--Thread 1152170336 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds the semaphore:
Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0
waiters flag 0
wait is ending
--Thread 1147709792 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds the semaphore:
Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0
waiters flag 0
wait is ending
Mutex spin waits 153873448, rounds 52690538, OS waits 1037962
RW-shared spins 66106957, rounds 2526364206, OS waits 72728391
RW-excl spins 22519696, rounds 862681564, OS waits 15671776
Spin rounds per wait: 0.34 mutex, 38.22 RW-shared, 38.31 RW-excl
1st Information:
One is list of current waits. This section will only contain any entries if you’re running in high concurrency envinronment, so Innodb has to fall back to OS waits frequently. If wait was resolved via Spinlock it will not be seen in this section.
Looking at this section you can get an idea what might be hot spot in your workload. It however requires some knowledge of source code – you only get file names and lines (which are different in different versions), you get no information what this object is responsible for. You however can well guess from file names – in this case file is “buf0buf.ic” what means there is some buffer pool contention. However if you want to know more – you need to browse source.
You also see some details printed about wait. “lock var” is current value for the mutex object (locked=1/free=0) , “waiters flag” is current number of waiters, plus you can see wait status information “wait is ending” in this case which means mutex is already free for grabs but os has not yet scheduled thread so it could proceed with execution.
2nd Information:
The second piece of information is event counters – “reservation count” and “signal count” show how actively innodb uses internal sync array – how frequently slots are allocated in it and how frequently threads are signaled using sync array. These counters can be used to represent frequency with which Innodb needs to fall back to OS Wait. There is direct information about OS waits as well – you can see “OS Waits” for
mutexes, as well as for read-write locks. For these information both for exclusive locks and for shared locks is displayed. OS Wait is not exactly the same as “reservation” – before falling back to complex wait using sync_array Innodb tries to “yield” to OS hoping when name thread is scheduled next time object will be free already. OS Waits are relatively slow, and if you get tens of thousands of OS waits per second it may be the problem. The other way to look at it is context switch rate in your OS stats.
The other important peice of information is number of “spin waits” and “spin rounds”. Spin locks are low cost wait, compared to OS wait, it is however active wait which wastes your CPU cycles, so if you see very large amount of spin waits and spin rounds significant CPU resources may be wasted. It should come to hundreds of thousands spin rounds per second to start really worry for most CPUs. innodb_sync_spin_loops can be used to ballance between wasting CPU time running spin locks and doing unneeded context switches.
Note: innodb_thread_concurrency = 0;
InnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to the limit given by this variable (InnoDB uses operating system threads to process user transactions). Once the number of threads reaches this limit, additional threads are placed into a wait state within a “First In, First Out” (FIFO) queue for execution. Threads waiting for locks are not counted in the number of concurrently executing threads.
The range of this variable is 0 to 1000. A value of 0 (the default) is interpreted as infinite concurrency (no concurrency checking). Disabling thread concurrency checking enables InnoDB to create as many threads as it needs. A value of 0 also disables the queries inside InnoDB and queries in queue counters in the ROW OPERATIONS section of SHOW ENGINE INNODB STATUS output.
References:
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_thread_concurrency
https://www.percona.com/blog/2006/07/17/show-innodb-status-walk-through/
http://dba.stackexchange.com/questions/123703/query-about-mysql-semaphores-in-engine-status
---------------------
OS WAIT ARRAY INFO: reservation count 90528934
OS WAIT ARRAY INFO: signal count 210610453
OS WAIT ARRAY INFO: reservation count 13569, signal count 11421
--Thread 1152170336 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds the semaphore:
Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0
waiters flag 0
wait is ending
--Thread 1147709792 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds the semaphore:
Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0
waiters flag 0
wait is ending
Mutex spin waits 153873448, rounds 52690538, OS waits 1037962
RW-shared spins 66106957, rounds 2526364206, OS waits 72728391
RW-excl spins 22519696, rounds 862681564, OS waits 15671776
Spin rounds per wait: 0.34 mutex, 38.22 RW-shared, 38.31 RW-excl
1st Information:
One is list of current waits. This section will only contain any entries if you’re running in high concurrency envinronment, so Innodb has to fall back to OS waits frequently. If wait was resolved via Spinlock it will not be seen in this section.
Looking at this section you can get an idea what might be hot spot in your workload. It however requires some knowledge of source code – you only get file names and lines (which are different in different versions), you get no information what this object is responsible for. You however can well guess from file names – in this case file is “buf0buf.ic” what means there is some buffer pool contention. However if you want to know more – you need to browse source.
You also see some details printed about wait. “lock var” is current value for the mutex object (locked=1/free=0) , “waiters flag” is current number of waiters, plus you can see wait status information “wait is ending” in this case which means mutex is already free for grabs but os has not yet scheduled thread so it could proceed with execution.
2nd Information:
The second piece of information is event counters – “reservation count” and “signal count” show how actively innodb uses internal sync array – how frequently slots are allocated in it and how frequently threads are signaled using sync array. These counters can be used to represent frequency with which Innodb needs to fall back to OS Wait. There is direct information about OS waits as well – you can see “OS Waits” for
mutexes, as well as for read-write locks. For these information both for exclusive locks and for shared locks is displayed. OS Wait is not exactly the same as “reservation” – before falling back to complex wait using sync_array Innodb tries to “yield” to OS hoping when name thread is scheduled next time object will be free already. OS Waits are relatively slow, and if you get tens of thousands of OS waits per second it may be the problem. The other way to look at it is context switch rate in your OS stats.
The other important peice of information is number of “spin waits” and “spin rounds”. Spin locks are low cost wait, compared to OS wait, it is however active wait which wastes your CPU cycles, so if you see very large amount of spin waits and spin rounds significant CPU resources may be wasted. It should come to hundreds of thousands spin rounds per second to start really worry for most CPUs. innodb_sync_spin_loops can be used to ballance between wasting CPU time running spin locks and doing unneeded context switches.
Note: innodb_thread_concurrency = 0;
InnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to the limit given by this variable (InnoDB uses operating system threads to process user transactions). Once the number of threads reaches this limit, additional threads are placed into a wait state within a “First In, First Out” (FIFO) queue for execution. Threads waiting for locks are not counted in the number of concurrently executing threads.
The range of this variable is 0 to 1000. A value of 0 (the default) is interpreted as infinite concurrency (no concurrency checking). Disabling thread concurrency checking enables InnoDB to create as many threads as it needs. A value of 0 also disables the queries inside InnoDB and queries in queue counters in the ROW OPERATIONS section of SHOW ENGINE INNODB STATUS output.
References:
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_thread_concurrency
https://www.percona.com/blog/2006/07/17/show-innodb-status-walk-through/
http://dba.stackexchange.com/questions/123703/query-about-mysql-semaphores-in-engine-status
No comments:
Post a Comment