MySQL Optimisation

[uk_viper]

ok just noticed my server was runnning with almost all ram used up, mostly by mysql, so can anyone provide me a mysql config that would make it use a lot less ram say 128-256, max connections 100

Thanks in advance

 

[Adz]

Post your current my.cnf .

 

[uk_viper]

[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

[Adz]

Default config then .

What are your tables like? What kind of queries are you running? What is the database being used for?

How much RAM is it using?

 

[uk_viper]

According to the system its using around 350-400 meg, this server hosts mostly websites with some very busy forums

 

[Adz]

400MB isn't too unreasonable if it's a shared hosting server.

If you're already running the default config (no increased caching, etc), I wouldn't recommend tweaking it to lower memory usage. Any steps you could take to lower it would most likely be detrimental to performance.

Personally I'd just put another stick of RAM in. Don't you work for Poundhost? Shouldn't cost you much .

 

[uk_viper]

looks like that would be the best option, should have some ram left lying around

 

[Beansprout]

Server spec? (RAM/CPU/disk)

'mysqladmin extended-status' should give you a ton of info. Paste it

Look into query caching if the extended status says it's not enabled. You'll save so much CPU

How busy is 'busy'? I'm serving ~300 forum/site users with barely any load, mmmmm RAM

key_buffer size will be responsible for most RAM usage. 100 - 200Mb should be alright - check the size of all your .MYI files as the aim is to have the key_buffer around this size.

Look for my-large.cnf and my-huge.cnf on your server - they'll provide you with a good base from which to setup your server

Lastly you might want to know what all hte status variables mean. For 4.1, see here