The total possible memory MySQL will use is: innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB
Someone please correct me if I'm wrong. Are your most heavily used tables innodb? If not, then you're not looking at the right fields. You're doing a TON of reads per second, I'm impressed.
6.65 inserts/s, 370.98 updates/s, 0.00 deletes/s, 163055.10 reads/s
You might want to check your slow query logs and use EXPLAIN on the queries to see if an index will help you use less memory (ie, if you're doing a full table scan).
Total memory allocated 611811110; in additional pool allocated 20954624
So you have about 583 M of memory allocated. That's not a lot, given the huge amount of reads you're doing. And your additional pool gives you about 2M more. I just did some stats on one of our medium-use servers: 7.00 inserts/s, 227.89 updates/s, 2.00 deletes/s, 14198.90 reads/s Total memory allocated 4753221600; in additional pool allocated 33100800 That's almost 4.5G of memory (and we need it all!!!!) allocated with an additional pool of 31M.
From your stats: Buffer pool size 32768 Free buffers 0
This means you're using ALL your buffers!
From our medium use server:
Buffer pool size 262144 Free buffers 37108 (and we peak Sun and Mon nights, so having free buffer space now is a good thing). What is innodb_buffer_pool_size set to? (ours is 4G) I'm betting that needs to be changed. -Sheeri On 5/5/06, Anil <[EMAIL PROTECTED]> wrote:
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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
