MySQL Performance: MySQL => Optimized Configuration

Optimized Configuration [message #1424]

Fri, 15 June 2007 09:08

VictorZ
Messages: 9
Registered: June 2007

Junior Member

Hi guys,

I'm setting up a PHP/MySQL web application on a new VPS. The VPS is running PHP 5 and MySQL 5, and has 512MB RAM (burstable to 1GB). The server and the application are freshly installed, so I don't have much benchmarks on the database performance, but I would like it to be robust and scalable under a high-usage multi-user scenario.

The database schema consists of 4 tables only, all InnoDB except for one which is a MyISAM table. The MyISAM table has a full-text index on one of the VarChar fields. There are indexes on all relevant primary key and foreign key fields (all MediumInt).

I would appreciate if someone can review my database configuration (my.cnf listed below) and please suggest suitable improvements:

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
max_connections = 400
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
max_allowed_packet = 16M
max_connect_errors = 10
table_cache = 1024
thread_cache_size = 286
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K
ft_min_word_len = 3
skip-bdb
skip-innodb
skip-locking

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 32M
sort_buffer = 32M
read_buffer = 16M
write_buffer = 16M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

Thanks in advance!

Report message to a moderator