[MDEV-21212] buf_page_get_gen -> buf_pool->stat.n_page_gets++ is a cpu waste (0.5-1%) - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.5
Fix Version/s: 10.6.0
Component/s: Storage Engine - InnoDB
Labels:
- affects-tests
- performance

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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

my.cnf
2 kB
2019-12-04 22:13
baseline (#f386fdd7), pr#7166 and tls (#1654bfee4) (single numa).png
13 kB
2021-03-12 10:52
baseline (#f386fdd7), pr#1766 and tls (#1654bfee4).png
15 kB
2021-03-11 15:17
baseline (#64f89b1f), pr#1766 and tls (#1654bfee4) (single numa).png
13 kB
2021-03-12 10:52

Issue Links

blocks

MDEV-24329 TSAN: Data race in srv_mon_default_on at srv/srv0mon.cc:2099

Open

MDEV-24330 Data race in buf_LRU_get_free_block at buf/buf0lru.cc:408

Open

MDEV-24332 TSAN: Data race in os_file_pread from os/os0file.cc

Open

relates to

MDEV-142 Check if Innodb_buffer_pool_read_requests is back to normal in latest 5.3

Open

MDEV-8932 innodb buffer pool hit rate is less than zero

Closed

MDEV-28537 Unused or useless InnoDB counters num_index_pages_written, num_non_index_pages_written

Closed

MDEV-28539 Some InnoDB counters are duplicating generic SHOW STATUS

Closed

MDEV-28540 Deprecate and ignore the parameter innodb_prefix_index_cluster_optimization

Closed

MDEV-28542 Useless INSERT BUFFER AND ADAPTIVE HASH INDEX output in SHOW ENGINE INNODB STATUS

Closed

MDEV-34297 get_rnd_value() of ib_counter_t is unnecessarily complex

Closed

MDEV-15058 Remove multiple InnoDB buffer pool instances

Closed

MDEV-23249 __builtin_readcyclecounter in include/my_rdtsc.h causes SIGILL on ARM

Closed

MDEV-24328 Data race in buf_page_get_low at buf/buf0buf.cc:2946 and in buf_page_read_complete at buf/buf0buf.cc:4240

Closed

MDEV-25281 Switch to use non-atomic (vs atomic) distributed counter to track page-access counter

Closed

links to

mysql bug 97822

mysql-8.0.20 fix

(9 relates to, 2 links to)

Activity

People

Assignee:: Eugene Kosov (Inactive)

Reporter:: Daniel Black

Votes:: 0 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2019-12-04 05:31

Updated:: 2024-06-04 11:18

Resolved:: 2021-03-12 10:59

Git Integration

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