Hello list,
I'm having some mysterious problems when I try to migrate several
databases from one server to another one. This
databases contain some InnoDB tables - db size is around ~3GB. On both
servers I'm using MySQL version 4.1.12,
the same kernel version, same libc (2.3.2), but different architectures
(source x86, target amd64). Filesystem on both
side are xfs formated (but also tried with reiser & ext3).
I tried two ways to transfer the databases between the servers:
*) No running mysqld on both servers (successfully shutdown, no crash).
Copying the whole bunch of datafiles
(/var/lib/mysql) with rsync/scp to the other machine (files are ok,
md5check). Startup -> Everything ok
*) Active mysqld, both sides no clients connected. mysqldump (--opt
--single-transaction) from the source server,
copy the dump to the target server (dump is fine, md5check). Import on
the target server -> Everything ok
Now I let my clients connecting to the new server.
As soon as there is any data-changing query (INSERT, UPDATE) on one of
the InnoDB tables on the new server,
the query hangs - the state is "update". All other queries which also
wants to update some data gets state "locked"
(like it should be).
Process-List:
mysql> show processlist\G
*************************** 2. row ***************************
Id: 16
User: sfz.info
Host: lskeletor.:51828
db: db_sfz
Command: Query
Time: 2524
State: update
Info: INSERT INTO 4images_sessionvars
(session_id, sessionvars_name, sessionvars_value)
*************************** 3. row ***************************
Id: 34
User: sfz.info
Host: lskeletor:52044
db: db_sfz
Command: Query
Time: 2455
State: Locked
Info: DELETE FROM 4images_sessionvars
WHERE session_id NOT IN ('37b5643b556224e8c6e43e11aa
3 rows in set (0.00 sec)
But SHOW INNODB STATUS doesn't showup these transactions:
mysql> show innodb status \G
*************************** 1. row ***************************
Status:
=====================================
050726 19:57:16 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 34 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1163, signal count 1160
Mutex spin waits 1129, rounds 7484, OS waits 220
RW-shared spins 1848, OS waits 922; RW-excl spins 20, OS waits 12
------------
TRANSACTIONS
------------
Trx id counter 0 1797
Purge done for trx's n:o < 0 0 undo n:o < 0 0
History list length 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 15450, OS thread id 1134426480
MySQL thread id 13, query id 1254 localhost root
show innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
537 OS file reads, 31672 OS file writes, 5620 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
355 inserts, 355 merged recs, 41 merges
Hash table size 553253, used cells 4527, node heap has 8 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 797420905
Log flushed up to 0 797420905
Last checkpoint at 0 797420905
0 pending log writes, 0 pending chkp writes
2464 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 329850640; in additional pool allocated 2808320
Buffer pool size 16384
Free buffers 0
Database pages 16376
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 701, created 54627, written 91113
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 15450, id 1132460400, state: waiting for server
activity
Number of rows inserted 2439094, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
These are simply queries like "insert into table (field1, field2)
values (value1, value2)". No tricky conditions or indexes
and so on.
When I strace the mysqld process it doesn't do anything, it's waiting
for more connections with select(); But the "update"
query never finish. In my.cnf I specified a 60sec timeout for the InnoDB
lock condition (innodb_lock_wait_timeout).
But the "locked" queries wait for ever (like seen above in process list).
CHECK TABLE, REPAIR TABLE, OPTIMIZE TABLE, myisamchk, mysqlcheck -A
--auto-repair --extended --optimize
--repair - all useless. They haven't fixed the problem. All tools
saided the tables are ok - also the indexes.
No errors in mysql.err.
Compile options for the database-server are the same on both servers
(debian sarge packages). Also both are using the
same parameters within my.cnf.
The only difference is the hardware architecture. Can this cause such a
problem?
Thx,
Andreas
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
