MySQL Performance Tuning on my server
Posted by:
Paola Moro
Date: November 14, 2008 10:01AM
Hi to all,
I need to optimize mysql server to manage a big tables (now about 78Mb, with increment of 1Mb/day)
---
I have used a table MyISAM, info from phpmyadmin:
Collation utf8_general_ci
lenght row 122
row dimension avg 194 byte
row 411,069
actual main phpmyadmin usage stat:
QueryType # dim/hour %
change db 2,103k 3,262,61 61,29%
select 933k 1,447,66 27,20%
insert 358k 555,08 10,43%
update 30k 47,19 0,89%
set option 1,189 1,84 0,03%
I have an xml gallery with SELECT query, ORDER BY, LIMIT and with paging
---
phpmyadmin parameters stat:
Handler_read_rnd 12 M
Handler_read_rnd_next 4,483 M
Created_tmp_disk_tables 5,270
Created_tmp_tables 5,274
Created_tmp_files 37 k
Key_reads 4,226
Key_write_requests 380 k
Key_writes 367 k
Sort_merge_passes 18 k
Sort_rows 12 M
---
Actual my.cnf:
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
big-tables
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
---
Questions:
- do i switch table from MyISAM to InnoDB (if yes, how to configure InnoDB for best performance?)
- what parameters i need to insert manually in my.cnf for best performance & low disk usage?
- may i remove old_passwords=1 & big-tables?
---
Web Server with Apache 2.2.
Mysql ver. 5.0.45 via socket on x86_64 centos 5.2, 1 CPU 4core with 4 Gb RAM, 3Tb SATA disk space
Load avg: 0.81 (1 min) 0.68 (5 mins) 0.73 (15 mins)
Real memory 3.86 GB total, 1.38 GB used
Virtual memory 4 GB total, 288 kB used
---
NOTE:
I have webmin installed, but when I change mysql vars from web interface, it seems that my changes are not applied and my.cnf are not modified. Why? Where are written this configs from webmin?
Please give me the best suggest!
thanks in advance,
warm regards,
Paola