MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:John B. Ivski Date:November 10 2004 2:24am
Subject:Re: InnoDB data files keep growing with innodb_file_per_table
View as plain text  
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
>>
> 
> 

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