
MariaDB 10.1 can do 1M queries per second - bratao
https://blog.mariadb.org/10-1-mio-qps/
======
bratao
MariaDB recently released the version 10.1 as GA.
[https://blog.mariadb.org/mariadb-10-1-is-stable-
ga/](https://blog.mariadb.org/mariadb-10-1-is-stable-ga/)

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...](https://github.com/percona/tokudb-engine/wiki/Read-Free-Replication-
with-TokuDB)) also enable us to have a very cheap VPS as slave.

~~~
swasheck
could you explain why the tokudb engine is an advantage?

~~~
bratao
TokuDB is a very modern engine that uses a data-structure called "Fractal
Trees". Imagine something like a B-Tree with buffers in the non-leaf nodes. It
also heavily compress the row data.

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.

~~~
tomcam
Not writing everything to disk ASAP makes me... queasy...

~~~
ddorian43
The log is always written, what's not written asap is changes on the tree
(changes go into the buffers first and then trickle down when buffers are
full).

~~~
leif
This is the critical insight. TokuDB uses a write-ahead log which is synced
according to the configuration, and can be made as immediate as full fsync on
commit. This provides the strongest durability available on a single machine.

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...](https://www.percona.com/blog/2011/09/22/write-optimization-
myths-comparison-clarifications/)

------
suneilp
This is sorta misleading. A quote from the article, "The changes in the
MariaDB source code leading to those impressive performance improvements are
part of porting MariaDB to Power8."

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.

~~~
glogla
It's also 2 socket, 20 core, 8 thread per core = 160 hardware thread system.
And it's not general queries, but just "point selects" from 20 tables of 1
million rows combined.

That's far from typical hardware to deploy maria/mysql on, and far from
realistic workload.

~~~
suneilp
Yeah, I want to drool but utilizing that many threads in "practical" scenarios
seems difficult. It would probably be great for messaging systems though!

~~~
glogla
Not just that, but database performance drops quickly with writes.

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.

~~~
arielweisberg
What stood out to me was that the measurement was taken against 20 tables.

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.

~~~
hyperpape
"The data set is 1 million rows in 20 tables. Fewer tables can be used, but
below 4 tables the performance drops somewhat due to a hot spot in the table
definition cache."

------
maxdemarzi
Another 1M q/s nonsense benchmark to add to the list :
[http://maxdemarzi.com/2015/10/16/benchmarks-and-
supercharger...](http://maxdemarzi.com/2015/10/16/benchmarks-and-
superchargers/)

------
claudiug
I'm curious, does anybody know what are the advantages over PG?

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 :)

~~~
jand
Ok, a big selling point of current versions is:

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/](https://blog.mariadb.org/mariadb-10-1-1-galera-support/)

[2] [https://aphyr.com/posts/327-call-me-maybe-mariadb-galera-
clu...](https://aphyr.com/posts/327-call-me-maybe-mariadb-galera-cluster)

~~~
ddorian43
Isn't scaling usually in the way of sharding? While in this case galera it's
just replication (master-master sycn), and you can only scale read access.

------
no1youknowz
I see that fractal tree indexing was brought up back in 2003 for PG.
[http://www.postgresql.org/message-
id/511B5F12.7060500@vmware...](http://www.postgresql.org/message-
id/511B5F12.7060500@vmware.com)

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. :(

~~~
laurencerowe
Further down that thread:

> 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.postgresql.org/message-
id/CAPpHfdtV46zQjp88HR5U3hw-Uw7xYWrVDGgyqOE6NWYKQrtPLA@mail.gmail.com)

~~~
jdc0589
wow... thats pretty depressing

~~~
rch
It's been a while - patents are designed to expire, and in this area they
sometimes actually do.

~~~
hyperpape
Sometimes? I wasn't aware there were exceptions.

------
programminggeek
How does this compare to the standard MySQL performance?

~~~
morgo
Comparable number is 1.6m on Intel hardware [http://www.mysql.com/why-
mysql/benchmarks/](http://www.mysql.com/why-mysql/benchmarks/)

------
exabrial
Is this 1M qps with no foreign keys and no transactions? There's quite a few
open source databases that can do that.

~~~
pbowyer
Definitely no foreign keys according to another post in this thread. Which is
not something I want to go back to, however much I'd like this level of
compression.

------
uberneo
no parallel processing / shrading yet?

