Mysql – way to see completed process list in thesql (SHOW FULL PROCESSLIST)

First thing I noticed is that you have a redundant index.

This is your original table1:

id_a int
id_b int
val_a int
val_b int
pk (id_a, id_b), index id_a, index id_b

You do not need the index on id_a since it is already the first column of the primary key. That's 200k rows less of index entries to process. Just run this to get rid of it:

ALTER TABLE table1 DROP INDEX id_a;

Another step you will need is to reload the table and disable non-unique indexes while reloading (This is MyISAM only). In addition, you need a cleaner algorithm. I have two algorithms. Here is the first one:

ALGORITHM #1

CREATE TABLE table2 LIKE table1;
ALTER TABLE table2 DISABLE KEYS;
INSERT INTO table2 SELECT * FROM table1;
ALTER TABLE table2 ENABLE KEYS;
ALTER TABLE table1 RENAME table0;
ALTER TABLE table2 RENAME table1;
DROP TABLE table0;

Since DISABLE KEYS leave the primary key open for updating, the primary key is loaded during the INSERT INTO table2. The ENABLE KEYS step rebuilds the id_b index.

Now, here is the second algorithm:

ALGORITHM #2

OPTIMIZE TABLE table1;

A whole lot shorter, isn't it ? The second algorithm (using OPTIMIZE TABLE) does everything the first did plus recompute index statistics, do internal table repairs, and index reorganization.

UPDATE 2011-09-07 17:38 EDT

The reason these two algorithms should improve things is the fact MySQL indexes are now processed linearly.

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.