Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

[Addendum]

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.

share|improve this question
add comment

1 Answer

I am against writing to both Masters in a dual-master setup. There are too many things that can go wrong, and they can be messy to fix -- AUTO_INCREMENTs, other duplicate keys, etc.

Hundreds of "Sleep" connections is virtually no impact on a server, so limiting to 40 is not useful. 10 or more active connections (non-Sleep) can be an issue. In that case I would look at the queries. Usually optimizing the queries is the best answer.

Note also, that every write (INSERT, UPDATE, etc) that is done on one Master must be done on all the other Masters and Slaves. So, you can't really "spread" writes around.

If you have processes that do only reads (SELECTs), then they should go to Slaves and/or the backup Master, not the live, writable, Master. This will help.

Be aware of the "critical write" problem. Example: A user posts a blog comment, then looks at his comments, but it is missing. This can happen if the write went to one machine, but the read hit another, and replication is "behind".

(My comments apply to all versions, and all APIs, not just 5.1 and PHP's mysqli.)

I stay away from mysql_pconnect (and other connection pooling mechanisms). Connection startup/teardown is very fast in MySQL. Pooled connections may have issues with @variables, transaction modes, sql_modes, etc.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.