maria-developers team mailing list archive

[Axel Schwenke <axel@xxxxxxxxxxxx>]

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