maria-developers team mailing list archive

[Michael Widenius <monty@xxxxxxxxxxxx>]

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).

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)

Gordan>  Is there an better place than this list for a
Gordan>  feature request?

No, this is the right place.

Regards,
Monty