Forgot your password?
typodupeerror
Databases Software IT News

MemSQL Makers Say They've Created the Fastest Database On the Planet 377

Posted by timothy
from the before-you-even-think-it dept.
mikejuk writes "Two former Facebook developers have created a new database that they say is the world's fastest and it is MySQL compatible. According to Eric Frenkiel and Nikita Shamgunov, MemSQL, the database they have developed over the past year, is thirty times faster than conventional disk-based databases. MemSQL has put together a video showing MySQL versus MemSQL carrying out a sequence of queries, in which MySQL performs at around 3,500 queries per second, while MemSQL achieves around 80,000 queries per second. The documentation says that MemSQL writes back to disk/SSD as soon as the transaction is acknowledged in memory, and that using a combination of write-ahead logging and snapshotting ensures your data is secure. There is a free version but so far how much a full version will cost isn't given." (See also this article at SlashBI.)
This discussion has been archived. No new comments can be posted.

MemSQL Makers Say They've Created the Fastest Database On the Planet

Comments Filter:
  • by alphatel (1450715) * on Sunday June 24, 2012 @07:27PM (#40433251)
    It sounds cool, but we can get 200k iops on Raid10 SSD without degradation.
    • by tomhath (637240) on Sunday June 24, 2012 @07:44PM (#40433427)
      Price/performance is a better question. If it's fast enough that you don't need the Raid 10 SSD then it could be a good choice. Throw hardware at any DBMS and you'll get good throughput.
    • So, two guys meet and churn out some code that cache most of the DB work in memory. Great. But MySQL has a MEMORY engine and is pretty well optimized (eg keep indexes in memory, does some caching as well)... the hardest part is probably its setup: setting the right options in MySQL to achieve top performance is not easy.
      Besides, the "caching" or equivalent work is not the most difficult part of a DBMS, by far: What about the algorithms to "compile" queries in order to use indexes and perform the JOINS opti
    • by guruevi (827432)

      You can get more than 10M IOPS on certain RAM-based SSD, they're just mighty expensive.

  • Ya Don't Say! (Score:5, Insightful)

    by Rary (566291) on Sunday June 24, 2012 @07:28PM (#40433271)

    Really? Accessing RAM is faster than accessing a disk? What a novel discovery!

    It seems to me that MySQL can also be run in memory. Apparently that's how the clustered database works (or used to work). I've never tried it, but let's see some benchmarks between MemSQL and an entirely memory-based MySQL.

    • by JimboFBX (1097277)

      I was going to say, how does this perform on large queries in a large database.

      • by Alex Belits (437) *

        Didn't you get the memo? There are no large databases anymore, all database servers are supposed to have more RAM than the size of their database.

        *** BARF!!! ***

        • by drsmithy (35869)

          Given how trivial and relatively cheap it is to put 192GB+ RAM into a server these days, there's a lot of truth in that statement, whether you like it or not.

          • I find it funny how easy it is to order an AMD system with 256GB of ram (or even 512GB, just much more expensive) yet the Intel ones all seem to max out at 192 or really, really expensive 384GB.. I know it has to do with the memory controllers, but our loads are very, very memory dependent..

            • by Zak3056 (69287)

              I find it funny how easy it is to order an AMD system with 256GB of ram (or even 512GB, just much more expensive) yet the Intel ones all seem to max out at 192 or really, really expensive 384GB.. I know it has to do with the memory controllers, but our loads are very, very memory dependent..

              The Dell PE820 (a 4-socket intel server) supports up to 1.5TB of RAM. With 2-CPUs, though, it's only 768GB...

            • by Junta (36770)

              IBM x3950 x5 can do 3TB of ram with Intel processors.

    • It seems to me that MySQL can also be run in memory. Apparently that's how the clustered database works (or used to work).

      Absolutely correct. NDB Cluster. It's quite fast, even on older hardware providing you have enough RAM to hold your database.

    • Re:Ya Don't Say! (Score:4, Insightful)

      by errandum (2014454) on Sunday June 24, 2012 @08:41PM (#40433813)

      That and memcached (I think that's the name).

      This comparison is far from fair... Is it ACID? Or eventually synchs up? How does it compare with other memory based DB's?

      Comparing it with a slow relational DB will not give you any kind of credibility.

    • Isn't the implocation that in this case there's a lot less time between the transaction getting made and that data being committed to non-volitile memory?

      • Re:Ya Don't Say! (Score:5, Insightful)

        by xelah (176252) on Monday June 25, 2012 @05:48AM (#40436571)

        I don't think that's something which can be changed, except by changing the hardware. The starting point is this: When a COMMIT is made all changes have to be written to the write-ahead-log before a success response can be returned to the client. The WAL is written sequentially, and so if you're using ordinary disks and are sensible you give it its own set of spindles (RAID1, say). That means that between each write you have to wait for one disk rotation - you append to the log, you process the next transaction, then you have to wait for the disk to rotate to just after where you finished writing before you can write the next one. So you can do 1/15k transactions per minute with this basic setup.

        You can do things to make this faster. You can write several transactions at once, and you can put slight delays in to transaction commits to wait for others to bundle them with (PostgreSQL I believe will do the first and can be configured to do the second). You can use battery backed caches in your RAID system, which will have much the same effect (and leave you limited by disk bandwidth and cache size). You can use SSDs that don't need to seek.

        I can't see anything in TFA that MemSQL is supposed be doing differently here, or anything it CAN do differently. From TFA: 'The key ideas are that SQL code is translated into C++, so avoiding the need to use a slow SQL interpreter, and that the data is kept in memory, with disk read/writes taking place in the background.'. The first I'm not too sure I understand (presumably they're not turning it in to C++ and then passing it through a C++ compiler....) but maybe we can blame the journalist for that. Or maybe they've just reinvented prepared statements. The second is what databases do anyway - except, of course, for the WAL and when you're reading data which isn't in memory. Perhaps what they're doing is flushing the WAL after the commit has returned to the client - which makes the database very much not ACID, and is also something that other databases can be configured to do if you don't care about your data.

        Potentially what they could do, though, is to have designed all of their data structures, algorithms, locking and so on around the assumption that everything is in memory. There are big differences in the best query plan to use when data is in memory vs on disk, and traditional databases don't necessarily make the right choices. They try, but may for instance use table scans for queries which return a large proportion of the rows in a table because sequential IO is faster, when they should be using indexes if the data is in memory. And BTrees and the way data everywhere is split in to pages is something traditional DBs do because that works well even when most of your data is on disk. So maybe that's what they've done differently that other DBs haven't already been doing.

    • Re:Ya Don't Say! (Score:4, Interesting)

      by bill_mcgonigle (4333) * on Sunday June 24, 2012 @09:23PM (#40434063) Homepage Journal

      Not just that - you can get a FusionIO ramdisk device for really big databases and get performance that's somewhere between SSD and memory. Those are all battery backed and such, so no monkeying around with whether the ACID was done right or not.

    • Re:Ya Don't Say! (Score:5, Interesting)

      by gman003 (1693318) on Sunday June 24, 2012 @10:21PM (#40434449)

      It's a bit more complex. There's four main ways to do MySQL storage in RAM (which I know of because my current work project is a MySQL application).

      First, the NDB Cluster system is there, which is what you've mentioned. That's basically just a MySQL frontend to a distributed, memory-based NoSQL database, though. Convenient, but not truly "MySQL".

      The second is using the "Memory" storage engine, where it actually stores a normal MyISAM table in memory. However, this is a surprisingly crappy option, because it uses table-level locks for writing, so parallel write performance is only marginally faster than disk.

      The third is to store regular InnoDB tables on a ramdisk. This can be crazy fast, but it also means that if your server crashes or loses power, you're *fucked*

      The fourth is to use Memcached, which isn't really a MySQL thing at all. You're basically just caching data in a memory-only NoSQL database, at the application level. This is actually what we ended up doing, because all the others are pretty crappy options - Cluster is the best one, but the hardware requirements are higher than we could justify spending given our performance requirements. Shoving memcached onto the web server (which has RAM to spare) and setting certain queries to cache their results there sped things up significantly, at minimal cost.

      As far as I can tell from the summary (I refuse to read the articles for such a blantant slashvertisement), this "MemSQL" doesn't do anything you can't do by configuring MySQL properly, although they likely optimized some rarely-used modules to make them faster.

      • Re:Ya Don't Say! (Score:5, Informative)

        by arth1 (260657) on Sunday June 24, 2012 @11:08PM (#40434729) Homepage Journal

        The third is to store regular InnoDB tables on a ramdisk. This can be crazy fast, but it also means that if your server crashes or loses power, you're *fucked*

        Not necessarily. There are battery-backed volatile RAM devices that can last for days, and also non-volatile RAM devices like F-RAM and MRAM.
        Battery backed volatile RAM can even be considered "cheap" - if the bottleneck are in tables small enough to fit on these, or the amount of overall writes is so high that placing the innodb logs there makes sense, it can be cheaper than a RAID10 or 50 of high-speed SAS drives.

        The HyperCard / ACARD drives, for example, are only $300 plus RAM. And if the worst happens, you can even dump the RAM to a CF card before the battery runs out.

        • by gman003 (1693318)

          I was referring to software-based ramdisks, not RAM-based SSDs. Although I suppose there's not much of a performance difference - the only difference is durability.

    • by Hognoxious (631665) on Monday June 25, 2012 @02:17AM (#40435743) Homepage Journal

      MySQL is not webscale because it uses joins.

  • okay...? (Score:5, Funny)

    by bhcompy (1877290) on Sunday June 24, 2012 @07:38PM (#40433359)
    When I think of fast databases to compare to, the first thing I think of is MySQL.

    /Actually, I'd rather see a comparison to Pick or other lightning fast MV dbs
    • Re:okay...? (Score:5, Insightful)

      by Kergan (780543) on Sunday June 24, 2012 @07:51PM (#40433489)

      MySQL is the last thing I think of, personally. It sucks as soon as you make it ACID compliant and start hitting it with thousands of concurrent requests. You're much better off with PostgreSQL.

    • Re:okay...? (Score:4, Insightful)

      by evilviper (135110) on Sunday June 24, 2012 @08:15PM (#40433643) Journal

      When I think of fast databases to compare to, the first thing I think of is MySQL.

      MySQL is actually very fast under light loads / one-off queries, and if you choose to leave it at the non-ACID compliant default settings, and similar. eg. "innodb_flush_log_at_trx_commit"

      That's probably the only reason why it got popular... There weren't any open source NoSQL DBs at the time, and MySQL seems fast when tested with a basic, shallow benchmark. Of course others like PostgreSQL completely leave it in the dust once there's some real load, or complex queries, or you WANT to be absolutely sure transactions were committed to disk before returning.

      As a single point of evidence, I give you Zabbix... It supports the use of all the major databases (Postgresql, DB2, Oracle, SQLite, etc.) as backends, yet MySQL is recommended as it performs the fastest.
      http://www.zabbix.com/documentation/1.8/manual/performance_tuning [zabbix.com]

      /Actually, I'd rather see a comparison to Pick or other lightning fast MV dbs

      Level-2 overflow! Resize analysis! Change the modulo! Ahhhh!

      I've done the PICK-OS thing for a few years, and I'm not a big fan. I'm infinitely happier administering PostgreSQL DBs.

      Besides, you don't have to go to something as exotic as PICK to get away from SQL. Try ages-old Berkley DB (db4), or any of the newer NoSQL options.

      • by julesh (229690)

        That's probably the only reason why it got popular... There weren't any open source NoSQL DBs at the time

        Zope? BDB? Both of these were available at the time MySQL became popular.

      • As a single point of evidence, I give you Zabbix... It supports the use of all the major databases (Postgresql, DB2, Oracle, SQLite, etc.) as backends, yet MySQL is recommended as it performs the fastest. http://www.zabbix.com/documentation/1.8/manual/performance_tuning [zabbix.com]

        From the linked document:

        rebuild MySQL or PostgreSQL from sources to get maximum performance

        2003 just called. They want their Gentoo Ricers [funroll-loops.info] back.

    • Ahhhh, Pick! (Score:5, Interesting)

      by hedronist (233240) on Sunday June 24, 2012 @08:56PM (#40433891)

      The most over-the-top DB God I know started in Pick-land (ca 1972?). Although he does (is forced to?) use SQL nowadays, he thinks in ways that do not come out of any SQL DBA handbook. As a result he gets DBMSs to do things that are ... unnatural.

      He is currently doing some data-cubing stuff for us that I didn't think could be done with something less than a DOD budget. He says his touchstone is thinking in Pick and then 'translating' to SQL.

      I still think that the 2 missing courses from any CS degree program are 1) how to debug, and 2) history of computing.

      • I am intrigued by your ideas. Let us start a newsletter.
      • Re:Ahhhh, Pick! (Score:5, Insightful)

        by Zenin (266666) on Sunday June 24, 2012 @11:05PM (#40434713) Homepage

        I still think that the 2 missing courses from any CS degree program are 1) how to debug, and 2) history of computing.

        Practical software engineering is mostly about debugging. An actual course in debugging would imply that Computer Science curriculum had something to do with practical software engineering, which we're all painfully away it hasn't in the slightest.

      • As someone who programs in Pick/D3 still every day (a skill I picked up working for a company with a legacy product), as well as having had worked in pretty much every SQL product that exists, I am both startled and amazed to see it mentioned on Slashdot. I think this is the first time I've ever seen anyone mention it!!!

        And I am in agreement - Pick was something truly different which could have been as big as SQL - multi-value, "NoSQL"-ish which still had a query engine, fast, little to no maintenance, loo

  • by Kergan (780543) on Sunday June 24, 2012 @07:42PM (#40433395)

    Show me benchmarks vs Oracle, PostgreSQL or SQLServer. Spare me the comparison with MySQL or some other toy.

  • Err... what? (Score:4, Interesting)

    by Splab (574204) on Sunday June 24, 2012 @07:42PM (#40433399)

    Ok, so both article and video is extremely thin on details, the explanation for the massive performance is pretty much gibberish and their argumentation for ACID compliance is bullshit.

    Just leaves me with the question, what are they trying to get out of this BS?

    • Re:Err... what? (Score:5, Informative)

      by viperidaenz (2515578) on Sunday June 24, 2012 @07:52PM (#40433503)

      Just leaves me with the question, what are they trying to get out of this BS?

      Your money, its not a free piece of software.

    • by gweihir (88907)

      Self-aggrandizement and money. When somebody claims they are better than everybody else, they are usually lying and knowing it.

  • Meh. (Score:5, Insightful)

    by hey! (33014) on Sunday June 24, 2012 @07:45PM (#40433435) Homepage Journal

    Give me fast enough, robust, easy to administer and standards compliant. Maybe a little less fast means you throw more hardware at a problem, but it doesn't matter if overall the overall cost and risk is inflated. A platform decision boils down to three things: (1) is it good enough; (2) is it economical; (3) if we decide later this doesn't work for us, are we totally screwed.

    In any case, there's no meaningful way you can make a claim that a database management system is the fastest on the planet. All you have is benchmarks, and different benchmarks apply to different use-cases.

  • by PPH (736903) on Sunday June 24, 2012 @07:52PM (#40433497)

    What you have there is (or may be) the fastest database management system.

    I have the worlds fastest database. One table, one record, and one field (NULL).

  • by TheMiddleRoad (1153113) on Sunday June 24, 2012 @07:56PM (#40433531)

    I wouldn't run my toaster on software engineered by someone from Facebook, let alone a database. I'd have to spend ten minutes searching for my toast, and it would show up the following week.

  • by stanlyb (1839382) on Sunday June 24, 2012 @08:12PM (#40433617)
    Or its nowadays name: CACHE? The best, the fastest, and the most reliable commercial database on the planet? Common, guys, get real.
  • Some clever tricks and cache management. All the speed improvement seems to be coming via read/write speeds rather than any fundamental breakthrough or parallel implementation or massively parallel database of any such thing. And the test was the standard test but some hand picked data base and their own queries. Probably the original funders are planning to sell it down to the next set of chumps.
  • by mounthood (993037) on Sunday June 24, 2012 @08:36PM (#40433777)

    They're durable and synchronously log all changes to disk, so what makes them faster? They do say this, from: http://developers.memsql.com/docs/1b/durability.html [memsql.com]

    Reconfigure the server to use a faster disk. MemSQL exclusively relies on sequential (not random) disk writes, so using an SSD will dramatically improve durability write performance.

    Are SSDs better at sequential writes? I thought their advantage was random reads, and they weren't any faster at writes then HDDs. Also, the data would become hopelessly out of order by only doing sequential writes, unless they're periodically re-writing all the data in order, which would mean lots more I/O then a typical DB.

    • by hawguy (1600213)

      Are SSDs better at sequential writes? I thought their advantage was random reads, and they weren't any faster at writes then HDDs. Also, the data would become hopelessly out of order by only doing sequential writes, unless they're periodically re-writing all the data in order, which would mean lots more I/O then a typical DB.

      They say they rely on snapshots and logging. I'm assuming that it periodically writes a snapshot of RAM to disk, then logs transactions in the log for recovery. Hopefully it snapshots different portions of RAM at different times so there's not one huge snapshot being written to disk every time.

      Though if I had a database where I needed 80,000 query/second performance, I'd probably want a cluster of these so if one machine goes down, the other machine can take over so I don't have to wait for the service to

    • by Surt (22457) on Sunday June 24, 2012 @11:09PM (#40434731) Homepage Journal

      SSD is significantly faster than HDD at both sequential and random writes. Top 15K SAS drives write ~250MB/s sequential. Top SSD write 550MB/s sequential. Write random and it gets much worse for the SAS drive. Try to even find an enterprise HDD benchmark done in the last year. No one bothers because enterprise buys SSD if they care about performance.

  • Speed vs. speed (Score:5, Interesting)

    by Todd Knarr (15451) on Sunday June 24, 2012 @08:58PM (#40433903) Homepage

    Speed's fine, but what kind? Or more specifically, over what timeframe? High transaction rates are fine, but they don't do any good if you can only sustain them for a few seconds or minutes before the whole thing collapses. I want to know the transaction rate the thing can sustain over 24 hours of continuous operation. In the real world you have to be able to keep processing transactions continuously.

    That long-time-period test also shows up another potential problem area: disk bottleneck. In-memory's fine, but few serious databases are small enough to fit completely in memory. And even if it will fit, you can't lose your database when you shut down to upgrade the software so eventually the data has to be written to disk. And that becomes a bottleneck. If your system can't flush to disk at least as rapidly as you're handling transactions, your disk writes start to lag behind. Sooner or later that'll cause a collapse as the buffers needed to hold data waiting to be written to disk compete for memory with the actual data. You can play algorithmic games to minimize the competition, but sooner or later you run up against the hard wall of disk throughput. And the higher your transactions rates are, the harder you're going to hit that wall.

    • by symbolset (646467) *
      FusionIO
  • TimesTen Database (Score:2, Interesting)

    by Anonymous Coward

    So what is the difference between MemSQL and TimesTen [wikipedia.org]?

    Other than the 16 years TimesTen has been out longer, the fact that Oracle now owns TimesTen, that it runs on both 32bit and 64bit Linux and Windows, that it can run in front of another database engine to give it a boost, and that it has customer installations up to the Terabyte range.

    Just another lame attempt to reinvent the wheel.

  • Filesystem anyone? (Score:2, Informative)

    by Anonymous Coward

    Remember the good old days, when XYZ-db wasn't always available (or even disirable)? we used to use files.

    Yea, files. Novel concept, these days, mention ISAM to someone and they don't know what you're talking about!

    If you really need speed, maybe a database isn't your best bet. Maybe, just maybe, you should consider structuring the data in a way that makes sense for your application using files.

    • Re: (Score:3, Interesting)

      by Anonymous Coward

      I work on a system like that right now in a really big company. Let me tell you something- it's shit. If you need concurrent access to the files/directories by several processes, you'll have a heap of issues. Consumers pick up files before they are completely written by the producers (now fixed by file renaming, but required work). Sime directories now hold 300k files, and any file operations are extremely slow- filesystems aren't designed for this (in process of being fixed by splitting directories squid s

  • "No more porridge". Right.

    This thing is ACID at least?

  • MySQL the worlds most popular open source database

    memSQL the worlds fastest database

    PostgresSQL the worlds most advance open source database

    SQLite most widely deployed SQL database engine in the world

    I just wish people would dispense with their childish marketing bullshit already.

  • by Anonymous Coward on Monday June 25, 2012 @02:10AM (#40435717)

    I've had a love-hate relationship with MySQL for over ten years now, and have as much cause to hate it as anyone, but I have to point this out. Read the MemSQL docs carefully, and here's the killer - they only support single-query transactions, and only at isolation level READ COMMITTED.

    Until those two facts change, then its hardly a fair comparison.

  • by Fnord666 (889225) on Monday June 25, 2012 @09:58AM (#40438131) Journal

    Shamgunov has excellent credentials in the database world, in spite of having worked at Microsoft on SQL Server for six years.

    FTFY

Everything that can be invented has been invented. -- Charles Duell, Director of U.S. Patent Office, 1899

Working...