0

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?

  • 1
    Why do you think that row-level [exclusive] locks will not block readers? – mustaccio Oct 7 '19 at 17:28
  • Will they? If a reader is reading a different row than the writer (which is what is likely to happen), then it shouldn't be blocked by the writer ideally. – Aarati K Oct 7 '19 at 18:32
  • That totally depends on how the reader goes about finding that different row. – mustaccio Oct 7 '19 at 18:55
  • Can you elaborate? InnoDB uses B+-Tree primary indexes internally, so all the data is present in the leaf nodes. So even if a row of a leaf node is locked, that ideally shouldn't block a reader accessing a different row. – Aarati K Oct 7 '19 at 21:05
  • ...unless that reader performs an index range scan, however small, or a table scan. – mustaccio Oct 7 '19 at 22:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.