這是Jay Pipes 在Google 演講時有關MySQL Tuning 的Tips

Direct URL: http://video.google.com/videoplay?docid=2524524540025172110

• [02:20] Don’t benchmark without a goal. Have a goal like “improve performance by 20%”. Otherwise you’ll waste a lot of time tuning milliseconds out of your application.
• [02:50] Change just one thing at a time and re-run the benchmarks.
• [03:40] Disable the query cache by setting the cache size to 0 when running MySQL benchmarks.
• [05:22] The best tool for profiling MySQL queries is the EXPLAIN command. Understand it!
• [06:40] Log slow queries and use mysqldumpshow to parse the log. It also has an option (–log-queries-not-using-indexes) of logging any query that does not use an index on a table.
• [07:40] Jeremy Zawodny wrote the mytop utility for monitoring the threads and overall performance of MySQL servers.
• [08:55 && 11:30] Repeated queries on an un-indexed field will kill your application faster than anything else.
• [09:30] Don’t de-normalize just because you think it will be faster. Start with normalized database schemes.
• [10:15] Server parameter tweaking is not a catch-all. Tuning server parameters can help but it’s very specific to certain situations.
• [12:05] If you use MyISAM storage engine, exploit covering indexes.
• [12:50] Ensure good selectivity on index fields.
• [14:45] On multi-column indexes, pay attention to order of fields within the index definition.
• [15:40] Be aware that as your database grows, the data in the indexed fields can gradate, deteriorating the usefulness of that index. As you data grows, always examine if the indexes you originally thought are still relevant to the data.
• [17:02] Example of a common index problem, where an index is created on multiple fields.
• [20:30] Use the smallest data types possible. Don’t use bigint, when int will do. Or, don’t use char(200), when a varchar or smaller char() would do. Using the right type will fit more records in memory or index key block, meaning fewer reads, resulting in faster performance.
• [21:30] Consider horizontally spitting many-columned tables if they contain a lot of NULLs or rarely used columns.
• [23:35] Get rid of surrogate keys (with example).
• [24:05 && 33:20] Be an SQL programmer who thinks in sets, not procedural programming paradigms.
• [24:35] InnoDB can’t optimize SELECT COUNT(*) queries. Use counter tables! That’s how to scale InnoDB.
• [27:20] Always try to isolate index fields on one side of condition in a query (with example).
• [28:20] Avoid using CURRENT_DATE() as it invalidates the cache.
• [29:34] Example of using calculated fields when searching on top level domain. Idea – put a reversed TLD in the table.
• [33:20] Avoid correlated subqueries. Think in sets not loops! Here is a great article on visualizing SQL joins.
• [34:50] Example of using derived tables to avoid correlated subqueries.
• [36:25] Be aware of global and per-thread server variables.
• [37:50] Enable query cache if your application is doing a lot more reads than writes!
• [28:50] MySQL uses MyISAM for internal data storage.
• [40:00] MySQL loves ram!
• [40:35] Q and A.

PS: Jay Pipes 是 “破 MySQL” 一書的作者。

一如往常..在看訂閱的rss 的時後..
其中這篇
有介紹幾個mysql 的工具..
其中mytop 已經快樂使用中…
另一個就是
MySQL Active Report
看起來挺不錯的..

一樣在ports 裡面也有..
#cd /usr/ports/databases/mysqlard/
#make install clean

裝好後的提示似乎不是很完整

1) Add the ‘mysqlar’ user with USAGE privileges to your mysql server:

$ mysql -u root -p mysql
mysql> GRANT USAGE ON *.* TO mysqlar@localhost;
mysql> FLUSH PRIVILEGES;

2) Add the mysqlard crontab to root’s crontab:

*/5 * * * * /usr/bin/env hourly=1 daily=1 weekly=1 monthly=1 \
/usr/local/bin/mysqlar_graph > /dev/null

3) Add a line like this to your apache’s httpd.conf:

Alias /mysqlar/ /usr/local/www/mysqlard/
<Directory “/usr/local/www/mysqlard”>
Options Indexes FollowSymLinks MultiViews ExecCGI
AllowOverride All
Order allow,deny
Allow from all
DirectoryIndex mysqlar.php
</Directory>

照著ports 上的提示做完後
還有兩個地方要做
1.
cp /usr/local/www/mysqlard/mysqlar.daily /etc/periodic/daily
cp /usr/local/www/mysqlard/mysqlar.weekly /etc/periodic/weekly/
cp /usr/local/www/mysqlard/mysqlar.mounthly /etc/periodic/monthly/
2.
#vim /etc/rc.conf
mysqlard_enable=”YES”
#/usr/local/etc/rc.d/mysqlard start

這樣就大功告成了….