Not the answer you need?
Register and ask your own question!

HandlerSocket locking tables during set global read_only = ON?

marosteguimarostegui EntrantInactive User Role Beginner
loose_handlersocket_port = 9998
loose_handlersocket_port_wr = 9999
loose_handlersocket_threads = 18
loose_handlersocket_threads_wr = 1

We have an script that automatically adds read_only = ON if some conditions related to virtual IPs.
However, we have detected that we are never able to finish that set and the slave gets delayed forever waiting for a global read lock.

This is what a show full processlist shows:


+
+
+
+
+
+
+
+
+
+
+
+
| 1 | system user | connecting host | NULL | Connect | NULL | Waiting for table flush | NULL | 0 | 0 | 0 |
| 2 | system user | connecting host | NULL | Connect | NULL | Waiting for table flush | NULL | 0 | 0 | 0 |
| 3 | system user | connecting host | NULL | Connect | NULL | Waiting for table flush | NULL | 0 | 0 | 0 |
| 4 | system user | connecting host | NULL | Connect | NULL | Waiting for table flush | NULL | 0 | 0 | 0 |
| 5 | system user | connecting host | NULL | Connect | NULL | Waiting for table flush | NULL | 0 | 0 | 0 |
| 6 | system user | connecting host | NULL | Connect | NULL | Waiting for table flush | NULL | 0 | 0 | 0 |
| 7 | system user | connecting host | NULL | Connect | NULL | Waiting for table flush | NULL | 0 | 0 | 0 |
| 8 | system user | connecting host | NULL | Connect | NULL | Waiting for table flush | NULL | 0 | 0 | 0 |
| 9 | system user | connecting host | NULL | Connect | NULL | Waiting for table flush | NULL | 0 | 0 | 0 |
| 10 | system user | connecting host | NULL | Connect | NULL | Waiting for table flush | NULL | 0 | 0 | 0 |
| 11 | system user | connecting host | NULL | Connect | NULL | Waiting for table flush | NULL | 0 | 0 | 0 |
| 12 | system user | connecting host | NULL | Connect | NULL | Waiting for table flush | NULL | 0 | 0 | 0 |
| 13 | system user | connecting host | NULL | Connect | NULL | Waiting for table flush | NULL | 0 | 0 | 0 |
| 14 | system user | connecting host | NULL | Connect | NULL | handlersocket: mode=rd, 10 conns, 0 active | NULL | 0 | 0 | 0 |
| 15 | system user | connecting host | NULL | Connect | NULL | handlersocket: mode=rd, 10 conns, 0 active | NULL | 0 | 0 | 0 |
| 16 | system user | connecting host | NULL | Connect | NULL | handlersocket: mode=rd, 10 conns, 0 active | NULL | 0 | 0 | 0 |
| 17 | system user | connecting host | NULL | Connect | NULL | handlersocket: mode=rd, 10 conns, 0 active | NULL | 0 | 0 | 0 |
| 18 | system user | connecting host | NULL | Connect | NULL | handlersocket: mode=rd, 10 conns, 0 active | NULL | 0 | 0 | 0 |
| 19 | system user | connecting host | handlersocket | Connect | NULL | handlersocket: mode=wr, 0 conns, 0 active | NULL | 0 | 0 | 0 |
| 20 | system user | | NULL | Connect | 324 | Waiting for master to send event | NULL | 0 | 0 | 0 |
| 96 | root | 10.XXX:42833 | xxx | Sleep | 171 | | NULL | 0 | 0 | 0 |
| 288 | xxxx | localhost | NULL | Query | 227 | Waiting for table flush | set global read_only = ON | 0 | 0 | 0 |
| 413 | root | 10.XXX:48326 | NULL | Query | 0 | NULL | show full processlist | 0 | 0 | 0 |
| 582 | system user | | xxx | Connect | 227 | Waiting for global read lock | UPDATE XXXX SET `XXX`=YY, `request`=YY WHERE `XXX`=YY LIMIT 1 | 0 | 0 | 0 |
| 765 | replication | 10.xxxx:48976 | NULL | Binlog Dump | 1 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0 | 0 | 0 |
+
+
+
+
+
+
+
+
+
+
+
+
25 rows in set (0.00 sec)


It seems that handler socket is locking tables' metadata for some reason.
Even if you kill the set global read_only = ON manually, it will never get the read lock freed.

However, it only happens with that specific flag (read_only), as the rest of flags run just fine:

mysql> set global userstat = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global slave_net_timeout = 11;
Query OK, 0 rows affected (0.00 sec)


mysql> set global innodb_flush_log_at_trx_commit = 0;
Query OK, 0 rows affected (0.00 sec)




mysql> set global test=no;
ERROR 1193 (HY000): Unknown system variable 'test'



Has someone experienced something similar?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.