MySQL Forums :: Partitioning :: large InnoDB table partitioning without explicit PK


Advanced Search

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


Subject Views Written By Posted
large InnoDB table partitioning without explicit PK 2446 Miko M 11/12/2014 07:41PM
Re: large InnoDB table partitioning without explicit PK 1378 Rick James 11/13/2014 06:40PM
Re: large InnoDB table partitioning without explicit PK 1217 Miko M 11/14/2014 03:02AM
Re: large InnoDB table partitioning without explicit PK 968 Rick James 11/15/2014 12:05AM
Re: large InnoDB table partitioning without explicit PK 986 Miko M 11/16/2014 07:39PM
Re: large InnoDB table partitioning without explicit PK 873 Rick James 11/17/2014 04:21PM
Re: large InnoDB table partitioning without explicit PK 973 Rick James 11/17/2014 07:48PM
Re: large InnoDB table partitioning without explicit PK 934 Miko M 11/18/2014 02:00AM
Re: large InnoDB table partitioning without explicit PK 986 Rick James 11/18/2014 11:35PM
Re: large InnoDB table partitioning without explicit PK 1029 Miko M 11/22/2014 06:44AM
Re: large InnoDB table partitioning without explicit PK 923 Miko M 11/18/2014 01:13AM
(non-unique) index more efficient than partition pruning 1149 Miko M 11/19/2014 12:27AM
Re: (non-unique) index more efficient than partition pruning 1026 Rick James 11/19/2014 06:01PM
Re: (non-unique) index more efficient than partition pruning 1052 Miko M 11/22/2014 08:39AM
Re: large InnoDB table partitioning without explicit PK 1040 Miko M 11/14/2014 03:04AM
Re: large InnoDB table partitioning without explicit PK 1090 Miko M 11/14/2014 03:05AM
Re: large InnoDB table partitioning without explicit PK 1172 Rick James 11/15/2014 12:32AM
Re: large InnoDB table partitioning without explicit PK 1109 Miko M 11/16/2014 08:31PM
Re: large InnoDB table partitioning without explicit PK 949 Rick James 11/19/2014 12:12AM
Re: large InnoDB table partitioning without explicit PK 916 Miko M 11/22/2014 09:07AM
Re: large InnoDB table partitioning without explicit PK 908 Rick James 11/23/2014 09:00PM
Re: large InnoDB table partitioning without explicit PK 995 Miko M 12/01/2014 11:16AM


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.