John,
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?
Please use the innodb_tablespace_monitor to print the contents of the ibdata files.
Do like this:
mysql> create table innodb_tablespace_monitor(a int); Query OK, 0 rows affected (0.07 sec)
<wait 60 seconds here>
mysql> drop table innodb_tablespace_monitor; Query OK, 0 rows affected (0.00 sec)
mysqld prints:
================================================ 041111 1:39:22 INNODB TABLESPACE MONITOR OUTPUT ================================================ FILE SPACE INFO: id 0 size 1152, free limit 320, free extents 1 not full frag extents 2: used pages 71, full frag extents 0 first seg id not used 0 81 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 3 used 3; full ext 0 fragm pages 3; 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 4 used 4; full ext 0 fragm pages 4; 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 3 used 3; full ext 0 fragm pages 3; 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 65 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 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 20 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 21 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 22 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 23 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 24 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 73 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 68 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 70 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 75 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 33 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 35 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 79 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 80 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 40 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 59 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 60 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 NUMBER of file segments: 35 Validating tablespace Validation ok --------------------------------------- END OF INNODB TABLESPACE MONITOR OUTPUT =======================================
Regards,
Heikki
----- Original Message ----- From: ""Heikki Tuuri"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Thursday, November 11, 2004 1:55 AM
Subject: Re: InnoDB data files keep growing with innodb_file_per_table
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"" <[EMAIL PROTECTED]>
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"" <[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 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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
