MySQL Bugs: #104967: The fuction buf_validate() affects performance seriously and can be optimized

Bug #104967	The fuction buf_validate() affects performance seriously and can be optimized
Submitted:	17 Sep 2021 10:04	Modified:	19 Nov 2021 19:40
Reporter:	hobert lu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	8.0.26	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
storage/innobase/buf/buf0buf.cc

buf_validate() {
  for (i = 0; i < srv_buf_pool_instances; i++) {
  ... ...
    buf_pool_validate_instance(buf_pool);
    ... ...
  }
}

buf_pool_validate_instance() {
  ...
  ut_a(buf_LRU_validate());
  ...
}

storage/innobase/buf/buf0lru.cc

buf_LRU_validate() {
  for (ulint i = 0; i < srv_buf_pool_instances; i++) {
    ...
    buf_LRU_validate_instance(buf_pool);
  }
}

the times that one lru_list will be validated is 'srv_buf_pool_instances',
when calling buf_validate().

a lru_list just need be validated one time when calling buf_validate()

if srv_buf_pool_instances is 8, the counter of validating lru_list is  64(8 * 8),
so the performance has been reduced

This algorithm's time complexity is O ( N * N )
  
   

How to repeat:
version: MySQL Community Server 8.0.26 - debug
my.cnf: innodb_adaptive_hash_index=OFF

CREATE DATABASE test_ibuf;
USE test_ibuf;
CREATE TABLE `test_ibuf_insert`(
  `test_title` VARCHAR(200) NOT NULL,
  `test_number` VARCHAR(200) NOT NULL
);

CREATE INDEX title ON test_ibuf_insert (test_title);
CREATE INDEX number ON test_ibuf_insert (test_number);

DELIMITER //;
CREATE PROCEDURE func_insert(IN max INT)
BEGIN
  DECLARE i INT;
  SET i=1;
  INSERT INTO test_ibuf_insert (test_title,  test_number) VALUES ('begin_insert', 'begin_insert');
  WHILE i<=max DO
    INSERT INTO test_ibuf_insert (test_title,  test_number) VALUES ('title_test', i);
  SET i=i+1;
  END WHILE;
  INSERT INTO test_ibuf_insert (test_title,  test_number) VALUES ('end_insert', 'end_insert');
END //
DELIMITER ;//

BEGIN;
CALL func_insert(100000);
COMMIT;

SELECT * from test_ibuf.test_ibuf_insert where test_title='title_test';

SELECT * from test_ibuf.test_ibuf_insert where test_title='title_test';

SELECT * from test_ibuf.test_ibuf_insert where test_title='title_test';

Suggested fix:
storage/innobase/buf/buf0buf.cc

buf_pool_validate_instance() {
  ...
  - ut_a(buf_LRU_validate());
  + buf_LRU_validate_instance(buf_pool);
  ...
}

storage/innobase/buf/buf0lru.cc

- static void buf_LRU_validate_instance(buf_pool_t *buf_pool) {
+ void buf_LRU_validate_instance(buf_pool_t *buf_pool) {

storage/innobase/include/buf0lru.h 

+ /** Validates the LRU list for one buffer pool instance.
+ @param[in]	buf_pool	buffer pool instance */
+ void buf_LRU_validate_instance(buf_pool_t *buf_pool);

This algorithm's time complexity is O ( N )

performance will be increased (srv_buf_pool_instances-1)/srv_buf_pool_instances

modify category

need feedback

Hello hobert lu,

Thank you for the report and feedback.
I'm still analyzing this(need to apply patch and confirm the improvement) and get back to you if anything further needed.
In the meantime, could you please share the config file(if not on default)? Thank you.

regards,
Umesh

Thank you for the feedback.
I haven't seen much improvements after applying your patch(I could be wrong, hence verifying so that it goes to Dev's radar). 

regards,
Umesh

MySQL 8.0.26 test results

Attachment: 104967_8.0.26.results (application/octet-stream, text), 529.26 KiB.

Please note that in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html.
If you have any questions, please contact the MySQL community team - https://dev.mysql.com/community/

Posted by developer:
 
Fixed as of the upcoming 8.0.28 release, and here's the proposed changelog entry from the documentation team:

The buf_validate() function in the InnoDB sources was optimized,
improving performance on debug builds. 

Thanks to Hobert Lu for the contribution.