OptimizingMySQLApplicationsUsingPluggableStorageEnginesTutorial
[edit] Optimizing MySQL Applications Using the Pluggable Storage Engine Architecture (by Arjen Lentz)
Notes by Frank Mash
I am sitting in Arjen's session titled "Optimizing MySQL Applications Using the Pluggable Storage Engine Architecture." Arjen has been covering the basics of how databases work and touching up on issues related to different storage engines.
[Notes that I need to transfer (blogger wasn't working)]
Key Buffers: Are only for MyISAM tables and not for InnoDB tables. Look at the current values Key reads / Key read requests ratio should be 0.03. If the ratio is higher than this (.10) then increase the buffer. If its better than this (0.1) then we are wasting memory. If at 0.1, decrease it a bit.
The default settings are quite low on Linux. We can have multiple MyISAM key caches.
We can load MySQL key buffers into memory.
Different values of myisam_recover which can either be QUICK or BACKUP (If some corrupted tables are found, they are first backed up).
Arjen recommends that not to use external locking and then repair the table (answering a question). He says to lock tables using LOCK command and then perform operations.
table_cache (defaults to 64). This deals with file handles which tells the maximum number of open table files. If joining three tables, three MYD files would be opened. Even if we join the same table, two files would be opened so a pointer can be obtained.
myisam_sort_buffer_size: increase this for index builds. A trick is to increase and decrease it again when not needed.
myisam_max_[extra]_sort_file_size: By increasing this we can speed things up.
MyISAM local variables: allocated for each individual connection to the server.
read_buffer_size: used when reading chunk of data. Get this to be as big as we can afford to have. Think about altering it before SELECTs and then decrease it to original as this will be local.
sort_buffer_size: For GROUP BY and ORDER BY operations. If MySQL needs more value than this then it will start using disk (bad). Increase sort buffer before query and then decrease it.
tmp_table_size: It's a limit in bytes. While processing queries, tmp tables are created which are of type HEAP. When we run out of the specified size set by this variable, the table type is changed from HEAP to MyISAM.
Make the local variables bigger than default but don't make them huge.
Never make your database server swap as it will cost you greatly.
How to optimize MyISAM tables?
- Normalize
- separate static and dynamic storage format tables. (don't worry about joins as databases are made for that)
If accessing variable and fixed length data equally then it makes no sense to different separate them.
Avoid NULLS and declare the columns to be NOT NULLs whenever possible.
Inside MyISAM structure we waste resources when using NULLs as many IF statements that are used to check whether the value is NULL can add up.
When performing batch deletes, updates, inserts, consider first disabling indexes and then enabling them.
Q