Description:
MySQL Master version:5.6.42
MySQL Slave version:5.7.26（ or other 5.7.X）
Do zabbix monitoring system database upgrade, do database master-slave synchronization, a large number of write operations, a small number of read operations, QPS is about 2000, from the library delay can not be added, the delay is getting bigger. The main configuration file from my.cnf is the same as the server configuration. Memory: 128G, CPU: 64C, hard disk: SSD 16*960G. Even if the bin-log from the library is closed, the brush policy adjusts innodb_flush_log_at_trx_commit=0 and sync_binlog=0, and the result is also same. The table construction statement for the important table of the database is:
CREATE TABLE `zabbix.history_uint` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` bigint(20) unsigned NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_uint_1` (`itemid`, `clock`),
KEY `clock` (`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
/*!50100 PARTITION BY RANGE (`clock`)
(PARTITION p201907021200 VALUES LESS THAN (1562047200) ENGINE = InnoDB,
PARTITION p201907021400 VALUES LESS THAN (1562054400) ENGINE = InnoDB,
PARTITION p201907021600 VALUES LESS THAN (1562061600) ENGINE = InnoDB,
PARTITION p201907021800 VALUES LESS THAN (1562068800) ENGINE = InnoDB,
PARTITION p201907022000 VALUES LESS THAN (1562076000) ENGINE = InnoDB,
PARTITION p201907022200 VALUES LESS THAN (1562083200) ENGINE = InnoDB,
PARTITION p201907030000 VALUES LESS THAN (1562090400) ENGINE = InnoDB,
PARTITION p201907030200 VALUES LESS THAN (1562097600) ENGINE = InnoDB,
PARTITION p201907030400 VALUES LESS THAN (1562104800) ENGINE = InnoDB,
PARTITION p201907030600 VALUES LESS THAN (1562112000) ENGINE = InnoDB,
PARTITION p201907030800 VALUES LESS THAN (1562119200) ENGINE = InnoDB,
PARTITION p201907031000 VALUES LESS THAN (1562126400) ENGINE = InnoDB,
PARTITION p201907031200 VALUES LESS THAN (1562133600) ENGINE = InnoDB,
PARTITION p201907031400 VALUES LESS THAN (1562140800) ENGINE = InnoDB,
PARTITION p201907031600 VALUES LESS THAN (1562148000) ENGINE = InnoDB,
PARTITION p201907031800 VALUES LESS THAN (1562155200) ENGINE = InnoDB,
PARTITION p201907032000 VALUES LESS THAN (1562162400) ENGINE = InnoDB,
PARTITION p201907032200 VALUES LESS THAN (1562169600) ENGINE = InnoDB,
PARTITION p201907040000 VALUES LESS THAN (1562176800) ENGINE = InnoDB,
PARTITION p201907040200 VALUES LESS THAN (1562184000) ENGINE = InnoDB,
PARTITION p201907040400 VALUES LESS THAN (1562191200) ENGINE = InnoDB,
PARTITION p201907040600 VALUES LESS THAN (1562198400) ENGINE = InnoDB,
PARTITION p201907040800 VALUES LESS THAN (1562205600) ENGINE = InnoDB,
PARTITION p201907041000 VALUES LESS THAN (1562212800) ENGINE = InnoDB,
PARTITION p201907041200 VALUES LESS THAN (1562220000) ENGINE = InnoDB,
PARTITION p201907041400 VALUES LESS THAN (1562227200) ENGINE = InnoDB,
PARTITION p201907041600 VALUES LESS THAN (1562234400) ENGINE = InnoDB,
PARTITION p201907041800 VALUES LESS THAN (1562241600) ENGINE = InnoDB,
PARTITION p201907042000 VALUES LESS THAN (1562248800) ENGINE = InnoDB,
PARTITION p201907042200 VALUES LESS THAN (1562256000) ENGINE = InnoDB,
PARTITION p201907050000 VALUES LESS THAN (1562263200) ENGINE = InnoDB,
PARTITION p201907050200 VALUES LESS THAN (1562270400) ENGINE = InnoDB,
PARTITION p201907050400 VALUES LESS THAN (1562277600) ENGINE = InnoDB,
PARTITION p201907050600 VALUES LESS THAN (1562284800) ENGINE = InnoDB,
PARTITION p201907050800 VALUES LESS THAN (1562292000) ENGINE = InnoDB,
PARTITION p201907051000 VALUES LESS THAN (1562299200) ENGINE = InnoDB,
PARTITION p201907051200 VALUES LESS THAN (1562306400) ENGINE = InnoDB,
PARTITION p201907051400 VALUES LESS THAN (1562313600) ENGINE = InnoDB,
PARTITION p201907051600 VALUES LESS THAN (1562320800) ENGINE = InnoDB,
PARTITION p201907051800 VALUES LESS THAN (1562328000) ENGINE = InnoDB,
PARTITION p201907052000 VALUES LESS THAN (1562335200) ENGINE = InnoDB;
How to repeat:
any
