How can I safely use MyISAM tables?

Question

I like the safety, consistency and self-control of InnoDB.

But I need the speed and light weight of MyISAM.

How can I make MyISAM less prone to corruption due to crashes, bad data, etc.? Validation requires validation forever (either CHECK TABLE or myisamchk).

I do not require transactional security - what is InnoDB for? But I need a database that I can restart faster than hours (or days!) Later.

UPDATE: I am not asking how it is faster to load data into tables. I already banged my head about it, and decided that using MyISAM tables for my LOAD DATA is simply a lot faster. Now I reduce the risks of using MyISAM tables. That is, reducing the likelihood of damage, increasing the speed of recovery.

Answer 1

The alleged benefits of MyISAM speed can really go away quite quickly - the fact that it lacks row-level locking means that small updates can lead to blocking a lot of data and blocking requests. Because of this, I am skeptical about the declared advantages of MyISAM speed: start doing a few UPDATE, and queries per second will be a tank.

I think you better ask: "How can I speed up applications supported by InnoDB?" and the response then processes the caching data, possibly at the object level, in lightweight caches - the cost of ACID and, say, for web applications, is really not needed.

If UPDATEs are rare (if they are not, MyISAM is not a good choice), you can even use the MySQL query cache.

memcached ( http://www.danga.com/memcached/ ) is a very popular option for caching objects. Depending on your application, you have other options (HTTP caches, etc.)

Answer 2

In some cases, the operational benefits of MyISAM are actually quite minimal; you need to compare your own MyISAM vs InnoDB application. Using the InnoDB transaction mechanism also provides other benefits.

In my testing, InnoDB will use, as a rule, 150% more disk space than MyISAM - this is due to its block structure and lack of index compression.

If you can afford it, just use InnoDB.

As for your actual question: if you split the table into several MyISAM tables, the number of corrections required for the failure will be much less; if your data is large, it might be a good idea anyway for other reasons.

Answer 3

in normal practice, you should not receive corruption. if you get corruption, you need to look at things like bad memory, bad hard drive, bad disk controller or possibly mysql error.

if you want to do your best, you can configure the replication slave. when the master dies, stop replication on the slave and make it the new master. clear the data from the old master and configure it as a subordinate. The user downtime will be limited by the amount of time it takes to detect that the master is dead, and bring the slave.

this has the added benefit of being a good way to back up with zero downtime: turn off the slave process and back up the slave.

Answer 4

As long as I agree with innodb's comments, I will give a solution to your MyISAM problem.

A good way to prevent corruption and increase speed would be to use MERGE tables

You can use 2 or more MyISAM files. Typically, backups use old data that is often not used, while others use newer data. Then you will have 2 FRM (MyISAM table files) on your hard drive, and one of them will be protected. Usually you compress old MyISAM tables and then they defiantly will not be corrupted, as they become read-only.

This method is commonly used to speed up large MyISAM tables, but you can also apply it here.

Hope this helps your question. Although I understand that this did not really help Crash-proof MyISAM, it does provide quite a bit of protection.

Answer 5

Are you married to MySQL? Postgres is ACID-compliant (e.g., innoDB) and (if configured correctly) is almost as fast as MyISAM.

Answer 6

Your comment:

No, the main problem is the surprisingly initial import of data using hard disks into a table. MyISAM time: 12 minutes. InnoDB time: 3 hours. After my bootstrap, UPDATE does not exist and INSERTS are rare. The unknown solution of InnoDB is disappointing.

offers to abandon restrictions and indexes, and then enable / rebuild them after loading can significantly speed it up - I suppose you tried this? Has this improved the situation?

Answer 7

It really depends a lot on how you use the tables. If they are written heavy, then you may want to remove the indexes, which will speed up the recovery time. If they are considered heavy, you might want to use replication, which serializes all the records in your tables, minimizing the recovery time for your read copy after a crash.

Once you can do this, write a copy of the InnoDB table, and then copy the copy of MyISAM. The benefits of MyISAM are mainly read-oriented.

Using replication, you will have a delay time between reading and writing

Answer 8

Get a good UPS with decent air conditioning. Work on stable and redundant equipment.

I do not believe MyISAM tables to survive while recording while recording, so I believe that your best bet is to reduce the number of crashes (and writes).