OurSQL Episode 36: It's Not Our (De)fault! Part 1

Nick Pisarro, aka @highenergybeams, suggested we do a podcast about the MySQL defaults we recommend always having, as MySQL's pre-configured option files are not particularly helpful. So we have put together a 2-part podcast on the options that we change.

About the Configuration File
Location and precedence of configuration files for Windows, Unix and Mac OS X:
http://dev.mysql.com/doc/refman/5.5/en/option-files.html

Note: We use the term "directive" to refer to the groups of options that begin with [groupname]. Upon reading the manual page again, we realized that the term is actually "option group". (Sheeri takes complete blame for this -- "directive" is actually the term for what !includedir and !include are...)

Sheeri's blog post about MySQL being too helpful - group vs. group_concat_max_len saga

Links to the manual for the commands we usually change:
Directories
datadir
tmpdir

Overall Server Settings
default_storage_engine

We mentioned default-character-set but it has been deprecated in MySQL 5.1 and removed in MySQL 5.5, in favor of using character-set-server. As we mentioned in the beginning, check your error logs when you restart mysqld and fix any warnings -- deprecated features will definitely show up there.

skip-name-resolve - it may be helpful to read Sheeri's Blog post on "What is an unauthenticated user?" and the MySQL manual description of How MySQL Uses DNS.

MyISAM Variables
concurrent_insert - even if you are not planning on using MyISAM, just in case. There is a more descriptive manual page about concurrent inserts if you want to learn more.

key_buffer_size - you can also read more about MyISAM's multiple key cache feature.

InnoDB Variables
innodb_file_per_table

innodb_buffer_pool_size - Also the more detailed manual page on Using Per-Table Tablespaces.

innodb_flush_method

Ear Candy
log_slow_slave_statements - log slow queries that come through replication

log_slow_admin_statements

Part 2 of the series

Where you can see us
On Monday March 7th at 7pm PT, the Seattle MySQL meetup will happen with Brian Aker talking about Drizzle 7 and the upcoming GA version! http://www.meetup.com/seattlemysql/events/16489408/

Sheeri will be at Collaborate in Orlando, Florida from Sunday April 10th through Thursday April 14th. She is organizing the Community dinner, eastern US edition on Sunday April 10th at 5 pm at Maggiano's Little Italy near the Convention Center.

Sarah will be at the O'Reilly MySQL Conference in Santa Clara, California from Monday, April 11th through Thursday April 14th, including the community dinner, western US edition on Monday April 11th at 7 pm at Pedro's.

Sheeri will be speaking about monitoring MySQL efficiently with Nagios at the Professional IT Community Conference, otherwise known as PICC, in New Brunswick, New Jersey. The conference is Friday, April 29 – Saturday April 30, 2011.

Sheeri will be at OpenDBCamp, Fri May 6th - Sun May 8th in Sardinia, Italy.

Feedback
Facebook group
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast

Trackback URL for this post:

http://technocation.org/trackback/1106