thread_pool_size=1 causes mysqld hang

[Ramesh Sivaraman]

**************** RQG start up command

if [ -r /usr/lib64/libjemalloc.so.1 ]; then export LD_PRELOAD=/usr/lib64/libjemalloc.so.1
elif [ -r /usr/lib/x86_64-linux-gnu/libjemalloc.so.1 ]; then export LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libjemalloc.so.1
elif [ -r /ssd/ramesh/rqg/5.5/582358/lib/mysql/libjemalloc.so.1 ]; then export LD_PRELOAD=/ssd/ramesh/rqg/5.5/582358/lib/mysql/libjemalloc.so.1
else echo 'Error: jemalloc not found, please install it first'; exit 1; fi
ps -ef | grep 'rundir1_3' | grep -v grep | awk '{print $2}' | xargs sudo kill -9 > /dev/null 2>&1
rm -Rf /ssd/ramesh/rqg/5.5/582358/rundir1_3
rm -Rf /ssd/ramesh/rqg/5.5/582358/rundir1_3_epoch
mkdir /ssd/ramesh/rqg/5.5/582358/rundir1_3
mkdir /ssd/ramesh/rqg/5.5/582358/rundir1_3_epoch
cd /ssd/ramesh/randgen
bash -c "set -o pipefail; perl /ssd/ramesh/randgen/runall.pl --queries=100000000 --seed=14830 --duration=450 --querytimeout=60 --short_column_names --reporter=Shutdown,Backtrace,QueryTimeout,ErrorLog,ErrorLogAlarm --mysqld=--log-output=FILE --mysqld=--general_log --mysqld=--general_log_file=/ssd/ramesh/rqg/5.5/582358/3.sql --mysqld=--sql_mode=ONLY_FULL_GROUP_BY --mysqld=--core --mysqld=--utility-user-password=test --grammar=conf/percona_qa/5.5/5.5.yy_1 --gendata=conf/percona_qa/5.5/5.5.zz1 --threads=5 --no-mask --basedir=/ssd/ramesh/ps55dbg --notnull --mysqld=--innodb_change_buffering=none --mysqld=--innodb_changed_pages=FORCE --mysqld=--innodb_fast_shutdown=0 --mysqld=--innodb_file_per_table=1 --mysqld=--innodb_flush_log_at_trx_commit=1 --mysqld=--innodb_flush_method=O_DSYNC --mysqld=--innodb_purge_threads=32 --mysqld=--innodb_adaptive_hash_index_partitions=12 --mysqld=--innodb_buffer_pool_instances=8 --mysqld=--innodb_log_block_size=512 --mysqld=--innodb_log_group_home_dir=/ssd/ramesh/rqg/5.5/582358/rundir1_3_epoch --mysqld=--innodb_max_bitmap_file_size=20480 --mysqld=--innodb_max_changed_pages=0 --mysqld=--innodb_track_changed_pages=1 --mysqld=--loose-readonly-key-cache-division-limit=1 --mysqld=--query_cache_size=1048576 --mysqld=--query_cache_type=1 --mysqld=--readonly-loose-max-connect-errors=0 --mysqld=--skip-innodb_doublewrite --mysqld=--slow_query_log --mysqld=--thread_handling=pool-of-threads --mysqld=--extra_port=3307 --mysqld=--transaction-isolation=READ-COMMITTED --mysqld=--utility-user=roel@% --mysqld=--utility-user-password=test --mysqld=--utility-user-schema-access=mysqlinformation_schema --mysqld=--hidden-key-buffer-size=1 --mtr-build-thread=765 --mask=35031 --vardir1=/ssd/ramesh/rqg/5.5/582358/rundir1_3 > /ssd/ramesh/rqg/5.5/582358/cmd3.log 2>&1"
tail -n1 /ssd/ramesh/rqg/5.5/582358/cmd3.log

***************** PS version

[ramesh@qaserver-01 582358]$ /ssd/ramesh/ps55dbg/bin/mysqld --version
/ssd/ramesh/ps55dbg/bin/mysqld Ver 5.5.39-36.0-debug for Linux on x86_64 (Percona Server (GPL), Release 36.0, Revision 693-debug)
[ramesh@qaserver-01 582358]$

***************** Thread pool variable, status and processlist info

