另一個MySQL DBA: 如何調整MySQL服務器的

Original Post: http://anothermysqldba.blogspot.com/2013/05/how-to-tune-mysql-server.html

該博客文章的博客系列的一部分

MySQL服務器調優問題

如何識別MySQL的慢查詢及相關問題

如何調整MySQL服務器的

現在要繼續與服務器本身：

那麼除了剛才說做什麼彼得說，讓他們如何能解決這個問題，自己的審查的例子。

對於初學者來說，你可以比較你的Q＆A版本，可用於通過Percona的my.cnf文件對。它這是一個理想的解決方案？沒有，但它可以讓你重新審視你的配置文件後，你的回答他們的所有問題，通過配置嚮導。

https://tools.percona.com/wizard

的innodb_buffer_pool_size -

select @@innodb_buffer_pool_size;

設置將innodb_buffer_pool_size是迄今為止一個MySQL InnoDB的database.Some的良好的閱讀關於這一主題的最重要的設置如下：

你需要記住，沒有一個“一刀切”設置將innodb_buffer_pool_size所有查詢。我們確實有一些共同的指導和建議多年來已經浮出水面（見下文），我們也有一些不同的理論最好的選擇。

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS
FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;


SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 3 PowerOf1024) B;

這是肯定地說，如果你的innodb_buffer_pool_size變量的默認設置為8M，那麼這應該是第一次設置您調整。

基本上，允許它有所有你能負擔得起的可用內存給它 。當然，這需要考慮到幾個因素，如還有什麼需要服務器上的內存分配？請記住，磁盤I / O的性能和更多的內存，你讓這個變量有更少的磁盤I / O訪問表應要求是很重要的。

讓我們也認為這是....
由於一般的建議是設置the innodb_log_file_size innodb_log_file_size per 男爵的概念，我們有什麼我們應該評估緩衝池大小的25％，然後看怎麼看涉及將innodb_buffer_pool_size。

例如，以下的邏輯男爵的文章：

> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 3982683217
1 row in set (0.01 sec)

1 row in set (59.99 sec)

Log sequence number 3991367755
1 row in set (0.01 sec)

> SET @sequence1=3982683217;
Query OK, 0 rows affected (0.00 sec)
> SET @sequence2=3991367755;
Query OK, 0 rows affected (0.00 sec)
> select (@sequence2 - @sequence1) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 8.28222084 |
+------------+
1 row in set (0.00 sec)
> select ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 as MB_per_hour ;
+--------------+
| MB_per_hour |
+--------------+
| 496.93325043 |
+--------------+
1 row in set (0.00 sec)
> select ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 as estimated_buffer_pool ;
+-----------------------+
| estimated_buffer_pool |
+-----------------------+
| 1987.73300171 |
+-----------------------+
1 row in set (0.00 sec)
> select ( ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 ) / 1024 as estimated_buffer_pool_GB ;
+--------------------------+
| estimated_buffer_pool_GB |
+--------------------------+
| 1.941145509481 |
+--------------------------+
1 row in set (0.00 sec)

現在，當您比較這些結果，“指引”

