Threadpool Performance Assessment - MySQL vs Mariadb

 Why this assessment of mysql vs mariadb threadpool

Threadpool is a commercial feature in MySQL whereas it's opensource in Mariadb and some variation is there so,  I thought of assessing to know real gain of threadpool in terms of performance. This performance assessment, I did on  on my 6 core ubuntu 24 laptop for read-only, read-write and tpcc cached workloads. Here, a cached/memory-bound workload means that data fully fits into buffer-pool. 
Since, threadpool is available in commercial MySQL so, I could not test it and hope it will be opensource like other commercial features were  added to 9.7.0  community edition. I have taken latest build of MySQL commnunity 9.7.0 and Mariadb 13.0.1 . 

Summary
 
Threadpool did not have significant impact in case of Mariadb for read/read-write  loads. But, threadpool had very good impact  in TPCC workload.

Findings and observations

  • In the point-select test, MariaDB shows a performance drop for both TP (thread pool) and non-threadpool configurations, reaching up to -20% across most thread counts. However, at higher concurrency (128 threads), MariaDB with TP shows a gain, suggesting that the thread pool implementation may perform better under high-load scenarios, as expected.
  • A similar trend appears in the OLTP_RO test, where MariaDB shows a performance drop of up to -24%.
  • In the OLTP_RW test, there was no significant difference in TPS between the  TP (thread pool) and non-TP (threadpool) configurations in MariaDB.
  • Surprisingly, MariaDB showed a +200% gain compared to MySQL for OLTP_RW. Note that I did not use WAL (Write-Ahead Logging) for binlogs in MariaDB, and I maintained minimal settings for both MySQL and MariaDB to ensure a baseline comparison. This prompted me to check WAL+binlog for TP and non-TP in Mariadb and this is in-progress.
  • In TPCC-10 test, we see a clear gain in MariaDB when using the thread pool.
  • I opened MDEV-39819 in MariaDB for threadpool monitoring through PFS and I found it has a different viewpoint for not having it in PFS but in information_schema.

Point-select , memory bound







*blue: mysql (no tp) , red: mariadb (no tp) and green: mariadb (tp)




For detailed monitoring, you can refer github

OLTP_RO , memory bound







*blue: mysql (no tp) , red: mariadb (no tp) and green: mariadb (tp)



For detailed monitoring, you can refer github

OLTP_RW , memory bound





*blue: mysql (no tp) , red: mariadb (no tp) and green: mariadb (tp)



For detailed monitoring, you can refer github


TPCC ( 10 warehouse) , memory-bound , based on sysbench

    Note: I used sysbench's BMK's tpcc and executed through shell script since it's not implemented into               TAF and plan is to add it.



  • As you can see, commit/s graph is not very smooth for MySQL and Mariadb but  trend suggest that TP in Mariadb is helping a  to scale and give better tps with reduced cpu usage.


For detailed monitoring, you can refer github

Test Details

Test machine: 6 core , 8 GB RAM, ubuntu 24.0,  AMD Ryzen 5 7535U with Radeon Graphics
I used TAF with BMK's sysbench

Sample command of TAF:
#perl ./taf.pl --prop=/home/amrendra/projects/TP/sysbench_lua_mysql.properties --action=init-start-db-run-tests --verbose --skip-client-builds --db-software-install-dir=/home/amrendra/TAF/database_software_installs/mysql-9.7.0-linux-glibc2.28-x86_64 --ignore-running-db-process --threads=4,8,16,32,64,128 --tests=point_select,oltp_ro,oltp_rw --db-config-file=/home/amrendra/projects/TP/mysql_simple_2gbp.cnf --exec-sql-file-after-test-setup=/home/amrendra/projects/TP/mysql_global_variables_dump.sql # --exec-sql-file-after-test-setup=/home/amrendra/projects/TP/mysql_global_variables_dump.sql #echo "notp nossl mariadb" >>/var/tmp/log #perl ./taf.pl --prop=/home/amrendra/projects/TP/sysbench_lua.properties --action=init-start-db-run-tests --exec-sql-file-after-test-setup=/home/amrendra/TAF/scripts/sql/mariadb/status/global_variables_dump.sql --verbose --skip-client-builds --db-software-install-dir=/home/amrendra/TAF/database_software_installs/mariadb-13.0.1-linux-systemd-x86_64 --comments="notp ps" --ignore-running-db-process --threads=4,8,16,32,64,128 --tests=point_select,oltp_ro,oltp_rw --comments="notp nossl mariadb" #sleep 30 #echo "tp nossl mariadb" >>/var/tmp/log #perl ./taf.pl --prop=/home/amrendra/projects/TP/sysbench_lua.properties --action=init-start-db-run-tests --exec-sql-file-after-test-setup=/home/amrendra/TAF/scripts/sql/mariadb/status/global_variables_dump.sql --verbose --skip-client-builds --db-software-install-dir=/home/amrendra/TAF/database_software_installs/mariadb-13.0.1-linux-systemd-x86_64 --db-config-file=/home/amrendra/projects/TP/mariadb_simple_2gbp_tp.cnf --ignore-running-db-process --threads=4,8,16,32,64,128 --tests=point_select,oltp_ro,oltp_rw --comments="tp nossl mariadb"

