Re: large InnoDB table partitioning without explicit PK
Posted by:
Rick James
Date: November 17, 2014 07:48PM
Well, you forced me to learn something today:
INSERT ... ON DUPLICATE KEY UPDATE, without any explicit PRIMARY or UNIQUE keys, will degenerate into a plain INSERT. (Or so it seems -- both from your timings, and from a test case I built.)
But -- If you do add any PRIMARY/UNIQUE keys and insert into those fields, performance may decline since it will need to check for DUPLICATE KEY.
> [Miko] It was a 12 hour test, no PK, just two IDXs, the DB size was 700M rows, 250GB on a disk
That was partitioned, with the partitions being smaller than the buffer_pool, and with "BEGIN - lots of inserts - COMMIT"? Then it seems feasible. How many connections were inserting simultaneously?
> 72% %user, 16% idle (way too much for a regular usage).
That sounds like multiple connections spread across multiple cores? Some of that would be mutex contention between the threads. It _might_ be best to have the number of connections not be much more than the number of cores.
What do you mean by "for regular usage"?
Decreasing the number of indexes should decrease the CPU usage. Rationale:
The data plus the PRIMARY KEY is a BTree; each secondary index is another BTree. A plain INSERT, with the relevant blocks already cached, is mostly locating the right spot in each BTree, then doing an INSERT. When a block fills up, there is a "block split". More indexes => more time.
Batched INSERTs (not possible because of 3rd party) would also decrease the CPU usage.
Subject
Views
Written By
Posted
5215
November 12, 2014 07:41PM
2535
November 13, 2014 06:40PM
2386
November 14, 2014 03:02AM
1811
November 15, 2014 12:05AM
1839
November 16, 2014 07:39PM
1801
November 17, 2014 04:21PM
Re: large InnoDB table partitioning without explicit PK
1857
November 17, 2014 07:48PM
1846
November 18, 2014 02:00AM
1918
November 18, 2014 11:35PM
1908
November 22, 2014 06:44AM
1781
November 18, 2014 01:13AM
2238
November 19, 2014 12:27AM
2004
November 19, 2014 06:01PM
2127
November 22, 2014 08:39AM
2078
November 14, 2014 03:04AM
2119
November 14, 2014 03:05AM
2135
November 15, 2014 12:32AM
2138
November 16, 2014 08:31PM
1918
November 19, 2014 12:12AM
1810
November 22, 2014 09:07AM
1857
November 23, 2014 09:00PM
2000
December 01, 2014 11:16AM