A summary from the mySQL Performace Blog

Innodb takes averages over time and displays its numbers which are hard to figure out what a true average is from some sort of polling method.


----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 28655958, signal count 25483315
Mutex spin waits 1468249294, rounds 5838311115, OS waits 20658052
RW-shared spins 4676021, OS waits 1740480; RW-excl spins 13056306, OS waits 2135090


• 
  
• reservation count - counter for the sync array when slots are added to it
  
• signal count - counter for the sync array for when threads are signal for use
  
• OS Waits - used for exclusive and shared locks, and relates to …

Hello,

Although not MySQL Cluster specific, I am interested in speaking to developers or administrators about Web 2.0-type applications they are working on or actively support that use MySQL or MySQL Cluster in some capacity.

Please email me directly,

Jimmy Guerrero
Sr Product Manager
jguerrero@mysql.com


--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 25100, id 114696, state: sleeping
Number of rows inserted 200059954, updated 1278003724, deleted 130588363, read 47473646817
37.99 inserts/s, 1718.43 updates/s, 0.33 deletes/s, 5470.51 reads/s


This section shows innodb thread queue status and the state of the main thread.

I spent a productive hour with Krugle yesterday, meeting with a few members of management: Steve Larsen (CEO), Laura Merling (VP, Business Development - yes, that Laura Merling), and Ken Krugler (CTO). I have to admit: I went into the meeting with low expectations. In Ken's words, I figured a vertical search engine focused on open source and development might be interesting, but not useful (with "use" translating into dollars).

I was wrong.

First off, Krugle is more than a vertical search engine. It does that, and it does it extremely well. Krugle aggregates the many and various open source software repositories (Sourceforge being just one of them), indexes them, and makes them easily, productively searchable. So, if I know I need a utility to convert documents …


----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 13505821346; in additional pool allocated 17936896
Buffer pool size 768000
Free buffers 5
Database pages 728656
Modified db pages 108067
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 31203802, created 3930786, written 131784156
4.50 reads/s, 0.00 creates/s, 131.22 writes/s
Buffer pool hit rate 1000 / 1000


Pending reads and writes are pending requests on buffer pool.
Different types of IO are used by Innodb, think of it like a IO scheduler for INNODB itself.

LRU pages - dirty pages which were not accessed long time
flush list - old pages which need to be flushed by checkpointing process
single page - independent page writes.


---
LOG
---
Log sequence number 1039 3161691821
Log flushed up to 1039 3161691821
Last checkpoint at 1039 2401802667
0 pending log writes, 0 pending chkp writes
222872631 log i/o's done, 56.99 log i/o's/second


• 
  
• Log sequence number is the number of bytes written to the buffer pool.
  
• Log flushed up to is the number of bytes that has been flushed or unflush (sequence number - log flushed up to)
  
• Last checkpoint is a fuzzy line of what was flushed to logs ie recovery line for a crash (from my understanding).
  

Then there is some cool stats on log writes which should remain low.

Tuning
If you see more than 30% of log buffer size being unflushed (Log flushed up to) increase it if possible.


-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 16812, free list len 20200, seg size 37013, is not empty
Ibuf for space 0: size 16812, free list len 20200, seg size 37013,
89630355 inserts, 89285599 merged recs, 5642191 merges
Hash table size 24902177, used cells 16368903, node heap has 39339 buffer(s)
5523.49 hash searches/s, 1663.78 non-hash searches/s


1st 2 lines show segement size, and if their are any records in the innodb_buffer_pool
Next
How many inserts where done to the insert buffer,
Then
How many records where merged
Finally
How many merges it took to merge the records. (BTREE internals)

Adaptive hash index is hash index Innodb builds for …


--------
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: 1
29333024 OS file reads, 299924198 OS file writes, 226649540 OS fsyncs
5.33 reads/s, 17408 avg bytes/read, 195.60 writes/s, 77.31 fsyncs/s


Innodb support greater then 4 helper threads (the 4 above) in windows and with a small change the the source it can be increased for unix.

Each Helper thread shows the pending status of how many jobs are queued for executing, with just 4 helper threads on one of my most heavy hit dbs, …

I proudly present the first stable version of MySQL Community toolbar:

What is that?

MySQL Community Toolbar is a toolbar for your Firefox web browser.

You are able to:

* Navigate trough MySQL Forge and MySQL WebSite’s
* Search Internet, MySQL documentation and/or MySQL WebSite for any information, directly from your Firefox browser.

License
MySQL community Toolbar is under the GPL license. You are able to make this toolbar better. Please do it!. This version is not official MySQL one, but you are free to make improvements and bug reportings.

Download
You can install your personal copy of this toolbar by clickng here

You can visit MySQL Forge Wiki …


------------
TRANSACTIONS
------------
Trx id counter 4 4177811381
Purge done for trx's n:o < 4 4177810974 undo n:o < 0 0
History list length 68
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
MySQL thread id 6, query id 12052143 Has read all relay log; waiting for the slave I/O thread to update it
---TRANSACTION 4 4177811379, ACTIVE 0 sec, process no 1537, OS thread id 418595012843 committing


• 
  
• Purge done for trx's n:o - number of transactions to which a purge is done. Means gets rid of old transactions that are not needed anymore. *Look Tuning secion
  
• History list length 68 - number of transaction in the undo space
  
• Total number of lock structs in row lock hash table - not locked rows but structures which one row may have …