亞寶論壇's Archiver

青翁道人 發表於 2008-3-30 11:01

MySQL 優化大全:設定 my.cnf 優化指南

Linux 下的 my.cnf 文件位置:[color=Red][b]Redhat是在 /etc/my.cnf[/b][/color]; Debian是在 etc/mysql/my.cnf;Windows是在 my.ini 文件中

[b]Memory 在 1GB 以上:[/b]
[client]
port = 3306
socket = /var/tmp/mysql.sock

[mysqld]
port = 3306
socket = /var/tmp/mysql.sock
skip-locking
set-variable = key_buffer=384M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = join_buffer_size=4M
set-variable = thread_cache=8
#thread_concurrency = 主機cpu數量*2
set-variable = thread_concurrency=2
set-variable = myisam_sort_buffer_size=64M
set-variable = max_connections=750
log-bin
server-id = 1

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout
----------------------------------------------------------------
注意事項
key_buffer_size + (record_buffer + sort_buffer)*max_connections <= 2G
如果不熟悉 my.cnf 內的設定也沒關係,可以直接使用 MySQL 的範例檔,在 /usr/share/mysql/ 下會有 my-huge.cnf, my-large.cnf, my-medium.cnf 及 my-small.cnf 幾個檔案,根據伺服器的硬件而選擇適合的檔案:

    * my-huge.cnf: 適合 1GB - 2GB RAM的主機使用。
    * my-large.cnf: 適合 512MB RAM的主機使用。
    * my-medium.cnf: 只有 32MB - 64MB RAM 的主機使用,或者有 128MB RAM 但需要運行其他伺服器,例如 web server。
    * my-small.cnf: 記憶體少於 64MB 時適用這個,MySQL 會佔用較少資源。

我選用了 my-large.cnf,只要執行以下指令便完成了,但如果你原先已經有 my.cnf,請先備份起來及自行調整:
shell> cp /usr/share/mysql/my-large.cnf /etc/my.cnf
shell> /etc/init.d/mysqld restart

以上的第二句是重新啟動 MySQL,請根據你的系統設定修改。
開啟 MySQL query cache
如果 MySQL server 是非常繁忙,可以開啟 query cache 以加速回應時間,開啟方法可以在 my.cnf 裡面加入以下項目:
([color=Red][b]Redhat 下面是 /etc/my.cnf[/b][/color];Debian 是在 etc/mysql/my.cnf)
query_cache_size = 268435456
query_cache_type = 1
query_cache_limit = 1048576

以上語法的設定裡面,query_cache_size 是分配 256MB 記憶體空間給 query cache;query_cache_type 設定成 1,是給所有的查詢做 cache;query_cache_limit 是指定個別的查詢語句 1KB 的記憶體空間。

這些數值可根據自己的需要作出更改,設定及存檔後,重新啟動 MySQL 即可。
--------------------------------------------------------------
[b]my.ini與mysql優化指南(mysql4.0 myslq5.0 or 5.x)[/b]mysql優化了:
其實這個很複雜,對於不同的網站,其在線,訪問量,帖子數量,網絡情況,以及機器配置都有關係,優化不是一次可以完成的,需要不斷的觀察和調試,以取得最佳效果,下面我引用 Asiwish和老高的兩篇優化措施,給大家看看,這些設置只要通過修改win2000的c:/winnt/my.ini 或者 win2003的 c:/windows/my.ini 來實現:
首先,我們打開這個文件,先將最底部的密碼項改成其它的,這個對數據庫本身沒影響,主要是防止一些有心人獲取這個密碼,對我們不利。然後大家看看兩個例子,其中一些設置,略懂的英文的,即明白是什麼意思,不懂得可以去查單詞,我懶得一個個對照寫說明了,一個是千人在線,1G內存的:
#This File was made using the WinMySQLAdmin 1.4 Tool
#2004-2-23 16:28:14

#Uncomment or Add only the keys that you know how works.
#Read the MySQL Manual for instructions

[mysqld]
basedir=D:/mysql
#bind-address=210.5.*.*
datadir=D:/mysql/data
#language=D:/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
set-variable   = max_connections=1500
skip-locking
#skip-networking
set-variable     = key_buffer=384M
set-variable     = max_allowed_packet=1M
set-variable     = table_cache=512
set-variable     = sort_buffer=2M
set-variable     = record_buffer=2M
set-variable     = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable     = thread_concurrency=8
set-variable     = myisam_sort_buffer_size=64M
#set-variable     = connect_timeout=5
#set-variable     = wait_timeout=5
server-id     = 1
[isamchk]
set-variable     = key_buffer=128M
set-variable     = sort_buffer=128M
set-variable     = read_buffer=2M
set-variable     = write_buffer=2M

