Description:
Environment
Two linux servers with ROW-based master->slave replication.
1.Master server
8x GenuineIntel: Intel(R) Xeon(R) CPU E5430.
4SAS disks in raid10
Linux Gentoo kernel - 2.6.34 x86_64
mysql-5.1.46
Compiled whith USE="big-tables cluster community perl ssl"
2. Slave server
8x GenuineIntel: Intel(R) Xeon(R) CPU E5420
2SAS disks in raid1
Linux db2 2.6.34 Gentoo x86_64
mysql - 5.1.50
Compiled whith USE="big-tables cluster community perl ssl"
I have some big table on master and slave
where2buy_auction_log | CREATE TABLE `where2buy_auction_log` (
`firm_id` int(10) unsigned NOT NULL,
`sitezone_id` int(10) unsigned NOT NULL,
`cost` int(10) unsigned NOT NULL,
`user_id` int(11) NOT NULL,
`date` datetime NOT NULL,
KEY `firm_id` (`firm_id`),
KEY `section_id` (`sitezone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 COLLATE=cp1251_ukrainian_ci
When I delete 5000 rows by query:
DELETE FROM where2buy_auction_log WHERE firm_id = 100000;
Master server perform thith request in less 10 msec, Slave server - about 60 sec.
When I delete 10000 by same query
Master - 18 msec, Slave - 5 min.
20000
Master - less 1 sec, Slave - 15 min.
30000
Master - less 1 sec, Slave - 40 min.
When slave server out off sync and trying to perform request, it SQL_TREAD cannot be killed(only kill -9 mysqld process).
show slave status looked like
"Reading event from the relay log" in all time.
I need to delete 3000000 rows by one request. It seems, that I must wait severals days for my slave server .....
How to repeat:
Use 2 servers in mysql row-based replication.
Create table
where2buy_auction_log | CREATE TABLE `where2buy_auction_log` (
`firm_id` int(10) unsigned NOT NULL,
`sitezone_id` int(10) unsigned NOT NULL,
`cost` int(10) unsigned NOT NULL,
`user_id` int(11) NOT NULL,
`date` datetime NOT NULL,
KEY `firm_id` (`firm_id`),
KEY `section_id` (`sitezone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 COLLATE=cp1251_ukrainian_ci
Generate 3000000 rows data to table with same firm_id.
Try to delete it in master
DELETE FROM where2buy_auction_log WHERE firm_id = 100000;
Now slave out off sync.
