MySQL Performance Tuning

I am right now sitting in Introduction to MySQL Performance Tuning by Tobias.

Re-executing a command

We can use the -r option to re-execute a command.

For instance:

mysqladmin -ri 10 extended

will execute the command every 10 seconds

Open tables is the size of table cache.
Threads cached is caching of threads. When someone disconnects, we don't just throw it away.

If we have multiple queries at the same time, the number of tables opened will go up.

Brad of Live Journal has written a cool script called diskchecker.pl

Analyzing queries: Use the slow query log to obtain information about well, slow queries. One idea is to log all queries to CSV tables and then use them.

When enabling or disabling the slow queries log, we need to restart the server. By default the slow query log logs all those queries that take more than 10 seconds to execute.

A problem with slow query logs is that it does not tells us about the queries most frequently executed.

Tip: Get the regular expression from mysqldumpslow

If the difference between rows sent and rows examined is huge, we can add an index to the table.

If we need to obtain the information without having to get it through the slow queries log, we can use the EXPLAIN statement.

In the EXPLAIN statement, we have the following types of type:

• (system): if table has one row.
• const:
• eq_ref
• ref
• index_merge
• unique_subquery / index_subquery
• range
• index
• all

Fixing and Tuning Indexes

BTREE is always balanced. No other way to see whether index tree is balanced.

If we have redundant indexes, disk usage will go up and we will be wasting the buffer cache.

How do I see whether there are multiple (redundant) indexes created on a table

SHOW INDEX FROM ...

SHOW CREATE TABLE

For large columns that we need to have unique, it is better to store a hash value.

Data types such as VARCHAR take more space both in memory and on disk. Both VARCHAR (20) AND VARCHAR (255) will take same amount of space for small data sizes. This is because when a row is read up into memory, fixed memory buffer sizes are used. In addition, MEMORY tables use fixed size rows.

Q: Find the minimum and maximum length of the data in a table.

A: Use the PROCEDURE ANALYSE() option as in SELECT fieldname FROM table PROCEDURE ANALYSE

more soon...