----------------------
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 …

Last year I visited Utrecht where we installed some MySQL Cluster. This week I had the chance to attend a training (again MySQL Cluster) in Leiden. Tuesday I had a good excuse to visit Amsterdam and next day Leiden.

Both Amsterdam and Leiden have all these canals which were preserved unlike in Brussels where they actually made the river run under the city (stupid).. I did in both cities a tour on the water but Leiden won. I guess it was more romantic and I had lots of colleagues on the boat too.

One can actually get easy lost in Leiden, or I guess everywhere, but I just lost my colleagues whiles looking at some vinyl shops and old turntables (drool). That was the start of some 3 hours of interesting stuff..

Tomorrow I leave The Netherlands again for Germany after spending some weeks with my family in Belgium. I wonder how cluttered my mailbox is going to be.

(pictures will be posted later …

We think it's time to commoditize PHP web application security. You may have heard of Chorizo!. We're proud to announce that from now on it's possible to register for a free account on chorizo-scanner.com.

With this free account, it's possible to use the Chorizo! application service as a proxy and scan 1 host. All scan datas are encrypted, your data is only visible to you. There are also some valuable help documents available that explain the whole process from registering up to uploading the signature file onto your host and how to analyze the results. Please note: as Chorizo! is an application service, you can only scan your hosts that are publicly available or their firewall has the chorizo-scanner.com IP configured. For those of you who want to …

I’m playing with the latest 5.1.11 beta in a master/slave replication situation. Given a lack of H/W lying around I’m configuring a mixed H/W setup to leverage an existing office’s after hours CPUs running Windows XP for my slaves. So here is my test setup.

Server

Linux - 192.168.100.170

The following are the relevent my.cnf settings

[mysqld]
port=3307
server-id = 1
log-bin=mysql-bin

Confirming the server. I did some test commands prior to confirm the position.

$ mysql -uroot -p mysql
Your MySQL connection id is 47 to server version: 5.1.11-beta-log
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     1759 |              |                  | …

At both my current and previous employer I've been involved in designing and maintaining aggregate, or "rollup" tables for advertising traffic data. I have learned several methods of propagating changes from the atomic data through to its aggregations. This article discusses these methods, how to implement them, and their pros and cons.