Home / server / Questions / 340552
Accepted
isync
Asked: 2011-12-14 09:19:11 +0800 CST

Copy MySQL to RAM as a poor man's memcached replacement?

  • 772

What about this idea:

  • I've got a 4GB MySQL database, with few UPDATEs.
  • On system startup I copy it to RAM and run it from there.
  • Every couple of hours or even on UPDATES, I dump it to disk.

...as a kind-of poor-man's replacement for revisiting all code routines using the db and implementing memcached or similar.

mysql lamp memcached

3 Answers

  1. Best Answer

    Use the the MEMORY storage engine on a read only slave to do your reads from, is exactly what you really want and a sane setup. Forget "dumping it to disk" (?!) or other strange things.

    You can even put the slave as another instance on your existing server if you can't afford to setup a dedicated slave, but properly tuning the MySQL parameters for mostly read workloads will bring a significant performance enhancement too!

    • 10

  2. use innodb engine for all your tables, in my.cnf set innodb_buffer_pool_size to 2-3GB of ram, during startup run full table scan for all tables [or just mysqldump -A]. this should give you very decent read-only performance if you have good indices on all tables.

    also if you go with this solution your code will be simpler - you'll not have to take care of sync'ing back from in-MEMORY tables back to myisam/on-disk tables.

    • 4

  3. Shot in the dark, but are you actually experiencing performance issues first?

    Second, if you're having performance issues, you're going to have to look at tuning. Tune the application (if you're the dev or have strangle-privileges on the devs) to use proper queries so you're not using huge JOINs and such.

    Then look at database architecture. Making goofy workarounds will make it a bigger headache to manage later. The steps you take depends on your usage case. You might consider getting a beefy server to scale vertically, then put it behind a memcached server, and create a slave read-only for things like running reports and backups.

    Don't take away RAM from the database to try a scheme that subverts good practice. There are blogs for high-performance database tuning available to give ideas and the StackOverflow podcast blog has stats on how this site runs (MS Stack); relatively heavy traffic, but not a complicated setup.

    • 2