Re: Performance maintaining a live database

[Rick James]

Depends.

* DROP TABLE has trivial impact (unless you drop the wrong table)

* What is the clients' tolerance for locked tables?

* ALTER TABLE locks a table during the entire process.
* ALTER TABLE on 100K rows may not take so long as to cause trouble.

* DELETEing 10K rows from a table will possibly tie up that table too long.
* A loop, deleting 100 rows at a time, won't tie up the table noticeably.

* Making a replica -- well how are you going to keep both copies up to date? Or are there no INSERTs/UPDATEs?

* You can do some timing tests on what a "replica" might take by doing
CREATE TABLE foo SELECT * FROM real_table;
Then perform the maintenance on foo.
* But the CREATE..SELECT may tie up the real_table if there are INSERTs going on at the same time.

* MyISAM usually locks the entire table during an operation.
* InnoDB only locks rows (and gaps), but can still make other operations sluggish.

* The "real" solution is to have two Masters replicating from each other. One is the writable Master (A), the other is a hot standby (B).
1. Stop the SQL thread on A (so it won't execute the maintenance statements -- yet)
2. Do the maintenance on B. Replication and the maintenance are interfering with each other, but that is ok since clients are not touching it.
3. failover (this would involve pointing your clients to B and making it read-write)
4. Turn on the SQL thread on A. The DELETEs, etc, will flood in. But Clients are not impacted.
5. (Optional) failback.

* I have databases I haven't touched in years. But they are so small, it isn't worth the "maintenance effort".