October 31, 2008

A quest for the full InnoDB status

Posted by Maciej Dobrzanski |

When running InnoDB you are able to dig into the engine internals, look at various gauges and counters, see past deadlocks and the list of all open transactions. This is in your reach with one simple command — SHOW ENGINE InnoDB STATUS. On most occasions it works beautifully. The problems appear when you have a large spike in number of connections to MySQL, which often happens when several transactions kill the database performance resulting in very long execution times for even simplest queries, or a huge deadlock.

In such rare cases SHOW ENGINE InnoDB STATUS often fails to provide the necessary information. The reason is that its output is limited to 64000 bytes, so a long list of transactions or a large deadlock dump may easily exhaust the limit. MySQL in such situation truncates the output so it fits the required size and obviously this is not good since you may lose some valuable information from your sight.
[read more...]

Posted by Maciej Dobrzanski @ 3:11 pm :: Innodb, tips :: 4 comments
Print This Post Print This Post book mark A quest for the full InnoDB status in del.icio.usdel.icio.us :: submit A quest for the full InnoDB status to digg.comdigg

How expensive is a WHERE clause in MySQL?

Posted by Baron Schwartz |

This is a fun question I’ve been wanting to test for some time.  How much overhead does a trivial WHERE clause add to a MySQL query?  To find out, I set my InnoDB buffer pool to 256MB and created a table that’s large enough to test, but small enough to fit wholly in memory:

[read more...]

Posted by Baron Schwartz @ 5:42 am :: benchmarks, optimizer :: 15 comments
Print This Post Print This Post book mark How expensive is a WHERE clause in MySQL? in del.icio.usdel.icio.us :: submit How expensive is a WHERE clause in MySQL? to digg.comdigg