mysql> show variables like '%thread_pool%';
+-------------------------------+--------------+
| Variable_name | Value |
+-------------------------------+--------------+
| thread_pool_high_prio_mode | transactions |
| thread_pool_high_prio_tickets | 4294967295 |
| thread_pool_idle_timeout | 60 |
| thread_pool_max_threads | 100000 |
| thread_pool_oversubscribe | 3 |
| thread_pool_size | 1 |
| thread_pool_stall_limit | 500 |
+-------------------------------+--------------+
7 rows in set (0.00 sec)

mysql>

mysql> show processlist;
+----+----------------------+-----------------+------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
+----+----------------------+-----------------+------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
| 1 | root | 127.0.0.1:42280 | test | Sleep | 1715 | | NULL | 0 | 0 | 0 |
| 9 | root | 127.0.0.1:42307 | test | Sleep | 1471 | | NULL | 502 | 502 | 502 |
| 10 | root | 127.0.0.1:42308 | test | Query | 1475 | Waiting for global read lock | DROP TABLE IF EXISTS y | 0 | 0 | 0 |
| 11 | root | 127.0.0.1:42309 | test | Query | 1475 | Waiting for global read lock | REPLACE INTO `table500_innodb_dynamic` ( `c11` ) VALUES ( 'j' ) | 0 | 0 | 0 |
| 12 | root | 127.0.0.1:42310 | test | Query | 1475 | Waiting for global read lock | UPDATE `table100_innodb_compressed` t1, `table100_innodb_default_key_pk_parts_2_int` t2 SET t1.`c8 | 0 | 0 | 0 |
| 13 | root | 127.0.0.1:42311 | test | Query | 1476 | Waiting for global read lock | DROP TABLE IF EXISTS k | 0 | 0 | 0 |
| 27 | unauthenticated user | connecting host | NULL | Connect | NULL | login | NULL | 0 | 0 | 0 |
| 28 | unauthenticated user | connecting host | NULL | Connect | NULL | login | NULL | 0 | 0 | 0 |
| 30 | root | 127.0.0.1:33950 | NULL | Query | 0 | NULL | show processlist | 0 | 0 | 0 |
+----+----------------------+-----------------+------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
9 rows in set (0.00 sec)

mysql>

mysql> show global status like '%threadpool%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Threadpool_idle_threads | 13 |
| Threadpool_threads | 13 |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql>

[Ramesh Sivaraman (rameshvs02)]

GDB, mysqld and ldd files

[Ramesh Sivaraman (rameshvs02)]

Coredump.

[Nilnandan Joshi (nilnandan-joshi)]

Able to reproduce same issue with 5.5.yy_1 grammar file. Going to attach here.

[Nilnandan Joshi (nilnandan-joshi)]

Able to reproduce same issue with PS 5.6 wit above grammar.

nilnandan@desktop:~/randgen$ /home/nilnandan/ps56dbg/bin/mysql -uroot -p --socket=/home/nilnandan/rqg/5.5/582358/rundir1_3/tmp/master.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.6.21-70.1-log Percona Server (GPL), Release 70.1, Revision 698

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%thread_pool%';
+-------------------------------+--------------+
| Variable_name | Value |
+-------------------------------+--------------+
| thread_pool_high_prio_mode | transactions |
| thread_pool_high_prio_tickets | 4294967295 |
| thread_pool_idle_timeout | 60 |
| thread_pool_max_threads | 100000 |
| thread_pool_oversubscribe | 3 |
| thread_pool_size | 1 |
| thread_pool_stall_limit | 500 |
+-------------------------------+--------------+
7 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+-----------+---------------+
| 1 | root | 127.0.0.1:35874 | test | Sleep | 92 | | NULL | 0 | 0 |
| 10 | root | 127.0.0.1:35885 | test | Sleep | 2 | | NULL | 0 | 100 |
| 11 | root | 127.0.0.1:35886 | test | Sleep | 5 | | NULL | 0 | 505 |
| 12 | root | 127.0.0.1:35887 | test | Query | 1 | Sending data | UPDATE LOW_PRIORITY `table500_innodb_compressed_int` t1, `table100_innodb_default_key_pk_parts_2_in | 0 | 0 |
| 13 | root | 127.0.0.1:35888 | test | Query | 3 | System lock | LOAD DATA INFILE '/tmp/gentest5097.tmp' INTO TABLE `table500_innodb_dynamic_int_autoinc` | 0 | 0 |
| 14 | root | 127.0.0.1:35889 | test | Sleep | 2 | | NULL | 0 | 50803 |
| 17 |...

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1541