Bug #87637 High CPU occupancy rate
Submitted: 1 Sep 2017 8:44 Modified: 18 Dec 2017 8:46
Reporter: Xiaoyu Wang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S5 (Performance)
Version:5.7.17 OS:Linux
Assigned to: CPU Architecture:Any
Tags: MySQL Community

[1 Sep 2017 8:44] Xiaoyu Wang
Description:
/* TENCENT:TDSQL Team */
We encountered two occasions which leaded to high CPU occupancy rate, both of them were related with buf_get_n_pending_read_ios():
1. case 1: mysqld does not do any work.
If mysqld process was scheduled frequently, even though there was no activity( no read io and no dirty-page to flush), CPU occupancy rate reached 100%. In other word, idling of process resulted in high CPU occupancy.

2. Case 2: mysqld does UPDATE operation for a varchar field.
When carrying out a performance test with sysbench, CPU occupancy was 130%-180%. buf_get_n_pending_read_ios() costed at least 2%, and buf_pool_from_array() costed, too.
    44.06%  mysqld              [.] lock_rec_has_to_wait_in_queue(ib_lock_t co
    12.69%  [vdso]              [.] 0x0000000000000600
     8.17%  mysqld              [.] buf_get_n_pending_read_ios()
     5.57%  sap1006-64-v50107   [.] GoodMD5Transform
     3.01%  libpthread-2.17.so  [.] pthread_mutex_unlock
     2.71%  mysqld              [.] ut_time_ms()
     2.68%  mysqld              [.] buf_flush_page_cleaner_coordinator

Even though CPU occupation is related to os, hardware and so on, the changes we made can decrease CPU occupancy observably on the same computer.

How to repeat:
case 1: os schedules mysql process frequently, and process idles.

case 2:
on mysql server: 
CREATE TABLE `sbtest1_varchar` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` varchar(300) NOT NULL DEFAULT '',
  `pad` varchar(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

DROP FUNCTION IF EXISTS rand_string;
delimiter $$  
CREATE FUNCTION rand_string(strlen INT)
RETURNS VARCHAR(299)
BEGIN
    DECLARE chars_str varchar(300) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(300) DEFAULT 'hc';
    DECLARE start INT DEFAULT 0;
    DECLARE len INT DEFAULT 0;
    DECLARE total_len INT DEFAULT 0;
    
    SET start = FLOOR(1 + RAND()*200 );
    SET total_len = start + strlen - 297;
    if total_len > 0 then 
        SET total_len = start + strlen - total_len - 10;
        SET return_str = concat(return_str,substring(chars_str , start, total_len));
    ELSE
        SET return_str = concat(return_str,substring(chars_str , start, strlen));
    end if;
    
    RETURN return_str;
END $$
DELIMITER ;

INSERT INTO sbtest1_varchar VALUES(1,1,'cccccccc','padpad');

sysbench script:
   local table_name
   local strlen
   table_name = "sbtest1_varchar"
   strlen = sb_rand(1, 299)
   rs = db_query("UPDATE ".. table_name .." SET c= rand_string("..strlen..") WHERE id=1")

Suggested fix:
To suggest to fix this bug. 
We tryed to fix it with follow patch. After applying, CPU occupancy rate in 2 occasions were decreased observably.
case 1: CPU occupancy rate was basically about 0-4%, sometimes 6%
case 2: CPU occupancy rate was about 99%-101%. buf_get_n_pending_read_ios() and buf_pool_from_array() didn’t cost so much as they have done.
    85.20%  mysqld              [.] lock_rec_has_to_wait_in_queue(ib_lock_t const*)
    6.05%  mysqld               [.] lock_rec_dequeue_from_page(ib_lock_t*)
    4.45%  mysqld               [.] RecLock::lock_add(ib_lock_t*, bool)
    3.23%  mysqld               [.] lock_rec_add_to_queue(unsigned long, buf_block_t const*, unsigned long, dict_index_t*, trx_t*, bool) [clone .constprop.171]
    0.13%  mysqld               [.] lock_rec_get_first(hash_table_t*, buf_block_t const*, unsigned long)

patch as follows:
diff -uprN ./mysql-5.7.17/sql/mysqld.cc ./mysql-5.7.17.new/sql/mysqld.cc
--- ./mysql-5.7.17/sql/mysqld.cc	2016-11-28 21:32:52.000000000 +0800
+++ ./mysql-5.7.17.new/sql/mysqld.cc	2017-09-01 11:02:17.000000000 +0800
@@ -483,6 +483,9 @@ bool host_cache_size_specified= false;
 bool table_definition_cache_specified= false;
 ulong locked_account_connection_count= 0;
 
+/* TDSQL: reduce CPU occupancy */
+ulong idle_sleep_us; /*!< time to sleep(us) when idle */
+
 /**
   Limit of the total number of prepared statements in the server.
   Is necessary to protect the server against out-of-memory attacks.
diff -uprN ./mysql-5.7.17/sql/mysqld.h ./mysql-5.7.17.new/sql/mysqld.h
--- ./mysql-5.7.17/sql/mysqld.h	2016-11-28 21:32:52.000000000 +0800
+++ ./mysql-5.7.17.new/sql/mysqld.h	2017-09-01 11:01:20.000000000 +0800
@@ -974,4 +974,7 @@ static inline THD *_current_thd(void)
 
 #define ER(X)         ER_THD(current_thd,X)
 
+/* TDSQL: reduce CPU occupancy */
+extern ulong idle_sleep_us; /*!< time to sleep(us) when idle */
+
 #endif /* MYSQLD_INCLUDED */
diff -uprN ./mysql-5.7.17/sql/sys_vars.cc ./mysql-5.7.17.new/sql/sys_vars.cc
--- ./mysql-5.7.17/sql/sys_vars.cc	2016-11-28 21:32:52.000000000 +0800
+++ ./mysql-5.7.17.new/sql/sys_vars.cc	2017-09-01 11:05:01.000000000 +0800
@@ -2402,6 +2402,14 @@ static Sys_var_uint Sys_pseudo_thread_id
        BLOCK_SIZE(1), NO_MUTEX_GUARD, IN_BINLOG,
        ON_CHECK(check_has_super));
 
+/* TDSQL: reduce CPU occupancy */
+static Sys_var_ulong Sys_global_sleep_us(
+       "idle_sleep_us", "used in buf/buf0flu.cc",
+       GLOBAL_VAR(idle_sleep_us), CMD_LINE(OPT_ARG),
+       VALID_RANGE(0, 1000000), DEFAULT(10000), BLOCK_SIZE(1),
+       NO_MUTEX_GUARD, NOT_IN_BINLOG,
+       ON_CHECK(0), ON_UPDATE(0));
+
 static bool fix_max_join_size(sys_var *self, THD *thd, enum_var_type type)
 {
   SV *sv= type == OPT_GLOBAL ? &global_system_variables : &thd->variables;
diff -uprN ./mysql-5.7.17/storage/innobase/buf/buf0buf.cc ./mysql-5.7.17.new/storage/innobase/buf/buf0buf.cc
--- ./mysql-5.7.17/storage/innobase/buf/buf0buf.cc	2016-11-28 21:32:52.000000000 +0800
+++ ./mysql-5.7.17.new/storage/innobase/buf/buf0buf.cc	2017-09-01 11:27:04.000000000 +0800
@@ -321,6 +321,9 @@ static const ulint	BUF_READ_AHEAD_PORTIO
 /** The buffer pools of the database */
 buf_pool_t*	buf_pool_ptr;
 
+/* TDSQL: reduce CPU occupancy */
+MY_BITMAP  buf_pend_reads;
+
 /** true when resizing buffer pool is in the critical path. */
 volatile bool	buf_pool_resizing;
 
@@ -1949,6 +1952,10 @@ buf_pool_init(
 	buf_pool_ptr = (buf_pool_t*) ut_zalloc_nokey(
 		n_instances * sizeof *buf_pool_ptr);
 
+	/* TDSQL: reduce CPU occupancy */
+	bitmap_init(&buf_pend_reads, 0, n_instances, 0);
+	bitmap_clear_all(&buf_pend_reads);
+
 	buf_chunk_map_reg = UT_NEW_NOKEY(buf_pool_chunk_map_t());
 
 	for (i = 0; i < n_instances; i++) {
@@ -1990,6 +1997,9 @@ buf_pool_free(
 
 	ut_free(buf_pool_ptr);
 	buf_pool_ptr = NULL;
+
+	/* TDSQL: reduce CPU occupancy */
+	bitmap_free(&buf_pend_reads);
 }
 
 /** Reallocate a control block.
@@ -5116,6 +5126,9 @@ buf_page_init_for_read(
 	void*		data;
 	buf_pool_t*	buf_pool = buf_pool_get(page_id);
 
+	/* TDSQL: reduce CPU occupancy */               
+	ulint buffer_pool_index = buf_pool_index(buf_pool);
+
 	ut_ad(buf_pool);
 
 	*err = DB_SUCCESS;
@@ -5316,6 +5329,8 @@ buf_page_init_for_read(
 		mutex_exit(&buf_pool->zip_mutex);
 	}
 
+	/* TDSQL: reduce CPU occupancy */
+	bitmap_set_bit(&buf_pend_reads, buffer_pool_index);
 	buf_pool->n_pend_reads++;
 func_exit:
 	buf_pool_mutex_exit(buf_pool);
@@ -5624,6 +5639,12 @@ buf_mark_space_corrupt(
 	ut_ad(buf_pool->n_pend_reads > 0);
 	buf_pool->n_pend_reads--;
 
+/* TDSQL: reduce CPU occupancy */
+	if (buf_pool->n_pend_reads == 0) {
+		ulint buffer_pool_index = buf_pool_index(buf_pool);
+		bitmap_clear_bit(&buf_pend_reads, buffer_pool_index);
+	}
+
 	buf_pool_mutex_exit(buf_pool);
 
 	return(ret);
@@ -5854,6 +5875,13 @@ corrupt:
 
 		ut_ad(buf_pool->n_pend_reads > 0);
 		buf_pool->n_pend_reads--;
+
+		/* TDSQL: reduce CPU occupancy */
+		if (buf_pool->n_pend_reads == 0) {
+			ulint buffer_pool_index = buf_pool_index(buf_pool);
+			bitmap_clear_bit(&buf_pend_reads, buffer_pool_index);
+		}
+
 		buf_pool->stat.n_pages_read++;
 
 		if (uncompressed) {
@@ -6498,19 +6526,17 @@ buf_get_latched_pages_number(void)
 #endif /* UNIV_DEBUG */
 
 /*********************************************************************//**
-Returns the number of pending buf pool read ios.
-@return number of pending read I/O operations */
+Returns whether there exists buf pool whose pending read ios is not 0.
+@return whether any of buf_pend_reads.bitmap is not 0 */
 ulint
 buf_get_n_pending_read_ios(void)
 /*============================*/
 {
-	ulint	pend_ios = 0;
-
-	for (ulint i = 0; i < srv_buf_pool_instances; i++) {
-		pend_ios += buf_pool_from_array(i)->n_pend_reads;
+	for (ulint i = 0; i < bitmap_buffer_size(buf_pend_reads.n_bits) / sizeof(my_bitmap_map); i++) {
+		if (buf_pend_reads.bitmap[i])
+		return 1;
 	}
-
-	return(pend_ios);
+	return 0;
 }
 
 /*********************************************************************//**
diff -uprN ./mysql-5.7.17/storage/innobase/buf/buf0flu.cc ./mysql-5.7.17.new/storage/innobase/buf/buf0flu.cc
--- ./mysql-5.7.17/storage/innobase/buf/buf0flu.cc	2016-11-28 21:32:52.000000000 +0800
+++ ./mysql-5.7.17.new/storage/innobase/buf/buf0flu.cc	2017-09-01 11:28:09.000000000 +0800
@@ -3357,6 +3357,9 @@ DECLARE_THREAD(buf_flush_page_cleaner_co
 		} else {
 			/* no activity, but woken up by event */
 			n_flushed = 0;
+			/* TDSQL: reduce CPU occupancy */
+			if (idle_sleep_us != 0)
+				os_event_wait_time_low(buf_flush_event, idle_sleep_us, sig_count);
 		}
 
 		ut_d(buf_flush_page_cleaner_disabled_loop());
diff -uprN ./mysql-5.7.17/storage/innobase/buf/buf0rea.cc ./mysql-5.7.17.new/storage/innobase/buf/buf0rea.cc
--- ./mysql-5.7.17/storage/innobase/buf/buf0rea.cc	2016-11-28 21:32:52.000000000 +0800
+++ ./mysql-5.7.17.new/storage/innobase/buf/buf0rea.cc	2017-09-01 11:41:09.000000000 +0800
@@ -86,6 +86,12 @@ buf_read_page_handle_error(
 	ut_ad(buf_pool->n_pend_reads > 0);
 	buf_pool->n_pend_reads--;
 
+	/* TDSQL: reduce CPU occupancy */
+	if (buf_pool->n_pend_reads == 0) {
+		ulint buffer_pool_index = buf_pool_index(buf_pool);
+		bitmap_clear_bit(&buf_pend_reads, buffer_pool_index);
+	}
+
 	buf_pool_mutex_exit(buf_pool);
 }
 
diff -uprN ./mysql-5.7.17/storage/innobase/include/buf0buf.h ./mysql-5.7.17.new/storage/innobase/include/buf0buf.h
--- ./mysql-5.7.17/storage/innobase/include/buf0buf.h	2016-11-28 21:32:52.000000000 +0800
+++ ./mysql-5.7.17.new/storage/innobase/include/buf0buf.h	2017-09-01 11:08:25.000000000 +0800
@@ -41,6 +41,8 @@ Created 11/5/1995 Heikki Tuuri
 #include "srv0srv.h"
 #include <ostream>
 
+#include "my_bitmap.h"
+
 // Forward declaration
 struct fil_addr_t;
[1 Sep 2017 8:49] Xiaoyu Wang
patch

Attachment: high_CPU_occupancy_rate.patch (application/octet-stream, text), 6.68 KiB.

[1 Sep 2017 15:06] Sinisa Milivojevic
Hi!

First of all, thank you for your bug report and for your patch. Unfortunately, we can not accept your patch until you become our OCA contributor. If you wish to become a contributor, please, visit this page:

 http://www.oracle.com/technetwork/community/oca-486395.html

Regarding the bug itself, based on the patch that you have sent us, it seems to be a tuning problem. Please, try to follow our manual and try to make a combination of the following startup variables which would make a problem go away.

Before doing that, please make sure that you are using our binary.

This is the list of the most important variables that you should tune:

innodb_log_file_size 
innodb_log_files_in_group 
innodb_open_files

innodb_buffer_pool_instances 
innodb_log_buffer_size

innodb_checksums 
innodb_doublewrite
innodb_support_xa 
innodb_thread_concurrency 
innodb_flush_log_at_trx_commit 
 innodb_flush_method 
innodb_max_dirty_pages_pct

innodb_adaptive_flushing 
innodb_read_io_threads 
innodb_write_io_threads
innodb_io_capacity 
innodb_io_capacity_max
innodb_purge_threads
innodb_use_native_aio
innodb_stats_persistent
innodb_checksum_algorithm
innodb_stats_on_metadata
innodb_lock_wait_timeout
innodb_flush_neighbors
innodb_spin_wait_delay

We have had many reports of high CPU usage and in almost all of these cases tuning of the above variables solved the problem.

You should find a combination of the values and if that does not work we shall have to look further.

In that case, we need all the info of the sysbench script that you have run, including how did you run sysbench, how many threads and all other options .....
[4 Sep 2017 6:18] Xiaoyu Wang
Hi!

Thanks for your replying and sorry for my ambiguous expression. We believe tuning can better CPU usage, but what we are concentrated on may be another direction.Please allow me to make it clear. The complete info of sysbench script will be at the end part.

case 1: Corresponding source code is at “buf0flu.cc: 3092”(version 5.7.17). 
      In situation that There is no read or write operation, mysql is not busy. We attached two counters, one for a while-loop(buf0flu.cc: 3181), and one for an else-branch(buf0flu.cc: 3357). Result showed that else_count was almost as the same as loop_count. Thus, we can avoid idling by make mysql sleep when it is not busy. The sleep time should be no larger than 1 second, and at present, default sleep time is 0.01 seconds. We want mysql sleep for a little while, and be ready for read/write operation at most time.

case 2: According to what “perf” shows, we look into “buf_get_n_pending_read_ios()”. And it contains a “srv_n_instances” times loop. “buf_get_n_pnding_read_ios()” costs because, on one hand, it’s called whenever mysql is scheduled, on the other hand, every time it’s called, loop has to be finished. Instead of loop, MY_BITMAP costs less.
        Compared to “buf_get_n_pending_read_ios()”, “lock_rec_has_to_wait_in_queue” occupies much more. And this can be next problem to solve.

sysbench info:
how we run script:

./sysbench --num-threads=100 --test=./tests/db/update_index.lua.varchar    --oltp-table-size=1  --oltp-tables-count=1  --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 --oltp-order-ranges=0 --oltp-index-updates=1 --oltp-non-index-updates=0  --report-interval=1 --mysql-host=my_host   --mysql-port=my_port  --mysql-user=test --mysql-password=test --mysql-db=test_db  --max-time=360000  --max-requests=2000000000  run

script update_index.lua.varchar:

pathtest = string.match(test, "(.*/)") or ""

dofile(pathtest .. "common.lua")

function thread_init(thread_id)
   set_vars()
end

function event(thread_id)
   local table_name
   local strlen
   table_name = "sbtest1_varchar"
   strlen = sb_rand(1, 299)
   rs = db_query("UPDATE ".. table_name .." SET c= rand_string("..strlen..") WHERE id=1")
end
[5 Sep 2017 1:52] Xiaoyu Wang
After applying the patch, CPU usage decreased observably . We monitored it for about 1 minute, and here statistic is.
case 1: It toke up 40%-100% before applying this patch, and 0-4% after.
case 2: It toke up 100%-180% variably before, and 100% steadily.

Additionally, I submitted application to become a contributor.
[11 Sep 2017 22:04] Bogdan Kecman
Hi,

looking at your example and your fix, you are trying to hack a solution for what thread_pool plugin solves gracefully.

Please look at: https://dev.mysql.com/doc/refman/5.7/en/thread-pool.html

all best
Bogdan
[12 Sep 2017 2:20] Xiaoyu Wang
Hi,
Thanks for your reply. 
We also tested with Percona, which supports thread_pool, and CPU usage is still so high. And based on statistic of counters, we think idling is the key. We do not see the relation between thread_pool and this bug, we do not know why and how thread_pool solves, so, could you please explain in detail? The statistic of counters follows.

Meanings of variables:
start_time, end_time: in million second, used to determine an interval
interval: in million second, increase counters during it
loop_count: times of stepping into the while-loop which is located in buf0flu.cc : 3181
else_count: times of steeping into the else-branch which is located in buf0flu.cc : 3357 

Before applying patch:
Almost all statistic is like
2017-08-31T09:45:49.176001+08:00 0 [Note] InnoDB: start_time= 1504143949175 end_time= 1504143949176 interval= 1 loop_count= 7082 else_count= 7082
Only 1/20 or less statistic is like
2017-08-31T09:45:50.191131+08:00 0 [Note] InnoDB: start_time= 1504143949191 end_time= 1504143950191 interval= 1000 loop_count= 1 else_count= 0

After applying patch:
All statistic is like
2017-08-30T20:00:21.053916+08:00 0 [Note] InnoDB: start_time= 1504094419053 end_time= 1504094421053 interval= 2000 loop_count= 58 else_count= 56

In addition, MySQL Enterprise solves with thread_pool, MySQL Community may solve with this patch.

Yours Sincerely,
Xiaoyu Wang
[12 Sep 2017 18:24] Sinisa Milivojevic
Hi!

Thread Pool Plugin does MUCH more then what you are suggesting here ...... 

Whether community version would get some improvements or not is a decision to be made on much higher level.

Meanwhile, you can improve things significantly by tuning in the following variables:

innodb_thread_concurrency 
innodb_flush_log_at_trx_commit 
innodb_flush_method 
innodb_max_dirty_pages_pct
innodb_adaptive_flushing 
innodb_read_io_threads 
innodb_write_io_threads
innodb_io_capacity 
innodb_io_capacity_max
innodb_purge_threads
innodb_spin_wait_delay

Tuning in those variables, so that they are optimised for your hardware, OS, schemata and your SQL statements  can bring you much more benefit then the patch that you have submitted.
[13 Sep 2017 4:02] Xiaoyu Wang
Hi,
Maybe tuning solves, but sidestepping this problem is not right, as the idling matters, after all. There may be relation between idling and variables mentioned before, could you please make it clear? If tuning solves, show us your configuration and comparassion of results, please.
Applying this patch without tuning is effective, just as statistic shows, and if we can combine tuning and applying patch, it could perform better. About using this patch, I've signed oca and been waiting for reply.
Thanks
[13 Sep 2017 13:24] Bogdan Kecman
Hi,

When you finalize the OCA process your code will go to trough the contribution process with the dev team. I agree with Sinisa that these can be solved by retuning the config but the dev team might find your contribution of use, after you finalize the OCA application.

Thanks
Bogdan
[13 Sep 2017 15:16] Sinisa Milivojevic
Hi!

Exchanging data about tuning, would not help at all. Tuning is separate for each installation and has to take into account :

* Operating System and its version
* Versions of system software
* Tuning of OS
* MySQL version and make
* Plugins in use, like GR
* Schemata
* Replication configuration
* SQL statements and applications in general

That is why each installations requires different configuration and optimisation.

I am working on the machine with 8 cores, using SSD.  Its configuration would slow down your installation considerably.
[14 Sep 2017 4:01] Xiaoyu Wang
Hi,
Although the same configuration performs differently on different machine, it would help us understand the meaning of tuning. So, paste it, please.
More importantly, it is idling which causes high CPU occupancy, as statistic shows. I agree that tuning solves most of problems in a general way, but if we find the essense of problem, why do not suit the remedy to the case? If tuning solves, tell us whether it influences idling, please.If not, tuning covers up instead of solving.
About case2, ignored for long, a bitmap performs better than add operation, without doubt. CPU usage decreases from 130-180% to 100%, observably. Please take it into consideration.
[14 Sep 2017 15:05] Sinisa Milivojevic
Here is one example for you ... This is an excerpt from my.cnf. I can not provide you with the entire configuration file as it contains some internal info, for example, sensitive directories and security protocols:

read_buffer_size = 4M
sort_buffer_size = 4M
table_definition_cache = 3000
table_open_cache = 30000
tmp_table_size = 64M
wait_timeout = 300

innodb_adaptive_hash_index = 0
innodb_purge_batch_size = 300 
innodb_adaptive_flushing = 1
innodb_stats_on_metadata = 0
innodb_file_per_table
innodb_flush_log_at_trx_commit = 2
innodb_support_xa = 1
innodb_io_capacity = 2000
innodb_lock_wait_timeout = 50
innodb_log_buffer_size = 32M
innodb_buffer_pool_size = 161G
innodb_buffer_pool_instances = 64
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_max_dirty_pages_pct = 80
innodb_open_files = 3000
innodb_thread_concurrency = 32
innodb_read_io_threads=16
innodb_write_io_threads=8
innodb_thread_sleep_delay=100000
innodb_use_native_aio=off
innodb_purge_threads=2
innodb-spin-wait-delay=512

But, it is just a configuration on one specific installation. You should not use it even for guidance, as it may harm your performance and even make your server unusable or even crash it.

In short, you have to find the best configuration for your system, not copy something that works well on a totally different installation.
[19 Sep 2017 9:44] Xiaoyu Wang
Please keep it open, before we reach a consensus. Thanks.
[19 Sep 2017 10:39] Xiaoyu Wang
Hi,
I tried tuning some variables. Result is as follows.
case 1: loop_count and else_count decreased by about 0.5%, and the number is still too large. Process still idled nearly all the time. 
case 2: CPU usage reached 200%, even.
I think, tuning costs a lot of time, and may cause potential problems. Next time we encounter another problem, we tune again? How can we guarantee a new solution will not revive a solved problem? Experts may solve it perfectly, but MySQL is generally used, if we can solve within source code,  we should not leave it to users.
We know why CPU usage is so high, based on statistic. We know how to fix it.Take it into your consideration, please.

In addition, it is the 4th time to re-open this bug. Before consensus is reached, do not close this bug, please. 

Thank you
[19 Sep 2017 11:01] Bogdan Kecman
Hi,

Not sure what consensus you are talking about? Bug is not "closed", I set the "not a bug" status, that's all.

As for the config optimization, bugs system is not a place to discuss / train / test optimization methods. You can discuss optimization methods on MySQL forum ( https://forums.mysql.com/ ) or you can contact our MySQL Support team and discuss proper configuration optimization with them, or find any number of 3rd party consulting agencies to help you deal with it; in any way the bugs db is not the place to discuss "what's the best way to optimize your mysql config".

As for the patch, I tried it in 5 different scenarios and it doesn't make "my properly configured, for my needs, system" better in any way (faster, less cpu usage, less memory usage etc etc.. none of those were achieved) so I agree with Sinisa's assessment.

With regards to "Experts may solve it perfectly, but MySQL is generally used, if we can solve within source code,  we should not leave it to users"; I do not agree. Config is there so you can tweak the system to your needs. If you don't know how there are DBA's for hire or consultants, support engineers etc. Making forceful changes into source code forcing stuff is wrong path that I doubt we will ever make.

kind regards
Bogdan
[20 Sep 2017 3:35] Xiaoyu Wang
Hi,
Sorry for my misunderstanding about 'closed' and 'not a bug'.
The consensus I'm talking about is the reason why tuning works, and the relation between tuning and idling. Because idling causes high CPU and I do not find that tuning reduces it.
About the patch. buf_get_n_pending_read_ios() is always called as a 'bool' condition in if-clause, so replacing the while-loop with bitmap is theoretically better and our test result shows that it practically makes difference. 

As for "Making forceful changes...", we find the cause and fix it, so we can not call it making forceful changes.Idling is the major cause, and this patch reduce idling ,as statistic shows. That's why we say this patch works. Maybe you can count how many times it idles before and after applying this patch.
 
By the way, it is the 5th time to open this bug. Please do not set it "not-a-bug", because this performace problem is not solved by tuning, and relation between uning and idling is not clear.

Thanks a lot
[20 Sep 2017 4:50] Sunny Bains
From what I understand the patch helps in short circuiting the loop, skipping buffer pool instances that don't have any pending IO. This makes sense. Scanning a large number of instances where only a few have pending IO is a waste of CPU.

Xiaoyu, is my understanding correct?
[20 Sep 2017 5:09] Xiaoyu Wang
Thanks, that's exactly what I mean. And that is about case2.
As for case1, we find idling causes high CPU usage.
Best wishes
[20 Sep 2017 7:27] Xiaoyu Wang
Hi,
This bug is result from two aspects.
First, when there is nothing to do(no activity, no pending read ios, no page to flush,as shown in buf0flu.cc:3213), it steps into an else-branch(buf0flu.cc:3375), does nothing and continues looping. We attach two counters to record how many times it steps into the while-loop(buf0flu.cc:3208) and else-branch(buf0flu.cc:3375), and the result shows that the process steps into the else-branch, almost all the time. So, we want it sleep for a little while, but we are not absolutely sure about how long it should sleep. If the process sleeps too long, it won't be as responsive as it is now. As we understand, work cycle is 1 second, so we make it sleep for 0.01s, so that 99% of the time is preserved for responsing. And the time to sleep is adjustable, as a variable 'idle_sleep_us' is attached.
Second, as Sunny understands, with bitmap, we reduce the loop times from '<=64' to '<= 2'. Considering that buf_get_n_pending_read_ios is called frequently, bitmap makes difference.
We have reached a consensus about 2nd aspect, please take 1st part into your consideration. Thanks a lot.
Sincerely
Xiaoyu
[20 Sep 2017 23:57] Bogdan Kecman
I'm verifying this and after OCA is signed you can push this as a contribution so dev team can then decide how to integrate it.

all best
Bogdan
[26 Sep 2017 2:05] Xiaoyu Wang
Hi Bogdan, 
My OCA gets approved, and I am honored to be able to contribute.Could you please let me know how to push the patch as a contribution?
Thank you, sincerely
Xiaoyu
[28 Sep 2017 4:14] Bogdan Kecman
> Could you please let me know how to push the patch as a contribution?

Hi, It's already done by us so it should show up on the dev contribution report soon.

Thank you
Bogdan
[17 Nov 2017 4:07] Xiaoyu Wang
patch for solving high CPU usage

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: high_CPU_occupancy_rate.patch (application/octet-stream, text), 6.68 KiB.

[18 Dec 2017 8:00] Allen Lai
Hi Xiaoyu,

I'm trying to merge your patch to InnoDB code. And trying to verify this fix.
But I have question, that is why you need to modify function buf_get_n_pending_read_ios?

Seems your modification will cause the return value to be different.

BTW, would you please give us your contact information? Just for efficient communication. 

Thanks,
Allen.
[18 Dec 2017 8:46] Xiaoyu Wang
Hi Allen,
Thanks for your replying.
We find that function buf_get_n_pending_read_ios is only called in if-clauses, and we care about whether the return value is 0 or not. So, whether the return value  is 1, 2 or 3 does not matter. 
That's why we do not need to calculate the total number of n_pend_reads, as well as the reason why using a bitmap is acceptable.
Please contact me through the email 934109129@qq.com