maria-discuss team mailing list archive

[Aarati K <aarati.agc10@xxxxxxxxx>]

I'm benchmarking MariaDB 10.4 with TATP benchmark. The InnoDB config file
has the following parameters set:

[mysqld]
innodb_data_home_dir=/usr/local/mysql/data
innodb_undo_directory=/usr/local/mysql/data
innodb_undo_log_truncate=1
innodb_undo_tablespaces=3
innodb_flush_method=O_DIRECT
innodb_page_size=65536

When I run just the read-mix of TATP (i.e, the read queries alone), I get a
result as follows:

I 2019-10-06 16:06:06 STATISTICS 90% response time (us), by transaction
I 2019-10-06 16:06:06 STATISTICS GET_SUBSCRIBER_DATA: 744
I 2019-10-06 16:06:06 STATISTICS GET_NEW_DESTINATION: 1323
I 2019-10-06 16:06:06 STATISTICS GET_ACCESS_DATA: 738
I 2019-10-06 16:06:06 CONTROL MQTh for test run is 6772

Focusing on the first query, GET_SUBSCRIBER_DATA, it has a 90% response
time of 744us.

However, when I run the R90/W10 mix (i.e, 90% read requests, 10% write
requests), the result is as follows:

I 2019-10-07 01:23:27 STATISTICS 90% response time (us), by transaction
I 2019-10-07 01:23:27 STATISTICS GET_SUBSCRIBER_DATA: 3771
I 2019-10-07 01:23:27 STATISTICS GET_NEW_DESTINATION: 4712
I 2019-10-07 01:23:27 STATISTICS GET_ACCESS_DATA: 3687
I 2019-10-07 01:23:27 STATISTICS UPDATE_SUBSCRIBER_DATA: 18801
I 2019-10-07 01:23:27 STATISTICS UPDATE_LOCATION: 18831
I 2019-10-07 01:23:27 STATISTICS INSERT_CALL_FORWARDING: 20017
I 2019-10-07 01:23:27 STATISTICS DELETE_CALL_FORWARDING: 19027
I 2019-10-07 01:23:27 CONTROL MQTh for test run is 1464


The response time of GET_SUBSCRIBER_DATA is 3771 us, which is much slower.

Queries are generated probabilistically, and keys are chosen uniformly at
random. However, what I don't understand is, how are the writers slowing
down the readers by that much, especially when MariaDB/InnoDB has *row-level
locking*, to avoid this issue of writers blocking readers. Are there any
configuration parameters that need to be tweaked?