Mysql優(yōu)化配置_MySQL

Mysql優(yōu)化配置

一、環(huán)境介紹

Mysql版本:5.5.27

二、優(yōu)化內(nèi)容

字段

介紹

推薦值

skip-locking

避免MySQL的外部鎖定，減少出錯(cuò)幾率增強(qiáng)穩(wěn)定性

back_log

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

384

key_buffer_size

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

對(duì)于內(nèi)存在4GB左右的服務(wù)器該參數(shù)可設(shè)置為256M或384M。

注意：該參數(shù)值設(shè)置的過(guò)大反而會(huì)是服務(wù)器整體效率降低!

4G服務(wù)器

256M

max_allowed_packet

當(dāng)MySQL客戶端或mysqld服務(wù)器收到大于max_allowed_packet字節(jié)的信息包時(shí)，將發(fā)出“信息包過(guò)大”錯(cuò)誤，并關(guān)閉連接

4M

thread_stack

主要用來(lái)存放每一個(gè)線程自身的標(biāo)識(shí)信息，如線程id，線程運(yùn)行時(shí)基本信息等等，我們可以通過(guò) thread_stack 參數(shù)來(lái)設(shè)置為每一個(gè)線程棧分配多大的內(nèi)存

192kb

table_cache

表高速緩存的數(shù)目

512

sort_buffer_size

n第一次需要使用這個(gè)buffer的時(shí)候，一次性分配設(shè)置的內(nèi)存

512K

read_buffer_size

讀查詢操作所能使用的緩沖區(qū)大小

4M

join_buffer_size

聯(lián)合查詢操作所能使用的緩沖區(qū)大小

8M

myisam_sort_buffer_size

當(dāng)在REPAIR TABLE或用CREATE INDEX創(chuàng)建索引或ALTER TABLE過(guò)程中排序 MyISAM索引分配的緩沖區(qū)。

64M

thread_cache_size

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

64

query_cache_size

查詢緩存區(qū)的最大長(zhǎng)度

64M

tmp_table_size

如果一張臨時(shí)表超出該大小，MySQL產(chǎn)生一個(gè) The table tbl_name is full 形式的錯(cuò)誤

256M

max_connections

最大用戶連接數(shù)

最大連接數(shù)占上限連接數(shù)的85％左右

3000

max_connect_errors

它負(fù)責(zé)阻止過(guò)多嘗試失敗的客戶端以防止暴力破解密碼的情況。max_connect_errors的值與性能并無(wú)太大關(guān)系

10000000

wait_timeout

指定一個(gè)請(qǐng)求的最大連接時(shí)間，對(duì)于4GB左右內(nèi)存的服務(wù)器可以設(shè)置為5-10。

10

thread_concurrency

該參數(shù)取值為服務(wù)器邏輯CPU數(shù)量×2

4

innodb_log_file_size

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

256M

innodb_log_buffer_size

事務(wù)日志文件寫操作緩存區(qū)的最大長(zhǎng)度

8M

innodb_flush_logs_at_trx_commit

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

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

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

2

innodb_buffer_pool_size

innodb_buffer_pool_size 定義了 InnoDB 存儲(chǔ)引擎的表數(shù)據(jù)和索引數(shù)據(jù)的最大內(nèi)存緩沖區(qū)大小

在專用數(shù)據(jù)庫(kù)服務(wù)器上,可以考慮該值為物理內(nèi)存大小的 60%-80%

1G

innodb_additional_mem_pool_size

除了緩存表數(shù)據(jù)和索引外,可以為操作所需的其他內(nèi)部項(xiàng)分配緩存來(lái)提升InnoDB的性能。這些內(nèi)存就可以通過(guò)此參數(shù)來(lái)分配。推薦此參數(shù)至少設(shè)置為2MB

2M

三、優(yōu)化重點(diǎn)

1:max_connections

經(jīng)常會(huì)遇見”MySQL: ERROR 1040: Too many connections”的情況，一種是訪問量確實(shí)很高，MySQL服務(wù)器抗不住，這個(gè)時(shí)候就要考慮增加從服務(wù)器分散讀壓力，另外一種情況是MySQL配置文件中max_connections值過(guò)小：

比較理想的設(shè)置是

Max_used_connections / max_connections * 100% ≈ 85%

最大連接數(shù)占上限連接數(shù)的85％左右，如果發(fā)現(xiàn)比例在10%以下，MySQL服務(wù)器連接數(shù)上限設(shè)置的過(guò)高了。

2:Key_buffer_size

key_buffer_size是對(duì)MyISAM表性能影響最大的一個(gè)參數(shù)：

key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%

key_cache_miss_rate在0.1%以下都很好（每1000個(gè)請(qǐng)求有一個(gè)直接讀硬盤），如果key_cache_miss_rate在0.01%以下的話，key_buffer_size分配的過(guò)多，可以適當(dāng)減少

3:臨時(shí)表

比較理想的配置是：

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:進(jìn)程使用情況

如果發(fā)現(xiàn)Threads_created值比較大,那么就可以考慮把thread_cache_size的值設(shè)大一些

6:查詢緩存

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

查詢緩存利用率在25%以下的話說(shuō)明query_cache_size設(shè)置的過(guò)大，可適當(dāng)減小；查詢緩存利用率在80％以上而且Qcache_lowmem_prunes > 50的話說(shuō)明query_cache_size可能有點(diǎn)小，要不就是碎片太多。

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

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

7:文件打開數(shù)

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

8:表鎖情況

Table_locks_immediate表示立即釋放表鎖數(shù)，Table_locks_waited表示需要等待的表鎖數(shù)，如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因?yàn)镮nnoDB是行鎖而MyISAM是表鎖，對(duì)于高并發(fā)寫入的應(yīng)用InnoDB效果會(huì)好些。

9:表掃描情況

計(jì)算表掃描率：

表掃描率 ＝ Handler_read_rnd_next / Com_select

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

四、Mysql指定ip用戶訪問

主機(jī)部分就是代表允許的主機(jī)訪問,%符號(hào)代表允許所有的主機(jī)

添加用戶授權(quán)IP命令例子:

使用myuser/mypassword從ip為61.129.51.8的主機(jī)連接到mysql服務(wù)器:

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

FLUSH PRIVILEGES;

五、總結(jié)

在實(shí)際配置中,每一臺(tái)服務(wù)器性能是不一樣,因此Mysql優(yōu)化配置主要參照第三部分為主,先測(cè)試運(yùn)行一段時(shí)間游戲,然后在進(jìn)入Mysql去查看各個(gè)變量的值,然后根據(jù)公式去計(jì)算各個(gè)變量的值,是否在標(biāo)準(zhǔn)范圍內(nèi),不在標(biāo)準(zhǔn)范圍內(nèi)的,都相應(yīng)的上下調(diào)動(dòng)一下.

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com