Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

MySQL is bazillion times faster than MemSQL

dom.as

Post reply on HN
Print view View original

91–100 of 148 posts

Re: MySQL is bazillion times faster than MemSQL

#91

Post by tigerBL00D »

phillmv wrote: post #79
tigerBL00D wrote: post #71

Earlier quoted context omitted.

You are asserting that databases are always I/O bound and never CPU bound. Your assertion is wrong. A properly tuned database will become CPU bound (whether it's MemSQL or MySQL). At that point hyper-efficient execution results in higher throughput and lower latency.

> 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 in how MemSQL handles executing compiled queries. Executing compiled queries is what a (well tuned) database spends most of its time doing. It is the hot code path. And if you cut the instruction count in half you can expect latency to decrease proportionally and throughput to increase.

You are making all these skeptical claims that are technically wrong. I suggest you download MemSQL, compile a query and read the generated C++ code. Then download MySQL source and look at that too. At that point you will be coming to your own conclusions and I'll be happy to resume this debate.

Re: MySQL is bazillion times faster than MemSQL

#92

Post by tintor »

dhruvbird wrote: post #57
nikita wrote: post #33

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 t…

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.

Re: MySQL is bazillion times faster than MemSQL

#93

Post by tintor »

slewis wrote: post #21

From 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.

Re: MySQL is bazillion times faster than MemSQL

#94

Post by jakejake »

dhruvbird wrote: post #75
tigerBL00D wrote: post #71

Earlier quoted context omitted.

You are asserting that databases are always I/O bound and never CPU bound. Your assertion is wrong. A properly tuned database will become CPU bound (whether it's MemSQL or MySQL). At that point hyper-efficient execution results in higher throughput and lower latency.

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.

Re: MySQL is bazillion times faster than MemSQL

#95

Post by tigerBL00D »

jhugg wrote: post #87
roel_v wrote: post #81

Earlier quoted context omitted.

"And what's different is that MemSQL translates you SQL query into extremely efficient C++ code." What does this mean? Does the SQL get converted into actual C++ source code, then compiled with an internal C++ compiler? That seems like a weird thing to do. Or is it translated directly into an AST? If yes, I can't imagine other db's not doing it? I don't understand the claim being made here.

Yes, they compile SQL statements into linux binaries using a bundled GNU compiler toolchain. I am also curious if there is some way the developers have demonstrated the benefits of this approach vs. what other systems do. Most of the CPU time in a simple query in VoltDB is spent in networking, validating and suffering the cache-miss pain of walking index data structures. I can't see clearly how compiling to native co…

In-memory storage layer is very efficient. There are no buffer pool pages to manage or cache misses that result in I/O. That puts an accent on what goes on between storage engine and network, that is on query execution. Tight loops in native code is a good way to cut down on CPU cycles required to evaluate a query.

Re: MySQL is bazillion times faster than MemSQL

#96

Post by [deleted] »

bsg75 wrote: post #54
Domenic_S wrote: post #42

Earlier quoted context omitted.

> Or because of the shortcuts it has taken to avoid the "hard stuff", which results in developers taking shortcuts by using MySQL to avoid the "hard stuff". I am deeply interested in what you think this is

Avoiding RI (in part due to poor FK support depending on version / storage engine), designs that avoid simple schema changes because they can't be done online, point version migration difficulties. Consider unsupported SQL constructs from other engines (CTEs, window functions, better join and subquery support), and the effort and mess to accomplish what should be straightforward tasks generates SQL hate. Some NOSQL p…

[deleted]

Re: MySQL is bazillion times faster than MemSQL

#97

Post by jharsman »

forgotusername wrote: post #83
blake8086 wrote: post #74

Why is "written to hard disk" considered to be "durable"? Isn't that just a higher likelihood of "durability"?

This is actually a fair and valid point. On all but the most delicately configured systems, the standard configuration of basically all SQL databases does not occlude committed data loss during power failure due to the hard disk's write cache. See for example http://www.postgresql.org/docs/8.3/static/wal-reliability.ht... : When the operating system sends a write request to the disk hardware, there is little it can d…

Note that writing data to a single disk (or SAN array, or RAID controller) really isn't durable either, even if the the data does actually get to the disk and isn't in a write cache somewhere.

What if that disk crashes, or the SAN array brakes and kills all the data, or the data center burns down?

Re: MySQL is bazillion times faster than MemSQL

#98

Post by tintor »

caladri wrote: post #18
ericfrenkiel wrote: post #5

When 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…

How do you feel about the reported performance of "SELECT * FROM table ORDER BY id DESC LIMIT 5;"? Is this something that needs to be improved there, or are you happy with that being one of the built-in anchors? Additionally, I'm not sure that the implementation of the hard reliable case sounds very mature or even well thought-out. Having a syncer running at 20hz and waiting for it even in the synchronous path seems…

"How do you feel about the reported performance of "SELECT * FROM table ORDER BY id DESC LIMIT 5;"?"

Index was missing on ID column in MemSQL's case, so MemSQL was running entire table scan instead of using the index as MySQL.

Re: MySQL is bazillion times faster than MemSQL

#99

Post by jamesaguilar »

nikita wrote: post #33

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 t…

The 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.

Re: MySQL is bazillion times faster than MemSQL

#100

Post by jamesaguilar »

cs702 wrote: post #15

Whenever I read an article like this, I often end up feeling that on the 'truthiness' spectrum the scale goes like this: lies, damn lies, statistics, benchmarks ;-)

And yet, there are useful statistics and useless ones, useful benchmarks and useless ones. The benchmarks posted here seem good enough to demonstrate a few truths about MemSQL for people that might be earlier been taken in by hype.
Post reply on HN