We have replication setup having MySQL 4.1 acting as Master Database and MySQL 5.1.32 on replication slaves, We have five (5) replication nodes for read operations. We are in the process of upgrading our database servers to MySQL 5.1 from MySQL 4.1. Initially, we upgraded slaves nodes and upgraded three (3) slaves to MySQL 5.1 while 2 slave nodes are still running MySQL 4.1 (Master database is still on MySQL 4.1).
From the day we upgraded our database server to MySQL 5.1. We are facing serious performance issues. We are facing locking issues on MySQL 5.1 slaves queries are stuck quite regularly in queue and state is locked because of this server ran out max-connections and we are getting error “Too many connections”. Interesting thing is that whenever MySQL 5.1 slaves ran out to max-connections server load is zero.
To prove my point, MySQL 4.1 slaves are still running fine enough while we are facing issues only with MySQL 5.1. I taught it might be because of master database
version is MySQL 4.1 and we are replicating it to MySQL 5.1 slaves and MySQL 4.1 slaves. We are using old replication format i.e. STATEMENT BASED REPLICATION.
Beyond of this we are using ldirectord as loadbalancer on MySQL slaves to distribute read queries.
Is there any compatibility issues b/w MySQL 4.1 as master db and MySQL 5.1 as slave db. Anyone else faced this sort of issues ?
server : CentOS 4.7 (Final)
MySQL Community Server (4.1.22) --> Master DB
MySQL Community Server (5.1.32) --> 3 Slaves
MySQL Community Server (4.1.22) --> 2 Slaves
max_connections = 200 (slave nodes)
Looking forward to hear from you.