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 2487 Miko M 11/12/2014 07:41PM
Re: large InnoDB table partitioning without explicit PK 1404 Rick James 11/13/2014 06:40PM
Re: large InnoDB table partitioning without explicit PK 1240 Miko M 11/14/2014 03:02AM
Re: large InnoDB table partitioning without explicit PK 986 Rick James 11/15/2014 12:05AM
Re: large InnoDB table partitioning without explicit PK 1008 Miko M 11/16/2014 07:39PM
Re: large InnoDB table partitioning without explicit PK 891 Rick James 11/17/2014 04:21PM
Re: large InnoDB table partitioning without explicit PK 992 Rick James 11/17/2014 07:48PM
Re: large InnoDB table partitioning without explicit PK 959 Miko M 11/18/2014 02:00AM
Re: large InnoDB table partitioning without explicit PK 1002 Rick James 11/18/2014 11:35PM
Re: large InnoDB table partitioning without explicit PK 1050 Miko M 11/22/2014 06:44AM
Re: large InnoDB table partitioning without explicit PK 943 Miko M 11/18/2014 01:13AM
(non-unique) index more efficient than partition pruning 1170 Miko M 11/19/2014 12:27AM
Re: (non-unique) index more efficient than partition pruning 1041 Rick James 11/19/2014 06:01PM
Re: (non-unique) index more efficient than partition pruning 1070 Miko M 11/22/2014 08:39AM
Re: large InnoDB table partitioning without explicit PK 1057 Miko M 11/14/2014 03:04AM
Re: large InnoDB table partitioning without explicit PK 1108 Miko M 11/14/2014 03:05AM
Re: large InnoDB table partitioning without explicit PK 1199 Rick James 11/15/2014 12:32AM
Re: large InnoDB table partitioning without explicit PK 1125 Miko M 11/16/2014 08:31PM
Re: large InnoDB table partitioning without explicit PK 964 Rick James 11/19/2014 12:12AM
Re: large InnoDB table partitioning without explicit PK 933 Miko M 11/22/2014 09:07AM
Re: large InnoDB table partitioning without explicit PK 923 Rick James 11/23/2014 09:00PM
Re: large InnoDB table partitioning without explicit PK 1012 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.