mysqld-conf:
[mysqld]
max_connections=200
open_files_limit=65535
table_open_cache=2048
thread_cache_size=128
# InnoDB
innodb_buffer_pool_size=2G
innodb_redo_log_capacity=2G
innodb_flush_method=O_DIRECT
innodb_file_per_table=1
innodb_flush_neighbors=0
innodb_read_io_threads=4
innodb_write_io_threads=4
log_bin
innodb_monitor_enable='%'
performance_schema=ON
secure-file-priv=/home/amrendra/TAF/tmp/
mysqlx_socket=/home/amrendra/TAF/data/mysqlx.sock

Mariadb-conf:
[mysqld]
max_connections=200
open_files_limit=65535
table_open_cache=2048
thread_cache_size=128
# Innodb
innodb_buffer_pool_size=2G
innodb_log_file_size=2G
innodb_flush_method=O_DIRECT
innodb_file_per_table=1
innodb_flush_neighbors=0
innodb_read_io_threads=4
innodb_write_io_threads=4
log_bin
innodb_snapshot_isolation=0
innodb_monitor_enable='%'
performance_schema=ON
secure-file-priv=/home/amrendra/TAF/tmp/
#thread_handling=pool-of-threads , this for TP test

You can find results and config @github


References

  • https://github.com/MariaDB/TAF
  • https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/buffers-caches-and-threads/thread-pool/thread-pool-in-mariadb


Comments

Post a Comment

Popular posts from this blog

MySQL- Crash Recovery Performance Assessment

Why we need to assess the performance of   crash recovery in MySQ and here is my explanation:  It’s not just WLs or bug fixes — any change in redo format or code changes around it, flushing behavior, or recovery concurrency can silently slow things down and if you have huge volume of data to recover than it could be painful. MySQL errorlog is the best place to look into details like whats happening during recovery and which phase of recovery is time consuming. Basically , I measured the recovery  timing  - from “Starting crash recovery” to “Apply batch completed , in MySQLD error log. Refer this about recovery in Innodb here: https://dev.mysql.com/doc/refman/8.4/en/innodb-recovery.html#innodb-crash-recovery Let me share some of my hack to measure the time taken for crash-recovery experiment. 1.  I used ` innodb_checkpoint_disabled ` which is strictly a testing mechanism. It forces maximum redo accumulation and doesn’t reflect real‑world recovery .   in...
Read more

Mariadb Performance : Openssl and wolfssl

Image
Why this SSL performance assessment of Mariadb ? My first objective was to assess new openssl 3.5 or 3.6 and see whether it's improving for Mariadb  or not and second , I could not find any comparative analysis of ssl for Mariabd in public space. Earlier in MySQL, I had done exhaustive assessment of  wolfssl and openssl's different version so thought of doing some against Mariadb which is another popular database and moving fast in db ranking. I used  memory bound test and sysbench's point-select without prepared statement to stress TLS layer the most. Another test , I picked is connect/disconnect which fires point-select query for each connection like PHP and this also stressess TLS layer the most. Key Findings In case of sysbench's point-select query like " SELECT c FROM sbtest1 WHERE id = 12345 ;" , wolfssl (5.8.4 default) is the best. openssl-1.1.1l/3.0.19/3.5.5/3.6.1 are less upto 9% wrt wolfssl. In case of sysbench's point-select query like "...
Read more

PGO in MySQL community 9.7

Image
  PGO ( Profile-guided optimization) in mysql community edition of 9.7.0 -------------------------------------------------------------------------------------------------------------------------- Straightforward ,  30% gain   we have seen in eneterprise edition of mysql   in read only sysbench test like point-select ( memory bound) .  I am curious to know whether similar gain , we see in pgo community build and what kind of test cases it use to train data? If pgo in community edition gives 30 percentage gain then it makes sense to use this feature by many other databases based on MySQL because it comes free without doing any extra tuning of mysqld config. As you can see,9.7.0 gives better performance wrt 9.6.0 and it's because of just pgo being put in. I referred INFO_BIN and 9.7 has fprofile compiler option whereas 9.6.0 doesn't have So, far I have seen release notes and I know, there is no other major changes would have contributed such gain. Its go...
Read more