Geek Answers Handbook

Choosing a MySQL Table Type for Non-Critical Webapp Data (MyISAM vs. InnoDB)

Consider this scenario with the following assumptions:

  • The database is used for non-critical webapp.
  • Request speed is vital.
  • Read / write patterns of approximately> 95% are read and <5% are written.
  • The database is backed up daily with mysqldump.
  • No need for transactions or advanced crash recovery. If the database crashes, I just import the latest mysqldump. This is good enough in this scenario.
  • No full-text search required.

The advantages of MyISAM in accordance with these assumptions:

  • It is very fast (with one exception - see below).
  • It is lightweight and has an easy to understand mapping between a database / table and physical files in the file system (.MYD / .MYI / .frm).
  • Simple backup (mysqldump).

All in one. I am very pleased with MyISAM with one major exception. MyISAM has one major drawback according to the above assumptions, and this is table-level locking. When UPDATE is run toward a frequently viewed table, all reads are blocked. Needless to say, this causes serious performance issues that need to be addressed.

My questions:

  • Is there a way to get rid of table level locking without disconnecting from MyISAM?
  • If I have to switch to InnoDB - how to configure InnoDB so that it works as close as possible to MyISAM (do not think about transactions, the structure of logical files, etc.). How to configure InnoDB as "like MyISAM, but without table-level locking"?
+4
source share
4 answers
  • No, MyISAM means table level locking.
  • You cannot get it “exactly the same”, but you can get it “much more” by turning on the innodb_file_per_table option. However, InnoDB still stores important information in the data file at the system level, and you cannot do things such as accidentally renaming a database by renaming the directory in which it lives, as you can with MyISAM.
+1
source

Have you really used performance metrics using the myisam and innodb tables? In my experience, the speed difference is not really that big when you consider all the ACID benefits you get from innodb. Only locking a table in itself will affect speed, so innodb will be faster.

Also note that myisam is much faster on inserts, not on choices. You insert only 5% of the time ... do the math.

You can always use mysqldump with innodb, so your backup process is the same.

0
source

I know that some projects use a mirror database for search. It is usually optimized for search, and sometimes even runs on another machine to isolate overhead.

The only drawback here is that keeping them in sync is a bit of a hassle. If the outdated data in your lookup table isn’t too worrying, this might be the best choice. If performance is a problem.

This is not my favorite decision, but in theory it is quite simple.

0
source
  • When it comes to backups, InnoDB doesn't stop you from using mysqldump.
  • Are you sure you really need to maintain a mapping between database tables and files on disk? Manual database file operations are rarely a good idea.
  • In InnoDB you do not need to use transactions, by default it works in the "autocommit" mode (each request will be automatically credited).
  • InnoDB is slower these days is a myth, but of course it depends on your workload.

In other words, I think you should definitely give InnoDB a try and compare the performance of your application. Migration is extremely simple, so I see no reason not to try. For me, InnoDB is the default choice for a long time.

0
source

More articles:


All Articles