2

I have an online game that is PHP & MySQL based (PHP 5.2.9 & MySQL 5.0.91). Lately I've been having issues where the entire site freezes sometimes during specific times when I'm deleting several thousand rows of old data in tables. I have also been having issues where strange things happen when queries have to wait for what I assume is a table lock and the game doesn't function as expected.

All of my tables are MyISAM and it runs over 900 queries per second. On the entire database (~150 tables), 88% of queries are reads and only 12% writes, but a few of the tables are closer to 50/50 and have a lot of data read and written multiple times per second from various clients (this is a multiplayer game). These tables also store anywhere from 1M-5M rows.

I know that MyISAM is supposed to read faster, but InnoDB doesn't have to lock the entire table on writes. I've gone through a bunch of topics on here and other sites, but I'm still not sure what to do to solve these issues.

James Simpson
  • 13,488
  • 26
  • 83
  • 108

2 Answers2

8

The biggest problem with MyISAM is that it uses table level locking for UPDATE and DELETE commands, so if you're deleting a record from your database, every other update or delete will block while that query runs.

InnoDB uses row-level locking, so only the record being updated will block, as it should be for obvious reasons.

InnoDB also has a lot of useful features like support for transacions and foreign key constraints, which makes it the engine of choice for myself and other developers.

As far as read speeds are concerned, InnoDB stores its record ordered by the primary key, which makes it a lot faster to retrieve records where you're searching by the PK (things like SELECT foo FROM bar WHERE id = baz;). MyISAM stores its records more or less in the order they are added to the database, which means it can be faster if you're mostly searching by the date records are added, for example things like financial tracking where you could want to grab all transactions which occurred on a certain day.

Wikipedia details a lot of the major differences between the two engines, but suffice it to say that in 99% of cases, you probably want to use InnoDB.

The only circumstance I can think of here MyISAM has a clear victory is if you need support for Full-Text Search. MyISAM supports FTS, but InnoDB does not. Even then, you're probably better off using a third party system, like Sphinx, rather than use MyISAM.

AgentConundrum
  • 20,288
  • 6
  • 64
  • 99
  • What if most of my reads aren't by the PK? A lot of my reads look for the game's id, which selects a few hundred rows out of the table, and so I have an index on gameID, but it isn't primary. – James Simpson Dec 17 '10 at 00:48
  • @James: Since I doubt you're trying to read in transactional order, you wouldn't be leveraging the default ordering of MyISAM anyway, so in either case you would just be reading against a secondary index which means you're taking the same performance "hit" (though it won't hurt either way). – AgentConundrum Dec 17 '10 at 01:44
1

If you have any sort of complex data model, and you are compelled to use MySQL or one of its great many forks, then use a transactional storage engine that supports foreign key references. Otherwise you will have to reinvent the data integrity "wheels" all over again, and that is not fun and is error prone (especially when you find that your data has integrity problems all over the place, and you're missing the proper information to put it back together.

I'd suggest using something like PostgreSQL, if you are able to do so, because such robustness features are built-in. You can still balance load between servers if your application grows to such a volume that it is required.

Michael Trausch
  • 3,187
  • 1
  • 21
  • 29