Re: InnoDB migration between servers
Hello. If you have a reproducible test case, you should report a bug. Andreas Unterkircher [EMAIL PROTECTED] wrote: I can now isolate the problems with the appearing lock of some queries. I updated MySQL from 4.1 to 5beta (mysql-standard-5.0.10-beta-linux-x86_64-glibc23) to test if the problem still appears in the newer version. I made a full sqldump on the old server and import it successfully on the new server. If I start MySQL5 manually (bin/mysqld_safe --user=mysql ) I have absolutely no troubles. Then I addepted the debian mysql init start script to the needings of the new MySQL5 installation. The Debian-Script makes a mysqlcheck of every table after the MySQL-Server is up and running. After mysqlcheck finished (irrelevant if storage engine is MyISAM or InnoDB) the lock problem comes back again. I guess that the index of these tables getting corrupted some how. I can also reproduce this with CHECK TABLE, ANALYZE TABLE, REPAIR TABLE and OPTIMIZE TABLE. I can fix the problem, if I restart the server to get away these unkillable queries and make a ALTER TABLE tablename TYPE=innodb or ALTER TABLE tablename TYPE=myisam - but this helps only till next time I run a table check or optimizer on these tables Cheers, Andreas -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB migration between servers
I can now isolate the problems with the appearing lock of some queries. I updated MySQL from 4.1 to 5beta (mysql-standard-5.0.10-beta-linux-x86_64-glibc23) to test if the problem still appears in the newer version. I made a full sqldump on the old server and import it successfully on the new server. If I start MySQL5 manually (bin/mysqld_safe --user=mysql ) I have absolutely no troubles. Then I addepted the debian mysql init start script to the needings of the new MySQL5 installation. The Debian-Script makes a mysqlcheck of every table after the MySQL-Server is up and running. After mysqlcheck finished (irrelevant if storage engine is MyISAM or InnoDB) the lock problem comes back again. I guess that the index of these tables getting corrupted some how. I can also reproduce this with CHECK TABLE, ANALYZE TABLE, REPAIR TABLE and OPTIMIZE TABLE. I can fix the problem, if I restart the server to get away these unkillable queries and make a ALTER TABLE tablename TYPE=innodb or ALTER TABLE tablename TYPE=myisam - but this helps only till next time I run a table check or optimizer on these tables Cheers, Andreas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB migration between servers
When I strace the mysqld process it doesn't do anything, it's waiting Do you use the official binaries? There're additional InnoDB monitors mentioned at: http://dev.mysql.com/doc/mysql/en/innodb-monitor.html which could provide more information. My first try was with the included debian-mysql-amd64 package (against 64bit libs compiled). But it also happens with the official binaries (for example with mysql-standard-4.1.13-unknown-linux-gnu-x86_64-glibc23.tar.gz). I already tried to activated the innodb monitor (create table ...). But SHOW INNODB STATUS still show no problem (or active transactions), while SHOW PROCESSLIST show me ongoing locked queries... When I strace the mysqld process it doesn't do anything, it's waiting MySQL trace files often help. See: http://dev.mysql.com/doc/mysql/en/making-trace-files.html I get a big trace file... but for me it's not readable or providing any help... Possible that I hit the problem with mysql/libc2.3/amd64 ... http://hashmysql.org/index.php?title=Opteron_HOWTO#pthread_rwlock_wrlock_hang_with_nptl Cheers, Andreas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB migration between servers
Hello.
When I strace the mysqld process it doesn't do anything, it's waiting
Do you use the official binaries? There're additional InnoDB monitors
mentioned at:
http://dev.mysql.com/doc/mysql/en/innodb-monitor.html
which could provide more information.
When I strace the mysqld process it doesn't do anything, it's waiting
MySQL trace files often help. See:
http://dev.mysql.com/doc/mysql/en/making-trace-files.html
Andreas Unterkircher [EMAIL PROTECTED] wrote:
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
InnoDB migration between servers
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
