MySQL Forums
Forum List  »  Partitioning

Re: large InnoDB table partitioning without explicit PK
Posted by: Miko M
Date: November 22, 2014 06:44AM

> > 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.
[Miko] Ok, I see.

> > 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?
[Miko] Yes, definitely there is some kind of overhead here.
However, this MySQL VM is the only VM on the ESXi host.
> 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.
[Miko] Many thanks for providing me with other options.
Plan B looks tempting but it looks to me a little bit too complicated as of now.
I am not sure whether I fully understand Plan C. Could you please give me a hint which variable should I use/tune here?

> > 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?)
[Miko] Hmm, I am a little bit confused here. As far as I understand MySQL is a multithreaded application.
I have not changed the default setting of innodb_thread_concurrency either (0).
But I am going to play with this variable (among the others) soon ;-)

> > [autocommit] It is 10% only and I would expect a
> higher number here.
[Miko] So would I, to be honest.

> 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.
> 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?
[Miko] Ok, good point, let me try to change it and test.
However with 200B of average size of a record, even high insert rate (5k inserts between BEGIN and COMMIT) should non be a problem for innodb_log_buffer_size=16M (as long as I understand this variable properly).
Moreover I've found very few sources in which authors advise to use more than 32M for inno_db_log_buffer.
However in one source I found a number of 25% of innodb_buffer_pool_size.
Any recommendations here?

> 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.
[Miko] Another good hint! I should have observed status after either every parameter/variable change or load characteristic change.

Options: ReplyQuote

Written By
Re: large InnoDB table partitioning without explicit PK
November 22, 2014 06:44AM

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.