MySQL Bugs: #67598: Feedback on MySQL Server 5.6 default my.cnf and my.ini

Bug #67598	Feedback on MySQL Server 5.6 default my.cnf and my.ini
Submitted:	15 Nov 2012 14:19	Modified:	19 Mar 2014 0:39
Reporter:	James Day	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	James Day	CPU Architecture:	Any

Description:
This bug is here to provide feedback on the key settings that we should document for those doing initial server configuration.

What settings do you think it's most important for us to mention?

How to repeat:
Not applicable

Suggested fix:
Consider the feedback and act as appropriate.

I'd suggest a section about not doing hostname based auth, and disabling hostname lookups on connections. Allowing it to DNS stuff willy nilly has been the number one source of high load production slowdowns on web based services.

Sorry, number one cause on *our* web based services.

This is what I see in my-default.ini in 5.6.9 .zip with binaries for Windows:

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

Do you want us to discuss the above or something else? Here everything but sql_mode is commented...

If we should discuss content of comments also, I have strong objections against 128M for join_buffer_size, for example :) 2M for read_rnd_buffer_size is also questionable with InnoDB being default storage engine, etc.

James, can you be more specific in this "bug report"? What exact content we should check and what settings you want community to comment on?

would be nice to put empty sections for [mysql] and [mysqldump] so that people are aware they exist.

On windows I'd like to see "symbolic-links=0" at least mentioned in the my.ini file.

The performance impact of checking for .sym files on each table open is pretty severe under some workloads.  This fake symbolic links code is going away in a future version of mysql since NTFS supports real symlinks anyway.

please note that the installation instructions in «2.2. Installing MySQL from Generic Binaries on Unix/Linux» refer to the non existing file «my-medium.cnf» in the command sequence «(...) shell> cp support-files/my-medium.cnf /etc/my.cnf (...)». This file is missing at least since version 5.6.8.

Also, the chapter «5.1.7. Server SQL Modes» states that the default server sql mode is «The default SQL mode in MySQL 5.6.6 and later is NO_ENGINE_SUBSTITUTION; in MySQL 5.6.5 and earlier, it was empty (no modes set).», however the default my.cnf file "generated" by the script «scripts/mysql_install_db» contains «sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES». I couldn't find anywhere in the documentation saying that STRICT_TRANS_TABLES is also the default mode.

I think it's time to close this bug report (and maybe create new one(s) based on some comments here). No need for more feedback on the subject at the moment anyway.

I agree. So, I've closed this bug report now.

James Day, MySQL Senior Principal Support Engineer, Oracle