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

Show HN: I built a MySQL storage engine which is InnoDB compatible

upscaledb.com

Post reply on HN
Print view View original

21–30 of 32 posts

Re: Show HN: I built a MySQL storage engine which is InnoDB compatible

#21

Post by velox_io »

evanelias wrote: post #13

The upscaledb FAQ indicates "it is not yet concurrent; it uses a big lock to make sure that only one thread can access the upscaledb environment at a time". InnoDB is designed for concurrency (using MVCC, granular locking, etc) so I'd expect it to be slower at single-threaded workloads than another engine that skips all that. Only using single-threaded benchmarking is a bit misleading, imo. This is mentioned in the a…

There's literally a dozen ways to structure/ design a storage engine, every one of them has their pros and con's.

MVCC is so popular because it makes ACID compliance much easier to implement, but there is some additional read latency (and management overhead) because storage layout is disconnected from the natural layout.

A storage engine which sticks to the basics could be very fast, with predictable low-latency.

With regards to threading. I know locks are a dirty word, but you do need a single version of truth somewhere. Either this is done through locking or an allocator. Going single-threaded is a valid way to remove the overhead of locking (plus no race conditions!).

Hopefully a single writer, multiple readers is available soon.

Re: Show HN: I built a MySQL storage engine which is InnoDB compatible

#22

Post by cruppstahl »

morgo wrote: post #16

MySQL Product Manager here. Any feedback on the Storage Engine API?

It's ok! The example storage engine could implement more functionality, just to demonstrate i.e. how rows are serialized/deserialized etc. The test suite was very helpful.

Re: Show HN: I built a MySQL storage engine which is InnoDB compatible

#23

Post by endymi0n »

nine_k wrote: post #20
endymi0n wrote: post #17

No transactions? Single-threaded? Global lock? Don't want to put anyone's work down, but at first sight this looks much more like MyISAM than InnoDB - and just like this, MyISAM's a helluva lot faster than InnoDB for single writer workloads throwing ACID out of the window. Just that MyISAM is battle tested over the course of several years. Again, interested to see what comes out of it, but if history is a lesson, it'…

Version 0.0.1 looks much like an experiment in writing a working storage engine. It lacks a lot of critically important things. It's hard to say anything at this point about any real-world scenarios where this storage engine would be useful. What concerned me a bit was absence of an interesting and promising general idea behind the implementation, the reason to implement a new engine. Maybe it is there, but the blog…

Exactly this, I was missing the big picture. Having worked with at least a dozen databases, my claim today is that of Tony Million: Don't be a hipster and just use Postgres. Don't get me wrong, I also work with Aerospike, Redshift, Redis and a few other specialized data stores, but I always know why Postgres wouldn't do there. For any specialized data store, at least provide a rationale where the niche is where that thing is better than anything else that's on the market. Simply claiming it's "fast" without further specifications worked out for MongoDB, but I hope it's for the very last time. Optimization means tradeoffs.

Re: Show HN: I built a MySQL storage engine which is InnoDB compatible

#24

Post by ignoramous »

endymi0n wrote: post #17

No transactions? Single-threaded? Global lock? Don't want to put anyone's work down, but at first sight this looks much more like MyISAM than InnoDB - and just like this, MyISAM's a helluva lot faster than InnoDB for single writer workloads throwing ACID out of the window. Just that MyISAM is battle tested over the course of several years. Again, interested to see what comes out of it, but if history is a lesson, it'…

Thanks for pitching in. I'd like to know more about Postgres (correct->fast) and MySQL (fast->correct) comparison here. Can you pls share technical links that go in details about these? Thanks again.

Re: Show HN: I built a MySQL storage engine which is InnoDB compatible

#25

Post by cruppstahl »

evanelias wrote: post #13

The upscaledb FAQ indicates "it is not yet concurrent; it uses a big lock to make sure that only one thread can access the upscaledb environment at a time". InnoDB is designed for concurrency (using MVCC, granular locking, etc) so I'd expect it to be slower at single-threaded workloads than another engine that skips all that. Only using single-threaded benchmarking is a bit misleading, imo. This is mentioned in the a…

