Re: large InnoDB table partitioning without explicit PK

[Miko M]

> > 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.
>
> > 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?
[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.