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
<<inline: commit-wait-tuning.gif>>
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
