From:http://www.bsdlover.cn/html/89/n-1789.html
- thread_concurrency
- thread_cache_size
數量設置為CPU核心數量的兩倍.
按照內存大小來設置, 1G=8, 2G=16, 3G=32, >3G=64
- wait_timeout
- max_connections
- query_cache_size
- query_cache_type
- query_cache_limit
超時時間,如果連接數比較大,可以減少此參數的值,我使用的是10
最大連接數,mysql實際允許連接數的值是max_connections+1,按照系統庫不同而有不同性能。一般是500~1000,MySQL AB提供的linux靜態庫可以達到4000。
查詢緩衝,默認是0,所以必須打開以提高mysql性能,其本身需要40K來保存結構數據。所以不能設置的太小,初期可以設置成32M,然後根據實際運行情況另行調整。
指定查詢緩衝的類型:
0是關閉
1是緩衝除了使用 SELECT SQL_NO_CACHE 語句指明了不需要緩衝的數據意外的所有查詢
2是只緩衝 SELECT SQL_CACHE 指定的查詢
一般設置為1
允許進入查詢緩衝區的最小數據大小,默認值是1MB,可以修改的小一點以滿足更多查詢的需求。但是如果設置的過於小,則會導致很多新的小查詢的結果將原有的查詢結果交換出去,增加系統的顛簸。
相關命令
- 查詢mysql服務器相關狀態數據
- 查詢mysql服務器相關配置選項
- 整理查詢緩衝區裡的碎片
- 刪除查詢緩衝區裡的所有內容
- 設置mysql參數
- 查詢mysql當前執行的sql語句
>SHOW STATUS;
>SHOW VARIABLES;
>flush query cache;
>reset query cache;
>SET GLOBAL;
>show processlist;
變量含義
- 查詢緩存中的空閒內存塊的數目
- 查詢緩存的空閒內存總數
- 緩存採樣數數目
- 被加入到緩存中的查詢數目
- 因為缺少內存而被從緩存中刪除的查詢數目
- 沒有被緩存的查詢數目 (不能被緩存的,或由於 QUERY_CACHE_TYPE)
- 在緩存中已註冊的查詢數目
- 查詢緩存中的塊的總數目
Qcache_free_blocks
Qcache_free_memory
Qcache_hits
Qcache_inserts
Qcache_lowmem_prunes
Qcache_not_cached
Qcache_queries_in_cache
Qcache_total_blocks
MySQL查詢優化
查詢Cache狀態
>SHOW STATUS LIKE 'Qcache%';
如果Qcache_lowmem_prunes非常大,說明因為內存不足而被交換出cache的數據很多.如果增加內存.可以保證較小的交換次數以及較高的命中率
例如現在我們查詢的結果如下:
Qcache_free_blocks | 1234 |
Qcache_free_memory | 25957504 |
Qcache_hits | 55771119 |
Qcache_inserts | 7441153 |
Qcache_lowmem_prunes | 28332 |
Qcache_not_cached | 1233788 |
Qcache_queries_in_cache | 4810 |
Qcache_total_blocks | 11038 |
設置為64M cache內存後
>set global query_cache_size=67108864;
Qcache_free_blocks | 1 |
Qcache_free_memory | 66623616 |
Qcache_hits | 55788258 |
Qcache_inserts | 7445445 |
Qcache_lowmem_prunes | 28332 |
Qcache_not_cached | 1234057 |
Qcache_queries_in_cache | 183 |
Qcache_total_blocks | 392 |
自由內存塊看起來變小了。
是因為現在自由內存塊是一個整塊,而以前的內存塊都是分散的小塊。而因為重建了cache區,Qcache_queries_in_cache 變量變小了。因為此操作重新建立了cache內存區,所有數據重新緩存,在運行一兩天後我們再看此數據。如果變大了,說明增大cache內存區域是有效的,如果和以前數據差不多說明增加的內存並沒有實際起到多大的作用。
有人會覺得如果我將cache內存設置的非常大,然後將cache_limit設置成0,那麼所有查詢都會被緩存了。理論上是這樣,但是一台數據庫服務器的查詢非常多,如果連查詢單條數據都要緩存,那麼內存再大也會不夠的。到時候老的內容就會被交換出去,當cache內存使用滿的時候,就會不停的有新查詢進來將老查詢替換出去。
這樣導致兩個結果:一個是內存顛簸,效率反而下降;第二個是cache內存的小碎塊增多,內存利用率降低。如果是只有內容很少的小庫,並且查詢率不高,是可以使用這種方法提高響應速度。但是如果是實際生產環境,數據量會比較大,還是需要按照最佳比例來配置。
而不同的應用不同的數據量會有不同的搭配,這點大家不要看網上的優化配置隨便的填寫,還是要時時的查看mysql的狀態進行調整。即便是這個月調整好的優化參數,到了下個月業務不同,數據量增加,也會需要調整的。
更改前(8MB):
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 341 |
| Qcache_free_memory | 951328 |
| Qcache_hits | 582095 |
| Qcache_inserts | 840979 |
| Qcache_lowmem_prunes | 471145 |
| Qcache_not_cached | 14304 |
| Qcache_queries_in_cache| 1295 |
| Qcache_total_blocks | 3481 |
+-------------------------+--------+
更改後(32MB):
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 926 |
| Qcache_free_memory | 6064696 |
| Qcache_hits | 925690 |
| Qcache_inserts | 600668 |
| Qcache_lowmem_prunes | 106150 |
| Qcache_not_cached | 27196 |
| Qcache_queries_in_cache | 7513 |
| Qcache_total_blocks | 16123 |
+-------------------------+---------+