MySQL proxy and SHOW PROCESSLIST query time

innodbMySQLmysql-5.1mysql-proxyreplication

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.

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

Related Solutions

MySQL Master/Slave Master/Master setup

You might look into MySQL Proxy. Features include:

load balancing
failover
query analysis
query filtering and modification

I haven't run it myself, but it seems to cover what you want.

UPDATE

Apparently the Read/Write splitting is a proof of concept and not for production use

MySQL connection count much larger than query count

Here is something interesting to remember : the status variables Questions takes a count of all queries executed by mysqld, regardless of who issued each query.

Why do I say regardless of who issued each query ?

There are queries that are launched internally by mysqld. Here is a simple example:

mysql> show global status like 'Com_select'; show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 15    |
+---------------+-------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 56    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Com_select'; show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 15    |
+---------------+-------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 58    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 59    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 60    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

Please note that issuing a simple query like show global status like 'Questions'; is a query even if your are just requesting internal data from mysqld.

Multiply this by any number of DB Connections coming from MONyog, Munin, Nagios, MySQL Enterprise Monitor, or any other MySQL Monitoring tools and will always have a steady rise in the number of lightweight queries being executed.

I wrote about this before in ServerFault in a question "1 billion mysql queries in 24 hours? Can something be wrong?"

Queries can quitely come and go. The question left to answer is, why so many DB Connections? You need to make sure Apache is closing DB Connections at the same rate as mysqld. You may need to see run netstat and look for DB Connections to mysql that have the TIME_WAIT status.

There are bug reports on this going back to MySQL 4.1 although the root of the problem may actually be with PHP:

Best Answer

Related Solutions

MySQL Master/Slave Master/Master setup

MySQL connection count much larger than query count

Related Question