5 ms·

You are asserting that databases are always I/O bound and never CPU bound. Your assertion is wrong. A properly tuned database will become CPU bound (whether it'

You are asserting that databases are always I/O bound and never CPU bound. Your assertion is wrong. A properly tuned database will become CPU bound (whether it's MemSQL or MySQL). At that point hyper-efficient execution results in higher throughput and lower latency.

dhruvbird 14y ago
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 interpreting and running it with checks performed in every iteration, they are compiling the code. I don't know how much of a difference this would make without actually seeing the numbers.
- jakejake 14y ago
  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.
  - nikita 14y ago
    Not every parsing is equal. MySQL and other databases produce a tree as they parse a query. If you just want to match parameters you can avoid memory allocations and just replace parameters with ^ or @ signed in place in the query string. http://developers.memsql.com/docs/1b/faq.html http://developers.memsql.com/docs/1b/faq.html gives some more details.
  - dhruvbird 14y ago
    @nikita I see: http://developers.memsql.com/docs/1b/faq.html#does-this-mean-that-if-i-change-a-constant-in-a-query-memsql-will-compile-a-new-plan http://developers.memsql.com/docs/1b/faq.html#does-this-mean... Do you mean you determine placeholders without actually parsing the SQL? @jakejake The docs. seem to suggest that the benefits of prepared statements are won without using prepared statements in MemSQL.
  - bazzargh 14y ago
    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.
phillmv 14y ago
> 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 speed. This can't be hard to measure.
- tintor 14y ago
  It is not about the cost of parsing SQL text. Think of it as intermediate bytecode for SQL that is interpreted as query is being executed. Every SQL database does some variation of it. What is unique about MemSQL is that this bytecode is actually compiled to native code using GCC.
- tigerBL00D 14y ago
  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 in how MemSQL handles executing compiled queries. Executing compiled queries is what a (well tuned) database spends most of its time doing. It is the hot code path. And if you cut the instruction count in half you can expect latency to decrease proportionally and throughput to increase. You are making all these skeptical claims that are technically wrong. I suggest you download MemSQL, compile a query and read the generated C++ code. Then download MySQL source and look at that too. At that point you will be coming to your own conclusions and I'll be happy to resume this debate.
  - Nitramp 14y ago
    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 memory, then yes, you'll be CPU bound. But many (most?) databases are not entirely in memory, and there's no reason they need to be. With reasonable memory and indexing, most queries boil down to only a few IOs/disk seeks, so you're looking at <50ms, which is entirely fine for your average web request. That's the case for the majority of "get by ID" style queries, which in my experience really are the lions share of queries, and in particular are also what needs to be fast. A complicated reporting query can take a couple of seconds, but your "main page/item view" must be fast. If you have random reads on a large database that cannot be kept in memory, this will be the majority of your workload. Those queries will spend all their time waiting for the disk, and more or less none interpreting your query tree, so optimizing the query tree processing part makes no sense at all. TL;DR, in my experience optimizing query tree interpretation does not yield significant benefits for the majority of queries, and IMHO won't be a significant differentiator for MemSQL outside of marketing.

dhruvbird 14y ago

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 interpreting and running it with checks performed in every iteration, they are compiling the code. I don't know how much of a difference this would make without actually seeing the numbers.

jakejake 14y ago
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.
- nikita 14y ago
  Not every parsing is equal. MySQL and other databases produce a tree as they parse a query. If you just want to match parameters you can avoid memory allocations and just replace parameters with ^ or @ signed in place in the query string. http://developers.memsql.com/docs/1b/faq.html http://developers.memsql.com/docs/1b/faq.html gives some more details.
- dhruvbird 14y ago
  @nikita I see: http://developers.memsql.com/docs/1b/faq.html#does-this-mean-that-if-i-change-a-constant-in-a-query-memsql-will-compile-a-new-plan http://developers.memsql.com/docs/1b/faq.html#does-this-mean... Do you mean you determine placeholders without actually parsing the SQL? @jakejake The docs. seem to suggest that the benefits of prepared statements are won without using prepared statements in MemSQL.
- bazzargh 14y ago
  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.

phillmv 14y ago

> 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 speed. This can't be hard to measure.

tintor 14y ago
It is not about the cost of parsing SQL text. Think of it as intermediate bytecode for SQL that is interpreted as query is being executed. Every SQL database does some variation of it. What is unique about MemSQL is that this bytecode is actually compiled to native code using GCC.
tigerBL00D 14y ago
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 in how MemSQL handles executing compiled queries. Executing compiled queries is what a (well tuned) database spends most of its time doing. It is the hot code path. And if you cut the instruction count in half you can expect latency to decrease proportionally and throughput to increase. You are making all these skeptical claims that are technically wrong. I suggest you download MemSQL, compile a query and read the generated C++ code. Then download MySQL source and look at that too. At that point you will be coming to your own conclusions and I'll be happy to resume this debate.
- Nitramp 14y ago
  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 memory, then yes, you'll be CPU bound. But many (most?) databases are not entirely in memory, and there's no reason they need to be. With reasonable memory and indexing, most queries boil down to only a few IOs/disk seeks, so you're looking at <50ms, which is entirely fine for your average web request. That's the case for the majority of "get by ID" style queries, which in my experience really are the lions share of queries, and in particular are also what needs to be fast. A complicated reporting query can take a couple of seconds, but your "main page/item view" must be fast. If you have random reads on a large database that cannot be kept in memory, this will be the majority of your workload. Those queries will spend all their time waiting for the disk, and more or less none interpreting your query tree, so optimizing the query tree processing part makes no sense at all. TL;DR, in my experience optimizing query tree interpretation does not yield significant benefits for the majority of queries, and IMHO won't be a significant differentiator for MemSQL outside of marketing.