I'm benchmarking MariaDB 10.4 with TATP. 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 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 precisely this issue of writers blocking readers. Are there any configuration parameters that need to be tweaked?