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 18, 2014 11:35PM

> And from what you are saying, I presume that even the true working set was big (36M records, 10GB of partition size) it still fitted into the memory (innodb_buffer_pool_size ~ 40GB)

Correct; that is what I am saying. That explains why your I/O is low. And it gives you the parameters (partitions, buffer_pool, etc) that ultimately control how fast the system can run.

> it is 2 x 6 cores Xeon Processors but there is ESXi underneath, and 8 (v)CPU are configured for that VM.

My past experience (not recent, and not with that specific hardware or VM) is that VM has something like a 10-20% overhead. Perhaps you should consider a dedicated machine without VMs?
At 80% CPU, you are possibly at the CPU limit. InnoDB's lock (mutex) contention handling partially chews up CPU (in hopes that the lock will be released very soon), and partially 'sleeps'.

> MEMORY ... It does not make any sense since with multiple connections the table lock is significant (I wish row locking was supported) and the limit is 7k inserts per second. However IO gain is amazing ;-)

Good point; I forgot about the possibility of significant locking.
Plan B: One MEMORY 'cache table' per connection, with a LOCK TABLE / UNLOCK TABLE around it -- the writers would have to let go periodically (say, once per second) to let the copy process swap tables and shovel data.
Plan C: Using an single InnoDB cache table (with no indexes). However, because of various details, this idea may not be better than your current setup.

> 405196 rows / minute, 22% CPU

Nice! (for, MEMORY, multithreaded?)
22% might be equivalent to 2 cores mostly busy.

> There are BEGINs and COMMITs. But not many INSERTS between them.

BEGIN, one INSERT, COMMIT -- yet you are getting that high rate. Nice!
Note: That is equivalent to having autocommit=ON and not having 'BEGIN' or 'COMMIT'.

> 1048850 / minute

Nicer!! (for InnoDB, multithreaded?)

> [autocommit] It is 10% only and I would expect a higher number here.

I would have, too. Perhaps a lot of other mutexes, etc, are hitting their limits.

> 1400066

Nicerer !!!

BTW, in a carefully designed benchmark, someone (Mark Callahan? Oracle?) has managed to do 1M tps in 5.7. Your 23K inserts/sec is probably more realistic.

> BEGIN.. INSERT1..INSERT5000..COMMIT

There are certain default settings that you may be overflowing -- innodb_log_buffer_size, binlog buffer, etc. Could you try chunking 100 at a time instead of 5000?

Possibly related: Innodb_log_waits was 0 in the SHOW you gave me; is it still 0?
innodb_log_file_size is 1G; I doubt if it needs changing.

Options: ReplyQuote


Subject Views Written By Posted
large InnoDB table partitioning without explicit PK 2490 Miko M 11/12/2014 07:41PM
Re: large InnoDB table partitioning without explicit PK 1409 Rick James 11/13/2014 06:40PM
Re: large InnoDB table partitioning without explicit PK 1241 Miko M 11/14/2014 03:02AM
Re: large InnoDB table partitioning without explicit PK 987 Rick James 11/15/2014 12:05AM
Re: large InnoDB table partitioning without explicit PK 1009 Miko M 11/16/2014 07:39PM
Re: large InnoDB table partitioning without explicit PK 892 Rick James 11/17/2014 04:21PM
Re: large InnoDB table partitioning without explicit PK 995 Rick James 11/17/2014 07:48PM
Re: large InnoDB table partitioning without explicit PK 960 Miko M 11/18/2014 02:00AM
Re: large InnoDB table partitioning without explicit PK 1003 Rick James 11/18/2014 11:35PM
Re: large InnoDB table partitioning without explicit PK 1052 Miko M 11/22/2014 06:44AM
Re: large InnoDB table partitioning without explicit PK 945 Miko M 11/18/2014 01:13AM
(non-unique) index more efficient than partition pruning 1172 Miko M 11/19/2014 12:27AM
Re: (non-unique) index more efficient than partition pruning 1042 Rick James 11/19/2014 06:01PM
Re: (non-unique) index more efficient than partition pruning 1071 Miko M 11/22/2014 08:39AM
Re: large InnoDB table partitioning without explicit PK 1058 Miko M 11/14/2014 03:04AM
Re: large InnoDB table partitioning without explicit PK 1110 Miko M 11/14/2014 03:05AM
Re: large InnoDB table partitioning without explicit PK 1202 Rick James 11/15/2014 12:32AM
Re: large InnoDB table partitioning without explicit PK 1127 Miko M 11/16/2014 08:31PM
Re: large InnoDB table partitioning without explicit PK 965 Rick James 11/19/2014 12:12AM
Re: large InnoDB table partitioning without explicit PK 937 Miko M 11/22/2014 09:07AM
Re: large InnoDB table partitioning without explicit PK 924 Rick James 11/23/2014 09:00PM
Re: large InnoDB table partitioning without explicit PK 1014 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.