Optimizing Databases in Ram (mysql)

Posted by kevin Mon, 13 Jun 2005 16:35:10 GMT

When I first wrote allpoetry, I optimized it so that no query was not fully indexed. This makes for big indexes, and very fast performance. But I’m starting to realize that other’s instead add database servers instead to distribute out the ‘slow’ load.

Part of the whole problem is that disks are very slow. Since ram is cheap these days, I’m more interested in a in-memory database, especially if that can cut down on the index size.

Allpoetry is about 4 GB of database now, a little over half indexes.

Mysql Cluster is one option, but requires 2*Database size+ some memory, and at least two servers. But it scales nicely. But it isn’t that fast. I think it’s lame to need so much memory. An object-oriented database like madeline (based off a similar java system), doesn’t have any need of doubling the data storage? But perhaps mysql’s statistics includes the indexes?

Another option I’ve been obsessing about lately is the idea of running mysql on a ramdrive. It should be pretty straigtforward:

  1. Create a ramdisk on startup
  2. Copy the mysql database from /db/allpoetry to the ramdrive
  3. Symlink a directory under /lib/mysql to the new area
  4. Make the binary update log on the HD still
  5. Have a cron job to apply the update log to the disk-version of the database every 30 minutes or whatever.
  6. Replicate and repeat as normal.

A device like this could help make this much easier:
http://www.tomshardware.com/hardnews/20050601_115506.html

Any ideas or suggestions?

Technorati Tags: ,

Posted in  | 2 comments

Comments

Leave a response

  1. Becks said 9 days later:

    It’s amazing how Google works, it really is… I ran a search on Google for my AP author page(i wanted to see if it was listed, it was), and this totally unrelated personal page came up, and ordinarily I wouldn’t have looked twice but this particular page had berkeley in the web address and as a prospective student I decided to why not check it out. Well, upon clicking on the link I find out that the owner of the page’s boyfriend happens to be the creator of AP. I nearly fell off my chair. Life is weird.

  2. John said 19 days later:

    This sounds very interesting have you tried it yet. If so what was the results?

    I would like to do this with my database.

RSS feed for this post

(leave url/email »)