Mysql Optimization Configuration

Mysql Optimization Configuration

Tmp_table_size: 500 mb

Key_buffer_size = 384 m // key_buffer_size specifies the buffer size used for the index. increase it to get more

Good Index processing performance. This parameter can be set to 384 M or M for servers with around 4 GB of memory. Note:

: If this parameter value is set too large, the overall efficiency of the server will be reduced!

Sort_buffer_size = 17 mb // sort Cache

Read_buffer_size = 4 m // read Cache

Table_cache = 256 //

Ft_min_word_len // full-text search

Query_cache_size query Cache

<?
#! /Bin/sh
# Written by cuiyang 20081118
####### Check the mysql status
PORT = 'netstat-na | grep "LISTEN" | grep "3306" | awk '{print $4}' | awk-F. '{print $2 }''
If ["$ PORT"-eq "3306"]
Then
####### Check the CPU usage of mysql
Mysql_cpu = 'top-U root-B-n 1 | grep mysql | awk '{print $10}' | awk-F. '{print $1 }''
# If the mysql cpu load is greater than 80, restart 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
?>

Affected Number of columns: 4999 (query takes 0.1756 seconds)

UPDATE 'zgy _ jobs_faces' SET postime = '000000' WHERE jid <1250784000 AND jid> 505000

Jobs_faces Field
Field Type sorting property Null default additional operations
Jid int (10) UNSIGNED no auto_increment
Oid int (10) UNSIGNED no 0
Cid mediumint (8) UNSIGNED no 0
Requests smallint (4) UNSIGNED no 0
Views mediumint (6) UNSIGNED is 0
Checked tinyint (1) UNSIGNED no 0
Istoped tinyint (1) UNSIGNED no 0
Postime int (10) UNSIGNED no 0
Losetime int (10) UNSIGNED no 0
Toped tinyint (1) UNSIGNED no 0
Toptime int (10) UNSIGNED no 0
Bold tinyint (1) UNSIGNED no 0
Highlight varchar (7) gbk_chinese_ci No
Lightime int (10) UNSIGNED no 0
People smallint (4) UNSIGNED no 0
Sex tinyint (1) UNSIGNED no 0
Djobskinds varchar (30) gbk_chinese_ci No
Jname varchar (60) gbk_chinese_ci No

Affected Number of columns: 4999 (query takes 0.2393 seconds)

UPDATE 'zgy _ jobs_fields 'SET postime = '000000' WHERE jid <1250784000 AND jid> 455000