[myisamchk]
set-variable     = key_buffer=128M
set-variable     = sort_buffer=128M
set-variable     = read_buffer=2M
set-variable     = write_buffer=2M
[WinMySQLadmin]
Server=D:/mysql/bin/mysqld-nt.exe
這個方案,整體夠用了,但是在pconnect和最大連接數上,需要研究max_connections沒必要那麼大,我個人認為幾百就夠,否則給服務器加大了不少負擔,經常會當機,連接超時的設置也要根據實際情況調整,大家可以自由調整,然後觀察效果如何。下面是老高兩年前的一些建議,大家參考一下:
  7、MYSQL 的優化(/etc/my.cnf)
    1)確認在「[mysqld]」部分加入了「skip-innodb」和「skip-bdb」參數;
    2)確認在「[mysqld]」部分加入了「skip-name-resolve」和「skip-locking」參數;
    3)如果不需要的話,可以將二進制日誌(binlog)停掉,方法是將「log-bin」註釋掉;
    4)在內存允許的情況下,對一些參數進行重新配置,目標在於將大部分操作集中於內存中,儘量不進行磁盤操作,對於我的 MYSQL 服務器我是如下修改的,基於 2G 內存情況:
    [mysqld]
    set-variable   = key_buffer=512M
    set-variable   = max_allowed_packet=4M
    set-variable   = table_cache=1024
    set-variable   = thread_cache=64
    set-variable   = join_buffer_size=32M
    set-variable   = sort_buffer=32M
    set-variable   = record_buffer=32M
    set-variable   = max_connections=512
    set-variable   = wait_timeout=120
    set-variable   = interactive_timeout=120
    set-variable   = max_connect_errors=30000
    set-variable   = long_query_time=1
    set-variable   = max_heap_table_size=256M
    set-variable   = tmp_table_size=128M
    set-variable   = thread_concurrency=8
    set-variable   = myisam_sort_buffer_size=128M

    你可以根據「show status」命令返回的狀態進行微調。我主要注意以下變量的數值,越小越好,最好為零:)
    Created_tmp_disk_tables
    Created_tmp_tables
    Created_tmp_files
    Slow_queries

[color=Red][b][size=4]另外,基於 8G 內存情況:[/size][/b][/color]
[quote]# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password        = your_password
port                = 3306
socket                = /var/lib/mysql/mysql.sock
default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port                = 3306
socket                = /var/lib/mysql/mysql.sock
skip-locking
skip-innodb
skip-bdb
skip-name-resolve

default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci
init-connect='SET NAMES utf8'

wait_timeout = 5
interactive_timeout = 10
key_buffer = 800M
max_allowed_packet = 32M
table_cache = 4096
record_buffer = 400M
sort_buffer_size = 64M
net_buffer_length = 2M
myisam_sort_buffer_size = 256M
thread_cache_size = 64
read_buffer_size = 64M
read_rnd_buffer_size = 100M
query_cache_size = 512M
query_cache_type = 1
long_query_time = 4
thread_concurrency = 8
tmp_table_size = 512M
max_connections = 32000
max_heap_table_size = 800M
join_buffer_size = 64M
open_files_limit = 4096
max_connect_errors = 30000
query_cache_limit = 3145728

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id        = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin

# Point the following paths to different dedicated disks
#tmpdir                = /tmp/               
#log-update         = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 384M
#bdb_max_lock = 100000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout[/quote]
mysql wait_timeout 那個值設置大了沒用 做10左右就可了 (大C說得)wait_timeout是使用長久連線時 空閒進程的控制 只要數據庫在連接狀態 他是不進行干預的 不管是否有查詢或更新操作 把這個設置小一點 再使用pconnect就比較理想了),timeout的時間﹐我的經驗值是5-20﹐看你的SERVER的訪問量了~~(夢飛說的)訪問量越大 這個值就應該越小 否則留出的空閒進程太多 會佔用不必要的內存
在一個15分鐘在線3000人的論壇上 設置為3比較合適 同時打開pconnect,另外,我再引用一篇文章:
數據庫連接過多的錯誤,可能的原因分析及解決辦法:
分析:
系統不能連接數據庫,關鍵要看兩個數據:
1、數據庫系統允許的最大可連接數max_connections。這個參數是可以設置的。如果不設置,默認是100。最大是16384。
2、數據庫當前的連接線程數threads_connected。這是動態變化的。
查看max_connections、max_connections的辦法見後。
如果 threads_connected == max_connections 時,數據庫系統就不能提供更多的連接數了,這時,如果程序還想新建連接線程,數據庫系統就會拒絕,如果程序沒做太多的錯誤處理,就會出現類似強壇的報錯信息。因為創建和銷毀數據庫的連接,都會消耗系統的資源。而且為了避免在同一時間同時打開過多的連接線程,現在編程一般都使用所謂數據庫連接池技術。但數據庫連接池技術,並不能避免程序錯誤導致連接資源消耗殆盡。這種情況通常發生在程序未能及時釋放數據庫連接資源或其他原因造成數據庫連接資源不能釋放,但強壇系統估計不會發生這種低級的編程錯誤。該錯誤的簡便的檢查辦法是,在刷新強壇頁面時,不斷監視threads_connected的變化。如果max_connections足夠大,而 threads_connected值不斷增加以至達到max_connections,那麼,就應該檢查程序了。當然,如果採用數據庫連接池技術,threads_connected增長到數據庫連接池的最大連接線程數時,就不再增長了。從強壇出錯的情況看,更大的可能性是數據庫系統沒能進行適當地配置。

