Hacker News Reader

Comment by zwischenzug | Hacker News Reader

I'm obviously missing something. Is SQL really the bottleneck for SQL performance? I'm not sure how much compiling down to C++ will help.

Is there more to this than the article suggests?

View on HN

Most times that the press mention SQL, they actually mean "Relational Database Management System". But in this case, MemSQL seems to be doing two things: reducing locking semantics (the only transaction isolation level is read committed), and doing something or other to speed up query compilation.

Frankly, I can't see how their query compliation is much better than anything Oracle, SQL Server or PG does already. (see here: http://developers.memsql.com/docs/1b/codegen.html) Perhaps there is no plan cache in MySQL, or the plan cache is inadequate?

Incidentally, you don't want to use it if you need to do multi-statement transactions. Can't be done. Check out the documentation here:

http://developers.memsql.com/docs/1b/isolationlevel.html

It has a few extra tricks up its sleeve to enhance indexes in MySQL, but again nothing really particularly spectacular. http://developers.memsql.com/docs/1b/indexes.html

More interesting for me, is that it can be hosted on EC2, which is pretty neat!

Query compilation works incredibly well for high throughput. If you have a lot of small transaction (OLTP) you can benefit tremendously by dramatically reducing the amount of instruction you burn per query. Lots of database vendors know that, but they don't do it b/c they often have "one size fit all" dbms. BTW you will see some changed from the big db vendors as well.

There is no plan cache in MySQL. SQL Server has it, but it's nearly not as efficient. I know, since I used to work there.

Multistatement transaction are coming. We are beefing up SQL surface area, as well as working on many more exciting things.

Not sure what you are comparing against. However, from what I've seen of SQL Server execution plans, actually it's extremely robust and I would have to argue that it can often be far more efficient than even Oracle. I've used both.*

Incidentally, sorry if I seem to be disparaging the product. I'm actually looking at it myself, but want to know what the limitations are. Multi-statement queries would be a big win. Are you considering implementing higher isolation levels? Also, as was asked above, how are the startup times for multi-gigabyte/terrabyte databases?

* Hint for the unwary - if you run a SELECT statement with a WHERE 1=0 AND <rest of predicates> (for brain dead "dynamic" SQL generating apps), the initial compilation can take a dramatically long time in Oracle 10g (and I believe 11g, but cannot confirm). Oracle have not got any mechanism to shortcut the query compilation for such queries. A bit obscure, but I never saw this problem with SQL Server. Oracle support were stumped by the issue too and gave me short-shrift ("it might be fixed in an 11g patch... some time").

No worries :). MemSQL does very well for OLTP like workloads, but it's not an "all size fits all" database.

Recovery goes at the speed of disk.

The compile time for memsql is mostly code generation time. Once the code is generated/compiled MemSQL is doing a very good job matching the queries against the existing plan by removing parameters from the query text.

:-) I really like what you are attempting to do here. What would you say your core market is?

Some of the stuff is on the website (Capital Markets, Ad Serving, Mobile). It will be expanding as we add clustering and more analytical features.

Most DBMS products have "Stored Procedures" which, essentially, are compiled chunks of SQL that you can call by name, e.g.

CREATE PROCEDURE foobar AS SELECT * FROM users

On very large queries (many table joins) there can be a noticeable performance hit while the DBMS figures out the optimal query plans. Stored procedure compile and store this plan after first execution (some pre-compile) so the DBMS can just look it up in the cache.

It also removes the need to do runtime parsing (compiled vs. interpreted languages). There are more nuances to Stored Procedures than I can put here, but here's a link to a more in-depth analysis I wrote: http://stackoverflow.com/questions/59880/are-stored-procedur...

In general, MySQL has limited query plan cache (last I read, it was one cache per database connection and was wiped out when the client disconnected), so this is definitely a win. However, since every other DBMS out there already has robust query cache, I don't think the Postgres/Oracle/MS SQL etc. folks are losing any sleep.

Supporting stored procedures doesn't mean compiling to native code. E.g. sql server just stores a compiled plan so saves of parsing/optimization. Execution is still an interpretation.

