List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:November 10 2004 11:54pm
Subject:Re: InnoDB data files keep growing with innodb_file_per_table
View as plain text  
John,

the output shows that there are no dangling transactions, and purge is not 
lagging behind.

If you update a secondary index column, that requires purge to clean up the 
index.

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.

After this, you can shut down mysqld, return my.cnf to what it was, and 
restart mysqld.

Regards,

Heikki


----- Original Message ----- 
From: ""John B. Ivski"" <ivski@stripped>
Newsgroups: mailing.database.myodbc
Sent: Wednesday, November 10, 2004 4:25 AM
Subject: 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"" <ivski@stripped>
>> 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
>>> 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/mysql?unsub=1
>>>
>>
>>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
> http://lists.mysql.com/mysql?unsub=1
> 

Thread
InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski9 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri9 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski10 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableSasha Pachev11 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri11 Nov
Re: InnoDB data files keep growing with innodb_file_per_tableHeikki Tuuri21 Nov
  • Re: InnoDB data files keep growing with innodb_file_per_tableJohn B. Ivski23 Nov