Mysql 5.0.33 on FreeBSD 6.2 amd64 platform. Using stock my-innodb-heavy-4G.cnf, with just the following changes
max_connections = 250 innodb_buffer_pool_size = 8G innodb_log_file_size = 1024M innodb_data_file_path = /var/db/mysql/tblgrp1/ibdata1:4096M;/var/db/mysql/tblgrp1/ibdata2:4096M;/var/db/mysql/tblgrp1/ibdata3 var/db/mysql/tblgrp1/ibdata4:4096M;/var/db/mysql/tblgrp2/ibdata5:4096M;/var/db/mysql/tblgrp2/ibdata6:4096M;/var/db/mysql/tblg ta7:4096M;/var/db/mysql/tblgrp2/ibdata8:4096M;/var/db/mysql/tblgrp3/ibdata9:4096M;/var/db/mysql/tblgrp3/ibdata10:4096M;/var/d tblgrp3/ibdata11:4096M;/var/db/mysql/tblgrp3/ibdata12:4096M;/var/db/mysql/tblgrp4/ibdata13:4096M;/var/db/mysql/tblgrp4/ibdata ;/var/db/mysql/tblgrp4/ibdata15:4096M;/var/db/mysql/tblgrp4/ibdata16:4096M;/var/db/mysql/tblgrp5/ibdata17:4096M;/var/db/mysql /ibdata18:4096M;/var/db/mysql/tblgrp5/ibdata19:4096M;/var/db/mysql/tblgrp5/ibdata2O:4096M;/var/db/mysql/tblgrp6/ibdata21:4096 b/mysql/tblgrp6/ibdata22:4096M;/var/db/mysql/tblgrp6/ibdata23:4096M;/var/db/mysql/tblgrp6/ibdata24:4096M;/var/db/mysql/tblgrp 25:4096M The application is SpamAssassin bayes and AWL db, which has grown to 10gb so far and is expected to grow to 60-80gb. The machine has 16 gb of ram and 17gb allocated for swap. These values were added to /boot/loader.conf to ensure the process size can allocate all the memory it needs. kern.dfldsiz="34359738368" # 32GB kern.maxdsiz="34359738368" #32GB the following was added to /etc/libmap.conf as per many FreeBSD/MySQL advisories. [mysqld] libpthread.so.2 libthr.so.2 libpthread.so libthr.so innodb engine status, never shows more then 10gb with change allocated. The database keeps crashing, but does come back on line after rolling back some transactions. I did a full database dump, dropped the database, re imported everything and it's still happening. check table statement returns OK on all tables. below is the error output and innodb monitor output prior to crash. InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Error: semaphore wait has lasted > 600 seconds InnoDB: We intentionally crash the server, because it appears to be hung. 070302 21:26:19InnoDB: Assertion failure in thread 9916029952 in file srv0srv.c line 2093 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=33554432 read_buffer_size=2093056 max_used_connections=143 max_connections=250 threads_connected=143 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2591766 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. and here's innod db monitor stuff. ===================================== 070302 21:26:12 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 16 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 635154, signal count 531528 --Thread 9952626176 has waited at ./../include/fut0fut.ic line 31 for 797.00 seconds the semaphore: X-lock on RW-latch at 0x218a38c40 created in file buf0buf.c line 497 a writer (thread id 9952980992) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file buf0flu.c line 646 Last time write locked in file ./../include/trx0undo.ic line 127 --Thread 9952980992 has waited at ./../include/log0log.ic line 315 for 797.00 seconds the semaphore: Mutex at 0x4dda900 created file log0log.c line 744, lock var 0 waiters flag 0 --Thread 9916030464 has waited at trx0purge.c line 619 for 797.00 seconds the semaphore: Mutex at 0x4df42b0 created file trx0rseg.c line 150, lock var 1 waiters flag 1 --Thread 9992396800 has waited at trx0rec.c line 1124 for 797.00 seconds the semaphore: Mutex at 0x4df42b0 created file trx0rseg.c line 150, lock var 1 waiters flag 1 Mutex spin waits 0, rounds 48623509, OS waits 354300 RW-shared spins 287504, OS waits 140347; RW-excl spins 55637, OS waits 17683 ------------ TRANSACTIONS ------------ Trx id counter 0 37189766 Purge done for trx's n:o < 0 37175588 undo n:o < 0 0 History list length 3417 Total number of lock structs in row lock hash table 117 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 37189506, not started, OS thread id 9953617408 waiting in InnoDB queue mysql tables in use 1, locked 1 MySQL thread id 212637, query id 14223490 nero.aecom.yu.edu 129.98.1.54 spamassassin update INSERT INTO bayes_token (id, token, spam_count, ham_count, atime) VALUES ('1785','<?!?','1','0','1172887965') ---TRANSACTION 0 37189641, not started, OS thread id 9953616896 waiting in InnoDB queue mysql tables in use 1, locked 1 MySQL thread id 212631, query id 14223477 herod.aecom.yu.edu 129.98.1.169 spamassassin update INSERT INTO bayes_token (id, token, spam_count, ham_count, atime) VALUES ('363','>\0s','1','0','1172887962') ---TRANSACTION 0 37189647, not started, OS thread id 9960011776 waiting in InnoDB queue mysql tables in use 1, locked 1 MySQL thread id 212630, query id 14223481 nero.aecom.yu.edu 129.98.1.54 spamassassin Updating UPDATE bayes_token SET spam_count = spam_count + '1', atime = '1172887956' WHERE id = '164' AND token = '??1 AND spam_count + '1' >= 0 ---TRANSACTION 0 37189574, not started, OS thread id 9992536064 waiting in InnoDB queue mysql tables in use 1, locked 1 MySQL thread id 212629, query id 14223442 nero.aecom.yu.edu 129.98.1.54 spamassassin Updating UPDATE bayes_token SET spam_count = spam_count + '1', atime = '1172887955' WHERE id = '777' AND token = '-[c' AND spam_count + '1' >= 0 ---TRANSACTION 0 37189643, not started, OS thread id 10019289600 waiting in InnoDB queue mysql tables in use 1, locked 1 MySQL thread id 212622, query id 14223478 herod.aecom.yu.edu 129.98.1.169 spamassassin update INSERT INTO bayes_token (id, token, spam_count, ham_count, atime) VALUES ('1037','??5?','1','0','1172887962') hundreds more transactions..... -------------------------------------------------------------------cut. FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (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 1862774 OS file reads, 14287791 OS file writes, 136773 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 3, free list len 85, seg size 89, 156237 inserts, 155932 merged recs, 133765 merges Hash table size 17700857, used cells 4884304, node heap has 8432 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 7 1211973294 Log flushed up to 7 1211949595 Last checkpoint at 7 659507702 0 pending log writes, 0 pending chkp writes 13224389 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 9449062464; in additional pool allocated 15338752 Buffer pool size 524288 Free buffers 30 Database pages 515825 Modified db pages 280098 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 2780882, created 34047, written 2135990 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -------------- ROW OPERATIONS -------------- 16 queries inside InnoDB, 124 queries in queue 16 read views open inside InnoDB Main thread id 9916030464, state: purging Number of rows inserted 1431335, updated 6771333, deleted 358583, read 185133272 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
