5

Many sites and script still use MySQL instead of PostgreSQL. I have a couple low-priority blogs and such that I don't want to migrate to another database so I'm using MySQL.

Here's the problem, their on a low-memory VPS. This means I can't enable InnoDB since it uses about 80MB of memory just to be loaded. So I have to risk running MyISAM.

With that in mind, what kind of data loss am I looking at with MyISAM? If there was a power-outage as someone was saving a blog post, would I just lose that post, or the whole database?

On these low-end-boxes I'm fine with losing some recent comments or a blog post as long as the whole database isn't lost.

Xeoncross
  • 55,620
  • 80
  • 262
  • 364
  • How little memory is your VPS giving you? You might want to consider going with a different host. Dreamhost has 600 MB VPS for $30 a month. Which they say is enough for a top 100 blog. If $30 is too much maybe your data isn't worth all that much and you should just set up a script to do daily (or more often) backups via MySQL dump. – Kibbee May 27 '11 at 23:59
  • Thanks, but I'm not willing to pay $30 a month for only 600MB. Also, there is no reason to increase my RAM if I can find a way to make MyISAM work. – Xeoncross May 28 '11 at 15:07
  • 1
    http://stackoverflow.com/search?q=myisam+corrupt – Denis de Bernardy May 30 '11 at 14:07

6 Answers6

5

MyISAM isn't ACID compliant and therefore lacks durability. It really depends on what costs more...memory to utilise InnoDB or downtime. MyISAM is certainly a viable option but what does your application require from the database layer? Using MyISAM can make life harder due to it's limitations but in certain scenarios MyISAM can be fine. Using only logical mysqldump backups will interrupt your service due to their locking nature. If you're utilising binary logging you can back these up to give you incremental backups that could be replayed to aid recovery should something corrupt in the MyISAM tables.

eroomydna
  • 1,261
  • 9
  • 4
  • +1 In the end, I really would rather pay for more memory than deal with downtime. If there was only one or two seldom written tables that would be one thing, but with +10 tables I just shouldn't risk it. Even SQLite would be a better choice. – Xeoncross Jun 04 '11 at 16:32
2

You might find the following MySQL Performance article of interest:

For me it is not only about table locks. Table locks is only one of MyISAM limitations you need to consider using it in production. Especially if you’re comming from “traditional” databases you’re likely to be shocked by MyISAM behavior (and default MySQL behavior due to this) – it will be corrupted by unproper shutdown, it will fail with partial statement execution if certain errors are discovered etc...

http://www.mysqlperformanceblog.com/2006/06/17/using-myisam-in-production/

Jon Black
  • 16,223
  • 5
  • 43
  • 42
  • "Hidden corruptions. If could be bad memory OS or MySQL bugs but corruption may happen and go for long unnoticed with MyISAM storage engine. This hidden corruption may later cause crashes wrong query results and further data corruption." – Xeoncross May 31 '11 at 15:03
0

The MySQL manual points out the types of events that can corrupt your table and there is an article explaining how to use myisamchk to repair tables. You can even issue a query to fix it.

REPAIR TABLE table;

However, there is no information about whether some types of crashes might be "unfix-able". That is the type of data loss that I can't allow even if I'm doing backups.

Xeoncross
  • 55,620
  • 80
  • 262
  • 364
0

With a server crash your auto increment primary key can get corrupted, so your blog post IDs can jump from 122, 123, 75912371234, 75912371235 (where the server crashed after 123). I've seen it happen and it's not pretty.

preinheimer
  • 3,712
  • 20
  • 34
0

You could always get another host on the same VLAN that is slaved to your database as a backup, this would reduce the risk considerably. I believe the only other options you have are:

  1. Get more RAM for your server or kill of some services
  2. See if your host has shared database hosting of any kind on the VLAN you can use for a small fee.
  3. Make regular backups and be prepared for the worst.
Geoffrey
  • 10,843
  • 3
  • 33
  • 46
-4

In my humble opinion, there is no kind of data loss with MyISAM.

The risk of data loss from a power outage is due to the power outage, not the database storage mechanism.

J. Bruni
  • 20,322
  • 12
  • 75
  • 92
  • Well, I'm assuming that some important write could be occurring to a MyISAM info table, header, or row that would leave that file in an invalid state. How does MyISAM handle this? – Xeoncross May 27 '11 at 23:32
  • Hmmm, sorry, I really don't know. I feel MySQL / MyISAM is reliable, but I ignore this level of detail of the implementation. I'd check MySQL documentation... or even the source code. – J. Bruni May 27 '11 at 23:38
  • This is only true to the extent that you ignore the existence of backends which do not lose data. Yes, the immediate cause of the loss was the power outage, but it's by no means wrong to say that MyISAM can cause data loss when it's possible to write a backend that will retain the data, and you're ignoring that there are other problems with MyISAM. – Andrew Aylett Jun 03 '11 at 09:11