MySQL 5.1 performance issues

MySQL & MariaDB Other MySQL® Questions
1

Hello,

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.
Thankyou.

2

It’s hard to answer exactly, but I don’t think it’s a problem with replication. Rather, I think it’s likely to be a problem with queries not running the same way on 5.1 as on 4.x. This is not uncommon, and if it’s the case then it’s a mysqld bug.

We wrote mk-upgrade to help mitigate the risk of upgrades for exactly these types of situations.

3

Hello,

I thought it might because we are replicating from MySQL 4.1 (master server) to mysql 5.1 (slaves) because MySQL manual stats that:

MySQL supports replication from one major version to the next higher major version. For example, you can replicate from a master running MySQL 4.1 to a slave running MySQL 5.0, from a master running MySQL 5.0 to a slave running MySQL 5.1, and so on.

Or it might be i didn’t follow proper upgrade procedure. I upgraded MySQL 4.1 servers to 5.1 by uninstalling 4.1 rpm’s than i installed mysql 5.1 rpm’s. Finally i didn’t backup/reload databases for upgrade instead i ran mysql_upgrade script.

What is mk-upgrade script. How it works and how to get it ?

Regards,
Aspen