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]
