GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Xtradb cluster hangs up..

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

  • Xtradb cluster hangs up..

    Hi,

    I have setup a percona xtradb cluster setup between 2 cpanel servers but when i server is online the database server get hangup.No sites are online as well as i am not even able to login to phpmyadmin too.When checking # mysqladmin proc , shows process state as "Waiting for global read lock" i could see only "Waiting for global read lock" for all process.After disconnecting second server everything works fine.

    Eg:

    | Query | 22760 | Waiting for global read lock | UPDATE
    | Execute | 22016 | Waiting for global read lock | UPDATE
    | Query | 22452 | Waiting for global read lock | UPDATE
    | Query | 22415 | Waiting for global read lock | UPDATE

    My my.cnf look exactly like:

    +++++++++++++++++++++++++++++++++++++++++++++
    Main Node:
    ========
    [mysqld]
    datadir=/var/lib/mysql
    user=mysql
    wsrep_provider=/usr/lib64/libgalera_smm.so
    wsrep_cluster_address=gcomm://IP-1,IP-2
    binlog_format=ROW
    default_storage_engine=InnoDB
    innodb_autoinc_lock_mode=2
    wsrep_node_name=node1
    wsrep_node_address=IP-1
    wsrep_sst_method=xtrabackup
    #wsrep_sst_method=rsync
    wsrep_cluster_name=TEST_CLUSTER
    wsrep_sst_auth="xxx:xxx"
    # sync slave updates
    server-id=1
    log-bin=binlog
    log-slave-updates=true

    Second Node:
    ==========
    [mysqld]
    datadir=/var/lib/mysql
    user=mysql
    wsrep_provider=/usr/lib64/libgalera_smm.so
    wsrep_cluster_address=gcomm://IP-1,IP-2
    binlog_format=ROW
    default_storage_engine=InnoDB
    innodb_autoinc_lock_mode=2
    wsrep_node_name=node2
    wsrep_node_address=IP-2
    wsrep_sst_method=xtrabackup
    #wsrep_sst_method=rsync
    wsrep_cluster_name=TEST_CLUSTER
    wsrep_sst_auth="xxx:xxx"
    # sync slave updates
    server-id=2
    log-bin=binlog
    log-slave-updates=true
    +++++++++++++++++++++++++++++++++++++++++++++

    My total database size is around 50GB. Most database consists both MyISAM and InnoDB tables.I have use sst_method both rsync and xtrabackup but situation is same in both cases. Mysql error logs are fine.I couldn't find any issue.

    How can i overcome this issue ?

    Please help me

  • #2
    XtraDB Cluster is not going to work well with MyISAM tables. Galera replication is meant to work with InnoDB tables. See this on how to migrate to InnoDB:
    http://galeracluster.com/documentati...ghlight=myisam
    Apparently some process (backup) tries to do FTWRL and cannot due to some blocking session. Full processlist would be helpful investigating this.

    Comment


    • #3
      Hi,

      Thank you for your reply.

      Currently server is in production it cannot afford any down time.So currently i cannot connect second server with my production server.So it is hard to get the process list.

      I could see more than 100 process with state as FTWRL .The FTWRL doesn't return quickly and this stops mysql server from responding.So i need to restart mysql server for stopping all process but after some minutes all proceeds will be in the same state.I think the FTWRL is occurring only for two databases which belongs to busy wordpress sites.I have converted all their tables to innodb . But issue persists.

      I think there is no firewall issues.All needed port 3306, 4444, 4567, 4568 are opened and they work fine.Will this be a network latency between servers ?

      Thanks !

      Comment


      • #4
        Hi,
        by a full processlist I mean getting the output of:
        SHOW PROCESSLIST;
        from the time the server is experiencing problems. You don't need down time to do that, just connect to the server with mysql client. You can redirect the output (if long) to a file like that:
        mysql> tee proclist.txt
        mysql> show processlist;
        mysql> notee
        and attach the txt file here.

        And FTWRL (flush tables with read lock) is a command not used normally by applications but usually only by backup scripts. Can you identify what those would be?

        Comment


        • #5
          Hi,

          Yes i understood.I could see this issue only when second server is connected to my primary server. Currently i have disconnected second server from my primary server .Now my primary server is standalone and its process list are fine.No problems.

          This issue ( FTWRL ) arises only when the second server is connected.I will try to connect the secondary server during non peak hours and will provide process list.

          My current process list:

          # mysqladmin proc
          +--------+--------------+-----------+--------------+---------+--------+--------------------+------------------+-----------+---------------+-----------+
          | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
          +--------+--------------+-----------+--------------+---------+--------+--------------------+------------------+-----------+---------------+-----------+
          | 1 | system user | | | Sleep | 145369 | | | 0 | 0 | 0 |
          | 2 | system user | | | Sleep | 145369 | wsrep aborter idle | | 0 | 0 | 0 |
          | 383955 | eximstats | localhost | eximstats | Sleep | 492 | | | 0 | 0 | 0 |
          | 602475 | leechprotect | localhost | leechprotect | Sleep | 1799 | | | 0 | 0 | 0 |
          | 613338 | root | localhost | | Query | 0 | | show processlist | 0 | 0 | 0 |
          +--------+--------------+-----------+--------------+---------+--------+--------------------+------------------+-----------+--



          Thanks !
          Last edited by vipin; 04-27-2014, 03:46 AM.

          Comment

          Working...
          X