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

121–130 of 148 posts

Re: MySQL is bazillion times faster than MemSQL

#121

Post by onetwothreefour »

tigerBL00D wrote: post #91
phillmv wrote: post #79

Earlier 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 don't know how to tell you this, but query plans parsing is never the bottleneck in any database. You're just plain wrong.

Compiling queries to C++ is great for some BS bullet point on a website with BS claims of performance gains, but it's only a niche feature at the end of the day. There are more important things that should be getting tackled first: multi-node distribution, better fsync handling, etc.

Re: MySQL is bazillion times faster than MemSQL

#123

Post by cbsmith »

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

Re: MySQL is bazillion times faster than MemSQL

#124

Post by chris_wot »

tigerBL00D wrote: post #61
timaelliott wrote: post #40

Earlier quoted context omitted.

You 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 awe…

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…

Actually, no. You speak like database engines have to parse a query every single time it's run. That's not true - any reasonable database engine has a plan cache. Just because something is compiled into object code, doesn't make it that impressive.

In fact, making a plan completely stable can cause problems if the data distribution changes dramatically. I asked about this problem in a previous article about MemSQL, and the answer was that the plan won't expire unless you drop an index or add a new one. [1]

1. https://news.ycombinator.com/item?id=4126330

Re: MySQL is bazillion times faster than MemSQL

#125

Post by chris_wot »

phillmv wrote: post #66
tigerBL00D wrote: post #61

Earlier 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 what's different is that MemSQL translates you SQL query into extremely efficient C++ code. Code that is compiled and executed natively. Whereas MySQL, SQL Server, Postgress, Oracle - all of these products evaluate queries by interpreting their respective tree representations of your SQL queries. This sounds like absurd cargo culting. I've never designed a database but parsing the SQL can not have ever been the…

Actually, it can have a significant impact but it's a corner case. It's a moot point though - as an example, SQL Server stores plans in a memory cache. [1]

1. http://www.sqlteam.com/article/what-query-plans-are-in-sql-s...

edit: incidentally, one of the corner cases I can think of is joining 10 tables, which gives you 10! combinations for the query processor to work through. This is irrelevant in MemSQL, because you can only join to another two tables, and you cannot do right outer for full outer joins.

Re: MySQL is bazillion times faster than MemSQL

#126

Post by zaphar »

[deleted] wrote: post #122
slurgfest wrote: post #63

Earlier quoted context omitted.

To clarify, memcached's page does not say "durable by default"

[deleted]

Read more carefully next time. The post you are responding didn't say anything a bout MemSQL. It was talking about memcached.

Re: MySQL is bazillion times faster than MemSQL

#127

Post by chris_wot »

vasilizo wrote: post #120
Nitramp wrote: post #105

Earlier quoted context omitted.

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

@Nitramp: Fortunately for all of us this world is full of unsolved problems many of which exist outside of consumer web. There are use cases where 50ms is way too long, and where jitter associated with disk I/O is not acceptable. Capital markets for example. You are looking for sub-millisecond latency to be in the intraday post-trade game, and single digit milliseconds for quote-to-trade. The number of instructions i…

Since you never wait on I/O and don't take locks, optimizing execution makes a lot of sense actually. All that's left is execution and network.

I'm not sure where you are getting this information. According to the MemSQL documentation, they have one and only one isolation level, READ COMMITTED. But READ COMMITTED takes write locks and blocks reads, but doesn't take any read locks. Blocking behaviour still (and should!) still occur.

It sounds like you are looking for dirty read behaviour, but from the literature MemSQL doesn't support READ UNCOMMITTED.

Update: Read a later FAQ on the MemSQL website. They are using MVCC, so write locks are irrelevant. My apologies to the MemSQL team.

Re: MySQL is bazillion times faster than MemSQL

#128

Post by vasilizo »

chris_wot wrote: post #127
vasilizo wrote: post #120

Earlier quoted context omitted.

@Nitramp: Fortunately for all of us this world is full of unsolved problems many of which exist outside of consumer web. There are use cases where 50ms is way too long, and where jitter associated with disk I/O is not acceptable. Capital markets for example. You are looking for sub-millisecond latency to be in the intraday post-trade game, and single digit milliseconds for quote-to-trade. The number of instructions i…

Since you never wait on I/O and don't take locks, optimizing execution makes a lot of sense actually. All that's left is execution and network. I'm not sure where you are getting this information. According to the MemSQL documentation, they have one and only one isolation level, READ COMMITTED. But READ COMMITTED takes write locks and blocks reads, but doesn't take any read locks. Blocking behaviour still (and should…

Chris, MemSQL utilizes versioning to implement READ COMMITTED. In this implementation readers are never blocked.

It is addressed in this FAQ: http://developers.memsql.com/docs/1b/faq.html#c1-q4.

Re: MySQL is bazillion times faster than MemSQL

#129

Post by tintor »

chris_wot wrote: post #127
vasilizo wrote: post #120

Earlier quoted context omitted.

@Nitramp: Fortunately for all of us this world is full of unsolved problems many of which exist outside of consumer web. There are use cases where 50ms is way too long, and where jitter associated with disk I/O is not acceptable. Capital markets for example. You are looking for sub-millisecond latency to be in the intraday post-trade game, and single digit milliseconds for quote-to-trade. The number of instructions i…

Since you never wait on I/O and don't take locks, optimizing execution makes a lot of sense actually. All that's left is execution and network. I'm not sure where you are getting this information. According to the MemSQL documentation, they have one and only one isolation level, READ COMMITTED. But READ COMMITTED takes write locks and blocks reads, but doesn't take any read locks. Blocking behaviour still (and should…

@Criss Isolation levels are used to specify semantics only. DBMS is free to implement them in different ways. Take a look at this VLDB 2012 paper about optimistic concurrent control in main memory databases, like MemSQL, which also describes implementation of isolation levels: http://arxiv.org/pdf/1201.0228v1.pdf

Re: MySQL is bazillion times faster than MemSQL

#130

Post by tintor »

bcoates wrote: post #119
tintor wrote: post #92

Earlier quoted context omitted.

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.

That shouldn't be any harder than updating one pointer atomically, modern x64s have two-pointer sized atomic exchange operations.

Modern x64s have 128-bit CAS instruction, but the problem is that those two pointers can be far apart in memory, in two different elements of the list.
Post reply on HN