5 ms·

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

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?

al_james 14y ago
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.
- chris_wot 14y ago
  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!
  - al_james 14y ago
    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.
    - chris_wot 14y ago
      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 http://developers.memsql.com/docs/1b/durability.html 2. http://developers.memsql.com/ http://developers.memsql.com/
  - deleted 14y ago
    [deleted]
- zwischenzug 14y ago
  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.
- ericfrenkiel 14y ago
  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.
chris_wot 14y ago
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 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 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 http://developers.memsql.com/docs/1b/indexes.html More interesting for me, is that it can be hosted on EC2, which is pretty neat!
- nikita 14y ago
  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.
  - chris_wot 14y ago
    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").
MattRogish 14y ago
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-procedures-more-efficient-in-general-than-inline-statements-on-mode/59932#59932 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.
- nikita 14y ago
  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.
  - MattRogish 14y ago
    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.
nikita 14y ago
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.
imfine 14y ago
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.
- nikita 14y ago
  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
  - iamleppert 14y ago
    lockfree data structures FTW!
  - imfine 14y ago
    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.
    - nikita 14y ago
      Correct! Different scenarios require different solution. One size doesn't fit all. And it's nice when solutions play nicely with each other.
  - genwin 14y ago
    > 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?

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?

al_james 14y ago

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.

chris_wot 14y ago
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!
- al_james 14y ago
  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.
  - chris_wot 14y ago
    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 http://developers.memsql.com/docs/1b/durability.html 2. http://developers.memsql.com/ http://developers.memsql.com/
- deleted 14y ago
  [deleted]
zwischenzug 14y ago
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.
ericfrenkiel 14y ago
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.

chris_wot 14y ago

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 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 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 http://developers.memsql.com/docs/1b/indexes.html More interesting for me, is that it can be hosted on EC2, which is pretty neat!

nikita 14y ago
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.
- chris_wot 14y ago
  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").

MattRogish 14y ago

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-procedures-more-efficient-in-general-than-inline-statements-on-mode/59932#59932 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.

nikita 14y ago
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.
- MattRogish 14y ago
  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.

nikita 14y ago

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.

imfine 14y ago

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.

nikita 14y ago
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
- iamleppert 14y ago
  lockfree data structures FTW!
- imfine 14y ago
  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.
  - nikita 14y ago
    Correct! Different scenarios require different solution. One size doesn't fit all. And it's nice when solutions play nicely with each other.
- genwin 14y ago
  > 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?