Hi list,
We are facing memory problems for our application and to analyze memory utilization by application we require below information on mysql memory utilization. how much of RAM mysql is utilizing and amount of memory allocated for innodb buffer is utilizing ,how much of innodb buffer is free and amount of thread based buffers allocated. We are getting some information with " show innodb status " please let me know how to interpret the output . I am pasting output of " show innodb status " below for reference. Please give us detailed explanation how to interpret the output. ===================================== 060504 20:00:01 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 522818462, signal count 504180146 Mutex spin waits 1363737867, rounds 305233322, OS waits 106732853 RW-shared spins 740792501, OS waits 360301857; RW-excl spins 45582064, OS waits 16212977 -------- 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 383418129 OS file reads, 59580243 OS file writes, 9957395 OS fsyncs 10.10 reads/s, 27901 avg bytes/read, 3.30 writes/s, 2.10 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 506, seg size 508, is empty Ibuf for space 0: size 1, free list len 506, seg size 508, 13076970 inserts, 13077112 merged recs, 2977056 merges Hash table size 2212699, used cells 679260, node heap has 796 buffer(s) 1161.94 hash searches/s, 984.85 non-hash searches/s --- LOG --- Log sequence number 89 54255649 Log flushed up to 89 54141193 Last checkpoint at 89 30596518 0 pending log writes, 0 pending chkp writes 2089244 log i/o's done, 1.30 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 611811110; in additional pool allocated 20954624 Buffer pool size 32768 Free buffers 0 Database pages 31972 Modified db pages 5217 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 1455127697, created 6339651, written 91917358 17.20 reads/s, 5.10 creates/s, 18.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 2 queries inside InnoDB, 0 queries in queue Main thread process no. 12278, id 28680, state: sleeping Number of rows inserted 172622749, updated 475137381, deleted 45859392, read 4138993176 6.65 inserts/s, 370.98 updates/s, 0.00 deletes/s, 163055.10 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ Thanks Anil
