9 ms·
MySQL is bazillion times faster than MemSQL
- fusiongyro 14y agoWhenever one sees a claim about new database technology outperforming standbys like MySQL and Postgres by factors of 10x or more, it's a good idea to suspect the out-of-the-box configuration isn't really durable. What kills write performance in databases is the time it takes to write to disk, not the software overhead.
- gouranga 14y agoOr in the case of a lot of vendors we won't mention - blatant lies.
- 16s 14y agoVendors lie. That is the sole truth of IT purchasing. When testing network gear, some of our network engineers would say, "Vendors lie, but packets don't".
- cluda01 14y agoNice quote! More generally when considering any solution you should always test it in your environment according to your performance needs BEFORE you buy it. Any vendor worth their salt will let you do this.
- gouranga 14y agoFrom experience, that's pretty much no vendor these days. I will say positive things for Microsoft: you can always trial their software before you buy it.
- cluda01 14y agoWe tested Azul Systems Zing JVM (pauseless GC) inhouse. They sent out support engineers and gave us great presentations to boot. Good company.
- dredmorbius 14y agoAre you specifically not mentioning Oracle by any chance?
- gouranga 14y agoI'm specifically making no comment on that ;-)
- jacques_chester 14y agoAnd according to the standard Oracle contract ... you can't.
- gouranga 14y agoThey didn't say you can't make no comment though!
- deleted 14y ago[deleted]
- wheaties 14y agoOr if they use a global write lock.
- endersshadow 14y agoFor what it's worth SQL Server doesn't meet his definition of "durable." It writes a transaction log and then writes those transactions to disk on a checkpoint [1]. Typically, that checkpoint automagically happens, but you can also force it to clear the buffer with a "checkpoint" command. InnoDB also does this, too [2]. I don't know if MemSQL actually has checkpointing or what, but it's just worth noting. [1]: http://msdn.microsoft.com/en-us/library/ms189573.aspx http://msdn.microsoft.com/en-us/library/ms189573.aspx [2]: http://dev.mysql.com/doc/refman/5.0/en/innodb-checkpoints.html http://dev.mysql.com/doc/refman/5.0/en/innodb-checkpoints.ht...
- spudlyo 14y agoI think you may be missing the point. Most databases use transaction/redo logs, it has to do with how often the writes to the transaction log are flushed. With proper durability, the transaction log is flushed with every commit, not when buffers become full or a background thread gets around to it.
- __dumbideas 14y agoMySQL's InnoDB has configurable durability: http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.htm...
- jstclair 14y agoThis is a little misleading; an internal checkpoint happens upon transaction commit[1]. The comment makes it sound as if one or more transactions can commit before a checkpoint writes them to disk. [1]: http://msdn.microsoft.com/en-us/library/ms186259(v=sql.105).aspx http://msdn.microsoft.com/en-us/library/ms186259(v=sql.105)....
- spudlyo 14y agoThe transaction is actually written to disk twice. Once when it is written to the transaction log, and then again when a checkpoint happens and it's written to the underlying data blocks. Transaction log writes are sequential and fast, writes to the underlying data blocks are random and slow. The whole point of the checkpoint is to convert this sequential i/o to random i/o in an efficient manner. Still, that's not what we're talking about. We're talking about the difference between writing data to the transaction log, and writing data to the transaction log and then flushing it. Once you call fsync() and the data is flushed you know for a fact (provided the hardware isn't lying to you) the bytes are on the damn platter, and not in some OS buffer cache.
- rdtsc 14y agoVery good observation. Established DBs already do a decent job optimizing the path from network to disk, at least in the most basic one client, one server test. So say they are hitting the 80MB/s throughput. If someone's benchmark shows 800MB/s throughput on same hardware -- you know they are making stuff up.
- ericfrenkiel 14y agoWhen someone writes on their intro "... and now in return I want to waste your time a bit." it's a sign that someone isn't being serious. Domas' main criticism is that our video, http://vimeo.com/44087431 http://vimeo.com/44087431, uses a MySQL with standard defaults. Since tuning a database is by definition a custom process, we wanted to demonstrate what performance you'd get "out of the box." The video speaks for itself. MemSQL can push 80k/sec vs 3.5k/sec for MySQL on EC2. Domas actually demonstrates MemSQL's robust durability controls: he bounds MemSQL to disk. If you want to throw an anchor behind a speed boat, you can do it, though we don't recommend it. Even Domas agrees his article isn't serious: "I do agree, that its execution speeds for table scans are not bad – 8M rows scanned a second (for SELECT COUNT(*) query) from single thread is sure a great achievement." If that's the highest praise I can get from Domas, I can live with it.
- spudlyo 14y agoFor me, the take away from this article is that MemSQL's ACID compliance is half-assed at best, which is a much more damning criticism than using a flawed benchmarking methodology.
- peeters 14y ago> Since tuning a database is by definition a custom process, we wanted to demonstrate what performance you'd get "out of the box." Yes, and Domas was criticizing that as a strawman; as comparing apples to oranges. Since they tailor their defaults to different criteria (e.g. different standards of durability), of course they will perform differently. The only really valid benchmarks are when you get an expert in each to tune their respective products to optimize for a specific scenario, and see how outperforms who. And then make public exactly what configuration was done to get there.
- mikeash 14y agoI thought his main criticism was that MemSQL isn't durable by default, and that when you enable that it becomes incredibly slow. That's a pretty big deal, and IMO running database benchmarks on an unsafe configuration like this is extremely dishonest.
- deleted 14y ago[deleted]
- cs702 14y agoWhenever I read an article like this, I often end up feeling that on the 'truthiness' spectrum the scale goes like this: lies, damn lies, statistics, benchmarks ;-)
- maratd 14y agoEvery public benchmark is a horrible blasphemous lie. The only benchmarks that are honest are the ones you conduct in private, with your very specific use case. There is no point in publishing them, because they're useless to everybody but you. Public benchmarks, on the other hand, are crafted as advertising for your favorite [fill in the blank].
- slurgfest 14y agoIf you generate some data which would be dishonest when published, it is also dishonest in private, except that you are only being dishonest to yourself and nobody can review your dishonesty. If you generate data which are honest in your use case, then you can publish them honestly as long as you are specific about your use case.
- jamesaguilar 14y agoAnd yet, there are useful statistics and useless ones, useful benchmarks and useless ones. The benchmarks posted here seem good enough to demonstrate a few truths about MemSQL for people that might be earlier been taken in by hype.
- slewis 14y agoFrom MemSQL's home page: "MemSQL writes back to disk/SSD as soon as the transaction is acknowledged in memory." From the article: "See, MemSQL also has “transaction-buffer” setting, which, in default “full durability mode” will asynchronously return “ok” until 128M buffer is full (or background log flusher thread writes it out)." So which is it?
- tintor 14y agoIn MemSQL transaction buffer is flushed continuously as long as it is not empty. Default value of 128M is there just to absorb bursts in workload.
- Devilboy 14y agoSo you could lose up to 128MB of updates if the machine crashes at the wrong time. NOT DURABLE.
- aioprisan 14y agomemSQL is NOT durable by design. you can lose the pool of unwritten queries
- ChikkaChiChi 14y agoSimple solution: [1] Someone create a project on GitHub [2] Upload an amply sized CSV full of data [3] Publish list of queries needed to "gauge" speed [4] Allow anyone to post their metrics to see how well tuned any database can be in comparison The only other criteria would be to ask them to all use the same form factor. EC2 makes the most sense.
- aphyr 14y agoShared environments with virtualized IO are not good places to run benchmarks.
- ChikkaChiChi 14y agoI couldn't agree more, but its the closest to standardized testing we can get that everyone can use. I HATE the idea personally, but I cannot think of a better way to start comparing apples to apples.
- aphyr 14y agoI'd opt for the TPC workloads on standardized hardware: http://www.tpc.org/tpcc/results/tpcc_price_perf_results.asp http://www.tpc.org/tpcc/results/tpcc_price_perf_results.asp
- btilly 14y agohttp://www.tpc.org/ http://www.tpc.org/ already does something like this, but much more rigorously.
- jacques_chester 14y agoThe DBT suite benchmarks are open source versions of the TPC suite.
- arihant 14y agoWhen debating a fresh new database project - benchmarks aren't really the way to go, because those can be improved. What should be debated is what underlying fundamental has the new project gotten right. Almost every successful database project got something fundamentally right - MySQL, Redis (in-memory), MongoDB(page based storage), Riak(Distributed). On that note, here's my take on MemSQL - People who love MySQL, almost never want to migrate. People who hate MySQL, usually hate it because of SQL. People who are far along in the projects enough that decision of database is no longer a matter of taste, don't use a brand new untested DB. Therefore, I am unsure how a brand new, in-memory, SQL based solution fits.
- bsg75 14y ago> People who hate MySQL, usually hate it because of SQL. Or because of the shortcuts it has taken to avoid the "hard stuff", which results in developers taking shortcuts by using MySQL to avoid the "hard stuff". Love or hate SQL for what it is, not the MySQL implementation of it.
- Domenic_S 14y ago> Or because of the shortcuts it has taken to avoid the "hard stuff", which results in developers taking shortcuts by using MySQL to avoid the "hard stuff". I am deeply interested in what you think this is
- bsg75 14y agoAvoiding RI (in part due to poor FK support depending on version / storage engine), designs that avoid simple schema changes because they can't be done online, point version migration difficulties. Consider unsupported SQL constructs from other engines (CTEs, window functions, better join and subquery support), and the effort and mess to accomplish what should be straightforward tasks generates SQL hate. Some NOSQL projects have merit as they provide mechanisms for working with data structures that do not map well to rows and columns. However, difficulties faced with MySQL have also pushed some projects to use equivalently broken data storage technology, when a "better" RDBMS would be more appropriate.
- nikita 14y agoMemSQL CTO here. Great article- Domas has done a good job of digging into the internals of MemSQL! A few questions/comments: 1.) The range query issue you pointed out can be explained by a well known limitation of skip lists. Unlike B-Trees, skip lists are unidirectional. By default, our indexes are ascending, so indeed you have to skip to the end to run a MAX() or "ORDER BY id DESC" query. To fix this, just change the primary key to be descending in your schema: CREATE TABLE x (id int, ..., PRIMARY KEY id (id) DESC, ...) This is explained here http://developers.memsql.com/docs/1b/indexes.html#skip-list-indexes http://developers.memsql.com/docs/1b/indexes.html#skip-list-.... If you want both behaviors in your app, you'll have to create two skip list indexes (for now). 2.) The transaction-buffer > 0 setting is really where we shine. Synchronous durability is something we have for flexibility, but I'll be the first to admit that it's not really optimized to perform well. The customers that we're working with are okay with this. And it's inspired by what modern companies do. Maybe it's changed in the time since I've left (Domas?) but Facebook certainly does not write comments synchronously to disk. 3.) Our synchronous durability does indeed flush in 50 MS cycles, so you'll see poor performance on single threaded inserts. However, as you pointed out, we're optimized for the case with multiple concurrent writers. Since MemSQL implements group commit on high parallel load throuput picks up. Sure, writing your own very specific benchmark you can show us writing poorly, but we've never worked with a customer that's needed single threaded, synchronous writes to shine. If this is your use case, unless you need substantial read performance, MemSQL is not the database for you.
- deleted 14y ago[deleted]
- timaelliott 14y agoYou seem to be missing the main reason why people have an issue with MemSQL. The issue are quotes like this on your website and video: "MEMSQL IS 30 TIMES FASTER THAN MYSQL." It's an extremely invalid and biased comparison. You're quite literally comparing the speed of writing to RAM versus the speed of writing to disk. If you didn't make such ridiculous assertions, people would accept your product for the actual awesome things it does and not focus on refuting your bullshit claims.
- mariusmg 14y agoWriting in a volatile storage is not "durable by default" you asshats.
- mumrah 14y agoThis reminds me of some early MongoDB benchmarks. Claims of extremely high write throughput when really it's writing to memory and relying on mmap to eventually sync the data to disk. Making write speed claims when you're not touching the disk is like bragging about how fast you can cat a file to /dev/null.
- bond 14y agoCan we say they're deceiving us with this "MemSQL is 30 times faster than MySQL"?
- aioprisan 14y agothat and the claim that it's a durable db
- blake8086 14y agoWhy is "written to hard disk" considered to be "durable"? Isn't that just a higher likelihood of "durability"?
- forgotusername 14y agoThis is actually a fair and valid point. On all but the most delicately configured systems, the standard configuration of basically all SQL databases does not occlude committed data loss during power failure due to the hard disk's write cache. See for example http://www.postgresql.org/docs/8.3/static/wal-reliability.html http://www.postgresql.org/docs/8.3/static/wal-reliability.ht...: When the operating system sends a write request to the disk hardware, there is little it can do to make sure the data has arrived at a truly non-volatile storage area. Rather, it is the administrator's responsibility to be sure that all storage components ensure data integrity. Avoid disk controllers that have non-battery-backed write caches. At the drive level, disable write-back caching if the drive cannot guarantee the data will be written before shutdown. Which almost nobody does, because performance falls through the floor unless you're pimping a $500 RAID card (don't even mention software RAID). The SQLite documentation goes into further detail surrounding the atomicity of writing a single 512 byte sector during a power failure (on old drives, on 4096 byte drives, and on SSDs). Few people seem to account for any of this stuff, yet sleep soundly at night regardless.
- Dylan16807 14y agoWhile backup power might be expensive in a hard drive situation, it looks like even cheap SSDs are available with power loss protection. And as far as I'm aware you're better off using SSDs for databases anyway.
- jharsman 14y agoNote that writing data to a single disk (or SAN array, or RAID controller) really isn't durable either, even if the the data does actually get to the disk and isn't in a write cache somewhere. What if that disk crashes, or the SAN array brakes and kills all the data, or the data center burns down?
- 14y ago
- willvarfar 14y agoPeople should also look at - and benchmark - tokudb with its fractal tree indices.
- jeltz 14y agoSadly for the development of databases their fractal tree technology is patent encumbered.
- aaronh 14y agoI demand a free lunch.
- mirou 14y agoHey Nikita having a colorful html5 home page is not enough for a project to be performant
- mirou 14y agoHey Nikita having a colorful html5 home page is not enough for a project to be performant
- peterwwillis 14y agoI think this whole conversation is making a mountain out of a molehill. When you're building sites which have enormous performance requirements, you take what you have and you force it to scale. You don't need to compare one tool to the other and find the perfect benchmark. Whatever you use, I promise you, you can make it perform as fast and scale as far as you need it to. So just pick the tool that fits your requirements the best and stick with it. That being said, I also urge you to pick something free and open-source if you can. Some of the ways big sites have leveraged MySQL to the extent they have is due to the custom patchsets they apply. Closed-source solutions may not provide similar hotfixes or customization (and when it comes it's months later than they claimed they'd have it ready)