每月彙整: 2008 年 7 月

Performance Tuning Best Practices for MySQL

這是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” 一書的作者。


這四個條件是在Seeking Wisdom (From Darwin to Munger)第四部的第四章Rules And Filters規則與篩選條件中所舉的例子,華倫巴菲特在2001年一次媒體採訪中被問到,他如何評估新的企業點子,他回答說他篩選企業有四個條件:

這 四個篩選新企業點子的條件是:第一,我能否了解(Understand)這個企業點子(商業點子Business Ideas),如果可以就通過篩選。第二,它是否看起來有某種持久的競爭優勢(Sustainable competitive advantage),如果看起來有就通過篩選。第三,它的管理階層人員是否由有能力且誠實的人所組成,如果是就通過篩選。第四,它的價格是否適當,如果 再通過這個最後的篩選,我們就可以簽支票決定狗買這間企業了。


華 倫巴菲特所謂能否了解的意思是表示,我們認為我們有合理的機率能夠預測未來十年後這個企業的商業模式將發展到的地步。如果我們無法預測某種企業的未來狀 況,我們就不投資。這不表示該公司就不是好投資,只是表示我們不知道如何評估它,我們不能評估的,我們就不投資。了解不是指暸不了解它所生產的產品,而是 能否預測未來十年該企業的經濟狀況。



孟 格舉幾個好規則為例:「不要投資任何人給你一本200頁的公開說明書,加上需付出高額佣金的生意。」班哲明富蘭克林:「對眼前的工作,把自己當作工業化企 業般地去設法專業分工、大量生產,提高產量,提昇品質;不要分心到其他嘗試一夜致富的愚蠢計畫;因為專業分工、大量生產與耐心(Industry and patience are the surest means of plenty)是經過確認達到富裕最佳的途徑。」「放棄任何不懂或不能量化或不可能的生意,只和值得信任的人合作。」保險業的三條規則「只承保懂得如何評 估的風險。忽略市佔率這個數字,對失去生意給低價亂承保的同業毫無猶豫,也不難過。」「將單一風險與其連結的風險的最高損失限制在可承受的範圍內,並不斷 尋找風險事件之間可能的連動關聯。」「避免承保任何有道德風險的人,和壞人簽訂一個好合約並不是好主意,和有道德風險的人做生意損失可能很大。」

Subversion on apache22

安裝 Apache

  1. cd /usr/ports/www/apache22
  3. # SVN 會使用到 bdb4, 所以 apache 在此要一起裝起來

安裝 Subversion

  1. cd /usr/ports/devel/subversion
  3. 讓 svn 支援 apache22 的 DAV_MODULES

設定 SVN 基本環境目錄

  1. mkdir -p /home/www/svn/repository/PROJECT_NAME # 路徑自行決定後再更改即可.
  2. mkdir -p /home/www/etc # 來放 svn 所需要的設定檔
  3. svnadmin create /home/www/svn/repository/PROJECT_NAME # svn 初始化
  4. chown -R www:www /home/www/svn/repository/PROJECT_NAME
  5. chown -R www:www /home/www/etc

設定 Apache2

LoadModule dav_svn_module libexec/apache22/mod_dav_svn.so
LoadModule authz_svn_module libexec/apache22/mod_authz_svn.so

<Directory />
# AllowOverride None
# Order deny,allow
# Deny from all
AllowOverride All
Order allow,deny
Allow from All

設定 virtual host and SVN

<VirtualHost *:80>
DocumentRoot /home/www/svn/repository
ErrorLog /var/log/httpd/svn.VIRTUAL_HOSTNAME-error_log
CustomLog /var/log/httpd/svn.VIRTUAL_HOSTNAME-access_log combined
<Location /svn>
DAV svn
# 注意, 這邊要用 SVNParentPath(不能放在 config 的後面…不然會一直出現error……………)
SVNParentPath /home/www/svn/repository
# Satisfy Any
AuthType Basic
AuthName “Svn repository”
AuthUserFile /home/www/etc/svn-auth-file
AuthzSVNAccessFile /home/www/etc/svn-access-file
Require valid-user


  1. htpasswd -c /home/www/etc/svn-auth-file USER1 # 建立存取帳號
  2. htpasswd /home/www/etc/svn-auth-file USER2 # 第二個以後的帳號記得不要 -c

建立 SVN 權限控制檔案

  • vim /home/www/etc/svn-access-file

* =

USER1 = rw
USER2 = r

設定開機自動啟動 Apache




就可以看到svn 了………….

以上幾乎都是抄…Tsung 大的..