MySQL config for 2GB ram

Posted by Tiffany Walker on Server Fault See other posts from Server Fault or by Tiffany Walker
Published on 2012-03-28T15:53:38Z Indexed on 2012/03/28 17:33 UTC
Read the original article Hit count: 246

Filed under:

How is my config? Does it work well for 2GB? What would be an ideal config for a 2GB ram server?

[mysqld]
set-variable = max_connections=500
log-slow-queries
safe-show-database
local-infile=0
skip-networking
symbolic-links=0
max_connections = 500
key_buffer = 256M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
thread_concurrency = 16
table_cache = 1024
thread_cache_size = 50
wait_timeout = 7200
connect_timeout = 10
tmp_table_size = 32M
max_allowed_packet = 160M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

UPDATE 2012-03-28 12:58 EDT By RolandoMySQLDBA

Please run these queries and paste them into your question:

For MyISAM

SELECT CONCAT(ROUND(KBS/POWER(1024, 
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), 
SUBSTR(' KMG',IF(PowerOf1024<0,0, 
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) 
recommended_key_buffer_size FROM 
(SELECT LEAST(POWER(2,32),KBS1) KBS 
FROM (SELECT SUM(index_length) KBS1 
FROM information_schema.tables 
WHERE engine='MyISAM' AND 
table_schema NOT IN ('information_schema','mysql')) AA ) A, 
(SELECT 2 PowerOf1024) B; 

For InnoDB

SELECT CONCAT(ROUND(KBS/POWER(1024, 
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999), 
SUBSTR(' KMG',IF(PowerOf1024<0,0, 
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size 
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables 
WHERE engine='InnoDB') A, 
(SELECT 2 PowerOf1024) B; 

© Server Fault or respective owner

Related posts about mysql