I'm looking for help understanding why this insert query gets slower as the table size grows.
The query is a batch insert of 1k rows. By batch i mean multiple 'values' rows as described in this answer.
There is also a longblob field in the table. Typical size of blob field data is rather small at around 2kb (but it can be much larger).
The image shows the results of SHOW PROFILE for the query when inserting into an empty table vs inserting into a table which already had 1 Million rows. The FK tables are nearly empty in both cases.
Table structure (column names changed for anonymity):
CREATE TABLE `MyTable` (
`modifiedtime` datetime DEFAULT NULL,
`column1` varchar(255) DEFAULT NULL,
`column2` varchar(255) DEFAULT NULL,
`column3` varchar(128) DEFAULT NULL,
`column4` decimal(20,5) NOT NULL,
`column5` varchar(128) DEFAULT NULL,
`column6` varchar(255) DEFAULT NULL,
`id` varchar(128) NOT NULL,
`creationtime` datetime DEFAULT NULL,
`version` decimal(20,5) DEFAULT NULL,
`status` varchar(255) DEFAULT NULL,
`column7` varchar(128) DEFAULT NULL,
`column8` varchar(128) DEFAULT NULL,
`schemaless_attrs` longblob,
PRIMARY KEY (`id`),
UNIQUE KEY `AK_MyTable` (`column4`,`column8`),
KEY `FK_MyTable_column3` (`column3`),
KEY `FK_MyTable_column5` (`column5`),
KEY `IDX_ManagedBaseDataItem_column1` (`column1`),
KEY `IDX_MyTable_column3` (`column3`),
KEY `IDX_MyTable_column4` (`column4`),
KEY `IDX_MyTable_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
The query below is a typical example. Note: In the real query there are 1000 rows of 'values'.
insert into `MyTable` (`creationtime`, `modifiedtime`, `column8`,
`column5`, `column1`, `schemaless_attrs`, `version`, `column3`,
`column6`, `column2`, `column4`, `status`, `column7`, `id`)
values ('2015-07-01 14:47:41.259', '2015-07-01 14:47:41.259',
'single', 'single|MyApp|0', 'MyProduct',
_binary'a serialized Java ojbect which is usually around 2k in size',
0, 'single|column3|cfd3229a-b424-41cc-b140-08cd92241064',
null, 'en', 1000001, 'PENDING', 'ManagedAppEntity',
'single|ManagedAppEntity|b095c316-293b-4a28-9a18-5422dd66eadd')
Important Notes:
- We have another app which makes nearly the same insert query into the same table structure. The insert time never degrades for the other app as the tables grows.
- Since the other app uses the same exact MySQL db, same table (with the exception of 'version' field which is new), and and nearly the same insert query I'd prefer not to focus on db config such as 'innodb_buffer_pool_size', indexes, moving blog to another table, etc.
- While there is surely room for improvement in the db config, the other app is not showing the slowdowns. I'm trying to achieve similar performance with the same db config.
Related Questions:
- I have previously asked two related questions around this problem. To be clear, I'm not trying to spam SO with Questions around this topic but I've had a hard time pinning down whether the slowdown is coming from the app or the db. This seems like a good specific question for SO. Anyhow, the other questions are:
- Question #1: A (perhaps naive) question around whether the column order makes a difference when inserting blobs. Comments on this q indicate "no".
- Question #2: A question that is focused more on the dev stack for the app in question.