Monday, May 6, 2013

如何調整MySQL服務器的

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


該博客文章的博客系列的一部分
    現在要繼續與服務器本身:

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

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


    的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 |
    +---------------------+
    >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的倍數。
    注意此設置取決於你的應用程序變得越念。 我用來運行其設置為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數據庫。