Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21212

buf_page_get_gen -> buf_pool->stat.n_page_gets++ is a cpu waste (0.5-1%)

    XMLWordPrintable

Details

    Description

      Even on a 20 cpu Intel(R) Xeon(R) CPU E5-2667 v4 @ 3.20GHz this single increment shows up significantly in a performance profile:

      In a TPC-C test (10.5.0-523879dd51c3bf2ad23295852304531d856cc869)

      perf report -g --no-children -i mariadb-10.5.0-event_warmup.g.perf

      +    5.30%  mysqld   mysqld               [.] rec_get_offsets_func                                                                                                                                                                                                                                                 ▒
      -    4.36%  mysqld   mysqld               [.] buf_page_get_gen                                                                                                                                                                                                                                                     ▒
         + 2.23% 0                                                                                                                                                                                                                                                                                                       ▒
           0.82% buf_page_get_gen                                                                                                                                                                                                                                                                                        ▒
         - 0.79% 0x1                                                                                                                                                                                                                                                                                                     ▒
            - 0.75% ha_innobase::records_in_range                                                                                                                                                                                                                                                                        ▒
                 buf_page_get_gen                                                                                                                                                                                                                                                                                        ▒
      +    3.46%  mysqld   mysqld               [.] l_find                                                                                                                                                                                                                                                               ▒
      +    3.18%  mysqld   mysqld               [.] MYSQLparse                                                                                                                                                                                                                                                           ▒
      +    2.68%  mysqld   mysqld               [.] cmp_dtuple_rec_with_match_low                                                                                                                                                                                                                                        ▒
      +    2.41%  mysqld   mysqld               [.] page_cur_search_with_match           
      

      As the second highest CPU consuming function, the increment shows up in ~16% of that time.

      annotate on bug_page_get_gen

             │      _Z16buf_page_get_gen9page_id_tmmP11buf_block_tmPKcjP5mtr_tP7dberr_tb():                                                                                                                                                                                                                              ▒
             │      ↓ je     c311db <buf_page_get_gen(page_id_t, unsigned long, unsigned long, buf_block_t*, unsigned long, char const*, unsigned int, mtr_t*, dberr_t*, bool)+0xbb>                                                                                                                                     ▒
             │                      *err = DB_SUCCESS;                                                                                                                                                                                                                                                                   ▒
             │        movl   $0x0,(%rsi)                                                                                                                                                                                                                                                                                 ▒
             │      #endif /* UNIV_DEBUG */                                                                                                                                                                                                                                                                              ▒
             │                                                                                                                                                                                                                                                                                                           ▒
             │              ut_ad(!mtr || !ibuf_inside(mtr)                                                                                                                                                                                                                                                              ▒
             │                    || ibuf_page_low(page_id, zip_size, FALSE, file, line, NULL));                                                                                                                                                                                                                         ▒
             │                                                                                                                                                                                                                                                                                                           ▒
             │              buf_pool->stat.n_page_gets++;                                                                                                                                                                                                                                                                ▒
        1.08 │  bb:   mov    0x10(%rsp),%rbx                                                                                                                                                                                                                                                                             ▒
             │        add    0x60(%rsp),%ecx                                                                                                                                                                                                                                                                             ▒
             │      ut_hash_ulint():                                                                                                                                                                                                                                                                                     ▒
             │              ulint    table_size)    /*!< in: hash table size */                                                                                                                                                                                                                                          ▒
             │      {                                                                                                                                                                                                                                                                                                    ▒
             │              ut_ad(table_size);                                                                                                                                                                                                                                                                           ▒
             │              key = key ^ UT_HASH_RANDOM_MASK2;                                                                                                                                                                                                                                                            ▒
             │                                                                                                                                                                                                                                                                                                           ▒
             │              return(key % table_size);                                                                                                                                                                                                                                                                    ▒
             │        xor    %edx,%edx                                                                                                                                                                                                                                                                                   ▒
             │      _Z16buf_page_get_gen9page_id_tmmP11buf_block_tmPKcjP5mtr_tP7dberr_tb():                                                                                                                                                                                                                              ▒
             │              ulint           retries = 0;                                                                                                                                                                                                                                                                 ▒
             │        movq   $0x0,0x38(%rsp)                                                                                                                                                                                                                                                                             ▒
             │              buf_pool->stat.n_page_gets++;                                                                                                                                                                                                                                                                ▒
       16.52 │        addq   $0x1,0x188(%rbx)                                                                                                                                                                                                                                                                            ▒
             │      buf_page_hash_lock_get():                                                                                                                                                                                                                                                                            ▒
             │      /** Get appropriate page_hash_lock. */                                                                                                                                                                                                                                                               ▒
             │      UNIV_INLINE                                                                                                                                                                                                                                                                                          ▒
             │      rw_lock_t*                                                                                                                                                                                                                                                                                           ▒
             │      buf_page_hash_lock_get(const buf_pool_t* buf_pool, const page_id_t& page_id)                                                                                                                                                                                                                         ▒
             │      {                                                                                                                                                                                                                                                                                                    ▒
             │              return hash_get_lock(buf_pool->page_hash, page_id.fold());                                                                                                                                                                                                                                   ▒
        0.36 │        mov    0xb0(%rbx),%rsi                                                                                                                                                                                                                                                                             ▒
             │      ut_hash_ulint():                                                                                                                                                                                                                                                                                     ▒
             │              key = key ^ UT_HASH_RANDOM_MASK2;                                                                                                                                                                                                                                                            ▒
             │        mov    %ecx,%eax                                                                                                                                                                                                                                                                                   ▒
             │        movzbl 0x18(%rsp),%ecx                                                                                                                                                                                                                                                                             ▒
             │        xor    $0x62946a4f,%eax                                                                                                                                                                                                                                                                            ▒
             │        mov    %rax,0x8(%rsp)                                                                                                                                                                                                                                                                              ▒
             │              return(key % table_size);                                                                                                                                                                                                                                                                    ▒
        6.28 │        divq   0x8(%rsi)                                                                                                                                                                                                                                                                                   ▒
             │      _Z17ut_2pow_remainderImET_S0_S0_():                                                                                                                                                                                                                                                                  ▒
             │      /*******************************************
      

      Recommend removing statistic counter. I'm not sure a DBA could make a decision based on this statistic.

      Attachments

        Issue Links

          Activity

            People

              kevg Eugene Kosov (Inactive)
              danblack Daniel Black
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.