[Maria-developers] Feature Request - Working auto_increment on BlackHole Engine
How difficult would be be to implement working auto-increment functionality on the BlackHole engine? This would be really useful for what I'm trying to do at the moment. Since it is not available and there are no sequences in MariaDB and MySQL, I have to resort to a hack such as a single-int-field table doing the auto-increment, and an insert trigger on the blackhole table to insert a null into the auto-increment tracking table, get LAST_INSERT_ID() to set the value in the auto-increment table, and then delete the record. Even with InnoDB log files and tablespace on tmpfs this makes the performance nosedive by a factor of 4 if the trigger doesn't bother cleaning up the auto-increment table, and a factor of 13 if it deletes the record immediately after inserting it. The actual times I tested with 24 inserter threads on a 24-thread server are 8m vs. 30m vs. 105m, i.e. a massive drop. Is there an better place than this list for a feature request? Gordan
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). 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
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
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<https://launchpad.net/~maria-developers> Post to : maria-developers@lists.**launchpad.net<maria-developers@lists.launchpad.net> 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
On 05/15/2013 09:24 PM, Roberto Spadim wrote:
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
How would the function avoid the race condition? Is it actually possible to have user-defined global variables? Gordan
it's just an idea... maybe a user function could solve your problem, get_lock don't work with tables, it just use mysql server memory i think it's not 'crash fail', if you kill the mysql process all 'locks' are released and i think it use mutex and locks to only execute 1 lock for each 'str' variable, must check the source code 2013/5/15 Gordan Bobic <gordan@bobich.net>
On 05/15/2013 09:24 PM, Roberto Spadim wrote:
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
How would the function avoid the race condition? Is it actually possible to have user-defined global variables?
Gordan
-- Roberto Spadim SPAEmpresarial
Hi!
"Gordan" == Gordan Bobic <gordan@bobich.net> writes:
Gordan> On 05/15/2013 09:24 PM, Roberto Spadim wrote:
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
Gordan> How would the function avoid the race condition? By using atomic auto increments supported by the cpu. If you make this a two fold number: 32_bit_timestamp << 32 + auto_increment Then this would even be safe in regards for server crashes. Gordan> Is it actually possible to have user-defined global variables? With a plugin, yes. Regards, Monty
participants (3)
-
Gordan Bobic -
Michael Widenius -
Roberto Spadim