mysql優化配置參數

2024-07-24 12:44:32

字體：大中小

來源：轉載

供稿：網友

第一種
復制代碼代碼如下:

#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”命令返回的狀態進行微調。我主要注意以下變量的數值，越小越好，最好為零：）

上一篇：phpmyadmin中為站點設置mysql權限的圖文方法

下一篇：MySQL數據庫存儲引擎和分支現狀分析