Background:
I've developed a simplistic Perl program to test database performance with
concurrent session queries. The queries involve inserts, updates, and
deletes in order to test database performance in an OLTP mult-user ACID
compliant scenario. Obviously this is not a "real world test" but it does
stress the database engine's ability to manage transactions so it is
somewhat valid for comparison purposes.
Problem:
When I do an insert/update/delete I sporadically get the following:
":mysql::st execute failed: Deadlock found when trying to get lock; Try
restarting transaction at dafunc.pm line 340..." The word "deadlock" is
misleading because all the database changes are based on a session number
ensuring that no session is trying to change data that is also being changed
by another session. It appears a time out is occurring before the shared row
level locks are acquired. Changing innodb_lock_wait_timeout doesn't seem to
do much. How do I tune this?
Given table locking problems associated with MyISAM, it was thought that
InnoDB would perform better but in my tests performance is worse. It appears
InnoDB is taking a long time to acquire shared row level locks and is timing
out. If that's the case then the benefit of row level locks over table level
locks is more than offset by the internal InnoDB overhead to manage the
locks. Any other explanations? Any known performance issues with InnoDB? Any
Perl DBI driver performance issues?
Here's a snippet of the Perl code for the curious:
-------------------------------------------------------------------------
## Update by session, rand_val...
sub updSessionRand {
eval {
my $rtnval= 0 ;
$estart = time() ;
my $dbh = @_[1] ;
$sess_val = @_[2] ;
$sqlStmt = "UPDATE bench_data SET text_val='updated text by rand_val',
timestamp_val=$timestamp
WHERE sess_val = ? AND rand_val between ? AND ? ";
my $stmtHdl=$dbh->prepare($sqlStmt);
$stmtHdl->execute($sess_val,$sess_val+900,$sess_val+1500) ;
$dbh->commit();
$edone = time();
$totsec = $edone-$estart;
print "Session:$sess_val, upd02, seconds:$totsec\n";
};
if ($@) {
warn "Session $sess_val upd02 failed.\n $@";
$rtnval = 1 ;
}
return $rtnval ;
}
-------------------------------------------------------------------------
(Side Note: In a separate process I found out that the syntax "SELECT ...
FOR UPDATE" produces exclusive locks so I changed it to "SELECT ... LOCK IN
SHARE MODE" and that helped matters. I also tried setting the transaction
isolation level to serializable but that was worse.)
I am running MySQL version 3.23.52-max-log with Linux 2.4.18-3
(RedHat 7.3.2.96-110) on 2 PentiumIII processors with 2GB RAM.
Here are some current innodb related my.cnf settings:
set-variable = innodb_lock_wait_timeout=300
innodb_flush_log_at_trx_commit=1
set-variable = innodb_buffer_pool_size=384M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_thread_concurrency=4
TIA!
Steve Orr
sql,query
---------------------------------------------------------------------
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 <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
