Sean58238 commented 5 years ago

Error Report for Facebook MySQL 5.6's Crashing during sysbench Testing

installation environment

CentOS Linux release 7.6.1810 (Core) Linux version 4.19.0-1.el7.elrepo.x86_64 (mockbuild@Build64R7) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-28) (GCC)) #1 SMP Mon Oct 22 10:40:32 EDT 2018 MemTotal: 196719476 kB

Facebook MySQL's version:

branch: fb-mysql-5.6.35 commit: df34666abb743015c6039e4a440cfbe10f645114 We also tried for later commits (822bfe2cb935051b626db579d9e94731ae59f244), and searched for correlated crash information. However, we had not found any solution to this problem.

Facebook MySQL's installation process

git clone https://github.com/facebook/mysql-5.6.git cd mysql-5.6 git submodule init git submodule update

cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DWITH_SSL=system \ -DWITH_ZLIB=bundled -DMYSQL_MAINTAINER_MODE=0 -DENABLED_LOCAL_INFILE=1 \ -DENABLE_DTRACE=0 -DCMAKE_CXX_FLAGS="-march=native"

make -j make install

mkdir /data mkdir /data/mysql

vim /etc/my.cnf [client] default-character-set=utf8 port=3306 user=mysql socket=/data/mysql/mysql.sock

[mysqld]
key_buffer_size=16M
read-buffer-size=8M
sort-buffer-size=8M
max_allowed_packet=64M

# basedir=/usr/local/mysql
datadir=/data/mysql/myrocks
# socket=/var/lib/mysql/mysql.sock
socket=/data/mysql/myrocks.sock
pid-file=/data/mysql/myrocks.pid

character_set_server=utf8
port=3306
user=mysql

symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/error.log

cd /usr/local/mysql ./scripts/mysql_install_db --defaults-file=/etc/my.cnf

chgrp -R mysql /data/mysql/ chown -R mysql /data/mysql/

mysqld_safe --defaults-file=/etc/my.cnf

Testing tools (sysbench)

version: sysbench 1.0.9 we use sysbench to do Testing

Testing command

first step is to prepare

./bin/sysbench \ --num-threads=100 \ --test=/home/XXX/sysbench/tests/include/oltp_legacy/oltp.lua \ --oltp-table-size=2180000 \ --oltp-tables-count=10 \ --oltp-point-selects=1 \ --oltp-simple-ranges=0 \ --oltp-sum-ranges=0 \ --oltp-order-ranges=0 \ --oltp-index-updates=1 \ --oltp-non-index-updates=0 \ --report-interval=1 \ --mysql-user=root \ --mysql-password='' \ --mysql-host='XXX' \ --mysql-port=3306 \ --mysql-db=summertest \ --max-time=900 \ --max-requests=0 \ --oltp_secondary=on \ --oltp_auto_inc=off prepare

prepare statement can run successfully

2nd step is to insert ./src/sysbench \ --num-threads=100 \ --test=/home/XXX/sysbench/tests/include/oltp_legacy/insert.lua \ --oltp-table-size=2180000 \ --oltp-tables-count=10 \ --percentile=99 \ --report-interval=1 \ --mysql-host='XXX' \ --mysql-user=root \ --mysql-password='' \ --mysql-db=summertest \ --max-time=300 \ --max-requests=0 \ --mysql-port=3306 \ --oltp_secondary=on \ --oltp_auto_inc=off \ run

but the insert statement will cause Facebook MySQL to have error

Error log for Facebook's MySQL (from /var/log/error.log)

