I have MySQL replication currently occurring between two masters bidirectionally, one in a data center locally, another over at Amazon EC2.
Everything seems to function normally replication wise, no issues per se, except for the occasional query that causes a collision but those are few and far between. Recently I set up mysql-proxy to attempt to load balance the two servers, the load balancing really on kicks in after the local machine receives 40 or more connections at which point subsequent database connections get shuffled off to the EC2 machine.
Something we noticed recently is that mysql proxy will notify us that there are 41 connections and then start its balancing. However when I connect to the local machine and do a
SHOW PROCESSLIST; it may only provide me with 30 connections.
Anyone have any idea why this maybe?
In addition to that as a result of issuing the
SHOW PROCESSLIST; command I've noticed that there are a great many queries running on both machines that state they have been running in excess of 5000 seconds. I'm pretty sure these are "zombie" queries but does anyone know why they are created in the first place?
FYI We're running mysql version 5.1.54 on the latest versions of ubuntu and debian.
Any ideas would be extremely helpful.
Turns out we're not using mysql_pconnect and are infact using the mysqli libraries. I still have been unable to find out why this happening and will report back once I find out.