On 05/15/2013 09:06 PM, Michael Widenius wrote:
Hi!
"Gordan" == Gordan Bobic <gordan@bobich.net> 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