GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL 5.6 slave Slave_IO_State is in system lock state

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

  • MySQL 5.6 slave Slave_IO_State is in system lock state

    Hi all,
    I was wondering if someone had the same situation.

    I checked pretty much everything and cannot find out a solution for it.

    My installation as follow:
    CentOS 6.4 64 bit; ext4 + LVM; os, data and logs are on separate logical volumes; MySQL 5.6.14 tar.gz build 64 bit.

    A few days ago, when my QAs started testing an app, I found out that all my slave became a far far away behind my master.

    I found some transaction locks through "show engine innodb status" but they are gone by now.

    On my master, "show processlist" gives me an absolutely normal output, I think:

    mysql> show processlist\G
    *************************** 1. row ***************************
    Id: 1
    User: event_scheduler
    Host: localhost
    db: NULL
    Command: Daemon
    Time: 85208
    State: Waiting for next activation
    Info: NULL
    *************************** 3. row ***************************
    Id: 688
    User: rpluser
    Host: db3:44039
    db: NULL
    Command: Binlog Dump GTID
    Time: 97903
    State: Writing to net
    Info: NULL
    *************************** 4. row ***************************
    Id: 695
    User: rpluser
    Host: db2:36226
    db: NULL
    Command: Binlog Dump GTID
    Time: 97897
    State: Writing to net
    Info: NULL
    ...

    mysql> select * from performance_schema.threads\G
    *************************** 1. row ***************************
    THREAD_ID: 1
    NAME: thread/sql/main
    TYPE: BACKGROUND
    PROCESSLIST_ID: NULL
    PROCESSLIST_USER: NULL
    PROCESSLIST_HOST: NULL
    PROCESSLIST_DB: NULL
    PROCESSLIST_COMMAND: NULL
    PROCESSLIST_TIME: 97725
    PROCESSLIST_STATE: System lock
    PROCESSLIST_INFO: INTERNAL DDL LOG RECOVER IN PROGRESS
    PARENT_THREAD_ID: NULL
    ROLE: NULL
    INSTRUMENTED: YES

    On my slaves, "show processlist" shows me that the system user in the system lock state:

    mysql> show processlist\G
    *************************** 1. row ***************************
    Id: 601
    User: system user
    Host:
    db: NULL
    Command: Connect
    Time: 123530
    State: System lock
    Info: NULL
    *************************** 2. row ***************************
    Id: 602
    User: system user
    Host:
    db: NULL
    Command: Connect
    Time: 248924
    State: Reading event from the relay log
    Info: NULL
    ...

    mysql> select * from performance_schema.threads\G
    *************************** 1. row ***************************
    THREAD_ID: 1
    NAME: thread/sql/main
    TYPE: BACKGROUND
    PROCESSLIST_ID: NULL
    PROCESSLIST_USER: NULL
    PROCESSLIST_HOST: NULL
    PROCESSLIST_DB: NULL
    PROCESSLIST_COMMAND: NULL
    PROCESSLIST_TIME: 156259
    PROCESSLIST_STATE: System lock
    PROCESSLIST_INFO: INTERNAL DDL LOG RECOVER IN PROGRESS
    PARENT_THREAD_ID: NULL
    ROLE: NULL
    INSTRUMENTED: YES

    The problem is I couldn't figure out what is "PROCESSLIST_INFO: INTERNAL DDL LOG RECOVER IN PROGRESS".

    I saw some reports that it could be MySQL 5.6. bug when you run "flush tables with read locks" on a slave, but I am afraid it is not my case.

    Any comments or work around would be extremely appreciated !!!

  • #2
    I'm seeing the same problem and I can replicate, I also only found the "flush tables with read locks" case but I don't think I'm affected.
    We run 5.6.12 in AWS's RDS, it's a big Magento (php) installation.

    What's interesting for me is that I see System Lock on the master as well. Can you double-check in your case and confirm? I suspect you'll see it on the master as well.
    I use monyog's Real-Time feature and I've got 54% in System Lock...

    Comment


    • #3
      Oh, needless to say, any help will be very much appreciated. I will keep you posted of my progress.

      Comment

      Working...
      X