{"id":3290,"date":"2024-02-20T08:12:20","date_gmt":"2024-02-20T08:12:20","guid":{"rendered":"https:\/\/kedar.nitty-witty.com\/blog\/?p=3290"},"modified":"2024-02-20T08:12:22","modified_gmt":"2024-02-20T08:12:22","slug":"mysql-benchmarking-and-error-in-my_thread_global_end","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/mysql-benchmarking-and-error-in-my_thread_global_end","title":{"rendered":"MySQL Benchmarking and error in my_thread_global_end()"},"content":{"rendered":"\n

I encountered this error, “Error in my_thread_global_end(): xx threads didn’t exit<\/strong>“, while working on MySQL benchmarking using sysbench. In this blog I’ll share my experience, reveal the fix, and help you avoid lost threads in your future benchmarks.<\/p>\n\n\n\n

When it comes to ensuring your MySQL database can handle the heat, benchmarking is your your guiding light. Sysbench is our favorite benchmarking tool. I was happily benchmarking my MySQL database with Sysbench when bam! <\/p>\n\n\n\n

Error in my_thread_global_end(): xx threads didn’t exit<\/h4>\n\n\n\n

Above error popped up, halting my progress and that’s where my investigation begun.<\/p>\n\n\n\n

\n

TL;DR – verbosity is a great option in sysbench<\/p>\nsysbench has –verbosity option that comes handy for debugging using which we could identify the root cause of this error.<\/cite><\/blockquote>\n\n\n\n

Investigating Error in my_thread_global_end()<\/h2>\n\n\n\n

I received this error during multi-threaded benchmarks specifically in my case when we had higher number of threads. For all the threads higher than 32 the error appeared.<\/p>\n\n\n\n

Error in my_thread_global_end(): 32 threads didn't exit\nError in my_thread_global_end(): 64 threads didn't exit\nError in my_thread_global_end(): 128 threads didn't exit<\/code><\/pre>\n\n\n\n
Following command was used for benchmarking MySQL using sysbench<\/p>\n\n\n\n
[root@benchmark ~]# sysbench --db-driver=mysql --mysql-user=admin --mysql-password=password --mysql-host=mysql-benchmarking-instance --mysql-port=3306 --mysql-db=mysql_benchmark --range_size=500 --table_size=10000000 --tables=100 --threads=64 --events=0 --time=600 --report-interval=5 \/usr\/share\/sysbench\/oltp_write_only.lua run > mysql-benchmarking\/run_64.log<\/code><\/pre>\n\n\n\n
Above MySQL benchmarking command terminates immediately after the execution with the log having multiple errors “Error in my_thread_global_end(): 64 Threads Lost in Action”<\/p>\n\n\n\n
Sysbench version for MySQL benchmarking<\/p>\n\n\n\n
[root@benchmark ~]# sysbench --version
sysbench 1.0.20
[root@benchmark ~]#<\/code><\/pre>\n\n\n\n
MySQL error log has nothing logged that could point about this error being related to MySQL. My benchmarking instance had 8 cores and I wondered if that’s something to do with the number of CPU and if that’s the limitation.
But but that doesn’t seem to be the issue of mysql benchmarking server as I ran the CPU test for 5000 threads without any errors:<\/p>\n\n\n\n
[root@benchmark ~]# sysbench --test=cpu --threads=5000 --time=1 run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 5000
Initializing random number generator from current time

Prime numbers limit: 10000
Initializing worker threads\u2026
Threads started!
CPU speed:
events per second: 8006.17

General statistics:
total time: 1.0499s
total number of events: 8417

Latency (ms):
min: 0.82
avg: 6.86
max: 972.30
95th percentile: 33.72
sum: 57753.36

Threads fairness:
events (avg\/stddev): 1.6834\/18.67
execution time (avg\/stddev): 0.0116\/0.09<\/code><\/pre>\n\n\n\n
I noted that this benchamarking report errors during write workloads only! Sysbench command works with read_only test but read_write and write_only tests fails when threads are 32 or more.<\/p>\n\n\n\n
Ofcourse I Googled “Error in my_thread_global_end():” but none of the threads could really helped me which led me further to help of sysbench. I noted the log options having verbosity levels.<\/p>\n\n\n\n
sysbench --help

Log options:
--verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3]<\/code><\/pre>\n\n\n\n
I raised the verbosity to 5 and rerun the mysql benchmarking<\/p>\n\n\n\n
[root@benchmark ~]# sysbench --verbosity=5<\/strong> --db-driver=mysql --mysql-user=admin --mysql-password=password --mysql-host=mysql-benchmarking-instance --mysql-port=3306 --mysql-db=mysql_benchmark --range_size=500 --table_size=10000000 --tables=100 --threads=64 --events=0 --time=600 --report-interval=5 \/usr\/share\/sysbench\/oltp_write_only.lua run > mysql-benchmarking\/run_64.log\nsysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)\n\nRunning the test with following options:\nNumber of threads: 64\nReport intermediate results every 5 second(s)\nInitializing random number generator from current time\n\nInitializing worker threads\u2026\n\nDEBUG: Worker thread (#0) started\nDEBUG: Worker thread (#1) started\n...\nDEBUG: Worker thread (#60) started\nDEBUG: Worker thread (#61) started\nDEBUG: Worker thread (#62) started\nDEBUG: Worker thread (#63) started\nFailed to prepare query \"BEGIN\" (1295: This command is not supported in the prepared statement protocol yet), using emulation\n(last message repeated 63 times)\nFATAL: mysql_stmt_prepare() failed\nFATAL: MySQL error: 1461 \"Can't create more than max_prepared_stmt_count statements (current value: 16382)\"\n(last message repeated 1 times)\nFATAL: mysql_stmt_prepare() failed\nFATAL: MySQL error: 1461 \"Can't create more than max_prepared_stmt_count statements (current value: 16382)\"<\/strong>\n(last message repeated 1 times)\nFATAL: mysql_stmt_prepare() failed\n(last message repeated 1 times)\nFATAL: mysql_stmt_prepare() failed\n(last message repeated 1 times)\nFATAL: `thread_init' function failed: \/usr\/share\/sysbench\/oltp_common.lua:284: SQL API error\n\n...\n\nFATAL: Thread initialization failed!\nError in my_thread_global_end(): 64 threads didn't exit<\/code><\/pre>\n\n\n\n
great success ! We see the actual underlying issue now.<\/p>\n\n\n\n
1461 \"Can't create more than max_prepared_stmt_count statements (current value: 16382)\"<\/code><\/pre>\n\n\n\n
Further reading documentation about max_prepared_stmt_count<\/a><\/p>\n\n\n\n
This variable limits the total number of prepared statements in the server. It can be used in environments where there is the potential for denial-of-service attacks based on running the server out of memory by preparing huge numbers of statements. If the value is set lower than the current number of prepared statements, existing statements are not affected and can be used, but no new statements can be prepared until the current number drops below the limit. Setting the value to 0 disables prepared statements.<\/em><\/p>\n\n\n\n
Reviewing current configuration<\/p>\n\n\n\n
[root@benchmark ~]# mysql -hmysql-benchmarking-instance -e \"select @@global.max_prepared_stmt_count;\"\n+----------------------------------+\n| @@global.max_prepared_stmt_count |\n+----------------------------------+\n| 16382                            |\n+----------------------------------+<\/code><\/pre>\n\n\n\n
The Fix for Error in my_thread_global_end()<\/h2>\n\n\n\n
The verbose mode showed us the reason for the failure. The write traffic was causing benchmark to terminate as it was crossing the max prepared statement count for higher concurrency. I adjusted the max_prepared_stmt_count to a higher value “1048570” and restarted the journey of MySQL benchmarking.<\/p>\n\n\n\n
[root@benchmark ~]# mysql -hmysql-benchmarking-instance -e \"select @@global.max_prepared_stmt_count;\"\n+----------------------------------+\n| @@global.max_prepared_stmt_count |\n+----------------------------------+\n| 1048570                          |\n+----------------------------------+<\/code><\/pre>\n\n\n\n
Restarted the MySQL Behchmarking using sysbench and this time it completed successfully.<\/p>\n\n\n\n
The solution lies in adjusting the max_prepared_stmt_count variable in your MySQL configuration file. Simply increase this value to accommodate the anticipated number of statements used by sysbench.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
Debugging logs and raising verbosity allows us to troubleshoot the issues, rightly what helped us in this situation. Don’t blindly trust default settings. Adapt system variables like max_prepared_stmt_count<\/code> to your specific requirements. Debugging common errors like this one allows you to smoother benchmarking journey. Happy Benchmarking!<\/p>\n","protected":false},"excerpt":{"rendered":"I encountered this error, “Error in my_thread_global_end(): xx threads didn’t exit“, while working on MySQL benchmarking using sysbench. In this blog I’ll share my experience, reveal the fix, and help…\n","protected":false},"author":1,"featured_media":3295,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[868,869,8,870],"tags":[955,956,958,954],"class_list":{"0":"post-3290","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-aws-rds","8":"category-mariadb","9":"category-mysql","10":"category-mysql-tools","11":"tag-benchmarking-using-sysbench","12":"tag-error-in-my_thread_global_end","13":"tag-error-in-my_thread_global_end-xx-threads-didnt-exit","14":"tag-mysql-benchmarking"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/3290","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=3290"}],"version-history":[{"count":6,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/3290\/revisions"}],"predecessor-version":[{"id":3300,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/3290\/revisions\/3300"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media\/3295"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=3290"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=3290"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=3290"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}