mysql最佳化配置

mysql最佳化配置

tmp_table_size 設定大小500mb

key_buffer_size  =384m//key_buffer_size指定用於索引的緩衝區大小，增加它可得到更

好的索引處理效能。     對於記憶體在4GB左右的伺服器該參數可設定為256M或384M。注意

：該參數值設定的過大反而會是伺服器整體效率降低！

sort_buffer_size = 17mb //排序緩衝

read_buffer_size=4m //讀取緩衝

table_cache=256 //

ft_min_word_len //全文檢索搜尋

query_cache_size 查詢快取

<?
#!/bin/sh
#written by cuiyang 20081118
#######檢查mysql狀態
PORT=`netstat -na | grep "LISTEN" | grep "3306" | awk '{print $4}' | awk -F. '{print $2}'`
if [ "$PORT" -eq "3306" ]
        then
#######檢查mysql占CPU負載
        mysql_cpu=`top -U root -b -n 1 | grep mysql | awk '{print $10}'|awk -F. '{print $1}'`
##如果mysql cpu負載大於80，則重啟mysql
        if [ "$mysql_cpu" -ge "80" ]
                then
                ps xww |grep 'bin/mysqld_safe' |grep -v grep | awk '{print $1}' | xargs kill -9
                ps xww |grep 'libexec/mysqld' |grep -v grep | awk '{print $1}' | xargs kill -9
                sleep 5
                /usr/local/mysql/bin/mysqld_safe --user=root > /dev/null &
        else
                exit 0
        fi
else
         /usr/local/mysql/bin/mysqld_safe --user=root > /dev/null &
fi
?>

影響列數： 4999 (查詢花費 0.1756 秒)

UPDATE `zgy_jobs_faces` SET postime = '1250784000' WHERE jid <505000 AND jid >500000

jobs_faces欄位
 欄位 類型 整理 屬性 Null 預設 額外 操作
  jid int(10)   UNSIGNED 否  auto_increment              
  oid int(10)   UNSIGNED 否 0               
  cid mediumint(8)   UNSIGNED 否 0               
  requests smallint(4)   UNSIGNED 否 0               
  views mediumint(6)   UNSIGNED 是 0               
  checked tinyint(1)   UNSIGNED 否 0               
  istoped tinyint(1)   UNSIGNED 否 0               
  postime int(10)   UNSIGNED 否 0               
  losetime int(10)   UNSIGNED 否 0               
  toped tinyint(1)   UNSIGNED 否 0               
  toptime int(10)   UNSIGNED 否 0               
  bold tinyint(1)   UNSIGNED 否 0               
  highlight varchar(7) gbk_chinese_ci  否                
  lightime int(10)   UNSIGNED 否 0               
  people smallint(4)   UNSIGNED 否 0               
  sex tinyint(1)   UNSIGNED 否 0               
  djobskinds varchar(30) gbk_chinese_ci  否                
  jname varchar(60) gbk_chinese_ci  否 

影響列數： 4999 (查詢花費 0.2393 秒)

UPDATE `zgy_jobs_fields` SET postime = '1250784000' WHERE jid <455000 AND jid >450000