MySQL最佳化：mysql服務運行參數的設定

標籤：mysql   運行參數   

可以用管理員登入在mysql>命令列下設定，設定是臨時的。

如果希望永久生效，則要修改/etc/my.cnf檔案中的[mysqld]下相關參數：

#vim /ect/my.cnf

[mysqld]

……

下面詳細講解相關運行參數的設定：

1、並發串連數設定    max_connections

最大並發串連數的設定公式：曾經有過的最大串連數/要設定的最大串連數*100%約等於85%時是合適的，15%應付突發訪問量

mysql> show  variables like  "max_connections";

show global status like“max_used_connections”;   //查看曾經有過的最大串連數

flush status；//清空曾經有過的最大串連數，重新計數

臨時設定：

mysql> set global  max_connections=300;

永久設定：

Vim /etc/my.cnf

[mysqld]

max_connections=300

2、串連資料庫伺服器逾時時間的設定   connect_timeout wait_timeout

connect_timeout=10 (秒)  在擷取串連時，等待握手的逾時時間，只在登入時有效

wait_timeout=28800（秒）伺服器在關閉一個串連上等待行動的秒數，預設28800秒中斷連線的等待時間

查詢：showvariables like “connect_timeout”

臨時設定：setglobal connect_timeout=7；//過長會消耗系統資源，過短會頻繁響應請求也會耗系統資源，一般使用預設值

查詢：showvariables like “wait_timeout”；

臨時設定：set  wait_timeout=3600；//不能過短，否則用戶端資料沒有讀完就會中斷連線

3、設定可以重複使用的儲存在緩衝中線程的數量  thread_cache_size

thread_cache_size    //緩衝線程可以加快訪問速度，不用等使用者來訪問的時候臨時開放線程。如果訪問量小，線程數大的話會浪費資源，訪問量大線程數小訪問會慢，應設定合適的線程數。 

查詢：showvariables like “thread_cache_size”      預設：9個

臨時設定：setglobal  thread_cache_size=8

Show global status like “thread%”;

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Threads_cached    | 1    |   threads_cacheed_size的值

| Threads_connected | 1     |   已有的串連數

| Threads_created   | 2    |   建立過的線程數

| Threads_running   | 1    |   正在運行著的串連數

+-------------------+-------+

Threads_connected 和Threads_created值的差距過大不好，說明“thread_cache_size值設定太小了。

4、設定所有線程開啟表的數量   table_open_cache   預設：2000個

查看：mysql>show variables like "table_open_cache";

臨時設定：mysql>set global table_open_cache=50;

mysql> show global status like"open%table%";

+--------------------------+-------+

| Variable_name         | Value |

+--------------------------+-------+

| Open_table_definitions   | 3  |  

| Open_tables            | 3   |   開啟表的數量

| Opened_table_definitions | 73   |

| Opened_tables       | 73 |開啟過的表的數量，此值過大，table_open_cache值可能太小

+--------------------------+-------+

 table_open_cache 值的設定：Open_tables /table_open_cache*100%<=95%時是合理的

5、設定索引緩衝的大小  key_buffer_size   預設： 8388608位元組=8M

查看：mysql>show variables like "key_buffer_size";

mysql> show global status like"key_read%";

+-------------------+-------+

| Variable_name   | Value |

+-------------------+-------+

| Key_read_requests | 8  |   索引讀取請求總數

| Key_reads       | 4 |   在索引緩衝中沒有找到直接從硬碟讀取的索引數量

+-------------------+-------+

key_buffer_size值的設定： Key_reads/Key_read_requests *100%  此比率值越小越好

6、每個需要進行排序的線程分配給大小的緩衝區，增加此值加速order by 和group by操作    sort_buffer_size

查看：mysql>show variables like "sort_buffer_size";  預設：256K

7、從資料表順序讀取資料的讀操作保留的緩衝區的長度 read_buffer_size    預設：128K

mysql> show variables like "read_buffer_size";

8、按某種特定順序（比如使用裡order by子句的查詢）輸出查詢結果

read_rnd_buffer_size    預設：512K

select * from  mysql.usertab  order by  age;

mysql>show variables like "read_rnd_buffer_size"; 

本文出自 “IT技術學習” 部落格，請務必保留此出處http://learningit.blog.51cto.com/9666723/1790376

MySQL最佳化：mysql服務運行參數的設定