MySQL tuning in memory excess case

I have MySQL server with 500mb database running on a server with 8Gb of RAM. Are there any sensible tunings which will take all available memory (I mean about 80% o开发者_Go百科f 8Gb)?

It depends what you are tuning for. If you want to tune for InnoDB and or MyISAM, even in a low memory envitronment, you will need to scale formulas from this link --> https://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-myisam/2194#2194

Basically, you want 75% of RAM dedicated for InnoDB. If all MyISAM, measured recommendation from the formulas given in the link.

UPDATE 2011-07-21 13:52 EDT

Cut and Paste this Formula

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

It will output the InnoDB Buffer Pool Size for your given dataset.

Cut and Paste This One as Well

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo)))+0.4999), SUBSTR(' KMG',IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo))+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 PowerOfTwo) B;

It will output the MyISAM Key Buffer Size for your given dataset.

Please run both of these formulas. Keep in mind that MyISAM only caches index pages. InnoDB needs both data nad index pages cached. Probably the combined sizes of them both will be less than 500 MB.

UPDATE 2011-07-21 14:54 EDT

You may also want to tune per-DB Connection Settings

I recommend tuning your max_connections

You may also want to download mysqltuner.pl and run it on the DB server.

wget mysqltuner.pl

This perl script will tell if you have overconfigured for DB Connections or not as well the maximum RAM mysql expects to have. If the the percetage of installed RAM needed exceeds 80%, lower the per_connect DB settings (sort_buffer_size, read_buffer_size, join_buffer_size and so forth)