Quick benchmark

Windows:
sysbench 4.0

sysbench --test=oltp --oltp-table-size=1000000 --mysql-host=localhost --mysql-db=sbtest --mysql-user=root --mysql-password= --db-driver=mysql
--mysql-table-engine=innodb --max-time=60 --oltp-test-mode=complex --oltp-read-only=off --max-requests=0 --num-threads=%2
--oltp-point-selects=0 --oltp-simple-ranges=0 --oltp-sum-ranges=0 --oltp-order-ranges=0 --oltp-distinct-ranges=0
--oltp-index-updates=1 --oltp-delete-inserts=0 --oltp-non-index-updates=0 --oltp-skip-trx=on --oltp-dist-type=uniform --mysql-socket=MySQL %1

Quick benchmark, Linux (Hyper-V) ,same box as above

== my.cnf ==

[mysqld]

back_log=500

user=root

port=3306

max_connections=4096

max_connect_errors=5000

max_prepared_stmt_count=500000

table_open_cache=10000

transaction_isolation=REPEATABLE-READ

innodb_status_file=0

innodb_flush_method=O_DIRECT

innodb_buffer_pool_size=3G

innodb_log_file_size=4G

innodb_log_buffer_size=100M                                                                                             innodb_doublewrite=1

innodb_flush_log_at_trx_commit=1                                           

innodb-io-capacity=20000    

innodb-io-capacity-max=50000  

innodb-use-native-aio=1                                                                                               

 loose-enable-named-pipe

innodb-stats-persistent=0                                                                                               innodb_write_io_threads=4   

innodb-buffer-pool-instances=1      

Sysbench 1.0 script

==update_index.sh ==
sysbench --test=/usr/share/sysbench/oltp_update_index.lua --table-size=1000000 --mysql-host=localhost\
--mysql-db=sbtest --mysql-user=wlad --mysql-password= --db-driver=mysql --rand-type=uniform \
--max-time=60 --max-requests=0 --num-threads=$2 \
--skip-trx=on --mysql-socket=/tmp/mysql.sock $1 $3

run whole benchmark
for i in 1 2 4 8 16 32 64 128 256 512 1000
do
./update_index.sh cleanup 1
./update_index.sh prepare 1
./update_index.sh run $i
done

I tested the changes on my 2-socket system (Intel® Xeon® CPU E5-2630 v4 @ 2.20GHz), that is, with 2×10 CPU cores (40 with hyperthreading).
The base revision was 10.5 ded128aa9b3d40775e626a08a744a93d9ba71b01, and I did cherry-pick bb-10.5-MDEV-21534 fcb5d00816778d249569c2736e33158f0f5af255 on top of it.

I used sysbench version 1.0.18+ds-1 on Debian GNU/Linux unstable and the following update_index.sh script:

#!/bin/sh

exec sysbench --test=/usr/share/sysbench/oltp_update_index.lua --table-size=1000000 --mysql-host=localhost\

--mysql-db=sbtest --mysql-user=root --mysql-password= --db-driver=mysql --rand-type=uniform \

--max-time=60 --max-requests=0 --num-threads=$2 \

--skip-trx=on --mysql-socket=/tmp/mysql.sock $1

I created the database by running ./mtr innodb.101_compatibility,16k and then copying mysql-test/var/install.db to the root file system (ext4fs on an Intel Optane PCI-X NVMe device SSDPED1D960GAY).
The reported latency was virtually the same, and there is a clear trend that the number of transactions improves. Maybe we should have set innodb_change_buffering=none to reduce randomness further:

Sum of latency:

Note: I used the following kinds of commands to construct the above tables:

sed -ne 's/^ *write: *//p'< baseline.txt|tr \\012 \|; echo

sed -ne 's/^ *sum: *//p'< baseline.txt|tr \\012 \|; echo

I tested a build from bb-10.5-MDEV-21534 with sysbench and noticed odd behavior. The server would sporadically stop all operations (I waited several minutes before giving up).

And - maybe even more seriously - it was impossible to shutdown the server gracefully. It would just print a lot of warnings to the error log (warning: thread xxx did not stop) but not go down. I had to resort to sigkill to get rid of it.

axel, can you describe "how to reproduce"`? Nobody has seen "odd behaviour", but you. If there is no way to reproduce, please share the all threads callstacks.

The repository contains several commits for the MDEV-21534
4ce687d6d1f53be3b6928739d11febe9e1b936c7 fixes a potential deadlock, so it is necessary to know which commit was tested
axel ^

There is one easy way to cause a server hang: use parallel_prepare.lua (distributed with sysbench-mariadb) to load multiple tables in parallel. I just tried with 16 tables and it didn't even reach the "loading rows" stage.
I pulled stack traces with PMP. I tried to shutdown the server and got the same warnings in the error log. This time I waited a bit longer and InnoDB started printing "long semaphore wait" messages. I attach both the stack dump and the error log.

Files pprepare.stack.dump and pprepare.errlog.gz

I remember that when I spotted this, I reconfigured the sysbench script to load tables sequentially, but then got a hang during a benchmark run. A cannot reproduce it right now, but keep trying.

As for which commit it was - I cannot say. The build finished on Jan 21st 15:11 UTC and I pulled some minutes before. Wait, looks like I did not pull in this repo after that. It's at commit fcb5d008 now, so probably was at that when I built. And yes, that is before that deadlock fix.

I think that a merge of (or rebase to) the latest 10.5 is needed for the development branch. Some hangs were introduced in 10.5 by MDEV-16678 and MDEV-16264, and I have not seen any hangs since this commit marked with MDEV-21551.

OK, with commit 09fa2894d9e I did not see any anomalies from branch bb-10.5-MDEV-21534 anymore.