Re: Replication lag when no primary key on InnoDB
I used a simple DELETE Statement to reproduce the problem,
DELETE FROM agg_NoStation_BikeStationFact LIMIT 10000;
CREATE TABLE `agg_NoStation_BikeStationFact` (
`fact_count` int(11) NOT NULL,
`emptyDockPercent_avg` double NOT NULL,
`emptyDockPercent_max` double NOT NULL,
`emptyDockPercent_min` double NOT NULL,
`fullDockPercent_avg` double NOT NULL,
`fullDockPercent_max` double NOT NULL,
`fullDockPercent_min` double NOT NULL,
`nbBike_avg` int(11) NOT NULL,
`nbBike_max` int(11) NOT NULL,
`nbBike_min` int(11) NOT NULL,
`nbDock_avg` int(11) NOT NULL,
`nbDock_max` int(11) NOT NULL,
`nbDock_min` int(11) NOT NULL,
`nbEmptyDock_avg` int(11) NOT NULL,
`nbEmptyDock_max` int(11) NOT NULL,
`nbEmptyDock_min` int(11) NOT NULL,
`nbNotOkDock_avg` int(11) NOT NULL,
`nbNotOkDock_max` int(11) NOT NULL,
`nbNotOkDock_min` int(11) NOT NULL,
`date_id` bigint(20) NOT NULL,
`organization_id` tinyint(4) NOT NULL,
`time_id` smallint(6) NOT NULL,
KEY `fk_organization_id_agg_NoStation_BikeStationFact` (`organization_id`),
KEY `fk_date_id_agg_NoStation_BikeStationFact` (`date_id`),
KEY `fk_time_id_agg_NoStation_BikeStationFact` (`time_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*************************** 1. row ***************************
Name: agg_NoStation_BikeStationFact
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 147625
Avg_row_length: 153
Data_length: 22593536
Max_data_length: 0
Index_length: 13697024
Data_free: 46137344
Auto_increment: NULL
Create_time: 2010-09-13 20:43:49
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
- The delete IS fast on MASTER.
- MIXED mode replication. I looked in the binlog and the DELETE is converted to RBR (one delete for each row).
- The transfer of the relay log is almost instantaneous < 1sec (1.1MB for 10K rows), I checked
- The thread is showing during a long time... > 2 minutes' Reading event from the relay log'
mysql> show slave status\G show processlist;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: dbtest1
Master_User: su
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 1179048
Relay_Log_File: mysqld-relay-bin.000057
Relay_Log_Pos: 248
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 1179386
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 532
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
+----+-------------+-----------+------+---------+--------+----------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+--------+----------------------------------+------------------+
| 25 | system user | | NULL | Connect | 179393 | Waiting for master to send event | NULL |
| 26 | system user | | NULL | Connect | 157 | Reading event from the relay log | NULL |
| 41 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-------------+-----------+------+---------+--------+----------------------------------+------------------+
3 rows in set (0.00 sec)
When I add a AUTO INC id as a PRIMARY KEY, everything is fast on the slave;
Stephan