Hello Rick,
thank you for your answer.
Quote
Is this a more complete example:
BEGIN;
INSERT INTO table1 (...) VALUES (...);
INSERT INTO table2 (table1_id, ...) VALUES (LAST_INSERT_ID(), ...);
INSERT INTO table1 (...) VALUES (...);
INSERT INTO table2 (table1_id, ...) VALUES (LAST_INSERT_ID(), ...);
INSERT INTO table1 (...) VALUES (...);
INSERT INTO table2 (table1_id, ...) VALUES (LAST_INSERT_ID(), ...);
...
COMMIT;
Exactly what happens here!
Quote
80/sec sounds like an fsync to a SATA disk without a write cache. I would expect something like 80 for AUTOCOMMIT=1 and no BEGIN. RAID with Battery Backed Write Cache can increase that significantly. And so will (should) the BEGIN.
Yes, i suspected an fsync related issue. Battery Backed Write Cache might be an option in the future, the actual server does not provide that.
The action takes place in one commit already.
Quote
SHOW VARIABLES LIKE 'innodb%';
...
'innodb_buffer_pool_size', '635437056'
...
'innodb_flush_log_at_trx_commit', '1'
...
Quote
SHOW VARIABLES LIKE '%sync%';
'innodb_sync_spin_loops', '20'
'sync_binlog', '0'
'sync_frm', 'ON'
Table1:
Quote
CREATE TABLE `bauteil` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`lieferung` int(11) unsigned NOT NULL,
`lagerort` int(11) NOT NULL,
`status` int(11) NOT NULL,
`produktion` int(11) DEFAULT NULL,
`kommentar` varchar(10000) DEFAULT NULL,
`seriennr_hersteller` varchar(60) DEFAULT NULL,
`st_ok` tinyint(1) NOT NULL DEFAULT '0',
`st_mod` tinyint(1) NOT NULL DEFAULT '0',
`st_damage` tinyint(1) NOT NULL DEFAULT '0',
`st_calibrated` tinyint(1) NOT NULL DEFAULT '0',
`st_burnin` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`status`) USING BTREE,
KEY `FK_bauteil_2` (`lagerort`),
KEY `FK_bauteil_3` (`status`),
KEY `FK_bauteil_1` (`lieferung`) USING BTREE,
KEY `FK_bauteil_produktion` (`produktion`),
CONSTRAINT `FK_bauteil_2` FOREIGN KEY (`lagerort`) REFERENCES `lagerorte` (`id`),
CONSTRAINT `FK_bauteil_3` FOREIGN KEY (`status`) REFERENCES `bauteil_status` (`id`),
CONSTRAINT `FK_bauteil_lieferung` FOREIGN KEY (`lieferung`) REFERENCES `lieferung` (`id`),
CONSTRAINT `FK_bauteil_produktion` FOREIGN KEY (`produktion`) REFERENCES `produktion` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65142 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED
Table 2:
Quote
CREATE TABLE `bauteil_produkt` (
`id` int(11) NOT NULL,
`produkt` int(11) NOT NULL,
`seriennr` int(11) NOT NULL,
`start_time` datetime NOT NULL,
`ersteller` int(11) NOT NULL,
`outdated` tinyint(2) NOT NULL DEFAULT '0',
PRIMARY KEY (`produkt`,`seriennr`,`outdated`) USING BTREE,
UNIQUE KEY `FK_bauteil_produkt_bt` (`id`,`outdated`) USING BTREE,
KEY `FK_bauteil_produkt_ersteller` (`ersteller`),
CONSTRAINT `FK_bauteil_produkt_bt` FOREIGN KEY (`id`) REFERENCES `bauteil` (`id`),
CONSTRAINT `FK_bauteil_produkt_ersteller` FOREIGN KEY (`ersteller`) REFERENCES `personen` (`id`),
CONSTRAINT `FK_bauteil_produkt_produkt` FOREIGN KEY (`produkt`) REFERENCES `produkt` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED
Quote
Yet another thought:
INSERT INTO table1 VALUES (key1, val1), (key2, val2), ... -- all of these at once
SELECT key, val FROM table1 WHERE key IN (key1, key2, ...)
INSERT INTO table2 VALUES (...,key1,...), (...,key1,...), ... -- all the rows.
That's a total of 3 statements, not 2N. Batched INSERTs run faster. I recommend 100 at a time, which may give 10x speedup.
Yeah, that's what i already thought of, but i have no idea how to get the correct key (id) for the batched insert in table2 since MySQL will autoincrement it in table1.
BTW are there other "boxes" available than "quote" in this forum? I guessed source, but guessed wrong...
Best wishes, thank you so far!