atomic 内存序_MySQL因ARM弱内存序读写死锁问题说明

网络拓扑

Sysbench安装在一台x86服务器上，MySQL database安装在一台ARM服务器上，通过交换机连接。

-

CPU

DDR

磁盘类型

操作系统

Sysbench

E5-2695 v4/2.10GHz/72core

384G

SATA

Euler 2.0(4.16.2)

MySQL

HI1620/2.5GHz/96core

512G

SSD

Ubuntu 16.04(4.4.0)

数据集

测试数据库包含64个表，每张表包含10000000条数据。

测试脚本

使用sysbench自带oltp.lua进行测试，具体测试命令如下：

/ssd/zqsource/rds-perf/source/sysbench-0.5/sysbench/sysbench --test=/ssd/zqsource/rds-perf/source/sysbench-0.5/sysbench/tests/db/oltp.lua --oltp-tables-count=64 --oltp-table-size=10000000 --mysql-host=$HOST --mysql-port=$PORT --mysql-db=$TABLE --mysql-user=$USER --mysql-password=$PASSWD --oltp-read-only=off --oltp-point-selects=10 --oltp-simple-ranges=1 --oltp-sum-ranges=1 --oltp-order-ranges=1 --oltp-distinct-ranges=1 --max-requests=0 --max-time=$TIME --report-interval=5 --num-threads=$THREAD run

MySQL配置文件[mysqld_safe]

log-error=/data/mysql/log/mysql.log

pid-file=/data/mysql/run/mysqld.pid

[client]

socket=/data/mysql/run/mysql.sock

default-character-set=utf8mb4

[mysqld]

basedir=/home/mysql-8.0.17-linux-glibc2.12-x86_64

#basedir=/usr/local/mysql

tmpdir=/data/mysql/tmp

datadir=/data/mysql/data

socket=/data/mysql/run/mysql.sock

port=3306

user=root

default_authentication_plugin=mysql_native_password

innodb_page_size=4k

max_connections=2000

back_log=2048

performance_schema=OFF

max_prepared_stmt_count=128000

#file

innodb_file_per_table

innodb_log_file_size=2048M

innodb_log_files_in_group=32

innodb_open_files=10000

table_open_cache_instances=64

#buffers

innodb_buffer_pool_size=230G

innodb_buffer_pool_instances=16

innodb_log_buffer_size=2048M

#tune

sync_binlog=1

innodb_flush_log_at_trx_commit=1

innodb_use_native_aio=1

innodb_spin_wait_delay=180

innodb_sync_spin_loops=25

innodb_flush_method=O_DIRECT

innodb_io_capacity=30000

innodb_io_capacity_max=40000

innodb_lru_scan_depth=9000

innodb_page_cleaners=16

innodb_spin_wait_pause_multiplier=25

#perf special

innodb_flush_neighbors=0

innodb_write_io_threads=24

innodb_read_io_threads=16

innodb_purge_threads=32

sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES

log-bin=mysql-bin

ssl=0

table_open_cache=15000