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 = 3306socket = /var/lib/mysql/mysql.sockmax_connections = 400key_buffer = 16Mmyisam_sort_buffer_size = 32Mjoin_buffer_size = 1Mmax_allowed_packet = 16Mmax_connect_errors = 10table_cache = 1024thread_cache_size = 286query_cache_limit = 1Mquery_cache_size = 16Mquery_cache_type = 1tmp_table_size = 16Minteractive_timeout = 25wait_timeout = 1000connect_timeout = 10sort_buffer_size = 2Mread_buffer_size = 1Mread_rnd_buffer_size = 256Knet_buffer_length = 2Kthread_stack = 64Kft_min_word_len = 3skip-bdbskip-innodbskip-locking[mysqld_safe]open_files_limit = 8192[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash#safe-updates[isamchk]key_buffer = 8Msort_buffer_size = 8M[myisamchk]key_buffer = 32Msort_buffer = 32Mread_buffer = 16Mwrite_buffer = 16Msort_buffer_size = 8M[mysqlhotcopy]interactive-timeout
Thanks in advance!