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 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 > -- 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]
