slave_rows_search_algorithms with HASH_SCAN too slow
| Affects | Status | Importance | Assigned to | Milestone | ||
|---|---|---|---|---|---|---|
| MySQL Server |
Unknown
|
Unknown
|
||||
| Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
| 5.6 |
Triaged
|
Medium
|
Unassigned | |||
| 5.7 |
Triaged
|
Medium
|
Unassigned | |||
Bug Description
Description:
According to the manual and design about slave_rows_
one transaction replica.
But slave_rows_
I test on percona 5.7.16.
How to repeat:
config slave_rows_
On master:
CREATE TABLE `supplier` (
`s_suppkey` int(11) NOT NULL,
`s_name` char(25) NOT NULL,
`s_address` varchar(40) NOT NULL,
`s_nationkey` int(11) NOT NULL,
`s_phone` char(15) NOT NULL,
`s_acctbal` decimal(15,2) NOT NULL,
`s_comment` varchar(101) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
load 200000 rows into table supplier.
Then do the following update operation:
Update supplier set s_name = 'xxx';
The update sql cost about 4.48s.
But the slave cost about 18min to replica this transaction.
=======
That is too slow!!!
Why one table scan plus one index scan need so many time?
=======
Use the perf top, we can get the following:
11.97% mysqld [.] crc32
4.99% mysqld [.] PolicyMutex<
4.45% lic-2.17.so [.] __memmove_
4.21% mysqld [.] row_sel_
4.61% mysqld [.] Hash_slave_
3.52% mysqld [.] row_search_mvcc
3.33% mysqld [.] PolicyMutex<
1.91% mysqld [.] row_sel_
Use gdb attach to the sql thread, we can get the following:
(gdb) bt
#0 0x00007f4b109aee40 in __memmove_
#1 0x000000000102f54a in row_sel_
at /export/
#2 0x000000000102fad4 in row_sel_
at /export/
#3 0x000000000103565d in row_search_mvcc (buf=0x7f47f00348c0 "NI\017", mode=PAGE_CUR_G, prebuilt=
at /export/
#4 0x0000000000f4477e in ha_innobase:
at /export/
#5 0x00000000007e485d in handler:
#6 0x0000000000ea39a7 in Rows_log_
#7 0x0000000000ea4522 in Rows_log_
#8 0x0000000000ea5bd8 in Log_event:
#9 0x0000000000ee8096 in apply_event_
#10 0x0000000000ee93e9 in exec_relay_
#11 0x0000000000eeb296 in handle_slave_sql (arg=<optimized out>) at /export/
#12 0x00000000011cf5f4 in pfs_spawn_thread (arg=0x7f47f801
#13 0x00007f4b11c8cdf5 in start_thread () from /lib64/
#14 0x00007f4b109561ad in clone () from /lib64/libc.so.6
(gdb) quit
=======
We also do the following tests:
1. drop the supplier table, and re-create a new supplier with primary key:
CREATE TABLE `supplier` (
`s_suppkey` int(11) NOT NULL,
`s_name` char(25) NOT NULL,
`s_address` varchar(40) NOT NULL,
`s_nationkey` int(11) NOT NULL,
`s_phone` char(15) NOT NULL,
`s_acctbal` decimal(15,2) NOT NULL,
`s_comment` varchar(101) NOT NULL,
primary key (s_suppkey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Also load 200000 rows into the table.
Do the following update on master:
Update supplier set s_name = 'xxx';
The master cost 4.53s to execute, and the slave cost 10s to replica.
2. drop the supplier table, and re-create a new supplier with a key:
CREATE TABLE `supplier` (
`s_suppkey` int(11) NOT NULL,
`s_name` char(25) NOT NULL,
`s_address` varchar(40) NOT NULL,
`s_nationkey` int(11) NOT NULL,
`s_phone` char(15) NOT NULL,
`s_acctbal` decimal(15,2) NOT NULL,
`s_comment` varchar(101) NOT NULL,
key (s_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Also load 200000 rows into the table, and all values for s_address are the same.
Do the following update on master:
Update supplier set s_name = 'xxx';
The master cost 6.43s to execute, and the slave cost 36min to replica.
3. drop the supplier table, and re-create a new supplier with a key:
CREATE TABLE `supplier` (
`s_suppkey` int(11) NOT NULL,
`s_name` char(25) NOT NULL,
`s_address` varchar(40) NOT NULL,
`s_nationkey` int(11) NOT NULL,
`s_phone` char(15) NOT NULL,
`s_acctbal` decimal(15,2) NOT NULL,
`s_comment` varchar(101) NOT NULL,
key (s_suppkey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Also load 200000 rows into the table, and all values for s_suppkey are the different.
Do the following update on master:
Update supplier set s_name = 'xxx';
The master cost 3.11s to execute, and the slave cost 8s to replica.
It seams, the better index will get better replica performance.
But why?
In this test, the whole table need to be updated in all cases.
| affects: | percona-playback → percona-xtradb |
| no longer affects: | centos |
| description: | updated |
| affects: | percona-xtradb → percona-server |
| tags: |
added: performance upstream removed: based replication row |
| Sveta Smirnova (svetasmirnova) wrote | #1 |
| Changed in percona-server: | |
| status: | New → Confirmed |
| Laurynas Biveinis (laurynas-biveinis) wrote | #2 |
| Sveta Smirnova (svetasmirnova) wrote | #5 |
| Shahriyar Rzayev (rzayev-sehriyar) wrote | #6 |
Thank you for the report.
Verified as described.