MySQL Forums
Forum List  »  Performance

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

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL Performance Tuning on my server
4950
November 14, 2008 10:01AM
2287
November 14, 2008 09:10PM
2298
November 16, 2008 01:04PM
2427
November 16, 2008 11:00PM
1900
November 17, 2008 04:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.