← Back to team overview

maria-developers team mailing list archive

Re: Feature Request - Working auto_inc rement on BlackHole Engine

 

hum, maybe a 'global variable' + a 'counter' function could do the job?

select add_and_return(@global_var,1)
could return old global_var value +1, and set the global_var to +1

just a idea.. like GET_LOCK(str,timeout) do, but without timeout


2013/5/15 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
>
>
> ______________________________**_________________
> Mailing list: https://launchpad.net/~maria-**developers<https://launchpad.net/~maria-developers>
> Post to     : maria-developers@lists.**launchpad.net<maria-developers@xxxxxxxxxxxxxxxxxxx>
> Unsubscribe : https://launchpad.net/~maria-**developers<https://launchpad.net/~maria-developers>
> More help   : https://help.launchpad.net/**ListHelp<https://help.launchpad.net/ListHelp>
>



-- 
Roberto Spadim
SPAEmpresarial

Follow ups

References