Dissection of a my.cnf file
To that end, I invite everyone to have a look and post comments on some of the things I have noted and specifically pointed out.
Note:
'set-variable =' has been deprecated in mySQL 4.x and only required for earlier versions.
Entries marked with * are the most common I've seen.
Comments are marked with << or at end of variable listings
The following is based on Server specs of:
Linux RH 9, (1) CPU of whatever GHz, no hyperthreading, 1 GB RAM, PHP 4.x, mySQL 4.x
--------------------------------------------------
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
- seems to be an optional setting
- some also have it as the first two lines of [mysqld] have seen the two entries listed in both; [client] and again in the [mysqld] section
- could it be to 'force' any mysql processes to use the 'port' & 'socket' listed as php.ini does not mention specifically which ones to use, but instead, says go with defaults listed elsewhere?
--------------------------------------------------
[mysqld]
skip-locking << some do not have this entry
skip-innodb
datadir = /var/lib/mysql << seen only one file with this
max_user_connections = 45 (for very high mySQL usage =100)
max_connections = 500 (for very high mySQL usage =1000)
max_connect_errors = 10 (for very high mySQL usage =1500)
max_allowed_packet = 1M, *16, 32M << is this related to [mysqldump]?
interactive_timeout = 10, 100, 60, 100, *100
wait_timeout = 10, 100, 60, 200, *100
connect_timeout = 10, 100, 10, 30, *10
join_buffer = 1M, *4M << some do not have this entry
key_buffer = 16M, 192M, 256M, *384M
read_buffer = *1M, 2M << is this setting for mySQL 3.x and 4.x uses record_buffer instead?
record_buffer = 1M, *2M, 4M
sort_buffer = 1M, 2M, *4M, 5M
myisam_sort_buffer_size = 32M, *64M
table_cache = 256, 512, *1024
thread_cache = 8 << some have this and some don't?
thread_cache_size = *8, 128, 256, 1500
--------------------------------------------------
# Try number of CPU's * 2 for thread_concurrency or * 4 for hyperthreading
thread_concurrency = 2
some do not have this entry
--------------------------------------------------
query_cache_limit = 2M, 1M, *2M, 1M, 2M,
query_cache_size = 32M, 32M, *64M, 16M, 64M,
query_cache_type = 1M, 1M, *1M, 1M, 2M,
some do not have these 3 entries
--------------------------------------------------
flush-time=1800
log-slow-queries = /var/log/mysql_slow_query_log
long_query_time = 5
these entries seem to customized - are they of any value?
--------------------------------------------------
log-bin
server-id = 1
only some have these two lines, always together and always at end of [mysqld] settings
--------------------------------------------------
[safe_mysqld_safe]
err-log = /var/log/mysqld.log << any problems using this with RH 9?
open_files_limit = 8192
(note: can we get a definitive on whether: pid-file=/var/lib/mysql/whatever.pid is a required entry?)
--------------------------------------------------
[mysql.server]
user = mysql
basedir = /var/lib
is this entry neccessary?
--------------------------------------------------
[mysqldump]
quick
max_allowed_packet = 16M
--------------------------------------------------
[mysql]
no-auto-rehash
# Remove the comment character if your are not familiar with SQL.
#safe-updates
(What is this "safe-updates" statement all about?)
--------------------------------------------------
[isamchk]
key=16M << only one had this entry and it was not duplicated in [myisamchk]
key_buffer = 16M, 64M, 128M, 128M, *256M (for very high mySQL usage = 512)
sort_buffer = 16M, 64M, 128M, 128M, *256M (for very high mySQL usage = 512)
read_buffer = 4M, 16M, 2M, 16M, *2M (for very high mySQL usage = 2M)
write_buffer = 4M, 16M, 2M, 16M, *2M (for very high mySQL usage = 2M)
[myisamchk]
-- exact same settings as for [isamchk]
--------------------------------------------------
[mysqlhotcopy]
interactive-timeout
some did not have this entry
--------------------------------------------------
When commenting on any of the above, if we use the heading [...] to make specific mention of something, it will help to cut down on confusion. Most of the above entries are required and the settings (in MB or seconds) is where we can really hone down a good basic "my.conf" template.
