HIGH CPU usage

[xiaoyuwang]

Percona-Server-5.7.17-11
We find buf_get_n_pending_read_ios is related to 2 high CPU usage occasions.
We'll make breif, and "https://bugs.mysql.com/bug.php?id=87637" for "how to repeat".

case 1: If mysqld process is scheduled frequently, even though there is no activity, no read io, no dirty-page to flush, CPU usage reaches 100%.
    We count times the process steps into the while-loop at buf0flu.cc:3208 and the else-branch(means there is no activity, the process idles) at buf0flu.cc:3375. And the statistic is as follows.

    Almost all statistic is like
   start_time= 1504143949175 end_time= 1504143949176 interval= 1ms loop_count= 7082 else_count= 7082
    Only 1/20 or less statistic is like
   start_time= 1504143949191 end_time= 1504143950191 interval= 1000ms loop_count= 1 else_count= 0

   We see, everytime this process get scheduled, it idles mostly(in the case of there is no activity). We fix it by making this process sleep for a little while(0.01 second, by default) when there is no work, and CPU usage is decreased to 0-4%.

case 2: We use sysbench to test updating a varchar field, and CPU usage is about 130%-180%. buf_get_n_pending_read_ios takes up observably, as follows.
    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
    We find that buf_get_n_pending_read_ios is used as a bool condition, so, calculating pend_ios accurately, which results in a srv_buf_pool_instances-times-loop ,is not necessary. Instead, we can use bitmap to indicate whether buf_pool->n_pend_reads is 0, and that costs less. After applying this, CPU usage is reduced to 100%.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3741