Innodb:warning:a Long semaphore Wait Solution

View Database error log today, found innodb:warning:a long semaphore wait: Error:

Solution:

The original Linux kernel semaphore default setting is too small, causing a lot of waiting,
Default
# Cat/proc/sys/kernel/sem
250 32000 32 128
Description
The first column that represents the maximum number of semaphores per signal set.
The second column represents the total number of maximum semaphores in the system range.
The third column represents the maximum number of system operations per signal occurrence.
The fourth column represents the maximum number of lumped signals in the system range.


Turn up the third column and refer to the data on the Internet.
echo "kernel.sem=250 32000 100 128″>>/etc/sysctl.conf
Then Sysctl-p
Restart MySQL

The problem can be solved if the error no longer occurs

Specific details of the error below, detailed comparison of reference:

Innodb:warning:a Long semaphore wait: 813--thread 139957495039744 has waited at btr0cur.cc line 545 for 241.00 seconds the semaphore:
814 X-lock (WAIT_EX) on Rw-latch at 0x7f4a60043da8 created in file dict0dict.cc line 2341
815 a writer (thread ID 139957495039744) has reserved it in mode wait exclusive
816 number of readers 2, Waiters flag 1, Lock_word:fffffffffffffffe
817 last time read locked in file btr0cur.cc line 554
818 last time write locked on file/root/mysql-5.6.10/storage/innobase/btr/btr0cur.cc line 545
819 InnoDB: ###### starts InnoDB Monitor for secs to print diagnostic info:
820 innodb:pending preads 109, Pwrites 0
821
822 =====================================
823 2014-04-21 18:27:21 7f4899a00700 INNODB MONITOR OUTPUT
824 =====================================
825 per second averages calculated from the last seconds
826-----------------
827 BACKGROUND THREAD
828-----------------
829 srv_master_thread loops:704 srv_active, 0 srv_shutdown, 184 srv_idle
Cndb012.5405.zr.sh.gcimg.net.err [RO] 812,1 6%


[root@cndb012 mysql]# Cat/proc/sys/kernel/sem
250 32000 32 128
[root@cndb012 mysql]# Vim Cndb012.5405.zr.sh.gcimg.net.err


805 2014-04-21 18:04:55 16100 [note] Slave I/O thread exiting, read up to log ' mysql-bin.000124 ', position 61975299
812 innodb:warning:a long semaphore wait:
813--thread 139957495039744 has waited at btr0cur.cc line 545 for 241.00 seconds the semaphore:
814 X-lock (WAIT_EX) on Rw-latch at 0x7f4a60043da8 created in file dict0dict.cc line 2341
815 a writer (thread ID 139957495039744) has reserved it in mode wait exclusive
816 number of readers 2, Waiters flag 1, Lock_word:fffffffffffffffe
817 last time read locked in file btr0cur.cc line 554
818 last time write locked on file/root/mysql-5.6.10/storage/innobase/btr/btr0cur.cc line 545
819 InnoDB: ###### starts InnoDB Monitor for secs to print diagnostic info:
820 innodb:pending preads 109, Pwrites 0
821
822 =====================================
823 2014-04-21 18:27:21 7f4899a00700 INNODB MONITOR OUTPUT
824 =====================================
825 per second averages calculated from the last seconds
826-----------------
827 BACKGROUND THREAD
828-----------------
829 srv_master_thread loops:704 srv_active, 0 srv_shutdown, 184 srv_idle
830 srv_master_thread log Flush and writes:887
831----------
832 semaphores
833----------
834 OS Wait ARRAY info:reservation count 75098
835--thread 139948735776512 has waited at row0sel.cc line 3048 for 0.00 seconds The semaphore:
836 S-lock on Rw-latch at 0x7f48aa6535c0 created in file buf0buf.cc line 994
837 a writer (thread ID 139957491046144) has reserved it in mode exclusive
838 number of readers 0, waiters flag 1, lock_word:0
839 last time read locked in file not yet reserved line 0
840 last time write locked on file/root/mysql-5.6.10/storage/innobase/buf/buf0buf.cc line 3466
841--thread 139949644658432 has waited at row0sel.cc line 4111 for 1.00 seconds the semaphore:
842 S-lock on Rw-latch at 0X7F49D1BD1EC0 created in file buf0buf.cc line 994
843 a writer (thread ID 139948734711552) has reserved it in mode exclusive
844 number of readers 0, waiters flag 1, lock_word:0
845 last time read locked in file not yet reserved line 0
846 last time write locked on file/root/mysql-5.6.10/storage/innobase/buf/buf0buf.cc line 3466
847--thread 139949722711808 has waited at btr0cur.cc line 554 for 77.00 seconds the semaphore:
848 S-lock on Rw-latch at 0x7f4a60043da8 created in file dict0dict.cc line 2341
849 a writer (thread ID 139957495039744) has reserved it in mode wait exclusive
850 number of readers 2, Waiters flag 1, Lock_word:fffffffffffffffe
851 last time read locked in file btr0cur.cc line 554
852 last time write locked on file/root/mysql-5.6.10/storage/innobase/btr/btr0cur.cc line 545
853--thread 139945323890432 has waited at btr0cur.cc line 554 for 77.00 seconds the semaphore:
854 s-lock on Rw-latch at 0x7f4a60043da8 created in file dict0dict.cc line 2341
855 a writer (thread ID 139957495039744) has reserved it in mode wait exclusive
856 number of readers 2, Waiters flag 1, Lock_word:fffffffffffffffe

857 last time read locked in file btr0cur.cc line 554

.....................