my.cnf on debian squeeze. var names don't match, var values missing.

sneakyimp · 07-01-2011, 03:47 PM

I'm trying to configure a server to act as a mysql server and that's about it. i've got apache and stuff installed for phpmyadmin but I won't be running any web apps on this machine.

This machine is pretty muscular 64-bit debian squeeze machine and has 4GB of RAM and 4 cores. Obviously, I'd like MySQL to take advantage of this power.

This brings me to the mysql configuration file, /etc/mysql/my.cnf. This file lacks the vast majority of configuration variables listed on the mysql site and uses values like 128M and 128K which I assume refer to Megabytes and Kilobytes, respectively. I'm sort of wondering a) where the defaults come from and b) whether it's OK to just add entries to my.cnf which correspond to those values in the docs and c) what appropriate values might be for my new, muscular server.

Any advice would be most welcome.

I have attached both my.cnf and the output from mysqld --verbose --help.

catkin · 07-03-2011, 01:59 AM

I am answering because this thread has gone long without a reply and I, too, am curious about my.cnf values rather than because I have any expertise ...

There's amazingly little information on the 'net about adjusting my.cnf which, combined with the line "# The following values assume you have at least 32M ram" suggest that a) the sample my.cnf is historical and b) the values do not significantly effect performance (or there would be more discussion of adjustments online. So why have them?). One option would be to run MySQLTuner-perl and/or tuning-primer.sh and try what they suggest.

Other distros have a selection of sample files such as my-huge.cnf, my-large.cnf, my-medium.cnf and my-small.cnf with the intention that you copy the appropriate one to my.cnf. Hopefully the good people at Debian have packaged MySQL the way they have because it works OK. Curious then that the one-size-fits-all approach is used when it isn't/wasn't by other distros.

Here are the effective lines from my-huge.cnf from Slackware64 13.1 which describes itself as "for a large system with memory of 1G-2G where the system runs mainly MySQL":

Code:

[client]
port		= 3306
socket		= /var/run/mysql/mysql.sock
[mysqld]
port		= 3306
socket		= /var/run/mysql/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8
log-bin=mysql-bin
server-id	= 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout