MariaDB 10.1 can do 1M queries per second
blog.mariadb.org
blog.mariadb.org
We been using it for a large dataset, and has been fantastic. Compared to MySQL 5.7 and PostgreSQL, it have the advantage of supporting the TokuDB engine out of box. My data uncompressed is 3TB, with it , we can fit in 300GB with all indexes. Read Free Replication with TokuDB (https://github.com/percona/tokudb-engine/wiki/Read-Free-Repl...) also enable us to have a very cheap VPS as slave.
Fractal tree indexes[1]... Dude!
The main point of TokuDB is not writing everything to the disk thanks to the buffers, and with the very efficient compression, more data fits in the memory and saves the space for big-data applications. ( please someone correct me if I´m wrong)
In our tests TokuDB was vital for our startup. With it, we can use cheap dedicated servers and our performance is amazing. We tried PostgreSQL, MongoDB 3.0 and MySQL 5.7 and they can´t fit our data in a 2TB disk or were slow in our tests.
Not ideal, but simple and valid enough.
Where TokuDB gets its speed boost is by delaying the random reads associated with updating the indexing structure (the Fractal Tree). The buffers are written to disk on checkpoint, but because they're buffers, the potentially random writes are localized to a smaller number of nodes high in the tree, which minimizes the number of disk seeks required. Since sequential I/O is cheaper than random, the sequential writes to the write-ahead log are very fast, so even in very strict durability configurations, TokuDB can easily outperform databases which use random writes to update the indexing structures, such as the B-trees used by InnoDB and most other RDBMSes.
More details here: https://www.percona.com/blog/2011/09/22/write-optimization-m...
http://highscalability.com/blog/2014/8/6/tokutek-white-paper...
Quote:"The idea behind FT(fractal Trees) indexes is to maintain a B tree in which each internal node of the tree contains a buffer. When a data record is inserted into the tree, instead of traversing the entire tree the way a B tree would, we simply insert the Eventually the root buffer will fill up with new data records. At that point the FT index copies the inserted records down a level of the tree. Eventually the newly inserted records will reach the leaves, at which point they are simply stored in a leaf node as a B tree would store them. The data records descending through the buffers of the tree can be thought of as messages that say “insert this record”. FT indexes can use other kinds of messages, such as messages that delete a record, or messages that update a record."
some other links:
http://web.archive.org/web/20150414215556/http://www.tokutek...
http://assets.en.oreilly.com/1/event/36/How%20TokuDB%20Fract...
An interesting new development is http://getkudu.io/ which applies some ideas common with Fractal Trees and other write-optimized data structures (like LSM-trees) to column stores.
At Tokutek, we had some designs for how to implement column store-like data models on Fractal Trees (we called them "matrix stores" because they had some of the benefits of both row stores and column stores), but we didn't get around to implementing them.
(The hot path was a single well-optimised query that retrieved all content for the page. The query didn't need to change.)
As it stands now, this content is now being handled by a single (and quite unremarkable) MariaDB slave and assembled on-the-fly like any other web page.
For anyone dealing with gigabytes of text content, I highly recommend giving it a go. You don't even need to screw with your master, just change the table type on the slave.
I am pretty sure the only other engines that support online DDL is Sybase and DB2.
Online DDL generally means lock free DDL operations.
Not even Oracle, MSSQL, or Postgresql do online DDL. But at least they have transactional DDL, even if it isn't entirely lock-free all the time.
Compared to InnoDB/XtraDB; TokuDB lacks foreign key support, because this was worked into InnoDB years ago because the MySQL server layer doesn't support any kind of constraints. I am hoping for MariaDB to start implementing more modern SQL features which would this be available in supported storage engines.
How does one ,by tunning a vanilla PostgreSQL (on a per instance or per database or per table basis) can get the same kind of advantages (and tradeoff) than in MySQL/MariaDB by switching from InnoDB to TokuDB ?
The closest thing I know of to achieve something comparable with PG would be to use a data directory mounted on something with transparent filesystem-level compression (meaning, practically speaking, ZFS). This gives you the less-than-ideal choice of a not-mainline-linux filesystem (for your database's data directory, which is worth being nervous about) or running an OpenSolaris descendant, which is a big departure for plenty of people who have only ever run production dbs on Linux servers.
BTRFS has been in the stable kernel for over two years, so it might be worth checking out!
ZFS is the only filesystem it's reasonable to trust critical data on right now (I can't think of any other OSS self-validating Merkle trees that have been hammered in production for nearly a decade...), yet somehow some minor differences in Unicies trumps that.
I'm going to make a bold claim I know will stir the nest, but I feel confident in making given all the bad shit most filesystems miss: if you're not running your OSS RDBMS on ZFS right now, and you don't have compelling specialized needs to explain why not, you shouldn't be let near a production DB due to plain negligence and/or incompetence.
You can also mix-and-match. You could use PostgreSQL's regular storage engine for some tables, and TokuDB for some others.
"Sadly, fractal trees are an invention of Tokutek and are heavily and publically patented."
Pretty frustrating. I was pretty excited since MariaDB is a drop in replacement for MySQL (more or less). But you have to use it on a totally different architecture which can't be justified without a lot of deliberation.
That's far from typical hardware to deploy maria/mysql on, and far from realistic workload.
1M transactions per second with 100 % reads might as well mean 10K transactions per second with 95 % readsm 5 % writes ("the usual OLTP workload" per common wisdom) and 1K transactions per second on 50 % reads 50 % writes. Or even less. Or much more. It's difficult to even quess.
That makes me wonder if a smaller number of tables would have hit contention even in a read only workload.
Well it's not the only database that has this problem. I wonder if partitioning is enough or if you really need separate tables.
These are changes that all architectures can benefit from.
[1]: http://mysqlserverteam.com/removing-scalability-bottlenecks-...
Pg, have a lot of tractions this days, I use it for quite a while, but I never use mariaDB. So, I will be quite curious about features wise :)
Since version 10.1, MariaDB has merged the "classic" server with the galera server. Galera promises easy-to-do scaling (see [1],[2] for details) which is nice to have.
Please read the linked info and decide for yourself if this feature sounds tempting enough to you.
[1] https://blog.mariadb.org/mariadb-10-1-1-galera-support/
[2] https://aphyr.com/posts/327-call-me-maybe-mariadb-galera-clu...
it's MySQL but not from Oracle.
> over PG
if you are already running MySQL then you don't have to migrate to a new completely new platform.
But I don't see any patches. Would love to see something like this developed.
Also isn't there cstore_fdw for PG for compression? Although I know there are limitaitons with this, which has prevented me from using it. :(
> Fractal tree indexes are patented. It is distributed as commercial extension to MySQL. So we can't include it into PostgreSQL core.
http://www.postgresql.org/message-id/CAPpHfdtV46zQjp88HR5U3h...
http://www.google.com/patents/US8185551
Regarding using the tech in OSS, there is an interesting discussion here: https://www.reddit.com/r/linux/comments/1hn7rg/whats_next_af... (see the comment from bradleykuszmaul a little ways down).
Column stores are only good for big reads and big inserts.