News for hackers. Stuff that matters.
Re:In-Memory caching: Why we can't just trust the database t... by tocomment on Tuesday November 11, 2008 @09:19PM (#361274)
So if you're running a server with 32GB of RAM and your entire database is 4GB, why can't the whole database just be kept in memory and be done with it?

I don't see an obvious way to do this in MySQL at least and it certainly doesn't do it automatically.

Re:In-Memory caching: Why we can't just trust the database t... by acro on Wednesday November 12, 2008 @04:11AM (#361631)
If your application is only reading data then it is fairly easy to scale well. Most real world applications also update data, and when updating data it must be written to permanent storage. I would argue the real scalability problems are created by the writes, not the reads.
Re:In-Memory caching: Why we can't just trust the database t... by wayne on Tuesday November 11, 2008 @09:53PM (#361314)
It essentially will be after everything's paged in. If you want to be sure though, create a RAM disk and stick your data files on there, though then you need to deal with system crashes on your own.
Re:In-Memory caching: Why we can't just trust the database t... by thwarted on Wednesday November 12, 2008 @02:20AM (#361558)
I wonder how creating a raid1 device composed of a real block device and a ram disk would serve in this regard. Has anyone tested this?
Re:In-Memory caching: Why we can't just trust the database t... by ars on Wednesday November 12, 2008 @03:37AM (#361609)
It would be pointless. Just let linux cache the data and don't worry about it.

If you must, cat all your database tables to null (i.e. read all the data in them) a few times, then you'll be sure they are in memory.

If you used a ramdisk you'll be paying double: once for the data, and again for the cache.

Re:In-Memory caching: Why we can't just trust the database t... by thwarted on Sunday November 16, 2008 @02:35PM (#366212)
Raid1 idea was a suggestion of how to deal with system crashes, not to populate the buffer cache.
Re:In-Memory caching: Why we can't just trust the database t... by ars on Wednesday November 12, 2008 @03:39AM (#361613)
It is in memory, only it's linux that does it and not mysql.

You can also create a memory table in mysql if you don't need the data stored permanently.

Re:In-Memory caching: Why we can't just trust the database t... by mdasen on Tuesday November 11, 2008 @11:57PM (#361420)
So, I'm just back from a party and I'm still a little tipsy, but here goes. . .

It's not always about being faster. Sometimes, it's about being more scalable. If I gave you a computer twice as fast as mine and told you to O(n^2) sort a million things, it would take you a lot longer than me using the slower machine, but using an O(n * log n) algorithm.

This gets us into the difference between MySQL and memcached. With MySQL I can say "give me the people where the first name is 'Brian' and the last name is not 'Applestein'" Awesome! Convenient, useful, slow :-(. With memcached, I can say, "give me key 'Per:48'". Um, not that useful or convenient, but fast as hell.

Why? What memcached is doing is creating a hash table. Basically, it knows where something is going to be stored because of its key. So, 'Per:48' will always be in place X because that's where it hashes to. When I want to retrieve that key, it doesn't have to look. It can just go! That means that it runs in constant Order(1) time. MySQL is going to use an index (if you're lucky). That index is going to sort the items in a specific order and then MySQL can retrieve an item in log n time. log n > 1 for almost all values (sorry, I don't deal with math below like, 10 since it's never relevant in my work since, well, scaling to 3 items doesn't matter).

So, even if MySQL kept everything in memory, it wouldn't be as scalable as memcached. memcached is able to do an Order(1) hash lookup. MySQL (with an index scan) is log n. This brings me to the query cache. While I don't pretend to know all of the internals of these technologies, I'm guessing that MySQL's query cache is basically like memcached. You have another key -> value store. Basically, MySQL has stored the result set as the value of the key which is the query.

The problem with the query cache is that it becomes invalid the second something is changed in the database, thus making it useless for scale.

So, why does memcached overcome that problem? Well, part of it is that when you scale, you leave behind those awesome queries that are so neat. You want to get all of the friends of "Matt"? Well, grab the serialized object "Matt" from memcached. That should have a list of the ids of his friends. Ask memcached for the objects matching that list of keys. As you can see, memcached isn't relying on a query of Matt's friends. Maybe you have never done that before. BUT, you have put each record into memcached and therefore it is available in this context. Where the entire query cache becomes invalid everytime someone hits the database, you can hack MySQL to update memcached with the one record that has been updated without invalidating the things that haven't changed.

If you have questions, you can always respond and I'll try to answer in the morning. Basically, querying is never infinitely scalable. It's log n. Hash lookups can scale. While more memory makes a database faster and scales it higher, it doesn't match a cache like memcached because queries are a more costly operation than hash lookups. Yeah, it's a pain. You and I probably won't have to deal with it much since databases are pretty damn efficient and log n is pretty good. If you become the next Twitter, hire someone who can explain this comment. Before then, don't worry so much! You'll give yourself wrinkles!

Re:In-Memory caching: Why we can't just trust the database t... by lhorn on Wednesday November 12, 2008 @12:55AM (#361489)
Yeah. Every time when someone tries to make a case for memchached they bring in Facebook and LiveJournal.

Well guess what: you aren't Facebook. You don't serve 21,000 requests per second. And you never will. Go and look at the size of your MySQL tables, then look at how much RAM your server has and what percentage of it is allocated to filesystem cache and how many real I/O reads/writes your hardware RAID does per second. Then come back and tell me again why do "we need to switch to memcached"?

Re:In-Memory caching: Why we can't just trust the database t... by wheels on Wednesday November 12, 2008 @05:35AM (#361677)
MySQL can use hashes as an index. It's an option on index creation. I suspect MySQLs indexing and caching system is multilayered and column type-specific.

I've written a couple of small DBs designed for domain-specific speedups, and I can say that one of the most difficult problems is cache integrity and data-state integrity, and I suspect why most databases are on the slow side is that they're designed first for data integrity and second for speed. memcached and similar aren't and I suspect that's one of the major reasons that it is much faster.

Re:In-Memory caching: Why we can't just trust the database t... by patrickg-zill on Wednesday November 12, 2008 @01:34AM (#361522)
I think what he is trying to say, is that knowing how your database stores and accesses the data, can help you make better decisions in writing SQL statements.

The example he gives is that a stored procedure for a large query used to take almost 1GB but now it takes only about 1MB because he is "cooperating" with the way the database slings data around.