I have a heavily accessed table corresponding to user's private messages, with over 10 M rows. When I run the user deletion process, obsolete messages are deleted from the table, typically more than 1000 rows. The problem raises when this table is optimized, because that takes nearly 1 minute, during which the table is totally locked and all queries are delayed. The same applies for other large tables.

The question is, can I simply leave the table permanently un-optimized without major performance issues? Or should I optimize the table at least once a week, at a time with low traffic to prevent annoying my online users?

  • You are optimizing the table after the deletion process? Why? – the-wabbit Feb 22 '14 at 20:20
  • 1
    if you have a "big, heavily accessed" table, you probably should use an innodb storage rather than MyISAM. You will not be able to "optimise" anymore but global performance will be better since every operation will not make a table lock. – Olivier S Feb 23 '14 at 8:20
  • 1
    @OlivierS "will be better" is a bold claim. In fact, whether the overall performance is going to increase or plummet into the abyss instead will heavily depend on the access pattern. InnoDB is by no means a panacea to performance problems. – the-wabbit Feb 23 '14 at 21:01
  • With myisam you have a table lock for every operation. Even a SELECT. – Olivier S Feb 24 '14 at 7:17

Yes, the performance will deteriorate - but we can't tell you how quickly, or what level of deterioration is acceptable - since you are already doing this why don't you measure the impact yourself.

Akber is correct in saying that using a cluster would allow you to optimize one system while the other still serves up data - but why not set them up as a master-master pair - then you don't need to upgrade and downgrade when you switch. And there's no need to write temporary records - just wait for the server lag to recover then switch over. It's also a really good solution for backups and, of course, high availability.

Another solution already in wide use for this kind of exercise is zero-downtime schema changes - there are 2 good implementations I know of: one written in Perl by the guys at Percona, and one written in PHP by the Facebook people.

  • Thanks, but it seems master-master replication require another server, is that correct? Currently, I can't afford another MySQL server. – andreszs Feb 23 '14 at 15:54
  • 1
    @Andrew to be exact, master-master replication will require two instances of MySQL. They might very well run on the same server. – the-wabbit Feb 23 '14 at 21:03
  • Roger that, I'll look onto that. Thanks for your time. – andreszs Feb 23 '14 at 23:39

I presume you mean re-organization of tables to remove space taken up by deleted rows.

  • The easiest solution is by replication:

    1. Replicate the tables or the entire database to another mySQL server or instance (the slave)
    2. Create a user with read-only privileges on the slave.
    3. Switch your application connection to the slave with the read-only user. Show appropriate messages when a new record insert fails.
    4. Optimize the master and then switch back.
  • A more complicated variation of this can be used to write temporary records to another table in the slave and then copying that back to the master, but that would involve application-level changes.

  • The ideal solution is to have a write queue and writeable and read-only tables. This design pattern is similar to the one known as CQRS. It is based on eventual consistency and your users may not even notice a blip. All write transactions will eventually be written to the table, but may not be available immediately as they are held in the queue while the writeable table is being re-organized.

  • Thanks for the tip, but I can't use either... replication to a slave table is a time-consumig process, I just copied a 500 MB table and it took 1 minute, during which the table was locked, so that doesn't avoid the problem. CQRS seems too complex. For the moment, I will apply INSERT DELAYED to those tables, at least to prevent the long wait if the user is adding something while the table is locked. – andreszs Feb 23 '14 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.