Giants are investing into big memory technology no doubt about it, which is great. That's the beauty of the huge big data market - one size doesn't fit all.

SQL Server supports .NET stored procs, which are compiled code bits. Oracle / Sybase support Java.

I believe some DBMS vendors are working on the interpreted nature of the execution, but basically the query plan determination makes up the bulk of the parsing/execution time. Compiling SQL to something... "native" doesn't provide a whole lot of win in terms of execution latency.

I think its that its an in memory database, optimised for memory as opposed to disk. The SQL to C++ seems a bit of a confusion to me, I guess it means it has a JIT SQL compiler to optimise the query to native opcodes. Meh. But the in memory and mysql wire compatibility are big wins.

I don't believe this is the case - it's not an in-memory database.

Even if it is (and I can't see anything on their developer website) then if it's ACID compliant, like they say, then it will have to be writing to the disk at some point - at the very least it will need a transaction log. Otherwise, it's not really usable for anything you need to persist to long term storage!

Yes, its not clear on their site, but the gigaom article states: "As its name implies, MemSQL achieves its fast performance in part by keeping data in memory".

Yes, it does write to disk (append only logging I think) but my point was, that if you are keeping ALL your data in memory, you can optimise the storage for fast queries, as opposed to a hybrid / paged memory and disk system.

Oops, sorry - I'm wrong. Actually, according to their documentation:

"By default, MemSQL runs with full durability enabled: transactions are committed to disk as a log and later compressed into full-database snapshots. The snapshot and log files are stored in the datadir directory configured in memsql.cnf. When MemSQL is restarted, it will recover itself into the state it was in before the shutdown, by reading the snapshot and log files in datadir" [1]

I missed that piece of documentation on their website. I think they could do better to explain better the advantages and how their technology works. Their product overview doesn't really tell you much of anything, except that they have an enhanced query parser. [2]

1. http://developers.memsql.com/docs/1b/durability.html

2. http://developers.memsql.com/

Over what? The big iron DB I use is to all intents and purposes in memory in any case. The only Input requirement is to pull the data in at some point (which can be forced) and the Output requirement is to write to a logical log (which we buffer in any case). It flies.

yes, memsql is an in-memory database. the big innovation here is that we're literally hardwiring the SQL into the server and matching queries to those compiled query templates.

Since we work in memory, you see a huge perf boost with code generation because there are no lags with disk.

I am not sure this solves anything. From their implementation details I could just mount mysql on a memory filesystem and have 100% of MySQL's feature. If I need near 100% assurance that the data will be saved, there is always block replication, transaction logs and regular replication. their transition module adds nearly nothing but overhead, since stored procedures are better, and if you have the need for ultra performance, there is always the ability to access mysql at the command level and bypass sql entirely.

You could do that, but you won't be able to achieve as high throughput in highly concurrent environments. MemSQL achieves this via code generation and lockfree data structures.

We will also put the technology on steroids, by building a clustered system

lockfree data structures FTW!

lets see some stats. Its a bit unfair to compare an in memory database with a severely truncated feature set to a more robust sick based system.

Correct! Different scenarios require different solution. One size doesn't fit all. And it's nice when solutions play nicely with each other.

> We will also put the technology on steroids, by building a clustered system

Isn't that the same as MySQL Cluster, minus the SQL-to-C++ part (and maybe lock-free structures)? Why not fork MySQL Cluster to add the SQL-to-C++ code generation? Then it seems you could claim (from MemSQL site) "MemSQL is ... still faster than other in-memory databases because of SQL-to-C++ code generation" and also support every feature of MySQL Cluster. Why reinvent the wheel only to create something with great limitations compared to the existing wheel?

"Why not fork MySQL Cluster to add the SQL-to-C++ code generation (and lock-free storage engine)?"

:) It is a little more complicated than that.

There are many bottlenecks and it really depends on the workload. Sometimes it's IO, sometimes it's CPU. When you have a throughput problem, yes, it's the SQL.

Reply on news.ycombinator.com