程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql server 配置優化

mysql server 配置優化

編輯:MySQL綜合教程

MySql服務器的後台管理程序,要想使用客戶端程序,該程序必須運行,因為客戶端通過連接服務器來訪問數據庫。下面讓我們以服務器的系統變量和狀態變量為根據,優化我們的MySql數據庫服務。
在這之前,我們需要掌握以下方法:

查看MySql狀態及變量的方法:
Mysql> show status ——顯示狀態信息(擴展show status like XXX)
Mysql> show variables ——顯示系統變量(擴展show variables like XXX)
Mysql> show innodb status ——顯示InnoDB存儲引擎的狀態
Shell> mysqladmin variables -u username -p password——顯示系統變量
Shell> mysqladmin extended-status -u username -p password——顯示狀態信息
查看狀態變量及幫助:
Shell> mysqld --verbose --help [|more #逐行顯示]

首先,讓我們看看有關請求連接的變量:
為了能適應更多數據庫應用用戶,MySql提供了連接(客戶端)變量,以對不同性質的用戶群體提供不同的解決方案,筆者就max_connections,back_log做了一些細結,如下:


max_connections是指MySql的最大連接數,如果服務器的並發連接請求量比較大,建議調高此值,以增加並行連接數量,當然這建立在機器能支撐的情況下,因為如果連接數越多,介於MySql會為每個連接提供連接緩沖區,就會開銷越多的內存,所以要適當調整該值,不能盲目提高設值。可以過conn%通配符查看當前狀態的連接數量,以定奪該值的大小。


back_log是要求MySQL能有的連接數量。當主要MySQL線程在一個很短時間內得到非常多的連接請求,這就起作用,然後主線程花些時間(盡管很短)檢查連接並且啟動一個新線程。back_log值指出在MySQL暫時停止回答新請求之前的短時間內多少個請求可以被存在堆棧中。如果期望在一個短時間內有很多連接,你需要增加它。也就是說,如果MySql的連接數據達到max_connections時,新來的請求將會被存在堆棧中,以等待某一連接釋放資源,該堆棧的數量即back_log,如果等待連接的數量超過back_log,將不被授予連接資源。另外,這值(back_log)限於您的操作系統對到來的TCP/IP連接的偵聽隊列的大小。你的操作系統在這個隊列大小上有它自己的限制(可以檢查你的OS文檔找出這個變量的最大值),試圖設定back_log高於你的操作系統的限制將是無效的。


優化了MySql的連接後屬性後,我們需要看看緩沖區變量:


使用MySql數據庫存儲大量數據(或使用復雜查詢)時,我們應該考慮MySql的內存配置。如果配置MySQL服務器使用太少的內存會導致性能不是最優的;如果配置了太多的內存則會導致崩潰,無法執行查詢或者導致交換操作嚴重變慢。在現在的32位平台下,仍有可能把所有的地址空間都用完,因此需要審視。


計算內存使用的秘訣公式就能相對地解決這一部分問題。不過,如今這個公式已經很復雜了,更重要的是,通過它計算得到的值只是“理論可能”並不是真正消耗的值。事實上,有8GB內存的常規服務器經常能運行到最大的理論值(100GB甚至更高)。此外,你輕易不會使用到“超額因素”(它實際上依賴於應用以及配置)。一些應用可能需要理論內存的10%而有些僅需1%。
那麼,我們可以做什麼呢?


來看看那些在啟動時就需要分配並且總是存在的全局緩沖吧!


全局緩沖:
key_buffer_size, innodb_buffer_pool_size, innodb_additional_mem_pool_size,innodb_log_buffer_size, query_cache_size


注:如果你大量地使用MyISAM表,那麼你也可以增加操作系統的緩存空間使得MySQL也能用得著。把這些也都加到操作系統和應用程序所需的內存值之中,可能需要增加32MB甚至更多的內存給MySQL服務器代碼以及各種不同的小靜態緩沖。這些就是你需要考慮的在MySQL服務器啟動時所需的內存。其他剩下的內存用於連接。


key_buffer_size決定索引處理的速度,尤其是索引讀的速度。一般我們設為16M,通過檢查狀態值Key_read_requests和Key_reads,可以知道key_buffer_size設置是否合理。比例key_reads / key_read_requests應該盡可能的低,至少是1:100,1:1000更好(上述狀態值可以使用key_read%獲得用來顯示狀態數據)。key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表,但是內部的臨時磁盤表是MyISAM表,也要使用該值。可以使用檢查狀態值created_tmp_disk_tables得知詳情。


innodb_buffer_pool_size對於InnoDB表來說,作用就相當於key_buffer_size對於MyISAM表的作用一樣。InnoDB使用該參數指定大小的內存來緩沖數據和索引。對於單獨的MySQL數據庫服務器,最大可以把該值設置成物理內存的80%。


innodb_additional_mem_pool_size指定InnoDB用來存儲數據字典和其他內部數據結構的內存池大小。缺省值是1M。通常不用太大,只要夠用就行,應該與表結構的復雜度有關系。如果不夠用,MySQL會在錯誤日志中寫入一條警告信息。


innodb_log_buffer_size指定InnoDB用來存儲日志數據的緩存大小,如果您的表操作中包含大量並發事務(或大規模事務),並且在事務提交前要求記錄日志文件,請盡量調高此項值,以提高日志效率。


query_cache_size是MySql的查詢緩沖大小。(從4.0.1開始,MySQL提供了查詢緩沖機制)使用查詢緩沖,MySQL將SELECT語句和查詢結果存放在緩沖區中,今後對於同樣的SELECT語句(區分大小寫),將直接從緩沖區中讀取結果。根據MySQL用戶手冊,使用查詢緩沖最多可以達到238%的效率。通過檢查狀態值’Qcache_%’,可以知道query_cache_size設置是否合理:如果Qcache_lowmem_prunes的值非常大,則表明經常出現緩沖不夠的情況,如果Qcache_hits的值也非常大,則表明查詢緩沖使用非常頻繁,此時需要增加緩沖大小;如果Qcache_hits的值不大,則表明你的查詢重復率很低,這種情況下使用查詢緩沖反而會影響效率,那麼可以考慮不用查詢緩沖。此外,在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩沖。


除了全局緩沖,MySql還會為每個連接發放連接緩沖。


連接緩沖:
每個連接到MySQL服務器的線程都需要有自己的緩沖。大概需要立刻分配256K,甚至在線程空閒時,它們使用默認的線程堆棧,網絡緩存等。事務開始之後,則需要增加更多的空間。運行較小的查詢可能僅給指定的線程增加少量的內存消耗,然而如果對數據表做復雜的操作例如掃描、排序或者需要臨時表,則需分配大約read_buffer_size,sort_buffer_size,read_rnd_buffer_size,tmp_table_size大小的內存空間。不過它們只是在需要的時候才分配,並且在那些操作做完之後就釋放了。有的是立刻分配成單獨的組塊。tmp_table_size 可能高達MySQL所能分配給這個操作的最大內存空間了。注意,這裡需要考慮的不只有一點 —— 可能會分配多個同一種類型的緩存,例如用來處理子查詢。一些特殊的查詢的內存使用量可能更大——如果在MyISAM表上做成批的插入時需要分配 bulk_insert_buffer_size 大小的內存;執行 ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 命令時需要分配 myisam_sort_buffer_size 大小的內存。


read_buffer_size是MySql讀入緩沖區大小。對表進行順序掃描的請求將分配一個讀入緩沖區,MySql會為它分配一段內存緩沖區。read_buffer_size變量控制這一緩沖區的大小。如果對表的順序掃描請求非常頻繁,並且你認為頻繁掃描進行得太慢,可以通過增加該變量值以及內存緩沖區大小提高其性能。


sort_buffer_size是MySql執行排序使用的緩沖大小。如果想要增加ORDER BY的速度,首先看是否可以讓MySQL使用索引而不是額外的排序階段。如果不能,可以嘗試增加sort_buffer_size變量的大小。


read_rnd_buffer_size是MySql的隨機讀緩沖區大小。當按任意順序讀取行時(例如,按照排序順序),將分配一個隨機讀緩存區。進行排序查詢時,MySql會首先掃描一遍該緩沖,以避免磁盤搜索,提高查詢速度,如果需要排序大量數據,可適當調高該值。但MySql會為每個客戶連接發放該緩沖空間,所以應盡量適當設置該值,以避免內存開銷過大。


tmp_table_size是MySql的heap(堆積)表緩沖大小。所有聯合在一個DML指令內完成,並且大多數聯合甚至可以不用臨時表即可以完成。大多數臨時表是基於內存的(HEAP)表。具有大的記錄長度的臨時表 (所有列的長度的和)或包含BLOB列的表存儲在硬盤上。如果某個內部heap(堆積)表大小超過tmp_table_size,MySQL可以根據需要自動將內存中的heap表改為基於硬盤的MyISAM表。還可以通過設置tmp_table_size選項來增加臨時表的大小。也就是說,如果調高該值,MySql同時將增加heap表的大小,可達到提高聯接查詢速度的效果。


當我們設置好了緩沖區大小之後,再來看看:


table_cache所有線程打開的表的數目,增大該值可以增加mysqld需要的文件描述符的數量。每當MySQL訪問一個表時,如果在表緩沖區中還有空間,該表就被打開並放入其中,這樣可以更快地訪問表內容。通過檢查峰值時間的狀態值’Open_tables’和’Opened_tables’,可以決定是否需要增加table_cache的值。如果你發現open_tables等於table_cache,並且opened_tables在不斷增長,那麼你就需要增加table_cache的值了(上述狀態值可以使用’Open%tables’獲得)。注意,不能盲目地把table_cache設置成很大的值。如果設置得太高,可能會造成文件描述符不足,從而造成性能不穩定或者連接失敗。


做了以上方面的調優設置之後,MySql應該基本能滿足您需求(當

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved