I demand a free lunch.
MySQL is bazillion times faster than MemSQL
101–110 of 148 posts
Re: MySQL is bazillion times faster than MemSQL
#102From MemSQL's home page: "MemSQL writes back to disk/SSD as soon as the transaction is acknowledged in memory." From the article: "See, MemSQL also has “transaction-buffer” setting, which, in default “full durability mode” will asynchronously return “ok” until 128M buffer is full (or background log flusher thread writes it out)." So which is it?
In MemSQL transaction buffer is flushed continuously as long as it is not empty. Default value of 128M is there just to absorb bursts in workload.
So you could lose up to 128MB of updates if the machine crashes at the wrong time. NOT DURABLE.
Re: MySQL is bazillion times faster than MemSQL
#103Earlier quoted context omitted.
I agree with what you have to say, but I don't see how you can determine that this query: SELECT * FROM table WHERE id > 5 LIMIT 10; is actually of the same form as this query: SELECT * FROM table WHERE id > 10 LIMIT 5; without actually parsing the two. I mean you will incur a parsing overhead either way. What I think MemSQL is trying to optimize out is the actual execution of the query. I mean rather than interpreti…
That's why prepared statements are preferable because those two statements have to be parsed as unique queries. They are actually the same query with different parameters. As far as I know that's the same on just about all databases.
Not every parsing is equal. MySQL and other databases produce a tree as they parse a query. If you just want to match parameters you can avoid memory allocations and just replace parameters with ^ or @ signed in place in the query string.
http://developers.memsql.com/docs/1b/faq.html gives some more details.
Re: MySQL is bazillion times faster than MemSQL
#104When someone writes on their intro "... and now in return I want to waste your time a bit." it's a sign that someone isn't being serious. Domas' main criticism is that our video, http://vimeo.com/44087431 , uses a MySQL with standard defaults. Since tuning a database is by definition a custom process, we wanted to demonstrate what performance you'd get "out of the box." The video speaks for itself. MemSQL can push 80…
You're claiming 'full durability' and comparing it to InnoDB's durability which is complete bullshit as the article explains.
Re: MySQL is bazillion times faster than MemSQL
#105Earlier quoted context omitted.
> At that point hyper-efficient execution results in higher throughput and lower latency. I don't get excited for 1% decreases in latency. I'm willing to bet money that the performance penalty behind parsing the sql queries is asymptotically a constant - or at least, a tiny fraction of the time spent computing the data set. I feel especially confident in this because memsql never brags about the specific increase in…
Phill, parsing has little to do with what goes on at query execution time. Parsing is typically done once for a given parameterized query (and MemSQL automatically parameterizes queries with constants). After parsing comes compilation which produces a query plan. The query plan is cached and that is the thing that is actually used to evaluate the query when it's re-submitted again. We are discussing the differences i…
I've actually written a compiler from database queries to native code (JIT'ed bytecode actually, but doesn't make a difference here) once.
Some queries in databases are indeed CPU bound, but the general statement that well-optimized databases turn to be CPU bound is not correct like that. It all depends on the use case.
If you're thinking full table scans with complicated processing on a database that's entirely in memory, then yes, you'll be CPU bound. But many (most?) databases are not entirely in memory, and there's no reason they need to be. With reasonable memory and indexing, most queries boil down to only a few IOs/disk seeks, so you're looking at That's the case for the majority of "get by ID" style queries, which in my experience really are the lions share of queries, and in particular are also what needs to be fast. A complicated reporting query can take a couple of seconds, but your "main page/item view" must be fast.
If you have random reads on a large database that cannot be kept in memory, this will be the majority of your workload. Those queries will spend all their time waiting for the disk, and more or less none interpreting your query tree, so optimizing the query tree processing part makes no sense at all.
TL;DR, in my experience optimizing query tree interpretation does not yield significant benefits for the majority of queries, and IMHO won't be a significant differentiator for MemSQL outside of marketing.
Re: MySQL is bazillion times faster than MemSQL
#106Earlier quoted context omitted.
Tim, if you want to talk substance, you have to peer deeper under the covers. And if you do that, then you just can't overlook the fact that the query execution model used by MemSQL is significantly different than that used by old school relational databases, including MySQL. And what's different is that MemSQL translates you SQL query into extremely efficient C++ code. Code that is compiled and executed natively. Wh…
And so the wheel turns. The original DB2 for VSE and VM (not DB2 for z-series/390) which was the productized version of System R did exactly this - compiled to native code. Subsequent DB2 implementations chose not to go down this path - even for single OS systems like DB2/390 and DB2/400. In any event, I'm skeptical if this is going to make very much of a difference for the following reasons: 1. The time spent by the…
Re 2.: you can get significant improvements when compiling to native code. You still have to dereference those function pointers, which is essentially the same as a big switch statement (if not worse, your function pointers might be anywhere in the heap, not in your cache line).
When you compile to native code, you can essentially save the dynamic dispatch on query parts, plus all the other benefits you get from compilation.
E.g. if you do good type analysis, you can also compile to much more efficient data representations, which will again have benefits for memory access. The compiler can also inline code, do loop lifting, and all those goodies.
But overall I strongly agree with 1), any time spent doing execution is usually dwarfed by IO.
Re: MySQL is bazillion times faster than MemSQL
#107Earlier quoted context omitted.
I agree with what you have to say, but I don't see how you can determine that this query: SELECT * FROM table WHERE id > 5 LIMIT 10; is actually of the same form as this query: SELECT * FROM table WHERE id > 10 LIMIT 5; without actually parsing the two. I mean you will incur a parsing overhead either way. What I think MemSQL is trying to optimize out is the actual execution of the query. I mean rather than interpreti…
That's why prepared statements are preferable because those two statements have to be parsed as unique queries. They are actually the same query with different parameters. As far as I know that's the same on just about all databases.
@nikita I see: http://developers.memsql.com/docs/1b/faq.html#does-this-mean...
Do you mean you determine placeholders without actually parsing the SQL?
@jakejake The docs. seem to suggest that the benefits of prepared statements are won without using prepared statements in MemSQL.
Re: MySQL is bazillion times faster than MemSQL
#108Earlier quoted context omitted.
Even 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 str…
Skip 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.
You 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.
Re: MySQL is bazillion times faster than MemSQL
#109Earlier quoted context omitted.
This is a little misleading; an internal checkpoint happens upon transaction commit[1]. The comment makes it sound as if one or more transactions can commit before a checkpoint writes them to disk. [1]: http://msdn.microsoft.com/en-us/library/ms186259(v=sql.105)....
The transaction is actually written to disk twice. Once when it is written to the transaction log, and then again when a checkpoint happens and it's written to the underlying data blocks. Transaction log writes are sequential and fast, writes to the underlying data blocks are random and slow. The whole point of the checkpoint is to convert this sequential i/o to random i/o in an efficient manner. Still, that's not wh…
... you'd think.
However in practice most operating systems and/or file systems and/or disks cheat. fsync() is usually buffered in the hard disk itself. You can disable that and force the hard disk to truly write out on fsync, but that is so prohibitively slow that people rarely do that.
If you want absolute durability, you'll have to have hard disks running on some battery buffered power supply, which is a common configuration.
On the other hand, in a proper database system, at least the data files won't be corrupted by a missing fsync, so it'll come up. Figuring out whether that one commit did or did not go out in the very rare event of a fatal power failure and a just pending fsync() and the commit making it out of the network stack in time is probably a Heisenberg-esque inquiry into obscure realms of uncertainty.
Re: MySQL is bazillion times faster than MemSQL
#110Earlier quoted context omitted.
Even 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 str…
Skip 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.
Actually, there are papers about it 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.