Re[2]: Benchmarking innobase tables
Hello Heikki, Monday, March 19, 2001, 4:40:30 PM, you wrote: Also the problem with innobase_flush_log_at_trx_commit=0 should be there is no guarantie the last transaction commited will be on it's place if the power would be lost. Also I don't know is it possible in this case for database to be corrupted as some transactions may modify database but are not in a logfile (Let's ask Heikki about this). HT The database does not get corrupted even if you use HT innobase_flush_logs_at_trx_commit=0 and it crashes: Innobase always writes HT the appropriate log segment to disk before writing a modified database HT page to disk. In this sense the log on disk is always 'ahead' of the disk HT image of the database. But, of course, you may lose the updates of the HT latest transactions in a crash, if the database has not yet written the HT relevant log segment to disk. OK. The only question is is in this case only last transactions may be lost, and what the transaction can be only be lost completely ? I'm speaking about the situation - if I have connection there I have 1,2,3,4 transactions as a sequince - may it happen what changes made by transaction 4 take place while while by transaction 3 not ? -- Best regards, Petermailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Benchmarking innobase tables
At 20:43 Uhr -0600 17.3.2001, Dan Nelson wrote: In the last episode (Mar 17), Christian Jaeger said: innobase table: autocommit=0, rollback after each insert: 59 insert+rollback/sec. autocommit=0, one rollback at the end: 2926 inserts/sec. autocommit=0, one commit at the end:2763 inserts/sec. autocommit=1: 34 inserts/sec. In the last case I can hear the head from the hard disk vibrating, it seems that innobase synches each commit through to the disk oxide. I'm sure innobase isn't the fastest database in the world if this is true for everyone. Why could this be the case for me? If you are going to be committing on every record, you'll want your tablespace and logfile directories on separate disks to avoid thrashing. If you only have one disk and don't care if you lose the last few transactions if your system crashes, try setting innobase_flush_log_at_trx_commit=0 in my.cnf. Wow, thanks. With innobase_flush_log_at_trx_commit=0, the benchmark now shows: autocommit=0, rollback after each insert: 1587 inserts+rollbacks/sec autocommit=1: 2764 inserts/sec. That's even faster than myisam (2487 inserts/sec today)!!! ChristianJ -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: Benchmarking innobase tables
Hello Christian, Sunday, March 18, 2001, 12:22:44 PM, you wrote: If you are going to be committing on every record, you'll want your tablespace and logfile directories on separate disks to avoid thrashing. If you only have one disk and don't care if you lose the last few transactions if your system crashes, try setting innobase_flush_log_at_trx_commit=0 in my.cnf. CJ Wow, thanks. With innobase_flush_log_at_trx_commit=0, the benchmark now shows: CJ autocommit=0, rollback after each insert: 1587 inserts+rollbacks/sec CJ autocommit=1: 2764 inserts/sec. CJ That's even faster than myisam (2487 inserts/sec today)!!! In this case you should compare it to myisam created with delay_key_write=1, also the size of key_buffer matter. Also the problem with innobase_flush_log_at_trx_commit=0 should be there is no guarantie the last transaction commited will be on it's place if the power would be lost. Also I don't know is it possible in this case for database to be corrupted as some transactions may modify database but are not in a logfile (Let's ask Heikki about this). -- Best regards, Petermailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Benchmarking innobase tables
Hello
I've compiled mysql-3.23.35 with innobase support - it runs much
better than BDB for me - and run a simple benchmark with the
following script:
use DBI;
my $DB= DBI-connect("dbi:mysql:innobase","chris",shift) or die;
$DB-{RaiseError}=1;
$DB-do("drop table if exists speedtest");
$DB-do("create table speedtest (a int not null primary key, b int
not null) type=innobase");
$DB-do("set autocommit=0"); # or =1
my $ins=$DB-prepare("insert into speedtest values(?,?)");
foreach (0..1000) {
eval {
$ins-execute(int(rand(1000)),int(rand(10)) );
};
if ($@) {warn $@} else {$done++}
}
# $DB-do("commit"); # uncommented for some test
print "have inserted $done entries\n";
On a lightly loaded powermac G3 running linuxppc I get the following results:
myisam table: 2000 inserts/sec.
innobase table:
autocommit=0, rollback after each insert: 59 insert+rollback/sec.
autocommit=0, one rollback at the end: 2926 inserts/sec.
autocommit=0, one commit at the end:2763 inserts/sec.
autocommit=1: 34 inserts/sec.
In the last case I can hear the head from the hard disk vibrating, it
seems that innobase synches each commit through to the disk oxide.
I'm sure innobase isn't the fastest database in the world if this is
true for everyone. Why could this be the case for me?
Some system info:
LinuxPPC June 1999, Kernel 2.2.17-0.6.1,
glibc-2.1.3-0j
gcc-2.95.3-2f
Innobase data is written to an IDE harddisk.
Cheers
Christian Jaeger
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