2019-07-29 21:33:54 4450 [Note] Plugin 'FEDERATED' is disabled.
2019-07-29 21:33:54 4450 [Note] Plugin 'ROCKSDB' is disabled.
2019-07-29 21:33:54 4450 [Note] InnoDB: Using atomics to ref count buffer pool pages
2019-07-29 21:33:54 4450 [Note] InnoDB: The InnoDB memory heap is disabled
2019-07-29 21:33:54 4450 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-07-29 21:33:54 4450 [Note] InnoDB: Memory barrier is not used
2019-07-29 21:33:54 4450 [Note] InnoDB: Compressed tables use zlib 1.2.3
2019-07-29 21:33:54 4450 [Note] InnoDB: Not using CPU crc32 instructions
2019-07-29 21:33:54 4450 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2019-07-29 21:33:54 4450 [Note] InnoDB: Initializing buffer pool, total size = 128.0M, instances = 1
2019-07-29 21:33:54 4450 [Note] InnoDB: Completed initialization of buffer pool
InnoDB: Used computed checkpoint limits with 0 sync_checkpoint_limit,  100659200 capacity, preflush: 75774529/0.75 sy
nc, 70722894/0.70 async  checkpoint: 80826164/0.80 sync, 78300347/0.78 async
2019-07-29 21:33:54 4450 [Note] InnoDB: Highest supported file format is Barracuda.
2019-07-29 21:33:54 4450 [Note] InnoDB: 128 rollback segment(s) are active.
2019-07-29 21:33:54 4450 [Note] InnoDB: 5.6.35 started; log sequence number 6529231001
2019-07-29 21:33:54 4450 [Note] RocksDB storage engine is currently disabled. To enable it please add the following o
ptions to mysqld command-line: --skip-innodb --rocksdb --default-storage-engine=rocksdb --default-tmp-storage-engine=
MyISAM or add these settings to my.cnf configuration file under [mysqld] section.
2019-07-29 21:33:54 4450 [Note] [rpl_wait_for_semi_sync_ack] Last ACKed pos initialized to: :18446744073709551615
2019-07-29 21:33:54 4450 [Note] RSA private key file not found: /data/mysql/myrocks//private_key.pem. Some authentica
tion plugins will not work.
2019-07-29 21:33:54 4450 [Note] RSA public key file not found: /data/mysql/myrocks//public_key.pem. Some authentication plugins will not work.
2019-07-29 21:33:54 4450 [Note] Server hostname (bind-address): '*'; port: 3306
2019-07-29 21:33:54 4450 [Note] IPv6 is available.
2019-07-29 21:33:54 4450 [Note]   - '::' resolves to '::';
2019-07-29 21:33:54 4450 [Note] Server socket created on IP: '::'.
2019-07-29 21:33:54 4450 [Note] Event Scheduler: Loaded 0 events
2019-07-29 21:33:54 4450 [Note] MySQL git hash: 822bfe2cb935051b626db579d9e94731ae59f244 - %cI
2019-07-29 21:33:54 4450 [Note] RocksDB git hash: ba64a4cf52cce5cf180135e5aeddaa90b7887f9d - %cI
2019-07-29 21:33:54 4450 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.35'  socket: '/data/mysql/myrocks.sock'  port: 3306  Source distribution
13:35:37 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=8388608
max_used_connections=48
max_threads=151
thread_count=47
connection_count=48
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2493238 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x2f6e9d0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
2019-07-29 21:35:37 4565 [Note] Plugin 'FEDERATED' is disabled.
2019-07-29 21:35:37 4565 [Note] Plugin 'ROCKSDB' is disabled.
2019-07-29 21:35:37 4565 [Note] InnoDB: Using atomics to ref count buffer pool pages

Output for sysbench

Running the test with following options:
Number of threads: 100
Report intermediate results every 1 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

FATAL: unable to connect to MySQL server on host '10.239.131.217', port 3306, aborting...
(last message repeated 1 times)
FATAL: error 2013: Lost connection to MySQL server at 'reading initial communication packet', system error: 104
FATAL: unable to connect to MySQL server on host '10.239.131.217', port 3306, aborting...
FATAL: unable to connect to MySQL server on host '10.239.131.217', port 3306, aborting...

Additional information

We have ensured that there was enough memory for the program and unlimited memory for each process. We installed such Facebook MySQL version on 2 CentOS machines and 1 ubuntu machine, all of them had such issue. The crash did not always happen. Sometimes it ran successfully, but other times it crashed. Even if we adjusted key_buffer_size, read_buffer_size, and other buffer size, the crashing still happened. If we adjusted the number of threads to be smaller, or the table size to be smaller, the possibility of crashing would decrease.

mdcallag commented 5 years ago

Confirm via SHOW ENGINES that RocksDB is enabled ... 2019-07-29 21:33:54 4450 [Note] Plugin 'FEDERATED' is disabled. 2019-07-29 21:33:54 4450 [Note] Plugin 'ROCKSDB' is disabled. ...

Next, attach gdb to mysqld while the test is running to get a stack trace from the crash.