MySQL Forums
Forum List  »  Performance

innodb_sync_array_size
Posted by: Khurram Naseem
Date: January 05, 2018 05:37AM

Hi ,

When we run "SHOW ENGINE INNODB STATUS" we see following output under SEMAPHORES section

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 459319
OS WAIT ARRAY INFO: reservation count 445630
OS WAIT ARRAY INFO: reservation count 465648
OS WAIT ARRAY INFO: reservation count 443940
OS WAIT ARRAY INFO: reservation count 457842
OS WAIT ARRAY INFO: reservation count 445450
OS WAIT ARRAY INFO: reservation count 466656
OS WAIT ARRAY INFO: reservation count 444287
OS WAIT ARRAY INFO: reservation count 458399
OS WAIT ARRAY INFO: reservation count 445876
OS WAIT ARRAY INFO: reservation count 466047
OS WAIT ARRAY INFO: reservation count 444201
OS WAIT ARRAY INFO: reservation count 457613
OS WAIT ARRAY INFO: reservation count 444926
OS WAIT ARRAY INFO: reservation count 467009
OS WAIT ARRAY INFO: reservation count 444665
OS WAIT ARRAY INFO: reservation count 459320
OS WAIT ARRAY INFO: reservation count 445604
OS WAIT ARRAY INFO: reservation count 466747
OS WAIT ARRAY INFO: reservation count 443806
OS WAIT ARRAY INFO: reservation count 459788
OS WAIT ARRAY INFO: reservation count 445194
OS WAIT ARRAY INFO: reservation count 466869
OS WAIT ARRAY INFO: reservation count 443698
OS WAIT ARRAY INFO: reservation count 458305
OS WAIT ARRAY INFO: reservation count 446218
OS WAIT ARRAY INFO: reservation count 467132
OS WAIT ARRAY INFO: reservation count 444617
OS WAIT ARRAY INFO: reservation count 458877
OS WAIT ARRAY INFO: reservation count 446812
OS WAIT ARRAY INFO: reservation count 467047
OS WAIT ARRAY INFO: reservation count 445130
OS WAIT ARRAY INFO: reservation count 458579
OS WAIT ARRAY INFO: reservation count 445450
OS WAIT ARRAY INFO: reservation count 466912
OS WAIT ARRAY INFO: reservation count 444262
OS WAIT ARRAY INFO: reservation count 457582
OS WAIT ARRAY INFO: reservation count 444966
OS WAIT ARRAY INFO: reservation count 466015
OS WAIT ARRAY INFO: reservation count 443768
OS WAIT ARRAY INFO: reservation count 458454
OS WAIT ARRAY INFO: reservation count 445695
OS WAIT ARRAY INFO: reservation count 467481
OS WAIT ARRAY INFO: reservation count 444681
OS WAIT ARRAY INFO: reservation count 458611
OS WAIT ARRAY INFO: reservation count 445157
OS WAIT ARRAY INFO: reservation count 468686
OS WAIT ARRAY INFO: reservation count 445660
OS WAIT ARRAY INFO: reservation count 459265
OS WAIT ARRAY INFO: reservation count 446008
OS WAIT ARRAY INFO: reservation count 468105
OS WAIT ARRAY INFO: reservation count 443679
OS WAIT ARRAY INFO: reservation count 459822
OS WAIT ARRAY INFO: reservation count 444829
OS WAIT ARRAY INFO: reservation count 467091
OS WAIT ARRAY INFO: reservation count 444305
OS WAIT ARRAY INFO: reservation count 458720
OS WAIT ARRAY INFO: reservation count 444778
OS WAIT ARRAY INFO: reservation count 466710
OS WAIT ARRAY INFO: reservation count 445412
OS WAIT ARRAY INFO: reservation count 459785
OS WAIT ARRAY INFO: reservation count 444861
OS WAIT ARRAY INFO: reservation count 467216
OS WAIT ARRAY INFO: reservation count 443458
OS WAIT ARRAY INFO: reservation count 458625
OS WAIT ARRAY INFO: reservation count 446340
OS WAIT ARRAY INFO: reservation count 466121
OS WAIT ARRAY INFO: reservation count 444450
OS WAIT ARRAY INFO: reservation count 460309
OS WAIT ARRAY INFO: reservation count 446393
OS WAIT ARRAY INFO: reservation count 467432
OS WAIT ARRAY INFO: reservation count 443996
OS WAIT ARRAY INFO: reservation count 459882
OS WAIT ARRAY INFO: reservation count 445516
OS WAIT ARRAY INFO: reservation count 466703
OS WAIT ARRAY INFO: reservation count 443889
OS WAIT ARRAY INFO: reservation count 459346
OS WAIT ARRAY INFO: reservation count 446171
OS WAIT ARRAY INFO: reservation count 466451
OS WAIT ARRAY INFO: reservation count 443303
OS WAIT ARRAY INFO: reservation count 458923
OS WAIT ARRAY INFO: reservation count 445010
OS WAIT ARRAY INFO: reservation count 466492
OS WAIT ARRAY INFO: reservation count 443826
OS WAIT ARRAY INFO: reservation count 459770
OS WAIT ARRAY INFO: reservation count 446120
OS WAIT ARRAY INFO: reservation count 467934
OS WAIT ARRAY INFO: reservation count 443650
OS WAIT ARRAY INFO: reservation count 458685
OS WAIT ARRAY INFO: reservation count 446451
OS WAIT ARRAY INFO: reservation count 466426
OS WAIT ARRAY INFO: reservation count 444551
OS WAIT ARRAY INFO: reservation count 456919
OS WAIT ARRAY INFO: reservation count 444988
OS WAIT ARRAY INFO: reservation count 466823
OS WAIT ARRAY INFO: reservation count 443227
OS WAIT ARRAY INFO: reservation count 459119
OS WAIT ARRAY INFO: reservation count 445883
OS WAIT ARRAY INFO: reservation count 467060
OS WAIT ARRAY INFO: reservation count 443671
OS WAIT ARRAY INFO: reservation count 458486
OS WAIT ARRAY INFO: reservation count 445309
OS WAIT ARRAY INFO: reservation count 466831
OS WAIT ARRAY INFO: reservation count 444635
OS WAIT ARRAY INFO: reservation count 459108
OS WAIT ARRAY INFO: reservation count 445994
OS WAIT ARRAY INFO: reservation count 466192
OS WAIT ARRAY INFO: reservation count 443579
OS WAIT ARRAY INFO: reservation count 459545
OS WAIT ARRAY INFO: reservation count 445029
OS WAIT ARRAY INFO: reservation count 466983
OS WAIT ARRAY INFO: reservation count 443486
OS WAIT ARRAY INFO: reservation count 458868
OS WAIT ARRAY INFO: reservation count 445127
OS WAIT ARRAY INFO: reservation count 467395
OS WAIT ARRAY INFO: reservation count 443629
OS WAIT ARRAY INFO: reservation count 458909
OS WAIT ARRAY INFO: reservation count 445765
OS WAIT ARRAY INFO: reservation count 466823
OS WAIT ARRAY INFO: reservation count 443985
OS WAIT ARRAY INFO: reservation count 459414
OS WAIT ARRAY INFO: reservation count 446055
OS WAIT ARRAY INFO: reservation count 467675
OS WAIT ARRAY INFO: reservation count 444085
OS WAIT ARRAY INFO: reservation count 458923
OS WAIT ARRAY INFO: reservation count 444655
OS WAIT ARRAY INFO: reservation count 466756
OS WAIT ARRAY INFO: reservation count 445047
OS WAIT ARRAY INFO: signal count 74893523
RW-shared spins 0, rounds 114129233, OS waits 27493849
RW-excl spins 0, rounds 380635597, OS waits 5959376
RW-sx spins 2386525, rounds 51024784, OS waits 724774
Spin rounds per wait: 114129233.00 RW-shared, 380635597.00 RW-excl, 21.38 RW-sx

We have set db parameter "innodb_sync_array_size" to 128 and we see significant reduction in CPU spikes, but still we are not sure "Spin rounds per wait" is high or low or what we can do to reduce this number ( in case if its high ) .

We are running MySQL version 5.7.16 on AWS RDS.

Options: ReplyQuote


Subject
Views
Written By
Posted
innodb_sync_array_size
1426
January 05, 2018 05:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.