下面提出一點建議。供參考:
讓你們的工程師把MySQL的最大允許連接數從默認的100調成32000。這就不會老出現連接過多的問題了。
查看max_connections
進入MySQL,用命令:show variables
查看數據庫最大可連接數的變量值:max_connections
查看threads_connected

進入MySQL,用命令:show status
查看當前活動的連接線程變量值:threads_connected

設置max_connections
設置辦法是在my.cnf文件中,添加下面的最後紅色的一行:
------------------------------------------------------
[mysqld]
port=3306
#socket=MySQL
skip-locking
set-variable = key_buffer=16K
set-variable = max_allowed_packet=1M
set-variable = thread_stack=64K
set-variable = table_cache=4
set-variable = sort_buffer=64K
set-variable = net_buffer_length=2K
[color=Red]set-variable = max_connections=32000[/color]
-------------------------------------------------------------------------
修改完畢後,重啟MySQL即可。當然,為了確保設置正確,應該查看一下max_connections。
注意:
1、雖然這裡寫的32000。但實際MySQL服務器允許的最大連接數16384;
2、除max_connections外,上述其他配置應該根據你們系統自身需要進行配置,不必拘泥;
3、添加了最大允許連接數,對系統消耗增加不大。
4、如果你的mysql用的是my.ini作配置文件,設置類似,但設置的格式要稍作變通。

touzi1987 發表於 2009-5-16 03:52

mysql連接過多的錯誤解決問題

mysql連接過多

問:怎樣解決mysql連接過多的錯誤?

答:系統不能連接數據庫,關鍵要看兩個數據:

1、數據庫系統允許的最大可連接數max_connections。這個參數是可以設置的。如果不設置,默認是100。最大是16384。

2、數據庫當前的連接線程數threads_connected。這是動態變化的。

查看max_connections、max_connections的辦法見後。

如果 threads_connected == max_connections 時,數據庫系統就不能提供更多的連接數了,這時,如果程序還想新建連接線程,數據庫系統就會拒絕,如果程序沒做太多的錯誤處理,就會出現類似強壇的報錯信息。

因為創建和銷毀數據庫的連接,都會消耗系統的資源。而且為了避免在同一時間同時打開過多的連接線程,現在編程一般都使用所謂數據庫連接池技術。

但數據庫連接池技術,並不能避免程序錯誤導致連接資源消耗殆盡。

這種情況通常發生在程序未能及時釋放數據庫連接資源或其他原因造成數據庫連接資源不能釋放,但強壇系統估計不會發生這種低級的編程錯誤。

該錯誤的簡便的檢查辦法是,在刷新強壇頁面時,不斷監視threads_connected的變化。如果max_connections足夠大,而 threads_connected值不斷增加以至達到max_connections,那麼,就應該檢查程序了。當然,如果採用數據庫連接池技術, threads_connected增長到數據庫連接池的最大連接線程數時,就不再增長了。

從強壇出錯的情況看,更大的可能性是數據庫系統沒能進行適當地配置。下面提出一點建議。供參考

讓你們的工程師把MySQL的最大允許連接數從默認的100調成32000。這就不會老出現連接過多的問題了。

查看max_connections

進入MySQL,用命令:

show variables

查看數據庫最大可連接數的變量值:

max_connections

查看threads_connected

進入MySQL,用命令:

show status查看當前活動的連接線程變量值:

threads_connected

設置max_connections

設置辦法是在my.cnf文件中,添加下面的最後紅色的一行:

[mysqld]
port=3306
#socket=MySQL
skip-locking
set-variable = key_buffer=16K
set-variable = max_allowed_packet=1M
set-variable = thread_stack=64K
set-variable = table_cache=4
set-variable = sort_buffer=64K
set-variable = net_buffer_length=2K
[color=Red]set-variable = max_connections=32000[/color]

修改完畢後,重啟MySQL即可。當然,為了確保設置正確,應該查看一下max_connections。

注意:
1、雖然這裡寫的32000。但實際MySQL服務器允許的最大連接數16384;
2、除max_connections外,上述其他配置應該根據你們系統自身需要進行配置,不必拘泥;
3、添加了最大允許連接數,對系統消耗增加不大。
4、如果你的mysql用的是my.ini作配置文件,設置類似,但設置的格式要稍作變通。

頁: [1]

Powered by Ahpal Archiver 7.0.0  © 2001-2010 ahpal.com