Franky, you apparently have just the PRIMARY KEY defined on the table and no secondary indexes. I guess the fragmentation comes because you delete rows from in the middle with respect to the primary key ordering? Or do you also insert in random order? Anyway, you cannot do anything to prevent fragmentation.
Below the average length of a row in 50 bytes. It cannot yet be terribly fragmented, because the minimum length for an InnoDB row is about 20 bytes. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html ................ From: Franky Van Liedekerke ([EMAIL PROTECTED]) Subject: Re: innodb defragmentation question View: Complete Thread (4 articles) Original Format Newsgroups: mailing.database.myodbc Date: 2003-12-31 00:33:47 PST the problem is that, if it happens again, I get a file of 900 MB, which gets kinda big ... In order to rectify the situation after that, I'll need to dump all innodb tables, drop them and reinsert them. This would takes hours, and in the meantime the application running on top of it would be down ... Anyway, here's the output of "show table status" and "show innodb status" for the corresponding tables. Maybe you can tell me how to check if defrag is even needed? | history | InnoDB | Fixed | 6132057 | 50 | 310378496 | NULL | 0 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 8192 kB | users_groups | InnoDB | Fixed | 0 | 0 | 16384 | NULL | 0 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 8192 kB | | usrgrp | InnoDB | Dynamic | 7 | 2340 | 16384 | NULL | 16384 | 0 | 8 | NULL | NULL | NULL | | InnoDB free: 8192 kB And the "show innodb status": ===================================== 031231 9:31:03 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 23 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 269898, signal count 269639 Mutex spin waits 164682, rounds 1645099, OS waits 81402 RW-shared spins 345770, OS waits 172804; RW-excl spins 15688, OS waits 15679 ------------ TRANSACTIONS ------------ Trx id counter 0 7556158 Purge done for trx's n:o < 0 2341232 undo n:o < 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 150765 MySQL thread id 150754, query id 42886888 localhost root SHOW INNODB STATUS ---TRANSACTION 0 7556157, not started, OS thread id 55 MySQL thread id 44, query id 42886887 localhost root -------- 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 107685 OS file reads, 11603482 OS file writes, 6685465 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 8.09 writes/s, 4.26 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 144, node heap has 1 buffer(s) 0.96 hash searches/s, 3.35 non-hash searches/s --- LOG --- Log sequence number 0 395581663 Log flushed up to 0 395581663 Last checkpoint at 0 395580831 0 pending log writes, 0 pending chkp writes 6292464 log i/o's done, 4.00 log i/o's/second ---------------------- ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 17823008; in additional pool allocated 719232 Buffer pool size 512 Free buffers 0 Database pages 511 Modified db pages 14 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 110330, created 17200, written 6223975 0.00 reads/s, 0.00 creates/s, 4.30 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue Main thread id 10, state: sleeping Number of rows inserted 6116574, updated 0, deleted 9399, read 15517877 3.91 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.74 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
