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@bobich.net>
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


_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to     : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp



--
Roberto Spadim
SPAEmpresarial