MySQL Read/Write Distribution – Best Way to Find Out

I'm going to highlight one of the products listed in the wiki link provided by Jack Douglas.

Galera is a product I've been hearing a lot about recently, but have not had a cause to implement it yet into a production environment.

Back in July, Percona wrote a brief blog about implementing it, but mentioned there was no 'production' release at that time.

They've since released the first major version, v1.0. And Vladimir has been shedding more light on it this month. And with some benchmarks

Due to your write-heavy environment, I highlight this use-case:

Distributing writes across the cluster will harness the CPU power in slave nodes for better use to process client write transactions. Due to the row based replication method, only changes made during a client transaction will be replicated and applying such a transaction in slave applier is much faster than the processing of the original transaction. Therefore the cluster can distribute the heavy client transaction processing across many master nodes and this yields in better write transaction throughput overall.

_{Disclaimer: I'm not affiliate with Galeria or Percona, and have not used it personally...yet.}

First, each time you UPDATE the status column, you are having to update the index as well (source). Evaluate your indexing to see if you really need the index on the status column. My guess is no, since it has an extremely low cardinality and MySQL probably won't use it anyway.

If you ignore me and think you do need it, follow the advice in the article to drop the index before your loop and re-add it after you're done.

Here are some other things you might do if that doesn't help:

• You are taking all the columns from the data but only using number. Don't do a SELECT *, but instead a SELECT number. That won't help your writes, but it is a good performance practice. Only select the columns you're using.

• Your number index isn't getting used at all. This means it is not unique enough to be useful for updating. (Slight tangent: how many rows does a single UPDATE affect?) I would drop it, or at least add it to process index.

• It looks like process is unique enough for MySQL to whittle the amount of rows down to 16k, instead of 1 million. In light of this, I would add AND process=x to your update statement (I'm assuming you know process from the original SELECT statement):

  -- FAILED--
  UPDATE data SET status = 2, error='$error' WHERE process=X AND number = $data['number']
  
  -- SUCCESS --
  UPDATE data SET status = 1 WHERE process=X AND number = $data['number']
  

A hint about unnecessary indexes in InnoDB. InnoDB is using a hidden 'primary key' (since you don't have one defined) and is using that when it writes the indexes. So for each Index you're using, you add the size of the index + the size of the hidden primary key to the data file. If you're not using the index (or MySQL can't use it), you are wasting space and adding overhead each time you insert a new number (same for status, as discussed earlier)