List:General Discussion« Previous MessageNext Message »
From:Jacob M Date:November 18 2006 4:24am
Subject:Long readlocks with innodb under mysql-4.1.14
View as plain text  
I've got a FreeBSD system (named 'db1') running mysql 4.1.14 with
innodb.  I am running a dump onto another system in preparation for
setting up a third system as a slave.  (I can't use the "flush tables
with read lock" because the db1 has a single tablespace file and I
need to have per-database tablespace files on the slave.)

When I run the dump all the databases on db1 get a read lock and
updates hang.  The system has roughly 55GB of data in it and the dump
will take a long period of time, longer than we can have the
applications hang on inserts.

From the documentation I was not expecting read locks at all due to
innodb features.  I read elsewhere a brief read lock is issued, but
these don't seem to go away.  From the manual here:
http://mysql.he.net/doc/refman/4.1/en/mysqldump.html

#################
--single-transaction

This option issues a BEGIN SQL statement before dumping data from the
server. It is useful only with transactional tables such as InnoDB and
BDB, because then it dumps the consistent state of the database at the
time when BEGIN was issued without blocking any applications.
#################

Here is the relevant part of my dump script:

mysqldump -uroot -h $host --single-transaction --master-data \
--flush-logs --all-databases 2>> $backuplog | gzip --fast > $dumpfile

Version info:

root@db1 [ /usr/local/mysql ] (10:19 PM - Fri Nov 17)
$ ./libexec/mysqld --version
./libexec/mysqld  Ver 4.1.14-log for unknown-freebsd5.3 on i386
(Source distribution)

root@backup1 [ / ] (10:17 PM - Fri Nov 17)
$ mysqldump --version
mysqldump  Ver 10.7 Distrib 4.1.5-gamma, for portbld-freebsd5.3 (i386)

Sample readlock state from 'show processlist \G'

*************************** 9. row ***************************
     Id: 4537
   User: user
   Host: 10.0.0.1:60214
     db: replication_test
Command: Query
   Time: 93
  State: Waiting for release of readlock
   Info: UPDATE "blah" SET blah="blah"

I know the versions are slightly off between the mysqldump client and
the server but would 4.1.5 -> 4.1.14 make that big of a difference?

Thanks in advance,
Jacob
Thread
Long readlocks with innodb under mysql-4.1.14Jacob M18 Nov
Re: Long readlocks with innodb under mysql-4.1.14Heikki Tuuri20 Nov