Re: large InnoDB table partitioning without explicit PK

[Rick James]

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.