MySQL is bazillion times faster than MemSQL • Hacker News Board

Re: MySQL is bazillion times faster than MemSQL

#111

Post by Nitramp » Tue, Jun 26, 2012, 9:25 PM UTC

arihant wrote: post #31

When debating a fresh new database project - benchmarks aren't really the way to go, because those can be improved. What should be debated is what underlying fundamental has the new project gotten right. Almost every successful database project got something fundamentally right - MySQL, Redis (in-memory), MongoDB(page based storage), Riak(Distributed). On that note, here's my take on MemSQL - People who love MySQL, a…

> MongoDB(page based storage)

Citation needed. Which database system on earth doesn't do page based storage? In fact, you cannot get any durability guarantees if you don't write page-at-a-time.

MongoDB is relying on the operating system's cache manager by using memory mapped files. Whether that is a good idea depends a lot on your operating system, and whether it's page replacement algorithm fits your database use case. Most database implement their own page cache because they want tighter control over those algorithms.

I like MongoDB's data model, I don't understand their choice of query language, and I think their database engineering is odd to say the least. What's the point in having a non-durable system if you don't even get scalability across nodes? Where's the hot backup? And so on. It seems like something people use who just couldn't figure out indices in relational databases, just to run around and tout that NoSQL is so much faster :-(

Re: MySQL is bazillion times faster than MemSQL

#112

Post by spudlyo » Tue, Jun 26, 2012, 10:08 PM UTC

Nitramp wrote: post #109

spudlyo wrote: post #59

Earlier quoted context omitted.

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

First of all, ext(2|3|4) and XFS filesystems honor fsync. That's what all the noise about Firefox and SQLite was all about. Secondly the HP RAID controllers I'm familiar with disable the drive write cache by default, and throw dire warnings if you try to turn it on, and make you ACK your choice like:

Without the proper safety precautions, use of write cache on physical drives could cause data loss in the event of a power failure....

The bottom line is that Database pros insist on and get actual durability, and with a battery backed write cache, it's not painful.

Re: MySQL is bazillion times faster than MemSQL

#113

Post by aioprisan » Tue, Jun 26, 2012, 11:03 PM UTC

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?

memSQL is NOT durable by design. you can lose the pool of unwritten queries

Re: MySQL is bazillion times faster than MemSQL

#114

Post by aioprisan » Tue, Jun 26, 2012, 11:04 PM UTC

bond wrote: post #69

Can we say they're deceiving us with this "MemSQL is 30 times faster than MySQL"?

that and the claim that it's a durable db

Re: MySQL is bazillion times faster than MemSQL

#115

Post by aioprisan » Tue, Jun 26, 2012, 11:07 PM UTC

ericfrenkiel wrote: post #13

mikeash wrote: post #10

Earlier quoted context omitted.

I thought his main criticism was that MemSQL isn't durable by default, and that when you enable that it becomes incredibly slow. That's a pretty big deal, and IMO running database benchmarks on an unsafe configuration like this is extremely dishonest.

That's a bit heavy-handed; additional benchmarks will be published against a variety of configurations and other databases. Companies use MemSQL because it uses memory as the primary locus of data. If you have a fast data problem, you couldn't use a disk-based system. At high speeds, you'd want to deploy any database in an active-active mode.

then your marketing is bullshit, claiming durability when you know that's not what you're doing with memsql. talk about wasting people's time with claims of 30x speedup, laughable

Re: MySQL is bazillion times faster than MemSQL

#116

Post by leif » Tue, Jun 26, 2012, 11:16 PM UTC

willvarfar wrote: post #77

ankrgyl wrote: post #70

Earlier quoted context omitted.

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

Haven't thought for a second about how to recover being stomped on, but xor'ing the directions together might be promising...

That doesn't solve concurrency.

Re: MySQL is bazillion times faster than MemSQL

#117

Post by bazzargh » Tue, Jun 26, 2012, 11:53 PM UTC

jakejake wrote: post #94

dhruvbird wrote: post #75

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

At least some databases I've used have parsed out constants in the client library code and submitted prepared statements to the server, passing those constants in. That meant that the server got better use out of its query cache; on the server side, those are the same query.

Not sure which ones if any do this today, the optimisation might no longer be relevant - I spotted DBs doing this 10 years ago when CGIs that didn't bother with prepared statements were rife.

Re: MySQL is bazillion times faster than MemSQL

#118

Post by gbog » Wed, Jun 27, 2012, 12:13 AM UTC

Nitramp wrote: post #105

tigerBL00D wrote: post #91

Earlier quoted context omitted.

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

Get by id queries are majority when the database is used as a key-value store, and the only reason to such an underuse of RDBMSes is precisely because under heavy load some of them have hard time doing proper queries, with joins, subqueries and all the stuff databases are designed for.

Re: MySQL is bazillion times faster than MemSQL

#119

Post by bcoates » Wed, Jun 27, 2012, 12:30 AM UTC

tintor wrote: post #92

dhruvbird wrote: post #57

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

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

Re: MySQL is bazillion times faster than MemSQL

#120

Post by vasilizo » Wed, Jun 27, 2012, 1:24 AM UTC

Nitramp wrote: post #105

tigerBL00D wrote: post #91

Earlier quoted context omitted.

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 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 in the hot code path (and cycles per instruction) actually starts to matter. Lock-free sturcutres utilized for storage are also important. This combination gives MemSQL some rather unique low-latency properties that certain folks can exploit to their delight.

To your other point, random reads and key-value lookups is where MemSQL shines. 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.