MySQL Performance – Optimizing Read-Only Tables

MySQLoptimizationperformanceread-only-database

I have a MySQL database with 15 tables. Three of them are used for website authentication and three other are tables that are frequently read and written to. Then other tables are set in stone and I'd never need to change them unless I make some significant changes to the web app.

Is there anything I can do to make my database more efficient and for performance, given that I don't need to write to these tables at runtime?

From Googling, I was able to see some settings for MyISAM, but would prefer to stick with InnoDB. Couldn't find any other info other than certain people having their entire database as read only. However, mine has some tables that I'd need to read/write.

EDIT:

Four tables are 4MB and below and one is 7.55MB. Also wouldn't I then need to adjust all my procedures to check whether the table in memory exists or not? Cause it may disappear when the instance is restarted or something.

Best Answer

Short answer: You are making unnecessary work for yourself. Use InnoDB for everything. Forget about "readonly".

Long answer:

  • MyISAM is dying.
  • InnoDB is faster in some benchmarks than either MyISAM or MEMORY.
  • InnoDB "caches" things in its "buffer_pool", which is in RAM. If you were using MEMORY to get things held in RAM, then "why bother".
  • Because the buffer_pool is a "cache", there is no requirement that all the data and indexes be held in memory. Instead, only the "frequently" used blocks are kept in memory. Since it is at a block level, it is even better than a human trying to second guess what to pin in memory (via MEMORY engine) and what not to.
  • Using space for MEMORY tables takes away from using RAM for the buffer_pool
  • Both MyISAM and MEMORY lock the entire table (when needed); InnoDB locks only rows.
  • True, MEMORY tables are not "persistent". InnoDB tables are persistent.

Bottom line...

  • Use InnoDB for all of your tables.
  • Set key_buffer_size to 20M only because some system tables use that engine.
  • Set innodb_buffer_pool_size to 70% of RAM (or a smaller percentage if you have less than 4GB of RAM).
Related Question