3

I have an interesting database problem. I have a DB that is 150GB in size. My memory buffer is 8GB.

Most of my data is rarely being retrieved, or mainly being retrieved by backend processes. I would very much prefer to keep them around because some features require them.

Some of it (namely some tables, and some identifiable parts of certain tables) are used very often in a user facing manner

How can I make sure that the latter is always being kept in memory? (there is more than enough space for these)

More info: We are on Ruby on rails. The database is MYSQL, our tables are stored using INNODB. We are sharding the data across 2 partitions. Because we are sharding it, we store most of our data using JSON blobs, while indexing only the primary keys

2 Answers 2

1

There's a lot of options here. First, NDB is MySQL's clustering engine, which stores data in memory. NDB does have some limitations, however.

memcached is a popular solution that is often used but it requires the application architecture to support it.

You could have MyISAM tables that you specifically store within a RAM disk, as they are able to be relocated individually unlike with InnoDB. InnoDB's entire table space would have to be stored on the RAM disk.

You may find the memory engine better suited than my RAM disk hack, however. They're also more limited than other engines, as they can't support BLOBs among other things. For the data to maintained, you would have to have a wrapper script to dump and restore the data. This also introduces risk to the data, as a power loss even with scripts would result in data loss.

Ultimately, you will likely benefit the most from properly tuning and optimizing your MySQL database and queries. A properly tuning MySQL database utilizes memory caching.

There's a lot of resources available on this already both on Serverfault and the Internet as a whole. MySQL has a document and here's a MySQL performance blog post, which are both very useful resources. Here's another post where they have a formula for calculating InnoDB memory usage.

2
  • useless given that the post already talks of ONLY 8gb memory in the machine, you know.
    – TomTom
    May 20, 2010 at 17:58
  • 1
    Perhaps you should re-read my post. Is English not your native language?
    – Warner
    May 20, 2010 at 18:00
0

The best you can probably do is examine execution plans for your long running queries and tune 1) the query and 2) the database appropriately. You could build indexes for the "identifiable parts of certain tables" to speed queries. You could also move your more frequently used data into its own table, and the less frequently used data into its own.

Doing this with JSON blobs will be difficult because if you need access to one attribute of the JSON blob, you will have to fetch and parse the whole blob. If your JSON blobs are in a consistent format, build a real table structure to reflect that, and you'll probably 1) already have improved the performance and 2) have a much more flexible structure when you need to performance tune later.

You must log in to answer this question.