MySQL Forums
Forum List  »  Partitioning

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.

Options: ReplyQuote

Written By
Re: large InnoDB table partitioning without explicit PK
November 17, 2014 07:48PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.