[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?