因為被slow query 搞的很煩….網站速度一直起不來
因為Database 設計上的問題…..(幾個比較常用的Table 大蓋都差不多有70萬個Record )
且因為我Table Engine 是 MyISAM
用Rep 的話….若是crash 要修…好像很麻煩…
所以變成要用Rep 的話最好能先轉成InnoDB
不過因為我Slow Query 很多…若是我Rep Master Lock 的話
Slave 也會卡住…..
也不是完全的解決了Slow Query這問題….
所以我想最完美的解法可能是
Rep + Aging…
不過要花不少時間來整個改……
當前沒這麼多時間讓我來用這些…
所以我目前的解法是…
加Ram 然後把MySQL 的Ram 讓他吃愈多愈好…
Ref
MYSQL優化詳細介紹！

7.5.2. Tuning Server Parameters

Section 5.1.3, “System Variables”

5.1.2. Command Options

Section 5.1.5, “Status Variables”.

5.2.4. The Slow Query Log

結論就是RAM 有多少吃多少…
讓MySQL可以用的Ram 愈多愈好就沒錯了…

PS
FreeBSD 因為單一Process 預設只能吃到512MB 的Ram
所以在/boot/loader.conf 也要調一下

FreeBSD limits the size of a process to 512MB, even if you have much more RAM available on the system. So you may get an error such as this:

Out of memory (Needed 16391 bytes)

In current versions of FreeBSD (at least 4.x and greater), you may increase this limit by adding the following entries to the /boot/loader.conf file and rebooting the machine (these are not settings that can be changed at run time with the sysctl command):

kern.maxdsiz=”1073741824″ # 1GB
kern.dfldsiz=”1073741824″ # 1GB
kern.maxssiz=”134217728″ # 128MB

For older versions of FreeBSD, you must recompile your kernel to change the maximum data segment size for a process. In this case, you should look at the MAXDSIZ option in the LINT config file for more information.

注意一下…調到超過2G 要注意一下..
我調到4G 有發生慘事….
刻在另一篇..

剛剛在Joehorn 的個人版上看到的….
我整個很lag…
ports 裡面已經有了……
裝好後..
cp /usr/local/share/super-smack/* ~
才會有
select-key.smack和update-select.smack 這兩個檔案….
執行的話..就在console 下
#super-smack -d mysql select-key.smack 80 100
我的blog 機是FreeBSD 7.0 R
CPU: Intel(R) Xeon(TM) CPU 2.60GHz (2591.61-MHz 686-class CPU),
RAM:2G
HD: 10K SCSI 36.4G
FS: UFS2
數據是
Query_type num_queries max_time min_time q_per_s
select_index 8000 102 0 1164.87
update_index 8000 73 0 1164.87

/root>ldd /usr/local/libexec/mysqld
/usr/local/libexec/mysqld:
librt.so.1 => /usr/lib/librt.so.1 (0×2858c000)
libz.so.4 => /lib/libz.so.4 (0×28591000)
libwrap.so.5 => /usr/lib/libwrap.so.5 (0×285a3000)
libcrypt.so.4 => /lib/libcrypt.so.4 (0×285aa000)
libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0×285c3000)
libm.so.5 => /lib/libm.so.5 (0×286b8000)
libgcc_s.so.1 => /lib/libgcc_s.so.1 (0×286cd000)
libthr.so.3 => /lib/libthr.so.3 (0×286d8000)
libc.so.7 => /lib/libc.so.7 (0×286eb000)
另外一台工作機是
FreeBSD 6.2R
CPU: Intel(R) Xeon(R) CPU 5130 @ 2.00GHz (2000.08-MHz 686-class CPU)
RAM: 3G
HD:SAS 15k 好像..
Query_type num_queries max_time min_time q_per_s
select_index 8000 22 8 3012.96
update_index 8000 28 8 3012.96

db# ldd /usr/local/libexec/mysqld
/usr/local/libexec/mysqld:
libz.so.3 => /lib/libz.so.3 (0×28468000)
libwrap.so.4 => /usr/lib/libwrap.so.4 (0×28479000)
libcrypt.so.3 => /lib/libcrypt.so.3 (0×28480000)
libstdc++.so.5 => /usr/lib/libstdc++.so.5 (0×28498000)
libm.so.4 => /lib/libm.so.4 (0×28563000)
libpthread.so.2 => /lib/libpthread.so.2 (0×28579000)
libc.so.6 => /lib/libc.so.6 (0×2859e000)
我也想要破12K 呀…..