7 ms·
MemSQL CTO here. Great article- Domas has done a good job of digging into the internals of MemSQL! A few questions/comments: 1.) The range query issue you poin
by nikita 14y ago
MemSQL CTO here. Great article- Domas has done a good job of digging into the internals of MemSQL! A few questions/comments:
1.) The range query issue you pointed out can be explained by a well known limitation of skip lists. Unlike B-Trees, skip lists are unidirectional. By default, our indexes are ascending, so indeed you have to skip to the end to run a MAX() or "ORDER BY id DESC" query. To fix this, just change the primary key to be descending in your schema:
CREATE TABLE x (id int, ..., PRIMARY KEY id (id) DESC, ...)
This is explained here http://developers.memsql.com/docs/1b/indexes.html#skip-list-indexes http://developers.memsql.com/docs/1b/indexes.html#skip-list-.... If you want both behaviors in your app, you'll have to create two skip list indexes (for now).
2.) The transaction-buffer > 0 setting is really where we shine. Synchronous durability is something we have for flexibility, but I'll be the first to admit that it's not really optimized to perform well. The customers that we're working with are okay with this. And it's inspired by what modern companies do. Maybe it's changed in the time since I've left (Domas?) but Facebook certainly does not write comments synchronously to disk.
3.) Our synchronous durability does indeed flush in 50 MS cycles, so you'll see poor performance on single threaded inserts. However, as you pointed out, we're optimized for the case with multiple concurrent writers. Since MemSQL implements group commit on high parallel load throuput picks up. Sure, writing your own very specific benchmark you can show us writing poorly, but we've never worked with a customer that's needed single threaded, synchronous writes to shine. If this is your use case, unless you need substantial read performance, MemSQL is not the database for you.
- deleted 14y ago[deleted]
- timaelliott 14y agoYou seem to be missing the main reason why people have an issue with MemSQL. The issue are quotes like this on your website and video: "MEMSQL IS 30 TIMES FASTER THAN MYSQL." It's an extremely invalid and biased comparison. You're quite literally comparing the speed of writing to RAM versus the speed of writing to disk. If you didn't make such ridiculous assertions, people would accept your product for the actual awesome things it does and not focus on refuting your bullshit claims.
- calinet6 14y agoThis is exactly right, and should be addressed. memcached is probably 30 times faster than MySQL too, but they don't go around claiming it's a durable database.
- mariusmg 14y agoIt fucking says "Durable by Default" on their main page !!!! edit: memSQL main page says "Durable by Default"
- nikita 14y agoHi Tim, we're not comparing "the speed of writing to RAM versus the speed of writing to disk." You can run InnoDB with a buffer pool large enough to keep the entire database in memory and we'll still outperform it significantly. We're actually working on a blog post right now with that comparison.
- calinet6 14y agoI think it might help more to explain how your design works and why we should trust it, than to provide simple benchmarks proving the speed. Speed is only one part of the equation, and frankly it's the one I care about only after durability is taken care of. Explain thoroughly how you achieve both and it will be more relevant to me. Looking forward to the post.
- HarrisonFisk 14y agoRegarding #2, we do normally run MySQL with full transactional durability at Facebook. We have for a very long time (several years at least). For example, here is a FB note regarding our enhancing performance under full durability in MySQL from Oct 2010: https://www.facebook.com/note.php?note_id=438641125932 https://www.facebook.com/note.php?note_id=438641125932
- nikita 14y agoHarrison, what are these settings for InnoDB? innodb_flush_log_at_trx_commit, innodb_flush_methodm, innodb_doublewrite,
- midom 14y ago1, O_DIRECT, 1
- HarrisonFisk 14y agoThe primary settings are: sync_binlog=1 innodb_flush_log_at_trx_commit=1 (which is default, but we add it to our configs since we run with =2 on the slaves since they don't need full durability) We do use some performance related settings related to this as well: innodb_flush_method = O_DIRECT innodb_prepare_commit_mutex=0 (enables group commit in the Facebook patch, other branches have a different setting) For settings like innodb_doublewrite we leave the default of enabled.
- jeffdavis 14y agoI don't think the point of the article was seriously about the details of performance in MemSQL versus MySQL. I took it as a criticism of the irresponsible reporting of benchmarks.
- dhruvbird 14y agoEven though skip-lists as described in literature are singly linked (horizontally), they can be augmented with a back pointer to make them doubly linked. This would allow one to re-use an index as either a forward or backward index, and the order of the index is no longer important. I am not sure how easy this is to do in a lock free fashion. However, if one were to adopt a strategy which combines functional data structures and RCU, I am guessing that it would be possible to implement it with relatively good readability. Additionally, skip-lists can be used for performing range queries as well. This means that "SELECT * FROM table ORDER BY id DESC LIMIT 5;" can be executed very efficiently as long as there is a skip-list on "id". Additionally, even COUNT(), MAX(), MIN(), etc... queries can be optimized in a similar fashion. Again, however, doing it in a lock-free manner might not be the most fun thing to do.
- tintor 14y agoSkip list is essentially a hierarchy of linked lists. It is very hard to make a doubly linked list work concurrently without using locks. One would have to update two pointers atomically in order to insert a new element in a doubly linked list.
- Nitramp 14y agoYou could probably pull that off if you implement it like full text search indices in e.g. Lucene. Have every modification create a "diff" skip list that takes precedence over older skip lists, and then have a merge operation that runs asynchronously (and will either have to lock, or copy the entire data structure and switch it in atomically). That's also essentially how Bigtable's disk format works.
- dhruvbird 14y agoActually, there are papers about it http://citeseer.ist.psu.edu/viewdoc/summary?doi=10.1.1.140.4693 http://citeseer.ist.psu.edu/viewdoc/summary?doi=10.1.1.140.4... but I don't know much about the implementation complexity since I've never implemented one.
- bcoates 14y ago
- antirez 14y agoIt's not specifically related to the original article discussed here, but skiplists don't need to be unidirectional. For instance Redis implements all the sorted sets operations using doubly-linked skiplists, so once you identify a node you can traverse other nodes in both directions.
- ankrgyl 14y agoGood point- ours are currently unidirectional because they have to be lock free. We have some ideas on how to keep them lock free and make them bidirectional, but that's not part of the current release.
- willvarfar 14y agoHaven't thought for a second about how to recover being stomped on, but xor'ing the directions together might be promising...
- leif 14y agoThat doesn't solve concurrency.
- zobzu 14y agoYet you still claim your memSQL is durable by default. Except its not. That's the point. If you make memSQL durable in the configuration (since it's not actually durable by default as you guys claim), then the performance is apparently terrible. Very horrible kind of terrible. See the issue?
- jamesaguilar 14y agoThe problem is the claim of being durable by default. It is, to be extremely charitable, a stretch of the truth. Asynchronous durability is essentially an oxymoron.
- cbsmith 14y agoThe tl;dr here is: MemSQL is optimized for a different context. That's all well and good, but as a consequence, it is only fair to compare yourself to other systems that are configured to take advantage of the same context.
- ellie42 14y agoMemSQL isn't open source. If I can't hack it, who cares.