Re: InnoDB data files keep growing with innodb_file_per_table
Heikki, I recreated the tablespace more than a week ago, set datafile size to 10M:autoextend and its size hasn't changed since the first data import, so the problem was indeed caused by corrupted datafile. It's great that you added more debug logging, because the weirdest thing about the problem was that MySQL didn't report any errors or warnings. Thanks for looking into this issue! Good luck, Ivan Heikki Tuuri wrote: Ivan, I have now analyzed your ibdata1 file. As I suspected, the 'history list' was corrupt. It was broken at about transaction 1 500 000. Current trx id was already 20 million. The history list length was 8.5 million! Breakpoint 12, trx_purge_rseg_get_next_history_log (rseg=0x402c5268) at trx0purge.c:644 644 rseg-last_page_no = FIL_NULL; (gdb) print log_hdr $12 = (trx_ulogf_t *) 0x482dd7a6 (gdb) x/50b log_hdr 0x482dd7a6: 0x000x000x000x000x000x180x06 0xf4 0x482dd7ae: 0x000x000x000x000x000x180x06 0xf5 0x482dd7b6: 0x000x000x170xd40x000x000x00 0x00 0x482dd7be: 0x8c0x000x000x000x180x010x17 0xf6 0x482dd7c6: 0x160xc40xff0xff0xff0xff0x00 0x00 0x482dd7ce: 0x000x000x030x0f0x160xe60x17 0xf6 0x482dd7d6: 0x1c0x01 I do not know why the list had broken. The prev field is 0x, which means FIL_NULL. I have now added to 4.1.8 some debug code to track this. SHOW INNODB STATUS now prints the history list length. And if the list length exceeds 20 000 when purge thinks it has purged everything, mysqld will print to the .err log a warning like below: 041121 15:40:33 InnoDB: Warning: purge reached the head of the history list, InnoDB: but its length is still reported as 8546148! Make a detailed bug InnoDB: report, and post it to bugs.mysql.com We will see how common the history list corruption is. My tests did not produce any corruption. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, November 11, 2004 9:59 PM Subject: Re: InnoDB data files keep growing with innodb_file_per_table John, please zip ibdata1, which is 'only' 100 MB, and upload it when you have shut down mysqld. I have been simulating your workload, but I only get 25 segments. No leak seen. Regards, Heikki - Original Message - From: John B. Ivski [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, November 11, 2004 8:17 PM Subject: Re: InnoDB data files keep growing with innodb_file_per_table Heikki, Heikki Tuuri wrote: hmm... could it be that segments 0 1, 0 2, 0 3, etc. were printed close to the end of the output? The print routine first prints inode pages that are completely used, and after that other inode pages. Since the tablespace validation said the tablespace is ok, I guess the segments really are there. You're absolutely right, they're there - I must've missed them when looking through the output. They're not at the end but around 0 17000, though. SEGMENT id 0 16683 space 0; page 11430; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 16684 space 0; page 11430; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 1 space 0; page 2; res 2 used 2; full ext 0 fragm pages 2; free extents 0; not full extents 0: pages 0 SEGMENT id 0 2 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 3 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 4 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 5 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 6 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 7 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 8 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 9 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 10 space 0; page 2; res 4 used 4; full ext 0 fragm pages 4; free extents 0; not full extents 0: pages 0 SEGMENT id 0 11 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 12 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free
Re: InnoDB data files keep growing with innodb_file_per_table
Heikki, Heikki Tuuri wrote: InnoDB is clearly leaking 'segments' in the system tablespace. They are probably undo logs. For some reason, a trx commit or purge fails to free them. SEGMENT id 0 75994 space 0; page 82136; res 2720 used 2680; full ext 41 fragm pages 32; free extents 0; not full extents 1: pages 24 SEGMENT id 0 76006 space 0; page 82136; res 2592 used 2569; full ext 39 fragm pages 32; free extents 0; not full extents 1: pages 41 These big segments are about 40 MB in size. Did you do ALTER TABLE or some other big transaction involving a million rows? Certainly not ALTER TABLE... My program makes nightly backups of the database using SELECT * INTO OUTFILE but they're incremental and use less than 5 rows/table, each table being processed in a separate transaction. About a fortnight ago I had to do 3-4 complete backups when debugging the backup program, and the largest table has ~1.6M rows, so those big segments may be the (still not freed) leftovers from those complete backups. The viewer also makes SELECT * from tables, but it is rarely used. 99% of queries that involve data modification are inserts that use only one row/transaction and happen at most 4-5 times/second, 1% happen every 4 hours, update about 5 rows (total) but never change more than 1000 rows/transaction. SEGMENT id 0 88879 space 0; page 119802; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 0 space 0; page 119802; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 1 space 0; page 119802; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 Most segments seem to be undo logs resulting from small transactions. You said that you tried max_purge_lag, and it caused lengthy waits? What value did you try? I tried 100 and 1000. It was worse with the former, but both performed worse than with max_purge_lag=0. I will try to repeat the problem by simulating your database workload. Please let me know if there's any info I could help you with. Please use also innodb_table_monitor, and send the output to me. Sending it to you directly (it's ~100k and I'm not sure the list allows or needs it ;). Good luck, Ivan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB data files keep growing with innodb_file_per_table
Heikki, Heikki Tuuri wrote: hmm... could it be that segments 0 1, 0 2, 0 3, etc. were printed close to the end of the output? The print routine first prints inode pages that are completely used, and after that other inode pages. Since the tablespace validation said the tablespace is ok, I guess the segments really are there. You're absolutely right, they're there - I must've missed them when looking through the output. They're not at the end but around 0 17000, though. SEGMENT id 0 16683 space 0; page 11430; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 16684 space 0; page 11430; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 1 space 0; page 2; res 2 used 2; full ext 0 fragm pages 2; free extents 0; not full extents 0: pages 0 SEGMENT id 0 2 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 3 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 4 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 5 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 6 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 7 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 8 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 9 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 10 space 0; page 2; res 4 used 4; full ext 0 fragm pages 4; free extents 0; not full extents 0: pages 0 SEGMENT id 0 11 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 12 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 13 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 14 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 15 space 0; page 2; res 160 used 160; full ext 2 fragm pages 32; free extents 0; not full extents 0: pages 0 SEGMENT id 0 17259 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 17 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 18 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 19 space 0; page 2; res 1 used 1; full ext 0 Anyway, if we get the ibdata files, it should be relatively easy to find out what is wrong. I'll delete the whole tablespace this weekend and reimport data from backup. If it keeps growing I'll upload the data files (will be easier to do with them occupying much less than 2GB, too ;) Good luck, Ivan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB data files keep growing with innodb_file_per_table
Heikki, the output shows that there are no dangling transactions, and purge is not lagging behind. Yes, that's what I thought... weird, huh :/ If you update a secondary index column, that requires purge to clean up the index. The tables have structure similar to the following: create table table1 ( key1 tinyint unsigned not null default 0, key2 int unsigned not null default 0, data1 int not null default 0, dataN int not null default 0, primary key(key1,key2) ) engine=innodb; updates are performed like this: insert into table table1 (key1,key2,data1,dataN) values (...),...,(...) on duplicate key update data1=values(data1),dataN=values(dataN) so primary keys don't get updated... Please shut down mysqld, remove innodb_file_per_table from my.cnf, and restart mysqld. Then do CREATE TABLE test.t(a INT) TYPE=InnoDB; Then do SHOW TABLE STATUS FROM test; What does it print as the InnoDB free space for the table test.t? That is the free space in the system tablespace. Unfortunately I won't be able to shut down the server until this weekend. Will let you know the results. Thanks for the advice. Good luck, Ivan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB data files keep growing with innodb_file_per_table
Heikki, I can think of another explanation. Are you sure that all your tables really are in .ibd files? Maybe some older tables are actually in the ibdata files? Yes I'm sure they're all in .ibd files (and I've just checked just in case - they are indeed). Please use the innodb_tablespace_monitor to print the contents of the ibdata files. I feel uncomfortable about pasting here the whole 10MB output ;), but it went like this: 04 5:35:51 INNODB TABLESPACE MONITOR OUTPUT FILE SPACE INFO: id 0 size 120832, free limit 120064, free extents 3 not full frag extents 1: used pages 20, full frag extents 1462 first seg id not used 0 2 SEGMENT id 0 5635 space 0; page 461; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 5636 space 0; page 461; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 5637 space 0; page 461; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 ... the numbers increase by 1 all the way up from 5637 to 88879; res and used values are mostly 1, sometimes 2 or 3. Rarely (in like, 50 cases) they're pretty big, e.g. SEGMENT id 0 75994 space 0; page 82136; res 2720 used 2680; full ext 41 fragm pages 32; free extents 0; not full extents 1: pages 24 SEGMENT id 0 76006 space 0; page 82136; res 2592 used 2569; full ext 39 fragm pages 32; free extents 0; not full extents 1: pages 41 ... SEGMENT id 0 88879 space 0; page 119802; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 0 space 0; page 119802; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 1 space 0; page 119802; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 NUMBER of file segments: 82815 Validating tablespace Validation ok --- END OF INNODB TABLESPACE MONITOR OUTPUT === Let me know if you need the whole output - I'll zip it and send it to you. Good luck, Ivan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB data files keep growing with innodb_file_per_table
Hello, I have a DB of about 100 tables, and MySQL is configured to keep them in separate files. Total size of the files is ~2GB. Most of the tables are of similar format and consist of many short fixed-length rows (~50 bytes/row). The database size is increased for several MB a day. Initial configuration had 200MB for InnoDB data files. Initial database size was ~1GB. Current database size is, as I said, ~2GB. Current size of idbdata files is 1.8GB. As you can see, idbdata files are growing faster than the database itself. There are no lengthy transactions. I tried playing with the new 'innodb_max_purge_lag' setting, but it only made things worse (the data files kept growing and I started to get many slow queries). So, why do the data files keep growing??? Using MySQL 4.1.7 on Windows XP (3.2GHz CPU, 1GB RAM). Relevant settings from my.cnf below: max_connections=50 query_cache_size=32M table_cache=768 tmp_table_size=52M log-bin=binlog max_binlog_size=256M max_allowed_packet=32M innodb_data_file_path=ibdata1:100M;ibdata2:100M:autoextend innodb_additional_mem_pool_size=8M innodb_flush_log_at_trx_commit=0 innodb_log_buffer_size=2M innodb_buffer_pool_size=128M innodb_log_file_size=64M innodb_thread_concurrency=8 innodb_file_per_table innodb_open_files=2048 Good luck, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB data files keep growing with innodb_file_per_table
Heikki, Heikki Tuuri wrote: John, please post what SHOW INNODB STATUS says. The probable reason is that there are long transactions, or that purge is falling behind. Best regards, Heikki Tuuri Actually, I have no idea why purge was falling behind, since no row has ever been deleted from the database, and as far as I understand (and the docs say so) purge lag only concerns rows marked for deletion. Sorry, I forgot to attach 'SHOW INNODB STATUS' output to my first post, probably the main reason being that it never reported any errors or warnings, so it slipped off my mind ;) Here's some more info: The database accumulates price changes over time on foreign exchange market. 99% of all operations are single-row INSERTs/UPDATEs that happen 2-3 times/second. Once a day there're 'INSERT ... ON DUPLICATE KEY UPDATE' of several thousand rows, which are performed in 1000-row batches. All inserts use primary key and there're no auto_increment columns. SELECTs are rare (~once/hour) but can result in up to 500,000 rows returned. SELECTs use primary keys as well - there's no JOINs of any kind, only 'ORDER BY'. 'SHOW INNODB STATUS' output below. Good luck, Ivan P.S. Is there any way to see the structure/contents of idbdata files? It would probably shed some light on the subject... = 041110 5:12:05 INNODB MONITOR OUTPUT = Per second averages calculated from the last 14 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 33099, signal count 33069 Mutex spin waits 106616, rounds 265594, OS waits 2863 RW-shared spins 53566, OS waits 26538; RW-excl spins 2871, OS waits 2672 TRANSACTIONS Trx id counter 0 17800155 Purge done for trx's n:o 0 17800149 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 3696 MySQL thread id 534, query id 7111938 localhost 127.0.0.1 root show innodb status ---TRANSACTION 0 0, not started, OS thread id 896 MySQL thread id 511, query id 7080473 localhost 127.0.0.1 root ---TRANSACTION 0 17800153, not started, OS thread id 640 MySQL thread id 421, query id 7111935 localhost 127.0.0.1 mysql ---TRANSACTION 0 17800110, not started, OS thread id 2480 MySQL thread id 22, query id 7111918 localhost 127.0.0.1 mysql FILE I/O I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (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 226645 OS file reads, 1093510 OS file writes, 599391 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 3.93 writes/s, 1.64 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, 0 inserts, 0 merged recs, 0 merges Hash table size 553253, used cells 26, node heap has 1 buffer(s) 1.36 hash searches/s, 86.64 non-hash searches/s --- LOG --- Log sequence number 1 1826613855 Log flushed up to 1 1826613855 Last checkpoint at 1 1826612159 0 pending log writes, 0 pending chkp writes 125358 log i/o's done, 0.43 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 153403523; in additional pool allocated 918656 Buffer pool size 8192 Free buffers 0 Database pages 8191 Modified db pages 20 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 226501, created 8080, written 939024 0.00 reads/s, 0.00 creates/s, 3.43 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread id 956, state: sleeping Number of rows inserted 593305, updated 1606540, deleted 0, read 453171610 1.79 inserts/s, 4.00 updates/s, 0.00 deletes/s, 571.89 reads/s END OF INNODB MONITOR OUTPUT - Original Message - From: John B. Ivski [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, November 09, 2004 9:46 PM Subject: InnoDB data files keep growing with innodb_file_per_table Hello, I have a DB of about 100 tables, and MySQL is configured to keep them in separate files. Total size of the files is ~2GB. Most of the tables are of similar format and consist of many short fixed-length rows (~50 bytes/row). The database size is increased for several MB a day. Initial configuration had 200MB for InnoDB data files. Initial database size was ~1GB. Current database size is, as I said, ~2GB. Current size of idbdata files is 1.8GB. As you can see, idbdata files are growing faster than the database