I ran sysbench benchmarks with 30 concurrent connections. The performance gap between InnoDB and upscaledb shrank a bit, but not much.

The reason is that most of the performance is spent in MySQL and not in the key/value store, and then it does not make a big difference if the key/value store is concurrent or not.

In my experience the assumption of "concurrent = fast" is a misconception. Right now upscaledb moves certain operations (i.e. flushing dirty buffers) to the background. It is better to have fast single-threaded code instead of multi-threaded code with a huge locking overhead. A compromise would be to move the lock to the database level (instead of the Environment, which is basically the container for multiple databases), and make sure that there's no shared state between the databases. But that actually does not have that much priority for me because I do not expect to win that much performance.

Re: Show HN: I built a MySQL storage engine which is InnoDB compatible

#26

Post by dspillett »

philjohn wrote: post #10
mey wrote: post #9

Earlier quoted context omitted.

The most recent break I encountered was ibtmp1 exploding to 100's of GB and killing the box with InnoDB, so I'm not sure "how it breaks is well understood".

Funnily enough I encountered something similar years ago on Microsoft SQL Server 2005. Never got to the bottom of it either.

A lot of cases I've seen where tempdb suddenly grows massively is an runaway Cartesian product in a query. Sometimes this is intentional but was never expected to be used on large data, but usually it is accidental and worse an inexperienced DB person has "fixed" the problem with multiplied output rows by adding "distinct" instead of fixing the query logic properly.

Re: Show HN: I built a MySQL storage engine which is InnoDB compatible

#28

Post by dspillett »

ignoramous wrote: post #24
endymi0n wrote: post #17

No transactions? Single-threaded? Global lock? Don't want to put anyone's work down, but at first sight this looks much more like MyISAM than InnoDB - and just like this, MyISAM's a helluva lot faster than InnoDB for single writer workloads throwing ACID out of the window. Just that MyISAM is battle tested over the course of several years. Again, interested to see what comes out of it, but if history is a lesson, it'…

Thanks for pitching in. I'd like to know more about Postgres (correct->fast) and MySQL (fast->correct) comparison here. Can you pls share technical links that go in details about these? Thanks again.

I'm not sure how much of it is still relevant today but in the "bad old days" there were a lot of issues where mysql did things blatantly wrong in the name of speed and the devs & supporters usually suggested pushing fixes into business logic. Examples I remember were transaction support, foreign key issues and bad/no type validation (silently accepting impossible dates like 2016-02-30 for instance).

postgres was at that point already a mature, stable, and pretty much correct RDBMS, but it wasn't as fast for certain workloads (massively read heavy ones with low levels of concurrency, IIRC) or as easy to administer badly (I wouldn't say it was harder to administer well, but mysql was more forgiving to of mistakes by people who just wanted to plug it in and not care).

Re: Show HN: I built a MySQL storage engine which is InnoDB compatible

#29

Post by daenney »

I think this could be pretty neat, it'll be interesting to see how it evolves. However, looking at the comments people have left here I would suggest putting up a table/comparison with InnoDB side by side, detailing which features are implemented, which are not and which are but aren't available through the SQL layer yet.

This would also put the "compatible with InnoDB" thing in perspective for people and potentially clear up some other confusion.

Re: Show HN: I built a MySQL storage engine which is InnoDB compatible

#30

Post by cruppstahl »

j_jochem wrote: post #11
cruppstahl wrote: post #7

Earlier quoted context omitted.

If that's your definition of atomicity - yes, upscaledb offers atomic operations. If you define atomicity as the "A" in "ACID" then no - BEGIN/COMMIT wrappers are not yet available. Work in progress!

So what you're saying is that in general, transactions are implemented, but the begin/commit syntax for transactions is not yet supported?

Correct.
Post reply on HN