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:
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.
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:
[url]http://galeracluster.com/documentation-webpages/myisamtoinnodb.html?highlight=myisam[/url]
Apparently some process (backup) tries to do FTWRL and cannot due to some blocking session. Full processlist would be helpful investigating this.
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 ?
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?
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.