List:General Discussion« Previous MessageNext Message »
From:TianJing Date:December 15 2009 2:41am
Subject:mysql server optimization
View as plain text  
Dear all,

i am nowing having a problem with the mysql server optimization, i have 20
database on a server,each database is about 80Gb,the sql seems very
slow,almost > 5s.and the server i/o is so high,when i check the
processlist,the 'copying to tmp table' state  takes a long time.

i have already use index,but the sql use lots of 'and','or','order by', and
for some reason i can not optimization the sql,i hope to do some
optimization on mysql server to mitigate this phenomenon´╝îcould any one give
me some suggestion?

thanks.

my server is linux,8CPU and 4G memery,the my.cnf is:

[mysqld]
port            = 3306
skip-locking
skip-name-resolve
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512M
net_buffer_length = 8K
read_buffer_size = 512K
read_rnd_buffer_size = 512M
myisam_sort_buffer_size = 8M
table_cache = 1024
log-bin=mysql-bin
binlog_format=mixed


-- 
Tianjing

Thread
mysql server optimizationTianJing15 Dec
RE: mysql server optimizationJohn Daisley 15 Dec
  • Re: mysql server optimizationTianJing15 Dec
RE: mysql server optimizationJohn \(Burton\) Daisley15 Dec
  • Re: mysql server optimizationTianJing15 Dec
RE: mysql server optimizationJohn \(Burton\) Daisley15 Dec
  • Re: mysql server optimizationTianJing16 Dec