Today found a system InnoDB spin rounds per wait is negative, it feels strange, it turns out to be a bug:
forExample (output fromPS But we had no patches here),----------semaphores----------OS WAIT ARRAY info:reservationCount 19811539060OS WAIT ARRAY info:signalCount 8333711487Mutex Spin Waits192857078576, Rounds290389036704, OS waits1568180069RW-Shared spins19747848205, Rounds565755834160, OS waits14799492482RW-Excl Spins7774140570, Rounds144578121130, OS waits3188338144Spin rounds per wait:-697.30Mutex-327.60RW-Shared-177.22RW-Exclthis isCaused by (Double) Mutex_spin_round_count/(mutex_spin_wait_count? Mutex_spin_wait_count:1), conditional operator Part:mutex_spin_wait_count is notAn integral typevarBut a C++Object withType conversion operator toib_int64_t.1 isA constant ofType (Signed)int. Now C++Language rules somehow decide (credit toMy colleague Igor Solodovnikov) that the type ofThe result ofConditional operator is intToo. This can also is confirmed bydisassembling sync_print_wait_info: Leaq __zl21mutex_spin_wait_count (%RIP),%R15 # Mutex_spin_wait_count.this toR15 ... addq the(%Rcx%R15),%RDX # Countersum toRDX ( onlyThe finalAddShown, RCX= 0) ... cvtsi2sdl%EdX%XMM1 # EDX notRdx! OpCode isAlso for"Doublewordinteger"... divsd%XMM1,%xmm0the Bug was introduced together withib_counter_t Introduction. Before, the counter variables being plain integers were working OK (thus5.5 is notaffected) How toRepeat:code analysis, running serverLongEnough, Thenlooking at the status. Suggested fix:probably the easiest fix isS/1/1ULL/GinchThe conditional operators. But at the same time observe so each counter object mention causes a fresh summation forThe counter. Thus Better toIntroduce local VARs and to sumEach counter only once. Also get rid ofRedundant typecastsinchThe first printf, andResolve the ib_int64_t/ib_uint64_t mismatch.[Dec 14:09]Laurynas Biveinisbug79703Fix for 5.6(*) IConfirmThe code being submitted isOffered under the terms ofThe OCA, andThat I am authorized tocontribute it. Contribution:bug79703.patch (Application/Octet-Streamtext),3.17KiB.[Dec 14:11]Laurynas Biveinis withProposed patchinteger to DoubleConversion uses CVTSI2SDQ%Rax%xmm2, which looks correct.[Feb 20:52]Staffan Flinkwe've also hits this bug but it seems that the correct numbers for'Spin rounds per wait'can retrieved from I_S.INNODB_METRICS.snippet output from'SHOW ENGINE INNODB Status\g':* ************************** 1. Row ***************************type:innodbname:status:=====================================2016-02-29 17:04:50 7f1613cb2700 INNODB MONITOR output=====================================per second averages calculated from the last I-SE Conds-----------------BACKGROUND THREAD-----------------srv_master_thread loops:27828639 srv_active, 0 srv_shutdown , 552 Srv_idlesrv_master_thread log flush and writes:27829190----------semaphores----------OS WAIT ARRAY Info:reservati On Count 562748487OS WAIT ARRAY info:signal count 4440218264Mutex spin waits 4031631980, Rounds 19046728943, OS waits 221 855330rw-shared spins 1337835479, rounds 10346069330, OS waits 159065324rw-excl spins 436493896, rounds 12607401587, OS WA Its 149384138Spin rounds per wait: -72.33 mutexes, 7.73 rw-shared, 28.88 rw-excloutput from i_s.innodb_metrics:mysql> use Information_schema;database changedmysql> Select Name,count,max_count,avg_count from innodb_metrics where name'%Spin%'; +-----------------------------+-------------+-------------+--------------------+| name | count | max_count | avg _count |+-----------------------------+-------------+-------------+--------------------+| Innodb_rwlock_s_spin_waits | 1338920718 | 1338920718 | 48.02786821549153 | | Innodb_rwlock_x_spin_waits | 436953500 | 436953500 | 15.673777268638679 | | Innodb_rwlock_s_spin_rounds | 10353523860 | 10353523860 | 371.3869483301454 | | Innodb_rwlock_x_spin_rounds | 12620856222 | 12620856222 | 452.7174844992445 |+-----------------------------+-------------+-------------+--------------------+4 rows in Set ( 0.00 sec) This would suggests the IT'S'SHOW ENGINE INNODB STATUS'That interprets the numbers incorrectly. forInformation, we're running 5.6.23.
InnoDB Spin rounds per wait may be negative on the >32 bit machine