Hi Kristian, Kristian Nielsen wrote:
The poor performance of MySQL with 1 database is expected. However MySQL is much better with 32 databases. Even better than MariaDB :(
Ok, so I looked closer at the numbers (of which there were quite a few, thanks for doing this work :-). I have some idea of what could be going on.
The short story is that for this kind of configuration, it will be necessary to configure @@slave_domain_parallel_threads to be at most the value of @@slave_parallel_threads divided by the number of domain ids used. Eg. with 32 databases/domains, --slave-parallel-threads=64 --slave-domain-parallel-threads=2. Otherwise the domains will starve each other for threads, leading to poor parallelism.
OK, that is something a can easily add to the configuration. I'll schedule two more runs of the benchmark for that.
I think 32 databases is a bit too much, not only that MySQL then shines so bright, it's also unrealistic in the real world. What do you think? I can rerun the benchmark with 4 or 8 databases (will take 4-5 hours). Quite certainly MySQL will have worse numbers then :)
Well, I think 32 databases is not that unrealistic - shared hosting for example could have many more. The completely even spacing of the load among those databases however is probably unrealistic.
Right. Maybe I should have said: 32 domains that receive work rather evenly distributed is a bit unrealistic. In fact that would only hold if you shard a big dataset and then put all shards onto a single MySQL instance.
I also notice that for MariaDB 10.0 the tuning of the commit-wait-* variables is critical. Settings that give trx per commit group tend to slow down the master a lot. The default settings give only few transactions per
Right, this is something to be aware of.
Note that for a typical web client application or similar, --commit-wait-* probably will not be much of an issue. An individual client will see slightly higher latency, which should not be much of an issue if it is in the order of a few milliseconds.
I disagree. Many customers complain about poor single thread performance and not all of them mean the traditional single slave thread alone.
Throughput is not likely to be affected
The numbers say something different. I have now added a plot for this, I attach it to this mail. Setting commit-wait-usec to 10000 - already just 1/10th of the default - significantly reduces throughput - even with 64 benchmark clients. What I wonder: if there is only little concurrency, couldn't the server peek inside InnoDB how many transactions are there at all and skip any waits if there are only few trx? Simplest case: commit-wait-count=8, commit-wait-usec=10000. Only one client. Once that client requested a commit, there's no point to wait. More generally: if we want N commits per group and already have K<N, then the time to wait should decrease as K gets closer to N. And the time should fall to 0 if there are no more than K active transactions. XL
