Mysql優化配置

一、環境介紹

Mysql版本:5.5.27

二、優化內容

字段

介紹

推薦值

skip-locking

避免MySQL的外部鎖定，減少出錯幾率增強穩定性

back_log

MySQL可能的連接數量(linux下推薦小于512)

384

key_buffer_size

key_buffer_size指定用于索引的緩沖區大小，增加它可得到更好的索引處理性能。

對于內存在4GB左右的服務器該參數可設置為256M或384M。

注意：該參數值設置的過大反而會是服務器整體效率降低!

4G服務器

256M

max_allowed_packet

當MySQL客戶端或mysqld服務器收到大于max_allowed_packet字節的信息包時，將發出“信息包過大”錯誤，并關閉連接

4M

thread_stack

主要用來存放每一個線程自身的標識信息，如線程id，線程運行時基本信息等等，我們可以通過 thread_stack 參數來設置為每一個線程棧分配多大的內存

192kb

table_cache

表高速緩存的數目

512

sort_buffer_size

n第一次需要使用這個buffer的時候，一次性分配設置的內存

512K

read_buffer_size

讀查詢操作所能使用的緩沖區大小

4M

join_buffer_size

聯合查詢操作所能使用的緩沖區大小

8M

myisam_sort_buffer_size

當在REPAIR TABLE或用CREATE INDEX創建索引或ALTER TABLE過程中排序 MyISAM索引分配的緩沖區。

64M

thread_cache_size

表示可以重新利用保存在緩存中線程的數量,當斷開連接時如果緩存中還有空間,那么客戶端的線程將被放到緩存中,如果線程重新被請求，那么請求將從緩存中讀取,(3G以上內存推薦為64)

64

query_cache_size

查詢緩存區的最大長度

64M

tmp_table_size

如果一張臨時表超出該大小，MySQL產生一個 The table tbl_name is full 形式的錯誤

256M

max_connections

最大用戶連接數

最大連接數占上限連接數的85％左右

3000

max_connect_errors

它負責阻止過多嘗試失敗的客戶端以防止暴力破解密碼的情況。max_connect_errors的值與性能并無太大關系

10000000

wait_timeout

指定一個請求的最大連接時間，對于4GB左右內存的服務器可以設置為5-10。

10

thread_concurrency

該參數取值為服務器邏輯CPU數量×2

4

innodb_log_file_size

如果對 Innodb 數據表有大量的寫入操作，那么選擇合適的 innodb_log_file_size 值對提升MySQL性能很重要

256M

innodb_log_buffer_size

事務日志文件寫操作緩存區的最大長度

8M

innodb_flush_logs_at_trx_commit

1) =1時，在每個事務提交時，日志緩沖被寫到日志文件，對日志文件做到磁盤操作的刷新。Truly ACID。速度慢。

2) =2時，在每個事務提交時，日志緩沖被寫到文件，但不對日志文件做到磁盤操作的刷新。只有操作系統崩潰或掉電才會刪除最后一秒的事務，不然不會丟失事務。

3) =0時， 日志緩沖每秒一次地被寫到日志文件，并且對日志文件做到磁盤操作的刷新。任何mysqld進程的崩潰會刪除崩潰前最后一秒的事務

2

innodb_buffer_pool_size

innodb_buffer_pool_size 定義了 InnoDB 存儲引擎的表數據和索引數據的最大內存緩沖區大小

在專用數據庫服務器上,可以考慮該值為物理內存大小的 60%-80%

1G

innodb_additional_mem_pool_size

除了緩存表數據和索引外,可以為操作所需的其他內部項分配緩存來提升InnoDB的性能。這些內存就可以通過此參數來分配。推薦此參數至少設置為2MB

2M

三、優化重點

1:max_connections

經常會遇見”MySQL: ERROR 1040: Too many connections”的情況，一種是訪問量確實很高，MySQL服務器抗不住，這個時候就要考慮增加從服務器分散讀壓力，另外一種情況是MySQL配置文件中max_connections值過?。?/p>

比較理想的設置是

Max_used_connections / max_connections * 100% ≈ 85%

最大連接數占上限連接數的85％左右，如果發現比例在10%以下，MySQL服務器連接數上限設置的過高了。

2:Key_buffer_size

key_buffer_size是對MyISAM表性能影響最大的一個參數：

key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%

key_cache_miss_rate在0.1%以下都很好（每1000個請求有一個直接讀硬盤），如果key_cache_miss_rate在0.01%以下的話，key_buffer_size分配的過多，可以適當減少

3:臨時表

比較理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

4:open table

Open_tables / Opened_tables * 100% >= 85%

Open_tables / table_cache * 100% <= 95%

5:進程使用情況

如果發現Threads_created值比較大,那么就可以考慮把thread_cache_size的值設大一些

6:查詢緩存

查詢緩存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%

查詢緩存利用率在25%以下的話說明query_cache_size設置的過大，可適當減小；查詢緩存利用率在80％以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是碎片太多。

查詢緩存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

查詢緩存碎片率 ＝ 20.46％，查詢緩存利用率 ＝ 62.26％，查詢緩存命中率 ＝ 1.94％，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。

7:文件打開數

比較合適的設置：Open_files / open_files_limit * 100% <= 75％

8:表鎖情況

Table_locks_immediate表示立即釋放表鎖數，Table_locks_waited表示需要等待的表鎖數，如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因為InnoDB是行鎖而MyISAM是表鎖，對于高并發寫入的應用InnoDB效果會好些。

9:表掃描情況

計算表掃描率：

表掃描率 ＝ Handler_read_rnd_next / Com_select

如果表掃描率超過4000，說明進行了太多表掃描，很有可能索引沒有建好，增加read_buffer_size值會有一些好處，但最好不要超過8MB。

四、Mysql指定ip用戶訪問

主機部分就是代表允許的主機訪問,%符號代表允許所有的主機

添加用戶授權IP命令例子:

使用myuser/mypassword從ip為61.129.51.8的主機連接到mysql服務器:

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'61.129.0.0' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

FLUSH PRIVILEGES;

五、總結

在實際配置中,每一臺服務器性能是不一樣,因此Mysql優化配置主要參照第三部分為主,先測試運行一段時間游戲,然后在進入Mysql去查看各個變量的值,然后根據公式去計算各個變量的值,是否在標準范圍內,不在標準范圍內的,都相應的上下調動一下.

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com