maria-developers team mailing list archive

[Gordan Bobic <gordan@xxxxxxxxxx>]

On 05/15/2013 09:06 PM, Michael Widenius wrote:
> Hi!
>
> > > > > > "Gordan" == Gordan Bobic <gordan@xxxxxxxxxx> writes:
>
> Gordan>  How difficult would be be to implement working
> Gordan>  auto-increment functionality on the BlackHole engine?
>
> Gordan>  This would be really useful for what I'm trying to do at
> Gordan>  the moment. Since it is not available and there are no
> Gordan>  sequences in MariaDB and MySQL, I have to resort to a
> Gordan>  hack such as a single-int-field table doing the
> Gordan>  auto-increment, and an insert trigger on the blackhole
> Gordan>  table to insert a null into the auto-increment
> Gordan>  tracking table, get LAST_INSERT_ID() to set the
> Gordan>  value in the auto-increment table, and then delete
> Gordan>  the record.
>
> What problem is that you wanted to solve?
> Normally blackhole is used just for creating a slave of a slave that
> doesn't have to hold any data (only the logs).

The problem is a huge volume logging application that needs to have 
uniquely enumerated records for auditing purposes.

> Gordan>  Even with InnoDB log files and tablespace on tmpfs
> Gordan>  this makes the performance nosedive by a factor of 4
> Gordan>  if the trigger doesn't bother cleaning up the
> Gordan>  auto-increment table, and a factor of 13 if it
> Gordan>  deletes the record immediately after inserting it.
> Gordan>  The actual times I tested with 24 inserter threads
> Gordan>  on a 24-thread server are 8m vs. 30m vs. 105m, i.e.
> Gordan>  a massive drop.
>
> Why not use in memory tables for this?
> (ENGINE=MEMORY)

My tests show that ENGINE=MEMORY has terrible concurrency. It behaves 
essentially the same as MyISAM as far as write-concurrency is concerned. 
Sure, it's faster than MyISAM in the single-thread case, but my testing 
has shown that it doesn't scale past that single thread at all. 
Unfortunately, the application in question has hundreds of concurrent 
writers. Performance in my testing has been an order of magnitude worse 
with ENGINE=MEMORY than with ENGINE=InnoDB with ibdata and ib_log files 
on tmpfs.

Gordan