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]
