Mysql – managing a growing database in the long run (10 years from now)

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)

I don't have experience with DynamoDB so the best I can do is offer some pointers on optimizations to look for if you stay in the hosted MySQL world.

• Memory allocation. If you're using innodb be sure you have adequate buffer pool size with two log files as the lesser of 25% buffer pool size or 1 Gig. In a perfect world your buffer pool would be as large as all the .ibd files (aka sum(data_length+index_length) from information_schema.tables where engine='innodb'). Understandably this is not always possible, in which case try to allocate 60-70% of your system memory to buffer pool while leaving the rest available for disk cache. (This of course assumes this is a dedicated MySQL server not hosting apache or running your php scripts).

If you're running MyISAM tables then at least make sure your key_buffer_size can accommodate the index_length (or size of your .MYI files).

• Disks. If you're needing to fall back to disk you want those to be as fast as possible. In a perfect world you'd have PCI-E based SSD cards. I imagine this is out of your budget if you're worried about charging after switching to amazon, so maybe see if your hosting provider has any options above what you currently have. SSD based SAS drives > 15k hdd sas > nl-sas.

• Service layer between mysql and clients. There is memory overhead to each connection to the database that could be used toward buffer pool or disk cache. Try to have a service layer that maintains a bounded connection pool and providing application layer caching where possible.

• Distribute the work load. This is tantamount to going to the "cloud" just that you're managing all machines yourself. If your app is mostly read heavy and not always required to be up to the microsecond up to date you can setup replication between many physical machines and load balance the reads across them.

Look toward getting slaves setup at different physical datacenters across the country/world/what ever's applicable to your demographic. Load balance connections to the nearest one. (This assumes traffic is first routed to your front end in these locations otherwise the problem might just be worsened).

• Partitioning. This is especially true if you're using myisam and dealing with table level locks. It's also particularly useful if you have a table that has patterns of pruning old rows periodically (you can just drop partitions instead of running individual delete queries which fragment the data).