情境描述：
公司同事的Gmail已滿,但該同事又不想把信收到PC
因為想要雲端作業
解決方法，新增一「john.0」帳號，將john中所有信件移到「john.0」中，
如果之後要看舊的信件，就到john.0帳號中去看。
帳號:john
新增帳號:john.0
用Gmail登入到john帳號
點選「設定」->「轉寄和 POP/IMAP」
設定POP 下載，
選擇「對所有郵件啟用 POP 功能 (包括已經下載的郵件)」 & 當郵件以 POP 存取後「刪除Gmail副本」
然後按「儲存變更」
用Gmail登入到john.0帳號
點選「設定」->「帳戶和匯入」
設定以 POP3 檢查郵件：
點選「新增POP3電子郵件帳戶」
新增john帳號，然後點選「立即檢查郵件」
等所有信件都從john帳號中下載到john.0後
刪除john帳號
再用Gmail登入到john帳號
點選「設定」->「轉寄和 POP/IMAP」
設定POP 下載，
選擇「停用 POP」 & 當郵件以 POP 存取後「在收信匣保留Gmail副本」
最後再按儲存變更即可
2010年5月31日 星期一
[Debug]URL Rewrite escape dot
Question
RewriteRule ^(rank/review_express/ranking)-([^a-z]{1}[^.]+)\.htm$ rewrite/rewriteVerUrlRewriteCommon?rewrite_filename=$1&rewrite_params=$2 [QSA,L]
一直無法解析成功
原來是Server上的/rank/review_express/ranking的目錄被刪除了,新增完後就ok了
RewriteRule ^(rank/review_express/ranking)-([^a-z]{1}[^.]+)\.htm$ rewrite/rewriteVerUrlRewriteCommon?rewrite_filename=$1&rewrite_params=$2 [QSA,L]
一直無法解析成功
原來是Server上的/rank/review_express/ranking的目錄被刪除了,新增完後就ok了
2010年5月26日 星期三
TCP Tuning for Busy Apache Webserver on CentOS5
Recently I was in a situation where a very busy webserver was not responding. Strangely, top showed plenty of CPU available. The server was essentially just sitting there. What do do?
Upon further investigation, it turned out that the network queue was saturated. So many incoming connections were being attempted that they were falling off the end. Some TCP tuning was in order. Fortunately the server was not memory-starved so allocating more memory to the network stack was not a problem. Here's what ended up in /etc/sysctl.conf and turned the server back into a faithful workhorse.
# Kernel tuning settings for CentOS5,
# busy webserver with lots of free memory.
# Big queue for the network device
net.core.netdev_max_backlog=30000
# Lots of local ports for connections
net.ipv4.tcp_max_tw_buckets=2000000
# Bump up send/receive buffer sizes
net.core.rmem_default=262141
net.core.wmem_default=262141
net.core.rmem_max=262141
net.core.wmem_max=262141
# Disable TCP selective acknowledgements
net.ipv4.tcp_sack=0
net.ipv4.tcp_dsack=0
# Decrease the amount of time we spend
# trying to maintain connections
net.ipv4.tcp_retries2=5
net.ipv4.tcp_fin_timeout=60
net.ipv4.tcp_keepalive_time=120
net.ipv4.tcp_keepalive_intvl=30
net.ipv4.tcp_keepalive_probes=3
# Increase the number of incoming connections
# that can queue up before dropping
net.core.somaxconn=256
# Increase option memory buffers
net.core.optmem_max=20480
There are plenty of other sysctl options to tune, but the above made the most difference.
And netstat -s is your friend.
摘自：http://www.sysarchitects.com/node/69
Upon further investigation, it turned out that the network queue was saturated. So many incoming connections were being attempted that they were falling off the end. Some TCP tuning was in order. Fortunately the server was not memory-starved so allocating more memory to the network stack was not a problem. Here's what ended up in /etc/sysctl.conf and turned the server back into a faithful workhorse.
# Kernel tuning settings for CentOS5,
# busy webserver with lots of free memory.
# Big queue for the network device
net.core.netdev_max_backlog=30000
# Lots of local ports for connections
net.ipv4.tcp_max_tw_buckets=2000000
# Bump up send/receive buffer sizes
net.core.rmem_default=262141
net.core.wmem_default=262141
net.core.rmem_max=262141
net.core.wmem_max=262141
# Disable TCP selective acknowledgements
net.ipv4.tcp_sack=0
net.ipv4.tcp_dsack=0
# Decrease the amount of time we spend
# trying to maintain connections
net.ipv4.tcp_retries2=5
net.ipv4.tcp_fin_timeout=60
net.ipv4.tcp_keepalive_time=120
net.ipv4.tcp_keepalive_intvl=30
net.ipv4.tcp_keepalive_probes=3
# Increase the number of incoming connections
# that can queue up before dropping
net.core.somaxconn=256
# Increase option memory buffers
net.core.optmem_max=20480
There are plenty of other sysctl options to tune, but the above made the most difference.
And netstat -s is your friend.
摘自：http://www.sysarchitects.com/node/69
Linux Tuning Parameters
Using all the resources available to you?
Many default settings in Linux suck
Font server for X Windows is running as a daemon by default, but do you need it?
Check out these tunings that can give you lots of computing juice...
Kernel Network Disk I/O Others
Kernel
To successfully run enterprise applications, such as a database server, on your Linux distribution, you may be required to update some of the default kernel parameter settings. For example, the 2.4.x series kernel message queue parameter msgmni has a default value (for example, shared memory, or shmmax is only 33,554,432 bytes on Red Hat Linux by default) that allows only a limited number of simultaneous connections to a database. Here are some recommended values (by the IBM DB2 Support Web site) for database servers to run optimally:
- kernel.shmmax=268435456 for 32-bit
- kernel.shmmax=1073741824 for 64-bit
- kernel.msgmni=1024
- fs.file-max=8192
- kernel.sem="250 32000 32 1024"
Shared Memory
To view current settings, run command:
# more /proc/sys/kernel/shmmax
To set it to a new value for this running session, which takes effect immediately, run command:
# echo 268435456 > /proc/sys/kernel/shmmax
To set it to a new value permanently (so it survives reboots), modify the sysctl.conf file:
...
kernel.shmmax = 268435456
...
Semaphores
To view current settings, run command:
# more /proc/sys/kernel/sem
250 32000 32 1024
To set it to a new value for this running session, which takes effect immediately, run command:
# echo 500 512000 64 2048 > /proc/sys/kernel/sem
Parameters meaning:
SEMMSL - semaphores per ID
SEMMNS - (SEMMNI*SEMMSL) max semaphores in system
SEMOPM - max operations per semop call
SEMMNI - max semaphore identifiers
ulimits
To view current settings, run command:
# ulimit -a
To set it to a new value for this running session, which takes effect immediately, run command:
# ulimit -n 8800
# ulimit -n -1 // for unlimited; recommended if server isn't shared
Alternatively, if you want the changes to survive reboot, do the following:
- Exit all shell sessions for the user you want to change limits on.
- As root, edit the file /etc/security/limits.conf and add these two lines toward the end:
user1 soft nofile 16000
user1 hard nofile 20000
** the two lines above changes the max number of file handles - nofile - to new settings.
- Save the file.
- Login as the user1 again. The new changes will be in effect.
Message queues
To view current settings, run command:
# more /proc/sys/kernel/msgmni
# more /proc/sys/kernel/msgmax
To set it to a new value for this running session, which takes effect immediately, run command:
# echo 2048 > /proc/sys/kernel/msgmni
# echo 64000 > /proc/sys/kernel/msgmax
Network
Gigabit-based network interfaces have many performance-related parameters inside of their device driver such as CPU affinity. Also, the TCP protocol can be tuned to increase network throughput for connection-hungry applications.
Tune TCP
To view current TCP settings, run command:
# sysctl net.ipv4.tcp_keepalive_time
net.ipv4.tcp_keepalive_time = 7200 // 2 hours
where net.ipv4.tcp_keepalive_time is a TCP tuning parameter.
To set a TCP parameter to a value, run command:
# sysctl -w net.ipv4.tcp_keepalive_time=1800
A list of recommended TCP parameters, values, and their meanings:
Tuning Parameter Tuning Value Description of impact
------------------------------------------------------------------------------
net.ipv4.tcp_tw_reuse
net.ipv4.tcp_tw_recycle 1 Reuse sockets in the time-wait state
---
net.core.wmem_max 8388608 Increase the maximum write buffer queue size
---
net.core.rmem_max 8388608 Increase the maximum read buffer queue size
---
net.ipv4.tcp_rmem 4096 87380 8388608 Set the minimum, initial, and maximum sizes for the
read buffer. Note that this maximum should be less
than or equal to the value set in net.core.rmem_max.
---
net.ipv4.tcp_wmem 4096 87380 8388608 Set the minimum, initial, and maximum sizes for the
write buffer. Note that this maximum should be less
than or equal to the value set in net.core.wmem_max.
---
timeout_timewait echo 30 > /proc/sys/net/ipv4/tcp_fin_timeout Determines the time that must elapse before
TCP/IP can release a closed connection and reuse its resources.
This interval between closure and release is known as the TIME_WAIT
state or twice the maximum segment lifetime (2MSL) state.
During this time, reopening the connection to the client and
server cost less than establishing a new connection. By reducing the
value of this entry, TCP/IP can release closed connections faster, providing
more resources for new connections. Adjust this parameter if the running application
requires rapid release, the creation of new connections, and a low throughput
due to many connections sitting in the TIME_WAIT state.
Disk I/O
Choose the Right File System
Use 'ext3' file system in Linux.
- It is enhanced version of ext2
- With journaling capability - high level of data integrity (in event of unclean shutdown)
- It does not need to check disks on unclean shutdown and reboot (time consuming)
- Faster write - ext3 journaling optimizes hard drive head motion
# mke2fs -j -b 2048 -i 4096 /dev/sda
mke2fs 1.32 (09-Nov-2002)
/dev/sda is entire device, not just one partition!
Proceed anyway? (y,n) y
Filesystem label=
OS type: Linux
Block size=2048 (log=1)
Fragment size=2048 (log=1)
13107200 inodes, 26214400 blocks
1310720 blocks (5.00%) reserved for the super user
First data block=0
1600 block groups
16384 blocks per group, 16384 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
16384, 49152, 81920, 114688, 147456, 409600, 442368, 802816, 1327104,
2048000, 3981312, 5619712, 10240000, 11943936
Writing inode tables: done
Writing superblocks and filesystem accounting information: done
This filesystem will be automatically checked every 28 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
Use 'noatime' File System Mount Option
Use 'noatime' option in the file system boot-up configuration file 'fstab'. Edit the fstab file under /etc. This option works the best if external storage is used, for example, SAN:
# more /etc/fstab
LABEL=/ / ext3 defaults 1 1
none /dev/pts devpts gid=5,mode=620 0 0
none /proc proc defaults 0 0
none /dev/shm tmpfs defaults 0 0
/dev/sdc2 swap swap defaults 0 0
/dev/cdrom /mnt/cdrom udf,iso9660 noauto,owner,kudzu,ro 0 0
/dev/fd0 /mnt/floppy auto noauto,owner,kudzu 0 0
/dev/sda /database ext3 defaults,noatime 1 2
/dev/sdb /logs ext3 defaults,noatime 1 2
/dev/sdc /multimediafiles ext3 defaults,noatime 1 2
Tune the Elevator Algorithm in Linux Kernel for Disk I/O
After choosing the file system, there are several kernel and mounting options that can affect it. One such kernel setting is the elevator algorithm. Tuning the elevator algorithm helps the system balance the need for low latency with the need to collect enough data to efficiently organize batches of read and write requests to the disk. The elevator algorithm can be adjusted with the following command:
# elvtune -r 1024 -w 2048 /dev/sda
/dev/sda elevator ID 2
read_latency: 1024
write_latency: 2048
max_bomb_segments: 6
The parameters are: read latency (-r), write latency (-w) and the device affected.
Red Hat recommends using a read latency half the size of the write latency (as shown).
As usual, to make this setting permanent, add the 'elvtune' command to the
/etc/rc.d/rc.local script.
Others
Disable Unnecessary Daemons (They Take up Memory and CPU)
There are daemons (background services) running on every server that are probably not needed. Disabling these daemons frees memory, decreases startup time, and decreases the number of processes that the CPU has to handle. A side benefit to this is increased security of the server because fewer daemons mean fewer exploitable processes.
Some example Linux daemons running by default (and should be disabled). Use command:
#/sbin/chkconfig --levels 2345 sendmail off
#/sbin/chkconfig sendmail off
Daemon
Description
apmd
Advanced power management daemon
autofs
Automatically mounts file systems on demand (i.e.: mounts a CD-ROM automatically)
cups
Common UNIX� Printing System
hpoj
HP OfficeJet support
isdn
ISDN modem support
netfs
Used in support of exporting NFS shares
nfslock
Used for file locking with NFS
pcmcia
PCMCIA support on a server
rhnsd
Red Hat Network update service for checking for updates and security errata
sendmail
Mail Transport Agent
xfs
Font server for X Windows
Shutdown GUI
Normally, there is no need for a GUI on a Linux server. All administration tasks can be achieved by the command line, redirecting the X display or through a Web browser interface. Modify the 'inittab' file to set boot level as 3:
To set the initial runlevel (3 instead of 5) of a machine at boot,
modify the /etc/inittab file as shown:
摘自：http://www.performancewiki.com/linux-tuning.html
Many default settings in Linux suck
Font server for X Windows is running as a daemon by default, but do you need it?
Check out these tunings that can give you lots of computing juice...
Kernel Network Disk I/O Others
Kernel
To successfully run enterprise applications, such as a database server, on your Linux distribution, you may be required to update some of the default kernel parameter settings. For example, the 2.4.x series kernel message queue parameter msgmni has a default value (for example, shared memory, or shmmax is only 33,554,432 bytes on Red Hat Linux by default) that allows only a limited number of simultaneous connections to a database. Here are some recommended values (by the IBM DB2 Support Web site) for database servers to run optimally:
- kernel.shmmax=268435456 for 32-bit
- kernel.shmmax=1073741824 for 64-bit
- kernel.msgmni=1024
- fs.file-max=8192
- kernel.sem="250 32000 32 1024"
Shared Memory
To view current settings, run command:
# more /proc/sys/kernel/shmmax
To set it to a new value for this running session, which takes effect immediately, run command:
# echo 268435456 > /proc/sys/kernel/shmmax
To set it to a new value permanently (so it survives reboots), modify the sysctl.conf file:
...
kernel.shmmax = 268435456
...
Semaphores
To view current settings, run command:
# more /proc/sys/kernel/sem
250 32000 32 1024
To set it to a new value for this running session, which takes effect immediately, run command:
# echo 500 512000 64 2048 > /proc/sys/kernel/sem
Parameters meaning:
SEMMSL - semaphores per ID
SEMMNS - (SEMMNI*SEMMSL) max semaphores in system
SEMOPM - max operations per semop call
SEMMNI - max semaphore identifiers
ulimits
To view current settings, run command:
# ulimit -a
To set it to a new value for this running session, which takes effect immediately, run command:
# ulimit -n 8800
# ulimit -n -1 // for unlimited; recommended if server isn't shared
Alternatively, if you want the changes to survive reboot, do the following:
- Exit all shell sessions for the user you want to change limits on.
- As root, edit the file /etc/security/limits.conf and add these two lines toward the end:
user1 soft nofile 16000
user1 hard nofile 20000
** the two lines above changes the max number of file handles - nofile - to new settings.
- Save the file.
- Login as the user1 again. The new changes will be in effect.
Message queues
To view current settings, run command:
# more /proc/sys/kernel/msgmni
# more /proc/sys/kernel/msgmax
To set it to a new value for this running session, which takes effect immediately, run command:
# echo 2048 > /proc/sys/kernel/msgmni
# echo 64000 > /proc/sys/kernel/msgmax
Network
Gigabit-based network interfaces have many performance-related parameters inside of their device driver such as CPU affinity. Also, the TCP protocol can be tuned to increase network throughput for connection-hungry applications.
Tune TCP
To view current TCP settings, run command:
# sysctl net.ipv4.tcp_keepalive_time
net.ipv4.tcp_keepalive_time = 7200 // 2 hours
where net.ipv4.tcp_keepalive_time is a TCP tuning parameter.
To set a TCP parameter to a value, run command:
# sysctl -w net.ipv4.tcp_keepalive_time=1800
A list of recommended TCP parameters, values, and their meanings:
Tuning Parameter Tuning Value Description of impact
------------------------------------------------------------------------------
net.ipv4.tcp_tw_reuse
net.ipv4.tcp_tw_recycle 1 Reuse sockets in the time-wait state
---
net.core.wmem_max 8388608 Increase the maximum write buffer queue size
---
net.core.rmem_max 8388608 Increase the maximum read buffer queue size
---
net.ipv4.tcp_rmem 4096 87380 8388608 Set the minimum, initial, and maximum sizes for the
read buffer. Note that this maximum should be less
than or equal to the value set in net.core.rmem_max.
---
net.ipv4.tcp_wmem 4096 87380 8388608 Set the minimum, initial, and maximum sizes for the
write buffer. Note that this maximum should be less
than or equal to the value set in net.core.wmem_max.
---
timeout_timewait echo 30 > /proc/sys/net/ipv4/tcp_fin_timeout Determines the time that must elapse before
TCP/IP can release a closed connection and reuse its resources.
This interval between closure and release is known as the TIME_WAIT
state or twice the maximum segment lifetime (2MSL) state.
During this time, reopening the connection to the client and
server cost less than establishing a new connection. By reducing the
value of this entry, TCP/IP can release closed connections faster, providing
more resources for new connections. Adjust this parameter if the running application
requires rapid release, the creation of new connections, and a low throughput
due to many connections sitting in the TIME_WAIT state.
Disk I/O
Choose the Right File System
Use 'ext3' file system in Linux.
- It is enhanced version of ext2
- With journaling capability - high level of data integrity (in event of unclean shutdown)
- It does not need to check disks on unclean shutdown and reboot (time consuming)
- Faster write - ext3 journaling optimizes hard drive head motion
# mke2fs -j -b 2048 -i 4096 /dev/sda
mke2fs 1.32 (09-Nov-2002)
/dev/sda is entire device, not just one partition!
Proceed anyway? (y,n) y
Filesystem label=
OS type: Linux
Block size=2048 (log=1)
Fragment size=2048 (log=1)
13107200 inodes, 26214400 blocks
1310720 blocks (5.00%) reserved for the super user
First data block=0
1600 block groups
16384 blocks per group, 16384 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
16384, 49152, 81920, 114688, 147456, 409600, 442368, 802816, 1327104,
2048000, 3981312, 5619712, 10240000, 11943936
Writing inode tables: done
Writing superblocks and filesystem accounting information: done
This filesystem will be automatically checked every 28 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
Use 'noatime' File System Mount Option
Use 'noatime' option in the file system boot-up configuration file 'fstab'. Edit the fstab file under /etc. This option works the best if external storage is used, for example, SAN:
# more /etc/fstab
LABEL=/ / ext3 defaults 1 1
none /dev/pts devpts gid=5,mode=620 0 0
none /proc proc defaults 0 0
none /dev/shm tmpfs defaults 0 0
/dev/sdc2 swap swap defaults 0 0
/dev/cdrom /mnt/cdrom udf,iso9660 noauto,owner,kudzu,ro 0 0
/dev/fd0 /mnt/floppy auto noauto,owner,kudzu 0 0
/dev/sda /database ext3 defaults,noatime 1 2
/dev/sdb /logs ext3 defaults,noatime 1 2
/dev/sdc /multimediafiles ext3 defaults,noatime 1 2
Tune the Elevator Algorithm in Linux Kernel for Disk I/O
After choosing the file system, there are several kernel and mounting options that can affect it. One such kernel setting is the elevator algorithm. Tuning the elevator algorithm helps the system balance the need for low latency with the need to collect enough data to efficiently organize batches of read and write requests to the disk. The elevator algorithm can be adjusted with the following command:
# elvtune -r 1024 -w 2048 /dev/sda
/dev/sda elevator ID 2
read_latency: 1024
write_latency: 2048
max_bomb_segments: 6
The parameters are: read latency (-r), write latency (-w) and the device affected.
Red Hat recommends using a read latency half the size of the write latency (as shown).
As usual, to make this setting permanent, add the 'elvtune' command to the
/etc/rc.d/rc.local script.
Others
Disable Unnecessary Daemons (They Take up Memory and CPU)
There are daemons (background services) running on every server that are probably not needed. Disabling these daemons frees memory, decreases startup time, and decreases the number of processes that the CPU has to handle. A side benefit to this is increased security of the server because fewer daemons mean fewer exploitable processes.
Some example Linux daemons running by default (and should be disabled). Use command:
#/sbin/chkconfig --levels 2345 sendmail off
#/sbin/chkconfig sendmail off
Daemon
Description
apmd
Advanced power management daemon
autofs
Automatically mounts file systems on demand (i.e.: mounts a CD-ROM automatically)
cups
Common UNIX� Printing System
hpoj
HP OfficeJet support
isdn
ISDN modem support
netfs
Used in support of exporting NFS shares
nfslock
Used for file locking with NFS
pcmcia
PCMCIA support on a server
rhnsd
Red Hat Network update service for checking for updates and security errata
sendmail
Mail Transport Agent
xfs
Font server for X Windows
Shutdown GUI
Normally, there is no need for a GUI on a Linux server. All administration tasks can be achieved by the command line, redirecting the X display or through a Web browser interface. Modify the 'inittab' file to set boot level as 3:
To set the initial runlevel (3 instead of 5) of a machine at boot,
modify the /etc/inittab file as shown:
摘自：http://www.performancewiki.com/linux-tuning.html
my.cnf優化
對於單台運行的WEB服務器,建議加上:
skip-locking
skip-name-resolve
skip-networking
在PHP鏈接數據庫時使用”LOCALHOST”.這樣 MySQL客戶端庫將覆蓋之並嘗試連接到本地套接字. (
我們可以從PHP.INI中
代碼:
; Default socket name for local MySQL connects.If empty, uses the built- in ; MySQL defaults. mysql.default_socket = /tmp/mysql.sock
看出默認情況下UNIX 將訪問/tmp/mysql.sock)
以下是部分選項解釋:
my.cnf默認是不存在的.你可以在/usr/local/share/mysql/下看到:
my-huge.cnf
my-innodb-heavy-4G.cnf
my-large.cnf
my-medium.cnf
my-small.cnf
等文件.將其中合適你機器配置的文件拷貝到/etc/my.cnf或mysql data目錄/my.cnf(/var/db/mysql)下或~/.my.cnf.文件內都有詳細的說明
比如我是在freebsd下用ports裝的,copy其中一個文件到/usr/local/etc 這個目錄下即可
————————————–
[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-locking
#避免MySQL的外部鎖定，減少出錯機率增強穩定性。
skip-name-resolve
禁止MySQL對外部連接進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠程主機連接授權都要使用IP地址方式，否則MySQL將無法正常處理連接請求！
back_log = 384
指定MySQL可能的連接數量。當MySQL主線程在很短的時間內接收到非常多的連接請求，該參數生效，主線程花費很短的時間檢查連接並且啟動一個新線程。
back_log參數的值指出在MySQL暫時停止響應新請求之前的短時間內多少個請求可以被存在堆棧中。如果系統在一個短時間內有很多連接，則需要增大該參數的值，該參數值指定到來的TCP/IP連接的偵聽隊列的大小。不同的操作系統在這個隊列大小上有它自己的限制。
試圖設定back_log高於你的操作系統的限制將是無效的。默認值為50。對於Linux系統推薦設置為小於512的整數。
key_buffer_size = 256M
# key_buffer_size指定用於索引的緩衝區大小，增加它可得到更好的索引處理性能。
對於內存在4GB左右的服務器該參數可設置為256M或384M。
注意：該參數值設置的過大反而會是服務器整體效率降低！
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
查詢排序時所能使用的緩衝區大小。注意：該參數對應的分配內存是每連接獨占！如果有100個連接，那麼實際分配的總共排序緩衝區大小為100 × 6 ＝ 600MB。所以，對於內存在4GB左右的服務器推薦設置為6-8M。
read_buffer_size = 4M
讀查詢操作所能使用的緩衝區大小。和sort_buffer_size一樣，該參數對應的分配內存也是每連接獨享！
join_buffer_size = 8M
聯合查詢操作所能使用的緩衝區大小，和sort_buffer_size一樣，該參數對應的分配內存也是每連接獨享！
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
指定MySQL查詢緩衝區的大小。可以通過在MySQL控制台執行以下命令觀察：
代碼:
# > SHOW VARIABLES LIKE '%query_cache%' ; # > SHOW STATUS LIKE 'Qcache%' ;
如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩沖不夠的情況；
如果Qcache_hits的值非常大，則表明查詢緩衝使用非常頻繁，如果該值較小反而會影響效率，那麼可以考慮不用查詢緩衝；Qcache_free_blocks，如果該值非常大，則表明緩衝區中碎片很多。
tmp_table_size = 256M
max_connections = 768
指定MySQL允許的最大連接進程數。如果在訪問論壇時經常出現Too Many Connections的錯誤提示，則需要增大該參數值。
max_connect_errors = 10000000
wait_timeout = 10
指定一個請求的最大連接時間，對於4GB左右內存的服務器可以設置為5-10。
thread_concurrency = 8
該參數取值為服務器邏輯CPU數量×2，在本例中，服務器有2顆物理CPU，而每顆物理CPU又支持HT超線程，所以實際取值為4 × 2 ＝ 8
skip- networking
開啟該選項可以徹底關閉MySQL的TCP/IP連接方式，如果WEB服務器是以遠程連接的方式訪問MySQL數據庫服務器則不要開啟該選項！否則將無法正常連接！
———————–
update:
記錄兩個,今天在cu上看到有兩個人問起bsd下mysq的安裝問題,make install 後無法啟動mysql,如下:
1.在mysql的log裡看到如下的錯誤:
[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
解決辦法如下:
#/usr/local/bin/mysql_install_db2.在log裡發現如下:
/usr/local/libexec/mysqld: Can't find file: './mysql/host.frm'
出現這個問題是執行mysql_install_db後權限的問題,解決辦法如下:
# chown -R mysql:mysql /var/db/mysql/之後就可以用mysqld_safe來啟動了,不過首先記得給root加密碼
摘自：http://dev.firnow.com/course/7_databases/mysql/Mysqljs/2008829/138802.html
skip-locking
skip-name-resolve
skip-networking
在PHP鏈接數據庫時使用”LOCALHOST”.這樣 MySQL客戶端庫將覆蓋之並嘗試連接到本地套接字. (
我們可以從PHP.INI中
代碼:
; Default socket name for local MySQL connects.If empty, uses the built- in ; MySQL defaults. mysql.default_socket = /tmp/mysql.sock
看出默認情況下UNIX 將訪問/tmp/mysql.sock)
以下是部分選項解釋:
my.cnf默認是不存在的.你可以在/usr/local/share/mysql/下看到:
my-huge.cnf
my-innodb-heavy-4G.cnf
my-large.cnf
my-medium.cnf
my-small.cnf
等文件.將其中合適你機器配置的文件拷貝到/etc/my.cnf或mysql data目錄/my.cnf(/var/db/mysql)下或~/.my.cnf.文件內都有詳細的說明
比如我是在freebsd下用ports裝的,copy其中一個文件到/usr/local/etc 這個目錄下即可
————————————–
[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-locking
#避免MySQL的外部鎖定，減少出錯機率增強穩定性。
skip-name-resolve
禁止MySQL對外部連接進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠程主機連接授權都要使用IP地址方式，否則MySQL將無法正常處理連接請求！
back_log = 384
指定MySQL可能的連接數量。當MySQL主線程在很短的時間內接收到非常多的連接請求，該參數生效，主線程花費很短的時間檢查連接並且啟動一個新線程。
back_log參數的值指出在MySQL暫時停止響應新請求之前的短時間內多少個請求可以被存在堆棧中。如果系統在一個短時間內有很多連接，則需要增大該參數的值，該參數值指定到來的TCP/IP連接的偵聽隊列的大小。不同的操作系統在這個隊列大小上有它自己的限制。
試圖設定back_log高於你的操作系統的限制將是無效的。默認值為50。對於Linux系統推薦設置為小於512的整數。
key_buffer_size = 256M
# key_buffer_size指定用於索引的緩衝區大小，增加它可得到更好的索引處理性能。
對於內存在4GB左右的服務器該參數可設置為256M或384M。
注意：該參數值設置的過大反而會是服務器整體效率降低！
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
查詢排序時所能使用的緩衝區大小。注意：該參數對應的分配內存是每連接獨占！如果有100個連接，那麼實際分配的總共排序緩衝區大小為100 × 6 ＝ 600MB。所以，對於內存在4GB左右的服務器推薦設置為6-8M。
read_buffer_size = 4M
讀查詢操作所能使用的緩衝區大小。和sort_buffer_size一樣，該參數對應的分配內存也是每連接獨享！
join_buffer_size = 8M
聯合查詢操作所能使用的緩衝區大小，和sort_buffer_size一樣，該參數對應的分配內存也是每連接獨享！
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
指定MySQL查詢緩衝區的大小。可以通過在MySQL控制台執行以下命令觀察：
代碼:
# > SHOW VARIABLES LIKE '%query_cache%' ; # > SHOW STATUS LIKE 'Qcache%' ;
如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩沖不夠的情況；
如果Qcache_hits的值非常大，則表明查詢緩衝使用非常頻繁，如果該值較小反而會影響效率，那麼可以考慮不用查詢緩衝；Qcache_free_blocks，如果該值非常大，則表明緩衝區中碎片很多。
tmp_table_size = 256M
max_connections = 768
指定MySQL允許的最大連接進程數。如果在訪問論壇時經常出現Too Many Connections的錯誤提示，則需要增大該參數值。
max_connect_errors = 10000000
wait_timeout = 10
指定一個請求的最大連接時間，對於4GB左右內存的服務器可以設置為5-10。
thread_concurrency = 8
該參數取值為服務器邏輯CPU數量×2，在本例中，服務器有2顆物理CPU，而每顆物理CPU又支持HT超線程，所以實際取值為4 × 2 ＝ 8
skip- networking
開啟該選項可以徹底關閉MySQL的TCP/IP連接方式，如果WEB服務器是以遠程連接的方式訪問MySQL數據庫服務器則不要開啟該選項！否則將無法正常連接！
———————–
update:
記錄兩個,今天在cu上看到有兩個人問起bsd下mysq的安裝問題,make install 後無法啟動mysql,如下:
1.在mysql的log裡看到如下的錯誤:
[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
解決辦法如下:
#/usr/local/bin/mysql_install_db2.在log裡發現如下:
/usr/local/libexec/mysqld: Can't find file: './mysql/host.frm'
出現這個問題是執行mysql_install_db後權限的問題,解決辦法如下:
# chown -R mysql:mysql /var/db/mysql/之後就可以用mysqld_safe來啟動了,不過首先記得給root加密碼
摘自：http://dev.firnow.com/course/7_databases/mysql/Mysqljs/2008829/138802.html
百萬記錄級MySQL數據庫及Discuz!論壇優化
Discuz MYSQL 數據庫 性能：
最近，幫一個朋友優化一個擁有20萬主題，100萬帖子，3萬多會員，平均在線人數2000人的Discuz!論壇，採用 Linux2.6+Apache2+mod_php5+MySQL5，服務器配置為雙至強+4G內存，優化前，系統平均負載（load average）基本維持在10以上，MySQL的CPU佔用率基本在90%以上，優化後，系統平均負載降到0.5以下，MySQL的CPU佔用率很少有超過10%的時候。優化前YSlow得分只有35分，優化後YSlow得分86分。現將優化的過程和經驗做一個記錄：
首先，對 Apache進行優化，編輯httpd.conf，設置HostnameLookups、KeepAlive、MaxKeepAliveRequests 以及KeepAliveTimeout四個參數，調整MaxSpareServers、ServerLimit、MaxClients以及 MaxRequestsPerChild參數，還可以考慮棄用prefork而採用worker MPM。設置mod_deflate及mod_expires模塊，不過注意Discuz!不能對PHP文件開啟Expires，否則會出現問題。另外還可以考慮開啟mod_cache和mod_mem_cache模塊。另外利用cronolog按天對日誌進行輪循截斷，如果日誌特別大，也可以按小時截斷。另外再加上Awstats對日誌進行分析，並用gzip對日誌進行壓縮，自動刪除1個月前的日誌。
其次，對PHP進行優化，編輯 php.ini，調整output_buffering、zlib.output_compression及max_execution_time、 max_input_time、memory_limit等參數，並安裝Xcache和Zend Optimizer。
然後對MySQL 進行優化。首先重新靜態編譯MySQL，使其只支持MyISAM和Memory兩種引擎，並按Discuz!編碼選擇只支持UTF-8或者GBK字符集。編輯my.cnf，設置skip-locking、skip-external-locking、skip-networking和skip-name- resolve，根據內存和數據庫狀態具體調整key_buffer_size、query_cache_size、 query_cache_limit、max_allowed_packet、table_cache、thread_cache_size、 sort_buffer_size、read_buffer_size、read_rnd_buffer_size、join_buffer_size、 tmp_table_size、max_tmp_tables、back_log、max_connections、wait_timeout的參數。
對數據庫進行優化，將threads和posts表中部分未索引的字段增加索引，並將supersite數據庫表從bbs數據庫獨立出去。修改discuz!配置文件，設置開啟pconnect。
對 Discuz！設置進行優化。進入Discuz!系統設置，修改頁面緩存設置中的緩存有效期和緩存係數，修改服務器優化中的禁止瀏覽器緩衝和頁面Gzip 壓縮，修改防盜鏈設置中下載附件來路檢查，用JSMin自動對js文件進行縮減（Discuz! 6.1的common.js原文件29.3k，經JSMin縮減後為24.1k，再經deflate後為7.3k），修改attachments.php 文件，將：
//dheader(』Cache-control: max-age=31536000′);
//dheader(』Expires: 『.gmdate(』D, d M Y H:i:s', $timestamp + 31536000).』 GMT');
前的註釋去掉。修改模板目錄下adv.htm，去掉與Insenz有關的代碼。
通過查看MySQL的status，可以看出優化後，長時間運行的Key_read_ratio基本保持在0.05%以下，Threads_cache_hitrate保持在99.9%以上。個人感覺，Discuz!將Session保存在數據庫中，極大地降低了 Query Cache的命中率，如果需要進一步優化，可以考慮修改Discuz!源碼，將Session保存到Memcache中。
優化之後用Siege做並發壓力測試，在200並發下，基本沒有任何錯誤。如果將來人數更多，可以考慮將平台遷移到Ngix+PHP FastCGI上。
下面是用Siege在300並發下的測試結果：
#siege -c 300 -b -r 35 -f bbs.url
** SIEGE 2.67
** Preparing 300 concurrent users for battle.
The server is now under siege.. done.
Transactions: 10500 hits
Availability: 100.00 %
Elapsed time: 52.68 secs
Data transferred: 65.67 MB
Response time: 1.27 secs
Transaction rate: 199.32 trans/sec
Throughput: 1.25 MB/sec
Concurrency: 253.07
Successful transactions: 10500
Failed transactions: 0
Longest transaction: 24.88
Shortest transaction: 0.00
500並發下的測試結果：
#siege -c 500 -b -r 20 -f bbs.url
** SIEGE 2.67
** Preparing 300 concurrent users for battle.
The server is now under siege.. done.
Transactions: 9979 hits
Availability: 99.79 %
Elapsed time: 86.52 secs
Data transferred: 82.66 MB
Response time: 3.30 secs
Transaction rate: 115.34 trans/sec
Throughput: 0.96 MB/sec
Concurrency: 381.07
Successful transactions: 9979
Failed transactions: 21
Longest transaction: 34.80
Shortest transaction: 0.00
摘自：http://www.dk101.com/index.php/125562/viewspace-42577.html
最近，幫一個朋友優化一個擁有20萬主題，100萬帖子，3萬多會員，平均在線人數2000人的Discuz!論壇，採用 Linux2.6+Apache2+mod_php5+MySQL5，服務器配置為雙至強+4G內存，優化前，系統平均負載（load average）基本維持在10以上，MySQL的CPU佔用率基本在90%以上，優化後，系統平均負載降到0.5以下，MySQL的CPU佔用率很少有超過10%的時候。優化前YSlow得分只有35分，優化後YSlow得分86分。現將優化的過程和經驗做一個記錄：
首先，對 Apache進行優化，編輯httpd.conf，設置HostnameLookups、KeepAlive、MaxKeepAliveRequests 以及KeepAliveTimeout四個參數，調整MaxSpareServers、ServerLimit、MaxClients以及 MaxRequestsPerChild參數，還可以考慮棄用prefork而採用worker MPM。設置mod_deflate及mod_expires模塊，不過注意Discuz!不能對PHP文件開啟Expires，否則會出現問題。另外還可以考慮開啟mod_cache和mod_mem_cache模塊。另外利用cronolog按天對日誌進行輪循截斷，如果日誌特別大，也可以按小時截斷。另外再加上Awstats對日誌進行分析，並用gzip對日誌進行壓縮，自動刪除1個月前的日誌。
其次，對PHP進行優化，編輯 php.ini，調整output_buffering、zlib.output_compression及max_execution_time、 max_input_time、memory_limit等參數，並安裝Xcache和Zend Optimizer。
然後對MySQL 進行優化。首先重新靜態編譯MySQL，使其只支持MyISAM和Memory兩種引擎，並按Discuz!編碼選擇只支持UTF-8或者GBK字符集。編輯my.cnf，設置skip-locking、skip-external-locking、skip-networking和skip-name- resolve，根據內存和數據庫狀態具體調整key_buffer_size、query_cache_size、 query_cache_limit、max_allowed_packet、table_cache、thread_cache_size、 sort_buffer_size、read_buffer_size、read_rnd_buffer_size、join_buffer_size、 tmp_table_size、max_tmp_tables、back_log、max_connections、wait_timeout的參數。
對數據庫進行優化，將threads和posts表中部分未索引的字段增加索引，並將supersite數據庫表從bbs數據庫獨立出去。修改discuz!配置文件，設置開啟pconnect。
對 Discuz！設置進行優化。進入Discuz!系統設置，修改頁面緩存設置中的緩存有效期和緩存係數，修改服務器優化中的禁止瀏覽器緩衝和頁面Gzip 壓縮，修改防盜鏈設置中下載附件來路檢查，用JSMin自動對js文件進行縮減（Discuz! 6.1的common.js原文件29.3k，經JSMin縮減後為24.1k，再經deflate後為7.3k），修改attachments.php 文件，將：
//dheader(』Cache-control: max-age=31536000′);
//dheader(』Expires: 『.gmdate(』D, d M Y H:i:s', $timestamp + 31536000).』 GMT');
前的註釋去掉。修改模板目錄下adv.htm，去掉與Insenz有關的代碼。
通過查看MySQL的status，可以看出優化後，長時間運行的Key_read_ratio基本保持在0.05%以下，Threads_cache_hitrate保持在99.9%以上。個人感覺，Discuz!將Session保存在數據庫中，極大地降低了 Query Cache的命中率，如果需要進一步優化，可以考慮修改Discuz!源碼，將Session保存到Memcache中。
優化之後用Siege做並發壓力測試，在200並發下，基本沒有任何錯誤。如果將來人數更多，可以考慮將平台遷移到Ngix+PHP FastCGI上。
下面是用Siege在300並發下的測試結果：
#siege -c 300 -b -r 35 -f bbs.url
** SIEGE 2.67
** Preparing 300 concurrent users for battle.
The server is now under siege.. done.
Transactions: 10500 hits
Availability: 100.00 %
Elapsed time: 52.68 secs
Data transferred: 65.67 MB
Response time: 1.27 secs
Transaction rate: 199.32 trans/sec
Throughput: 1.25 MB/sec
Concurrency: 253.07
Successful transactions: 10500
Failed transactions: 0
Longest transaction: 24.88
Shortest transaction: 0.00
500並發下的測試結果：
#siege -c 500 -b -r 20 -f bbs.url
** SIEGE 2.67
** Preparing 300 concurrent users for battle.
The server is now under siege.. done.
Transactions: 9979 hits
Availability: 99.79 %
Elapsed time: 86.52 secs
Data transferred: 82.66 MB
Response time: 3.30 secs
Transaction rate: 115.34 trans/sec
Throughput: 0.96 MB/sec
Concurrency: 381.07
Successful transactions: 9979
Failed transactions: 21
Longest transaction: 34.80
Shortest transaction: 0.00
摘自：http://www.dk101.com/index.php/125562/viewspace-42577.html
MySQL優化實例
在Apache, PHP, MySQL的體系架構中，MySQL對於性能的影響最大，也是關鍵的核心部分。對於Discuz!論壇程序也是如此，MySQL的設置是否合理優化，直接影響到論壇的速度和承載量！同時，MySQL也是優化難度最大的一個部分，不但需要理解一些MySQL專業知識，同時還需要長時間的觀察統計並且根據經驗進行判斷，然後設置合理的參數。
下面我們了解一下MySQL優化的一些基礎，MySQL的優化我分為兩個部分，一是服務器物理硬件的優化；二是MySQL自身(my.cnf)的優化。
服務器硬件對MySQL性能的影響
磁盤尋道能力（磁盤I/O）,以目前高轉速SCSI硬盤(7200轉/秒)為例，這種硬盤理論上每秒尋道7200次，這是物理特性決定的，沒有辦法改變。 MySQL每秒鐘都在進行大量、複雜的查詢操作，對磁盤的讀寫量可想而知。所以，通常認為磁盤I/O是製約MySQL性能的最大因素之一，對於日均訪問量在100萬PV以上的Discuz!論壇，由於磁盤I/O的製約，MySQL的性能會非常低下！解決這一制約因素可以考慮以下幾種解決方案：
使用RAID-0+1磁盤陣列，注意不要嘗試使用RAID-5，MySQL在RAID-5磁盤陣列上的效率不會像你期待的那樣快； 拋棄傳統的硬盤，使用速度更快的閃存式存儲設備。經過Discuz!公司技術工程的測試，使用閃存式存儲設備可比傳統硬盤速度高出6－10倍左右。
CPU 對於MySQL應用，推薦使用SMP架構的多路對稱CPU，例如：可以使用兩顆Intel Xeon 3.6GHz的CPU。
物理內存對於一台使用MySQL的Database Server來說，服務器內存建議不要小於2GB，推薦使用4GB以上的物理內存。
MySQL自身因素當解決了上述服務器硬件製約因素後，讓我們看看MySQL自身的優化是如何操作的。對MySQL自身的優化主要是對其配置文件my.cnf中的各項參數進行優化調整。下面我們介紹一些對性能影響較大的參數。
由於my.cnf文件的優化設置是與服務器硬件配置息息相關的，因而我們指定一個假想的服務器硬件環境：
CPU : 2顆 Intel Xeon 2.4GHz
內存: 4GB DDR
硬盤: SCSI 73GB
下面，我們根據以上硬件配置結合一份已經優化好的my.cnf進行說明：
# vi /etc/my.cnf
以下只列出my.cnf文件中mysqld段落中的內容，其他段落內容對MySQL運行性能影響甚微，因而姑且忽略。
[mysqld ]
port = 3306
serverid = 1
socket = /tmp/ mysql .sock
skip - locking
#避免MySQL的外部鎖定，減少出錯機率增強穩定性。
skip - name - resolve
禁止MySQL對外部連接進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠程主機連接授權都要使用IP地址方式，否則MySQL將無法正常處理連接請求！
back_log = 384
指定MySQL可能的連接數量。當MySQL主線程在很短的時間內接收到非常多的連接請求，該參數生效，主線程花費很短的時間檢查連接並且啟動一個新線程。
back_log參數的值指出在MySQL暫時停止響應新請求之前的短時間內多少個請求可以被存在堆棧中。如果系統在一個短時間內有很多連接，則需要增大該參數的值，該參數值指定到來的TCP/IP連接的偵聽隊列的大小。不同的操作系統在這個隊列大小上有它自己的限制。
試圖設定back_log高於你的操作系統的限制將是無效的。默認值為50。對於Linux系統推薦設置為小於512的整數。
key_buffer_size = 256M
# key_buffer_size指定用於索引的緩衝區大小，增加它可得到更好的索引處理性能。
對於內存在4GB左右的服務器該參數可設置為256M或384M。注意：該參數值設置的過大反而會是服務器整體效率降低！
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
查詢排序時所能使用的緩衝區大小。注意：該參數對應的分配內存是每連接獨占！如果有100個連接，那麼實際分配的總共排序緩衝區大小為100 × 6 ＝ 600MB。所以，對於內存在4GB左右的服務器推薦設置為6-8M。
read_buffer_size = 4M
讀查詢操作所能使用的緩衝區大小。和sort_buffer_size一樣，該參數對應的分配內存也是每連接獨享！
join_buffer_size = 8M
聯合查詢操作所能使用的緩衝區大小，和sort_buffer_size一樣，該參數對應的分配內存也是每連接獨享！
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
指定MySQL查詢緩衝區的大小。可以通過在MySQL控制台執行以下命令觀察：
# > SHOW VARIABLES LIKE '%query_cache%';
# > SHOW STATUS LIKE 'Qcache%';
#如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩沖不夠的情況；
如果Qcache_hits的值非常大，則表明查詢緩衝使用非常頻繁，如果該值較小反而會影響效率，那麼可以考慮不用查詢緩衝；Qcache_free_blocks，如果該值非常大，則表明緩衝區中碎片很多。
tmp_table_size = 256M
max_connections = 768
指定MySQL允許的最大連接進程數。如果在訪問論壇時經常出現Too Many Connections的錯誤提示，則需要增大該參數值。
max_connect_errors = 10000000
wait_timeout = 10
指定一個請求的最大連接時間，對於4GB左右內存的服務器可以設置為5-10。
thread_concurrency = 8
該參數取值為服務器邏輯CPU數量×2，在本例中，服務器有2顆物理CPU，而每顆物理CPU又支持HT超線程，所以實際取值為4 × 2 ＝ 8
skip - networking
開啟該選項可以徹底關閉MySQL的TCP/IP連接方式，如果WEB服務器是以遠程連接的方式訪問MySQL數據庫服務器則不要開啟該選項！否則將無法正常連接！
摘自：https://code.google.com/p/scriptz/wiki/MySqlOptimize
下面我們了解一下MySQL優化的一些基礎，MySQL的優化我分為兩個部分，一是服務器物理硬件的優化；二是MySQL自身(my.cnf)的優化。
服務器硬件對MySQL性能的影響
磁盤尋道能力（磁盤I/O）,以目前高轉速SCSI硬盤(7200轉/秒)為例，這種硬盤理論上每秒尋道7200次，這是物理特性決定的，沒有辦法改變。 MySQL每秒鐘都在進行大量、複雜的查詢操作，對磁盤的讀寫量可想而知。所以，通常認為磁盤I/O是製約MySQL性能的最大因素之一，對於日均訪問量在100萬PV以上的Discuz!論壇，由於磁盤I/O的製約，MySQL的性能會非常低下！解決這一制約因素可以考慮以下幾種解決方案：
使用RAID-0+1磁盤陣列，注意不要嘗試使用RAID-5，MySQL在RAID-5磁盤陣列上的效率不會像你期待的那樣快； 拋棄傳統的硬盤，使用速度更快的閃存式存儲設備。經過Discuz!公司技術工程的測試，使用閃存式存儲設備可比傳統硬盤速度高出6－10倍左右。
CPU 對於MySQL應用，推薦使用SMP架構的多路對稱CPU，例如：可以使用兩顆Intel Xeon 3.6GHz的CPU。
物理內存對於一台使用MySQL的Database Server來說，服務器內存建議不要小於2GB，推薦使用4GB以上的物理內存。
MySQL自身因素當解決了上述服務器硬件製約因素後，讓我們看看MySQL自身的優化是如何操作的。對MySQL自身的優化主要是對其配置文件my.cnf中的各項參數進行優化調整。下面我們介紹一些對性能影響較大的參數。
由於my.cnf文件的優化設置是與服務器硬件配置息息相關的，因而我們指定一個假想的服務器硬件環境：
CPU : 2顆 Intel Xeon 2.4GHz
內存: 4GB DDR
硬盤: SCSI 73GB
下面，我們根據以上硬件配置結合一份已經優化好的my.cnf進行說明：
# vi /etc/my.cnf
以下只列出my.cnf文件中mysqld段落中的內容，其他段落內容對MySQL運行性能影響甚微，因而姑且忽略。
[mysqld ]
port = 3306
serverid = 1
socket = /tmp/ mysql .sock
skip - locking
#避免MySQL的外部鎖定，減少出錯機率增強穩定性。
skip - name - resolve
禁止MySQL對外部連接進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠程主機連接授權都要使用IP地址方式，否則MySQL將無法正常處理連接請求！
back_log = 384
指定MySQL可能的連接數量。當MySQL主線程在很短的時間內接收到非常多的連接請求，該參數生效，主線程花費很短的時間檢查連接並且啟動一個新線程。
back_log參數的值指出在MySQL暫時停止響應新請求之前的短時間內多少個請求可以被存在堆棧中。如果系統在一個短時間內有很多連接，則需要增大該參數的值，該參數值指定到來的TCP/IP連接的偵聽隊列的大小。不同的操作系統在這個隊列大小上有它自己的限制。
試圖設定back_log高於你的操作系統的限制將是無效的。默認值為50。對於Linux系統推薦設置為小於512的整數。
key_buffer_size = 256M
# key_buffer_size指定用於索引的緩衝區大小，增加它可得到更好的索引處理性能。
對於內存在4GB左右的服務器該參數可設置為256M或384M。注意：該參數值設置的過大反而會是服務器整體效率降低！
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
查詢排序時所能使用的緩衝區大小。注意：該參數對應的分配內存是每連接獨占！如果有100個連接，那麼實際分配的總共排序緩衝區大小為100 × 6 ＝ 600MB。所以，對於內存在4GB左右的服務器推薦設置為6-8M。
read_buffer_size = 4M
讀查詢操作所能使用的緩衝區大小。和sort_buffer_size一樣，該參數對應的分配內存也是每連接獨享！
join_buffer_size = 8M
聯合查詢操作所能使用的緩衝區大小，和sort_buffer_size一樣，該參數對應的分配內存也是每連接獨享！
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
指定MySQL查詢緩衝區的大小。可以通過在MySQL控制台執行以下命令觀察：
# > SHOW VARIABLES LIKE '%query_cache%';
# > SHOW STATUS LIKE 'Qcache%';
#如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩沖不夠的情況；
如果Qcache_hits的值非常大，則表明查詢緩衝使用非常頻繁，如果該值較小反而會影響效率，那麼可以考慮不用查詢緩衝；Qcache_free_blocks，如果該值非常大，則表明緩衝區中碎片很多。
tmp_table_size = 256M
max_connections = 768
指定MySQL允許的最大連接進程數。如果在訪問論壇時經常出現Too Many Connections的錯誤提示，則需要增大該參數值。
max_connect_errors = 10000000
wait_timeout = 10
指定一個請求的最大連接時間，對於4GB左右內存的服務器可以設置為5-10。
thread_concurrency = 8
該參數取值為服務器邏輯CPU數量×2，在本例中，服務器有2顆物理CPU，而每顆物理CPU又支持HT超線程，所以實際取值為4 × 2 ＝ 8
skip - networking
開啟該選項可以徹底關閉MySQL的TCP/IP連接方式，如果WEB服務器是以遠程連接的方式訪問MySQL數據庫服務器則不要開啟該選項！否則將無法正常連接！
摘自：https://code.google.com/p/scriptz/wiki/MySqlOptimize
訂閱：
文章 (Atom)
發表文章
