對於 Mysql 的調教

[markwu]

布大俠的來信：

Dear Mark,

我是布大俠,近來在調整系統,發現plog在運行一陣子之後會在我的mysql中留下許多不會關閉的thread,一個晚上就多了三十幾條死在那邊,請問有麼辦法查出是哪裡的問題?
我裝的外掛都是你這邊幹來的,我想應該有很多人也有相同問題只是沒注意到而已,但因為背包客棧流量不小,我的mysql跑一天就會掛了....請幫忙.

pLog 其實對於 DB 是沒有怎麼在調整的，所以當量一大很容易出現 performance 的瓶頸。有下面幾個方式可以來調整 pLog 的 performance。

1. DB 效能不彰：為 DB 加上 Index。

程式碼:

ALTER TABLE plog_articles ADD INDEX (num_reads), ADD INDEX (category_id), ADD INDEX (blog_id), ADD INDEX (user_id);
ALTER TABLE plog_articles_categories ADD INDEX (blog_id);
ALTER TABLE plog_articles_comments ADD INDEX (parent_id), ADD INDEX (article_id);
ALTER TABLE plog_articles_notifications ADD INDEX (article_id), ADD INDEX (user_id), ADD INDEX (blog_id);
ALTER TABLE plog_bayesian_filter_info ADD INDEX (blog_id); ALTER TABLE plog_bayesian_tokens ADD INDEX (blog_id);
ALTER TABLE plog_blogs ADD INDEX (owner_id);
ALTER TABLE plog_filtered_content ADD INDEX (blog_id);
ALTER TABLE plog_gallery_albums ADD INDEX (parent_id), ADD INDEX (owner_id);
ALTER TABLE plog_gallery_resources ADD INDEX (album_id), ADD INDEX (owner_id);
ALTER TABLE plog_host_blocking_rules ADD INDEX (blog_id);
ALTER TABLE plog_mylinks ADD INDEX (blog_id), ADD INDEX (category_id);
ALTER TABLE plog_mylinks_categories ADD INDEX (blog_id);
ALTER TABLE plog_myrecent ADD INDEX (blog_id), ADD INDEX (category_id), ADD INDEX (user_id);
ALTER TABLE plog_myrecent_categories ADD INDEX (blog_id);
ALTER TABLE plog_referers ADD INDEX (blog_id), ADD INDEX (article_id);
ALTER TABLE plog_trackbacks ADD INDEX (article_id);
ALTER TABLE plog_users_permissions ADD INDEX (blog_id), ADD INDEX (user_id), ADD INDEX (permission_id); 

2. 常發生 Too many connection：加大 Mysql 的 max_connection。

預設值為 50。請自行視情況加大，例如

程式碼:

set-variable = max_connections = 500

3. 常發生 mysql thread 沒被使用，佔據 connection：縮短 Mysql 的 interactive_timeout。

預設值為 28800（八小時）。請自行視情況縮短，例如

程式碼:

set-variable = interactive_timeout= 600

4. 虛擬主機商不准我用 pconnect：改為 non persistent connection。

把

程式碼:

define( "ADODB_NEVER_PERSIST", true );  

加到 index.php 與 admin.php 的最前面

還有沒有人有其他建議呢？

[brian]

感謝Mark的協助,我已經採用縮短wait_timeout(interactive_timeout)的方式暫時解決了連線過多不會關閉的問題.不過這種作法好像有點治標不治本,希望有更好的解決方式.

[far]

被MarkWu凹了一下..就來寫一點點針對plog 0.32的db來寫一點建議好了.:Q

    .32 最大的問題就是...db寫的真夠爛的.又沒加Index..:Q

    然後它裡面架構跟query根本就亂寫.所以下面的tips能造成的功效有限..

    最好的作法就是..去改寫plog底層..:Q

    1.0 雖然號稱改了很多啦..但是...我看了之後的結果是..

    是比.32好多了..但是還是有很明顯的問題阿.= =a

    plog_articles
     - topic 沒事設text是自找麻煩..VARCHAR(150) [150看你對topic覺得多長是極限]
     - user_id, blog_id 其實有必要設到INT(10)麼?= =a
       如果使用者(or blog)會小於250人(個)..設個TINYINT(3) unsigned就夠了
       60000人以內.SMALLINT(5) unsigned
       1000000人以內.INT(7) unsigned.
       INT(10) unsigned.....yahoo才有可能有這個量吧.:Q
       當然也不要設的太緊.以免overflow..~___~
       你動了這個值.其他table有相同的也要改.不然會變慢.
     - num_reads也是.INT(10)也太......INT(6) or INT(7)也綽綽有餘.
     - category_id, blog_id, user_id 加 INDEX
     - date 可加可不加.我試過只對前6位做INDEX.對於cal的速度有變快很多.
       但或許是因為我用mysql 5的關係..有時候出來的result怪怪的.:Q

    plog_articles_categories
    - blog_id 加 INDEX

    plog_articles_comments
    - topic VARCHAR(150) [150看你對topic覺得多長是極限]
    - article_id 加 INDEX

    plog_articles_notifications
    - 這整個db設計上就有問題.一篇文章只會有一個使用者去設定是否要
      notification.且一篇文章只會出現一次..基本上article_id他就可
      以是primary key.然後整個table只要剩article_id & user_id即可.
      甚至user_id也可以不要..
    - article_id 加 INDEX

    bayesian 系列我不管.因為基本上這只對判斷英文準確率才高才正確.
    對於中文來說根本是亂判..所以我是把它disable.:Q

    plog_blogs
    -  owner_id 加 INDEX

    plog_filtered_content
    -  blog_id 加 INDEX

    gallery系列因為我不使用.所以沒研究.

    plog_host_blocking_rules
    - blog_id 加 INDEX

    plog_mylinks
    - blog_id, category_id 加 INDEX

    plog_mylinks_categories
    - blog_id 加 INDEX

    plog_referers
    - blog_id, article_id 加 INDEX

    plog_trackbacks
    - article_id 加 INDEX

    plog_users_permissions
    - permission_id TINYINT(1)
    - (user_id, blog_id) 聯合加 INDEX


    mysql server的tuning.我只列出一些針對my.cnf的更動.


    skip-name-resolve   # 不去做反解.但是打開這個請注意你mysql這個db的
                        # 權限設定

    low-priority-updates # Table-modifying operations will have lower
                         # priority than selects.

    set-variable    = connect_timeout=30    # timeout設小一點
    set-variable    = join_buffer_size=16M  # 聽Mark說用到很多join..
    set-variable    = max_connections=1024
    set-variable    = wait_timeout=30
    query_cache_size = 128M


    其實還有很多可以設拉.以上mysql的數值是要看你的硬體去調整的..
    所以也不要照抄.:Q

==
懶的寫了...zzz..

[far]

加注幾點..

加INDEX只是治標不治本.真正要改善這個問題要去改寫query.

加INDEX也不是通通都加就好.在Query裡面有Where or ORDER BY到的才會有效果.(但是也不是一定.要有某些性質才會有用)

加INDEX會造成(INSERT.UPDATE.DELETE)變慢,但是SELECT變快,所以怎麼斟酌就看個人需求了.

建議要加INDEX的話.去看看他query怎麼寫.大多都是WHERE OR ORDER BY什麼.效果才會明顯.

還有阿..Mysql的Cache一定要開.差很多的...

[brian]

程式碼:

define( "ADODB_NEVER_PERSIST", true );

回報一下,這個方法似乎沒有用,我的mysql的errorlog裡面仍然有很多被強迫關閉的connection.