|
Not a complete answer, but I was looking recently at innodb_flush_method=O_DIRECT_NO_FSYNC and its documented as not recommended and disables AIO. I don't know how dated the XFS mention in the docs is, or what FS you are using but this may have an effect.
MariaDB has deprecated and removed innodb_log_files_in_group in 10.6 so a suitable alternate is innodb_log_file_size=32G.
4k io_capacity looks low for NVME but it is equal on MySQL and MariaDB.
|
|
Should I try with innodb_flush_method=O_DIRECT ?
The /var/lib partition is using XFS filesystem.
I will try with innodb_log_file_size=32G later.
|
|
First of all, the MariaDB Server 10.6.11 release was unfortunate to include the performance regression MDEV-29988.
For InnoDB performance, I believe that the two most critical parameters are innodb_buffer_pool_size and innodb_log_file_size. In MariaDB 10.5 or later, thanks to MDEV-21351 and some other changes, there should be no problem to set the log size to be several times the buffer pool size: crash recovery should never run out of memory, like it can on MySQL or on earlier versions of MariaDB.
Configuring a large write-ahead log size will remove one reason for page write-back. If some data pages are being repeatedly modified by the workload, the infrequent log checkpoints would allow many data page writes to be omitted or combined.
To my understanding, MySQL 8.0 at some point abandoned the circular redo log format, and are now dynamically creating several log files. MariaDB retains the circular log. In MariaDB Server 10.8, the format change MDEV-14425 enabled an optimization for log write performance, MDEV-27774.
|
|
I'm currently testing MariaDB 10.8.6 with innodb_flush_method=O_DIRECT & innodb_log_file_size=100G.
Will let you guys know the result when it is done.
|
|
Here is the result for the MariaDB 10.8.6 benchmark with innodb_flush_method=O_DIRECT & innodb_log_file_size=100G.
There's a slight improvement where the peak TPM reached 1.5 million TPM at 100 virtual users.
However, it is still far behind MySQL 8.34 peak performance.
What should I try next?
|
|
Also 10.8.6 will suffer from the MDEV-29988 performance regression. You could wait for the releases, which will hopefully be out this week, or you could grab a development snapshot, say, from http://hasky.askmonty.org/archive/10.8/build-52688/ for the currently latest 10.8.
|
|
Here is the benchmark result for the latest MariaDB 10.8.7 with
innodb_log_file_size=100G
innodb_buffer_pool_size=200G
innodb_flush_method=O_DIRECT
*Other configurations are the same as before.
The MariaDB TPM performance is back to below 1 million.
At vUser=30 and TPM=967894. the CPU usage is roughly 25%.
At vUser=100 and TPM=916571, the CPU usage is roughly 85%.
No IOwait.
What's possibly causing this?
Installed MariaDB version details :
mysql Ver 15.1 Distrib 10.8.7-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper
|
|
MariaDB 10.8.7 has not been released yet. Development snapshots typically identify themselves as the next minor version number release. The source code revision (a Git commit hash) should be included in the startup message, since recently:
if (!opt_help)
|
sql_print_information("Starting MariaDB %s source revision %s as process %lu",
|
server_version, SOURCE_REVISION, (ulong) getpid());
|
To find out what could cause the poor performance, it is often useful to collect some metrics and plot them over time. Potential explanations include MDEV-29401, MDEV-26055, MDEV-29967. I think that axel needs to reproduce this and conduct some more analysis.
|
|
e1d this could be an artifact from HammerDB. Those lines
dbset db maria
|
...
|
dbset db mysql
|
make HammerDB use different TCL scripts from ...HammerDB/src/. There have recently been some changes to said scripts that could well explain what you are seeing. For the comparison MySQL/MariaDB this is easily remedied: pick the same DBMS for both tests.
Also: the number of transactions per minute (TPM) is not comparable between different DBMS. For comparison you have to look at new order transactions per minute (NOTPM). Those mean the same. In case of MySQL/MariaDB you could also monitor internal counters, i.e. for the number of total queries per second.
Background
HammerDB uses a set of 5 "transactions" in a pseudo random order but with well-defined distribution. But: a HammerDB "transaction" is not to be confused with a database transaction. In the best case it means the same, but in some cases a HammerDB "transcaction" may consist of several database transactions.
The number of new order "transactions" (NOTPM) is counted at the HammerDB side, it's simply the number of executions of the neword TCL procedure. Each DBMS has it's own implementation of the procedure in .../HammerDB/src/${DBMS}/${DBMS}oltp.tcl
The number of transactions (TPM) is however not counted directly, but using a DBMS status variable, in case of MariaDB it is the sum of Com_commit and Com_rollback.
|
|
I see. I will try to re-generate the data and run HammerDB using mysql script on MariaDB later.
I will also using New Order Per Minute (NOPM) values to plot the graph.
|
|
I'm currently running the benchmark using the mysql TCL script.
The result for the NOPM is kind of consistent with the TPM (I'll post the detailed result later).
However, I found some interesting information that might help.
I monitored the MariaDB server with Percona Monitoring and Management (PMM) during this benchmark, and I found out that this graph is consistent with the NOPM & TPM for MariaDB :
*The dip in the graph is due to HammerDB stopping and starting the benchmark with a different number of vUsers.
How to increase the innodb i/o data writes?
|
|
SET GLOBAL innodb_max_dirty_pages_pct_lwm=0.001;
|
should enable more eager page writes, rate-limited by innodb_io_capacity. See also MDEV-26055, MDEV-27295 and Reasons for writeback with an update-in-place B-Tree.
|
|
Should I increase the innodb_io_capacity too?
|
|
Here's the result plotted using the NOPM values.
Even after increasing the innodb_io_capacity to 40000, and setting innodb_max_dirty_pages_pct_lwm=0.001, it doesn't improve MariaDB overall performance. Feels like there is something still holding back MariaDB from fully utilizing the server’s performance.
|
|
e1d OK, that is not a HammerDB issue, but real. You already increased the redo log to half the buffer pool, that is what I'd recommend too.
What could be worth trying, is to restrict MariaDB to only one socket using numactl. The problem is possibly mutex contention of the buffer pool, due to high IO load. Using multiple NUMA nodes makes this typically worse.
|
|
@axel do you think the removal of innodb_buffer_pool_instances caused this performance regression?
I read this article >> https://medium.com/@sumitlakradev/numa-support-in-mariadb-8aa8bb2a2c75 and it said that the innodb_buffer_pool_instances supposed to be default to 1 instance per numa node. Now that it is removed, how does the current MariaDB handle the buffer pool when the server has multiple socket/numa nodes?
|
|
> do you think the removal of innodb_buffer_pool_instances caused this performance regression?
This was a quite well tested change that was the last step after removing a lot of contention.
> Rather than making assumptions on the causes, can we take some more measurements.
- install debug info packages
- Using perf take some measurements after the warmup during the main part of the test run
Suggested Perf recording. In principle were looking at hot cases, so aim to get 1000 samples. This will show us things that occurred 0.1% of the time and keep the impact on the test run minimal. Samples is the frequency multiple by the time of recording. Drop the frequency and get a lightly longer recording. -g is to get stack traces.
- perf record -F 10 -p $(pidof mariadbd) -g -o ~/mariadb-xx-frequency.perf – sleep 100
Record this for all numa nodes and for a single numa node like axel suggested. This is starting mariadbd directly as an argument to numactl or from systemd, NUMAMask=node_number (nodenum from numactl -H), and CPUAffinity=numa.
The nature of a performance with regard to multinode can be characterized by replacing the -F 10 above with (One at a time). Watch the sample number generated more or less than time (I assume less) in the sleep statement. Oversampling to coverage a range of server activities is good in moderation (so 0.1 seconds is a bit meaningless).
- -e offcore_requests.all_data_rd
- -e LLC-load-misses -e LLC-store-misses
- -e dTLB-load-misses -e dTLB-store-misses
- -e iTLB-load-misses
- -e node-load-misses -e node-store-misses
See perf list for details
With all these recordings, perf report -i file can show you outcomes.
With -g slow to see top based samples.
Sometimes --no-children is useful to show some truely hot functions and sometime even where in the function is particularly hot (some locks).
--stdio is useful for a text report to attach here.
bcc-offcputime might also provide some insights but it might take a little tuning to keep the noise down.
|
|
My own benchmarks with sysbench-tpcc have shown me a regression in MariaDB 10.6.11 that is partially resolved by the new release of MariaDB 10.6.12. The best results I see however with MariaDB 10.5 - i.e. MariaDB 10.5.19:
But this is just fallout from regression testing which is running on a much smaller machine, a 16 core / 32 hyper threads XEON. We know that MySQL has an edge on a machine with high core number. Additionally you tried AMD EPYC. Our own tests show reduced throughput on AMD EPYC compared to latest generation Intel XEON.
|
|
I have somewhat similar situation just not compared to MySQL 8.0.x but MariaDB 10.4.x
Tested with 10.6.x and now the recently released 10.11.2 on a replica and the performance is drastically lower (to the point the replica can't ever catch up).
I have tried to play around with innodb_max_dirty_pages_pct / innodb_max_dirty_pages_pct_lwm to mimic the old flushing behavior, change the redo log / io capacity but it doesn't help much (except maybe less stalls).
Strange thing is that the performance doesn't change (is the same erratic) if I switch from (optimistic) parallel replication to the single-thread mode.
|
|
There might be some help for this in the first commit of MDEV-26055. I have been waiting for axel’s performance test results on that.
|
|
I also have tried the sysbench-tpcc. Here is the result summary for 64 threads :
TLDR:
MySQL 8.0.32 average transaction per second = 18274
MariaDB 10.11.2 average transaction per second = 9192
*The MySQL 8.0.32 average tps will go as high as 25000+ with 128 threads.
**MariaDB average tps is ~8976 with 128 threads.
MariaDB 10.11:
SQL statistics:
queries performed:
read: 35779792
write: 37135471
other: 5516104
total: 78431367
transactions: 2757956 (9192.18 per sec.)
queries: 78431367 (261409.21 per sec.)
ignored errors: 11779 (39.26 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0320s
total number of events: 2757956
Latency (ms):
min: 0.35
avg: 6.96
max: 258.56
95th percentile: 29.19
sum: 19196982.39
Threads fairness:
events (avg/stddev): 43093.0625/348.34
execution time (avg/stddev): 299.9528/0.01
MySQL 8.0.32:
SQL statistics:
queries performed:
read: 71134808
write: 73843532
other: 10965654
total: 155943994
transactions: 5482731 (18274.21 per sec.)
queries: 155943994 (519768.84 per sec.)
ignored errors: 24014 (80.04 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0246s
total number of events: 5482731
Latency (ms):
min: 0.34
avg: 3.50
max: 77.74
95th percentile: 10.46
sum: 19193666.75
Threads fairness:
events (avg/stddev): 85667.6719/1651.96
execution time (avg/stddev): 299.9010/0.01
|
|
For MariaDB Server 10.11 and later, MDEV-25341 introduced a performance regression MDEV-31953.
In MariaDB Server 10.6 and later, fixing MDEV-32050 may bring some performance improvement.
|
|
As additional data from a Cascade Lake system 2 x Intel(R) Xeon(R) Platinum 8280L CPU @ 2.70GHz - in HammerDB v4.9 we have added a “No stored procedure” client only SQL implementation for MySQL and MariaDB so have been testing this feature (not prepared statements at this point).
The attached data shows HammerDB workload TPROC-C – back to back load and 32 tests of 2 minutes rampup and 5 minute test – NOPM is tpmC / TPM is database transactions (TPM is appx 2.3X NOPM)
As shown in attached images:
1. With stored procedures both MariaDB (1.28X) and MySQL (1.54X) performed better than the Client SQL version but MySQL 8 has better scalability. Client SQL MariaDB and MySQL perform very similar (statements are not prepared in this initial release). As expected there is a big difference in the Bytes received/sent.
80 Active Virtual Users configured
BYTES_RECEIVED: 22847015761 / BYTES_RECEIVED: 3548506639 = 6X
BYTES_SENT: 50668052468 / 6335812312 = 8X
2. MariaDB 10.11 performed very similar to MySQL 5.7 with stored procedures
3. When using a local socket MariaDB experienced a drop off in performance, whereas on MySQL a local socket performed best
4. MySQL 8.0.34 scales better than MySQL 8.0.19 – MySQL 8.0.20 introduced the CATS/VATS lock scheduler which coincided with the scalability improvement
5. It was not possible to get good comparative performance from sysbench-tpcc with 1 tableset and 1000 warehouses (using multiple tablesets does not test scalability)
Build and Test scripts
./hammerdbcli auto ./scripts/tcl/mysql/tprocc/mysql_build.tcl
#!/bin/tclsh
|
# maintainer: Pooja Jain
|
|
puts "SETTING CONFIGURATION"
|
dbset db mysql
|
dbset bm TPC-C
|
|
diset connection mysql_host localhost
|
diset connection mysql_port 3507
|
diset connection mysql_socket /tmp/mysql.sock
|
|
set vu 64
|
set warehouse 1000
|
diset tpcc mysql_count_ware $warehouse
|
diset tpcc mysql_num_vu $vu
|
diset tpcc mysql_user root
|
diset tpcc mysql_pass mysql
|
diset tpcc mysql_dbase tpcc
|
diset tpcc mysql_storage_engine innodb
|
if { $warehouse >= 200 } {
|
diset tpcc mysql_partition true
|
} else {
|
diset tpcc mysql_partition false
|
}
|
puts "SCHEMA BUILD STARTED"
|
buildschema
|
puts "SCHEMA BUILD COMPLETED"
|
./hammerdbcli auto ./scripts/tcl/mysql/tprocc/mysql_run.tcl
#!/bin/tclsh
|
# maintainer: Pooja Jain
|
|
set tmpdir $::env(TMP)
|
puts "SETTING CONFIGURATION"
|
dbset db mysql
|
dbset bm TPC-C
|
|
diset connection mysql_host localhost
|
diset connection mysql_port 3507
|
diset connection mysql_socket /tmp/mysql.sock
|
|
diset tpcc mysql_user root
|
diset tpcc mysql_pass mysql
|
diset tpcc mysql_dbase tpcc
|
diset tpcc mysql_driver timed
|
diset tpcc mysql_rampup 2
|
diset tpcc mysql_duration 5
|
diset tpcc mysql_allwarehouse false
|
diset tpcc mysql_timeprofile false
|
diset tpcc mysql_no_stored_procs false
|
|
loadscript
|
puts "TEST STARTED"
|
foreach z { 1 2 4 8 12 16 20 24 28 32 36 40 44 48 52 56 60 64 68 72 76 80 84 88
|
92 96 100 104 108 112 116 120 } {
|
vuset vu $z
|
vucreate
|
tcstart
|
tcstatus
|
vurun
|
vudestroy
|
tcstop
|
after 30000
|
}
|
puts "TEST COMPLETE"
|
my.cnf files
mariadb.cnf
[mysqld]
|
skip-log-bin
|
datadir=/data/data_11.1
|
default_authentication_plugin=mysql_native_password
|
bind_address=localhost
|
socket=/tmp/mariadb.sock
|
port=3306
|
log-error=/tmp/mariadb.log
|
# general
|
userstat=1
|
max_connections=100000
|
table_open_cache=2000
|
table_open_cache_instances=16
|
back_log=1500
|
ssl=0
|
max_prepared_stmt_count=128000
|
skip_log_bin=1
|
character_set_server=latin1
|
collation_server=latin1_swedish_ci
|
transaction_isolation=REPEATABLE-READ
|
# files
|
innodb_file_per_table
|
innodb_log_file_size=32000M
|
innodb_open_files=500005
|
# buffers
|
innodb_buffer_pool_size=48000M
|
#innodb_buffer_pool_instances=16
|
innodb_log_buffer_size=64M
|
# tune
|
innodb_doublewrite=0
|
#innodb_thread_concurrency=0
|
innodb_flush_log_at_trx_commit=0
|
join_buffer_size=32K
|
sort_buffer_size=32K
|
innodb_use_native_aio=1
|
innodb_stats_persistent=1
|
innodb_spin_wait_delay=6
|
innodb_max_purge_lag=0
|
innodb_max_purge_lag_delay=1
|
#innodb_log_file_write_through=ON
|
#innodb_log_file_buffering=OFF
|
#innodb_data_file_write_through=ON
|
#innodb_data_file_buffering=OFF
|
#innodb_checksum_algorithm=none
|
innodb_io_capacity=200
|
innodb_io_capacity_max=2000
|
innodb_lru_scan_depth=128
|
innodb_change_buffering=none
|
innodb_read_only=0
|
#innodb_page_cleaners=2
|
innodb_adaptive_hash_index=0
|
innodb_undo_log_truncate=off
|
innodb_undo_tablespaces=1
|
innodb_fast_shutdown=0
|
# perf special
|
innodb_max_dirty_pages_pct=1
|
innodb_max_dirty_pages_pct_lwm=0.1
|
innodb_adaptive_flushing=1
|
innodb_adaptive_flushing_lwm=0.1
|
innodb_flush_neighbors=0
|
innodb_read_io_threads=8
|
innodb_write_io_threads=8
|
innodb_purge_threads=8
|
innodb_read_ahead_threshold=0
|
innodb_buffer_pool_dump_at_shutdown=0
|
innodb_buffer_pool_load_at_startup=0
|
|
my8.cnf
|
[mysqld]
|
skip-log-bin
|
datadir=/data/mysql819_data
|
default_authentication_plugin=mysql_native_password
|
bind_address=localhost
|
socket=/tmp/mysql.sock
|
port=3306
|
log-error=/tmp/mysql.log
|
max_connections=1000
|
table_open_cache=2000
|
table_open_cache_instances=16
|
back_log=1500
|
ssl=0
|
performance_schema=OFF
|
max_prepared_stmt_count=12800
|
skip_log_bin=1
|
character_set_server=latin1
|
collation_server=latin1_swedish_ci
|
transaction_isolation=REPEATABLE-READ
|
# files
|
innodb_file_per_table=1
|
innodb_log_file_size=32768M
|
# buffers
|
innodb_buffer_pool_size=32G
|
innodb_buffer_pool_instances=16
|
innodb_log_buffer_size=64M
|
# tune
|
innodb_doublewrite=0
|
innodb_thread_concurrency=0
|
innodb_flush_log_at_trx_commit=0
|
innodb_max_dirty_pages_pct=90
|
innodb_max_dirty_pages_pct_lwm=10
|
join_buffer_size=32K
|
sort_buffer_size=32K
|
innodb_use_native_aio=1
|
innodb_stats_persistent=1
|
innodb_spin_wait_delay=6
|
innodb_max_purge_lag_delay=300000
|
innodb_max_purge_lag=0
|
innodb_flush_method=O_DSYNC
|
innodb_checksum_algorithm=none
|
innodb_io_capacity=4000
|
innodb_io_capacity_max=20000
|
innodb_lru_scan_depth=9000
|
innodb_change_buffering=none
|
innodb_read_only=0
|
innodb_page_cleaners=2
|
innodb_undo_log_truncate=off
|
# perf special
|
innodb_adaptive_flushing=1
|
innodb_flush_neighbors=0
|
innodb_read_io_threads=16
|
innodb_write_io_threads=16
|
innodb_purge_threads=4
|
innodb_adaptive_hash_index=0
|
|
|
|
Attached flamegraphs for the workload running with stored procedures for 80 Virtual Users on MariaDB and MySQL 5.7 and 8. One difference is that above mtr_t::commit there is native_queued_spin_lock_slowpath and ut_delay on MariaDB and MySQL 5.7 respectively but not MySQL 8 that uses a different redo implementation https://dev.mysql.com/blog-archive/mysql-8-0-new-lock-free-scalable-wal-design/
|
|
steve.shaw@intel.com, I think is that to take benchmark results more seriously , setup should be is relatable to what customers would do. Innodb_flush_log_at_trx_commit=0, and innodb_doublewrite=0 is not what they will do. Database needs some persistency. When testing redo log, it also needs to be written not not just buffered. I'm sure neither our nor MySQL's redo log improvements were targeting specifically Innodb_flush_log_at_trx_commit=0. With normal settings , there could be entirely different bottlenecks, but they are closer to what people see in practice.
|
|
Definitely do not disagree. The data is intended to provide an answer to the question set by the OP as to why a performance difference is observed with this workload between MySQL 8 and MariaDB.
Often a maximum performance benchmark is used as a repeatable test to identify where the bar is set, beyond which no configuration could exceed, and this long been an approach for official TPC benchmarks by database vendors.
Also the data can be illustrative of other changes such as in this case the performance change after MySQL 8.0.19, the performance difference when using sockets and when Client SQL was used instead of stored procedures both MariaDB and MySQL 8.0 performed the same.
|
|
Right, I missed that the OP runs his benchmarks with the same setting that are for non-production use. In that case, if absolute numbers are the goal, maybe he can run the server on a single NUMA node, and the benchmark driver on another. It would not be surprising to achieve double performance, while using half of the power (I often see it in benchmarks that run on NUMA, though different vendors have different results). That might set the bar, definitely worth trying. axel suggest the same, running on single NUMA node, earlier in the thread.
|
|
e1d, can you please test the latest 10.6 development snapshot, which includes a fix of MDEV-32050? If you prefer pre-built packages, they should soon appear at https://ci.mariadb.org/39814/.
steve.shaw@intel.com, I agree that we should make another effort at improving the lock conflict resolution. The innodb_lock_schedule_algorithm=VATS was causing massive amounts of debug assertion failures, and it was removed in MDEV-16664. There have been some regressions and fixes in this area, such as MDEV-32096.
I expect 10.11 to perform better than 10.6, especially after MDEV-32374 has been fixed. Unfortunately, it looks like we have to wait for a merge of MDEV-32050 for some time. There are some conflicts, which I resolved in this 10.10 based commit. That branch can be merged to the current 10.11 without any conflicts.
|
|
I created a 10.10 based branch that includes an attempt to address MDEV-32374 as well as a merge of MDEV-32050. Ready-made packages of that should appear in https://ci.mariadb.org/39851/ shortly. 10.11 is expected to perform worse until MDEV-31953 (caused by MDEV-25341) has been addressed.
|
10.8.7-MariaDB_vs_mysql8.png
fg_mdb11_withsps.svg
