4 ms·
Here's a question: if you have a barebones join table like create table feed_items (item_id integer, feed_id integer, created_at datetime); create index id
by lsb 17y ago
Here's a question: if you have a barebones join table like
create table feed_items (item_id integer, feed_id integer, created_at datetime);
create index idx1 on feed_items (feed_id, datetime, item_id);
how much slower is that covering index for your query of
select item_id from feed_items where feed_id=42 order by datetime desc limit 10
than the mysql/memcached/redis trifecta, and why not upgrade your database machine to a 68GB EC2 instance? Writing's definitely going to be slower, but you can batch transactions every 10 seconds or so, and 9x the RAM will be a big help.
- mjrusso 17y agoThe first bottleneck on the MySQL set-up is the reading, not the writing. Even with the indices stored entirely in RAM (as the author pointed out in his blog post), the MySQL box can't keep up. (Consider the workflow of a software system that needs to parse hundreds of thousands of feeds on a continual basis. Each feed you download will have dozens of entries, and most of the time you'll have already processed the entry, so it can be ignored -- but the only way to know is to do a look-up.) Regardless, A 68GB EC2 instance costs ~$1700/month. Four 2GB slices on Slicehost are priced at $520/month, with the cheaper set-up performing significantly better.
- boundlessdreamz 17y agoCan you explain/give pointers as to how the indices can be stored entirely in ram ?
- julien 17y agoI think the only way to is add RAM when your indices grow.. that's what we did up until now. There is absolutely now way a data store to keep this kind of performance as soon as you involve a disk, so, yes, the secret of whatever datastore is to _always_ keep everything in memory.