Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.2.35, 10.4.17
Description
Try to run sysbench oltp_read_write test on 10.4.17 vs 10.4.15 and 10.5.8 vs 10.5.6, with several tables and notably more concurrent threads than you have cores, while total size of tables created is notably smaller than the buffer pool.
For short runs (100 or 300 seconds) at least you'll notice a clear drop in throughput in QPS and TPS, around 15%, and notable increase in 95th percentile latency.
Consider the following example when MariaDB is started with only the following non-default settings besides port/socket:
MariaDB [(none)]> select @@innodb_buffer_pool_size, @@innodb_flush_log_at_trx_commit;
|
+---------------------------+----------------------------------+
|
| @@innodb_buffer_pool_size | @@innodb_flush_log_at_trx_commit |
|
+---------------------------+----------------------------------+
|
| 1073741824 | 2 |
|
+---------------------------+----------------------------------+
|
1 row in set (0.000 sec)
|
and 5 tables 100000 rows each are created:
[openxs@fc31 maria10.4.17]$ sysbench oltp_read_write --db-driver=mysql --tables=5 --table-size=100000 --mysql-user=openxs --mysql-socket=/tmp/mariadb.sock --mysql-db=sbtest --threads=4 prepare
|
|
|
...
|
|
|
MariaDB [(none)]> show table status from sbtest like 'sbtest1'\G
|
*************************** 1. row ***************************
|
Name: sbtest1
|
Engine: InnoDB
|
Version: 10
|
Row_format: Dynamic
|
Rows: 100000
|
Avg_row_length: 225
|
Data_length: 22593536
|
Max_data_length: 0
|
Index_length: 1589248
|
Data_free: 2097152
|
Auto_increment: 100001
|
Create_time: 2020-11-24 10:44:53
|
Update_time: 2020-11-24 10:44:51
|
Check_time: NULL
|
Collation: latin1_swedish_ci
|
Checksum: NULL
|
Create_options:
|
Comment:
|
Max_index_length: 0
|
Temporary: N
|
1 row in set (0.000 sec)
|
With 10.4.15 on Fedora 31 I get the following on my old quadcore test box:
[openxs@fc31 maria10.4.15]$ sysbench oltp_read_write --db-driver=mysql --tables=5 --table-size=100000 --mysql-user=openxs --mysql-socket=/tmp/mariadb.sock --mysql-db=sbtest --threads=32 --time=100 run
|
sysbench 1.1.0-174f3aa (using bundled LuaJIT 2.1.0-beta2)
|
|
|
Running the test with following options:
|
Number of threads: 32
|
Initializing random number generator from current time
|
|
|
|
|
Initializing worker threads...
|
|
|
Threads started!
|
|
|
SQL statistics:
|
queries performed:
|
read: 1226148
|
write: 350328
|
other: 175164
|
total: 1751640
|
transactions: 87582 (875.32 per sec.)
|
queries: 1751640 (17506.49 per sec.)
|
ignored errors: 0 (0.00 per sec.)
|
reconnects: 0 (0.00 per sec.)
|
|
|
Throughput:
|
events/s (eps): 875.3245
|
time elapsed: 100.0566s
|
total number of events: 87582
|
|
|
Latency (ms):
|
min: 2.13
|
avg: 36.54
|
max: 1614.12
|
95th percentile: 125.52
|
...
|
while with 10.4.17:
[openxs@fc31 maria10.4.17]$ sysbench oltp_read_write --db-driver=mysql --tables=5 --table-size=100000 --mysql-user=openxs --mysql-socket=/tmp/mariadb.sock --mysql-db=sbtest --threads=32 --time=100 run
|
sysbench 1.1.0-174f3aa (using bundled LuaJIT 2.1.0-beta2)
|
|
|
Running the test with following options:
|
Number of threads: 32
|
Initializing random number generator from current time
|
|
|
|
|
Initializing worker threads...
|
|
|
Threads started!
|
|
|
SQL statistics:
|
queries performed:
|
read: 1058232
|
write: 302352
|
other: 151176
|
total: 1511760
|
transactions: 75588 (755.07 per sec.)
|
queries: 1511760 (15101.33 per sec.)
|
ignored errors: 0 (0.00 per sec.)
|
reconnects: 0 (0.00 per sec.)
|
|
|
Throughput:
|
events/s (eps): 755.0666
|
time elapsed: 100.1077s
|
total number of events: 75588
|
|
|
Latency (ms):
|
min: 2.14
|
avg: 42.34
|
max: 1113.80
|
95th percentile: 170.48
|
...
|
The difference is visible with 8 and 16 threads as well. So, there is a systematic notable performance regression in recent 10.4.x (and 10.5.x, for that matter) for this test.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
I filed MDEV-24537 for the 10.5 performance regression. Let us retain this ticket for the 10.2, 10.3, 10.4 regressions only, related to MDEV-23475 and MDEV-20638.
I leave it to axel to decide what the correct action for 10.2, 10.3, 10.4 should be. My personal opinion is that anyone who wants performance should consider upgrading to 10.5.9 once it becomes available.
Do we have any clear theory of the reason of regression in 10.2 ... 10.4 case (I still see current 10.4.19 from GitHub providing less TPS than 10.4.15, this time on current Ubuntu 20.04)? Looks like we purge more, but why exactly?
valerii, MDEV-23475 reverted MDEV-20638 in 10.2.35, 10.3.26, 10.4.16 due to an observed performance regression in some types of workloads. The srv_inc_activity_count() should affect both purging and page flushing.
Your observation could suggest that actually, MDEV-20638 (which appeared in 10.2.33, 10.3.24, 10.4.14) delivered an overall improvement of performance.
What we could do is to run new benchmarks, possibly also on RAM disk, to eliminate any fluctuation caused by the storage layer. We would need some details from performance counters to understand what is actually going on. Based on only one overall measure (such as the throughput), we can only see that there is a problem, not what that problem is.
This issue is about regression in 10.2–10.4, as Marko moved 10.5 related part to MDEV-24537.
Closing as 10.4 is EOL now
I've used my own builds from source for everything presented here. Specifically:
cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DWITH_SSL=system -DWITH_ZLIB=bundled -DMYSQL_MAINTAINER_MODE=OFF -DENABLED_LOCAL_INFILE=1 -DWITH_JEMALLOC=system -DWITH_INNODB_DISALLOW_WRITES=ON -DCMAKE_INSTALL_PREFIX=/home/openxs/dbs/maria10.2.34t -DWITHOUT_TOKUDB=1cmake .. -DCMAKE_BUILD_TYPE=RelWithDebInfo -DWITH_SSL=system -DWITH_ZLIB=bundled -DMYSQL_MAINTAINER_MODE=OFF -DENABLED_LOCAL_INFILE=1 -DWITH_JEMALLOC=system -DWITH_INNODB_DISALLOW_WRITES=ON -DCMAKE_INSTALL_PREFIX=/home/openxs/dbs/maria10.5and I tested with 16 threads on a very slow 2 core netbook with slow encrypted HDD capable to provide at most 260 IOPS for random sync writes of 16K pages:
openxs@ao756:~$ pt-summary# Percona Toolkit System Summary Report ######################Date | 2020-12-11 12:11:10 UTC (local TZ: EET +0200)Hostname | ao756Uptime | 3 days, 2:18, 2 users, load average: 0,14, 0,40, 0,31Platform | LinuxRelease | Ubuntu 16.04.7 LTS (xenial)Kernel | 4.4.0-197-genericArchitecture | CPU = 64-bit, OS = 64-bitThreading | NPTL 2.23SELinux | No SELinux detectedVirtualized | No virtualization detected# Processor ##################################################Processors | physical = 1, cores = 2, virtual = 2, hyperthreading = noSpeeds | 1x1421.367, 1x1474.042Models | 2xIntel(R) Pentium(R) CPU 987 @ 1.50GHzCaches | 2x2048 KB# Memory #####################################################Total | 3,7GFree | 173,1MUsed | physical = 189,5M, swap allocated = 3,8G, swap used = 133,9M, virtual = 323,4MShared | 11,7MBuffers | 3,3GCaches | 3,2GDirty | 152 kBUsedRSS | 438,6MSwappiness | 60DirtyPolicy | 20, 10DirtyStatus | 0, 0...For the original report I've also checked 10.4 similar builds on faster quad core system but with up to 32 threads, but the most detailed study of 10.2 and 10.5 commits was performed on the "slow" system above.
The test used was always this one:
sysbench oltp_read_write --db-driver=mysql --tables=5 --table-size=100000 --mysql-user=root --mysql-socket=/tmp/mariadb.sock --mysql-db=sbtest --threads=16 --time=300 --report-interval=10 runand MariaDB was started as follo2ws, all defaults but few settings:
bin/mysqld_safe --no-defaults --port=3309 --socket=/tmp/mariadb.sock --skip-grant-tables --innodb_buffer_pool_size=1G --innodb_flush_log_at_trx_commit=2 &I do not claim anything outside of these conditions. But the drop in performance I noted in 10.5.8 vs 10.5.6 and 10.4.17 vs 10.4.15 is of the same level as in much "larger" tests customer performed on real hardware and with MariaDB's official binaries. That's why I decided it makes sense to study the performance difference for commits under my restricted testing conditions.