SELECT CONCAT(ROUND(KBS/POWER(1024,
-> IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
-> SUBSTR(' KMG',IF(PowerOf1024<0,0,
-> IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
-> FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
-> WHERE engine='InnoDB') A,
-> (SELECT 3 PowerOf1024) B;
+-------------------------------------+
| recommended_innodb_buffer_pool_size |
+-------------------------------------+
| 1G |
+-------------------------------------+

你會得到一個不同的結果。，雖然男爵的檢查依賴的時間框架內執行（這就是為什麼你應該檢查這個交通高峰期間），它可以給你一個更為現實的觀點，你的流量和使用。我會考慮將innodb_buffer_pool_size設置2G不會1G在這個簡單的例子。

SET @從sequence1 = 3982683217;
SET @ sequence2 = 3991367755;
選擇（@ sequence2 - 從sequence1）/ 1024/1024作為MB_per_min;
選擇（（@ sequence2 - 從sequence1）/ 1024/1024）* 60 MB_per_hour;
選擇（（（的@ sequence2 - @序列1）/ 1024/1024）* 60）* 4 estimated_buffer_pool;
選擇（（（（@ sequence2 - 從sequence1）/ 1024/1024）* 60）* 4）/ 1024 estimated_buffer_pool_GB;

innodb_log_file_size -

select @@innodb_log_file_size;

我發現我喜歡上男爵的概念如何設置innodb_log_file_size。請記住，在高峰時段執行他的建議得到正確讀數。這些文件的大型數據集的性能更好，但沒有一樣是免費的規模越大，你會增加恢復時間。聽起來並不增加恢復時間，直到它是一個主要關注一些收入依賴數據庫，你都在觀望的過程中永遠。一般建議將其設置為緩衝池大小的25％。

select ( @@innodb_buffer_pool_size * .25 )

innodb_log_buffer_size -

select @@innodb_log_buffer_size;

我記得我曾經有過這組服務器上的innodb_log_buffer_size = 128M。這是一個很好的選擇嗎？不，它意味著我想要的內存時，我很可能沒得。這是一個非常沉重的數據庫寫入，但是這個設置是非常高的可能性。首先集中在默認，然後雙擊它（8 - 16MB）最大。

innodb_additional_mem_pool_size -

select @@innodb_additional_mem_pool_size;

彼得在他的博客文章中解決了這個設置。你可以解決它，並評估其他設置選項後，在我看來已經解決。

的innodb_flush_log_at_trx_commit -

select @@innodb_flush_log_at_trx_commit;

>設置全局的innodb_flush_log_at_trx_commit = 2;
彼得解決這個設置在他的博客文章。這是一個經常被忽視的變量，值得測試性能的提高，通過調整從0 - 2。請介意的風險。

thread_cache -

select @@thread_cache_size;
+---------------------+
| @@thread_cache_size |
+---------------------+
| 50 |
+---------------------+

http://dev.mysql.com/doc/refman/5.5/en/connection-threads.html

>show status like 'threads_created';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_created | 4 |
+-----------------+-------+
1 row in set (0.00 sec)

> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 962 |
+---------------+-------+
1 row in set (0.00 sec)

> SELECT 100 - ((4 / 962) * 100);
+-------------------------+
| 100 - ((4 / 962) * 100) |
+-------------------------+
| 99.5842 |
+-------------------------+

在此狀態下保持眼睛像'Threads_Created的'顯示狀態;
如果它爬上那麼你設置過低，有待提高。

query_cache_size變量 -

select ((@@query_cache_size / 1024) / 1024);

允許值是1024的倍數。

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html＃sysvar_query_cache_size

注意此設置取決於你的應用程序變得越念。我用來運行其設置為key_buffer = 16M，我可能應該已經翻了一番。

key_buffer_size的 -

select @@key_buffer_size;

與MySQL開關作為默認的存儲引擎InnoDB的，它可能是一些我們可以看到越來越少創建的MyISAM表。那是不是其實只是一種意見。的key_buffer_size對你很重要，如果你使用MyISAM表。

審查施洛米諾奇的職位，以幫助您找到易表發動機類型。
“每個表的大小（幾乎完全一樣INFORMATION_SCHEMA）” - 諾奇施洛米。

> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 15728640 |
+-----------------+----------+

table_cache的 -

show variables like '%table%cache%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_definition_cache | 4096 |
| table_open_cache | 4096 |
| table_open_cache_instances | 1 |
+----------------------------+-------+

根據手冊，這是一個很好的公式來，幫助確定table_definition_cache大小。

SELECT 400 + (@@table_open_cache / 2);

> SHOW status like '%Opened_tables%';

table_open_cache_instances
建議經常使用的16個或更多內核的系統上的8個或16個值，默認值是1。

希望這個公開的變量和設置需要進行審查，以幫助你得到最好的從你的MySQL數據庫。

另一個MySQL DBA

Pages

2013年5月6日星期一

如何調整MySQL服務器的