Announcement

Announcement Module
Collapse
No announcement yet.

HandlerSocket locking tables during set global read_only = ON?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • HandlerSocket locking tables during set global read_only = ON?

    Hello,
    We currently have a cluster running Percona 5.5.30-30.2-log + HandlerSocket.
    Our handler socket configuration is:

    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)


    Even with non-existing ones:

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



    Has someone experienced something similar?

Working...
X