Sorting out mysqld options

[Owen Taylor <otaylor redhat com>]

I spent some time going through the:

 - The mysqld configuration on button [referred to as 'button' below]
 - Max's example bugzilla mysqld example [referred to as 'mkanat' below]

And trying to figure out what variables actual need to be set, what
variables should usefully be scaled bigger on a server with more memory,
and so forth, in preparation for creating a configuration for
drawable.gnome.org.

Results are below with documentation links and comments. 

The vast majority of the cases where I didn't have a good idea of what
we should use are also cases where the value doesn't matter a whole lot,
and we can pretty much just pick something random.

The value I'm least sure about is innodb_file_per_table; it's also
something that we won't easily be able to change later without a
dump/reload.

- Owen

Variables with behavioral effects
==================================

character_set_server
http://dev.mysql.com/doc/refman/5.0/en/charset-configuration.html

  button: utf8 # actually sets deprecated default_character_set
  default: latin1

  plan: utf8

  Doesn't matter for Bugzilla; most robust apps should force this
  themselves as appropriate, but if we have homebrew apps that dont'
  force it, we definitely want UTF-8.

ft_min_word_len
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_ft_min_word_len

  button: 3
  mkanat: 2
  default: 4

  plan: 2

  Setting to 2 allows short keywords (pc) in full-text searches

innodb_file_per_table
http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html

  mkanat: set
  default: unset

  plan: ???

  I have no personal experience using this. The main advantage would be
  if (for disk space reasons, or to split IO load) we wanted to move
  some tables to a network storage. The main disadvantage is ???

log_slow_queries
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_log_slow_queries

  mkanat: set
  button: /var/log/mysqld-slow.log

  plan: /var/log/mysqld-slow.log

  Generally useful. The default slow_query_time of 10 is often either
  longer or shorter than what you want depending on what you want to 
  track down. For bugzilla, with a value of 10 we'll probably mostly be
  logging complicated searches, and logging most of them, unless things
  are going wrong.

Key tuning variables
====================

innodb_buffer_pool_size
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

  mkanat: 8192M
  default: 8M

  plan: 16384M

  We might want this even bigger if we were using only InnoDB tables,
  but we'll be using a mix, so some RAM should go to key_buffer_size
  for MyISAM index buffering.

innodb_log_file_size
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_log_file_size

  mkanat: 300M
  default: 5M

  plan: 300M

  Some recommendations are this should be as large as 25% of the buffer
  pool size. We'd expect our operations to generally be read-heavy
  rather than write-heavy, so I don't see using anything really large
  here.

  Needs special procedure to change, with the default value of 
  innodb_fast_shutdown, since log files are not cleaned up on
  shutdown. See:
  http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html
  
key_buffer_size
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_key_buffer_size

  button: 384M   # actually, key_buffer was set
  mkanat: 2048M
  
  plan: 2048M

  This is specific to MyISAM tables. bugs_fulltext table apparently is 
  typically for bugzilla the MyISAM table with the biggest index. Table
  doesn't exist in older bugzilla, so I don't know how big it will be
  for bugzilla.gnome.org.

max_allowed_packet
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_allowed_packet

  button: 10M
  mkanat: 32M
  default: 1M

  plan: 32M

  This correlates to the maximum possible attachment size in bugzilla,
  apparently. (I guess attachments are stored as blobs in current
  bugzilla?)

max_connections
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_connections

  button: 100
  mkanat: 1200
  default: 100

  plan: 1024

  Setting this too large for a bugzilla-only database server doesn't
  make much sense, since there can't be more connections than there are
  http clients. But if we are also spawning connections for
  art.gnome.org, blogs.gnome.org, etc, then we need more. Main concern
  is probably per-client memory overhead. Affects the maximum number
  of FDs we need.

open_files_limit
http://dev.mysql.com/doc/refman/5.0/en/server-options.html

  button: 4096
  default: 0

  plan: 4096

  Value passed to setrlimit(). Needs to be more than
  max_connections(1024) + table_cache(2048) + max_tmp_tables(32)

query_cache_size:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_query_cache_size

  button: 64M
  mkanat: 128M
  default: 0

  plan: 128M

  Personally not a huge fan of the query cache as a concept. (If you 
  are caching query results, do it memcached, say, rather than on the
  database server.) But can help a lot if a bugzilla URL gets
  slashdotted, e.g.

Other tuning variables [planned to set]
========================================

innodb_log_buffer_size
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_log_buffer_size

  mkanat: 50M
  default: 1M
  
  plan: 8M

  Docs say "Sensible values range from 1MB to 8MB". I don't think we
  have transactions writing tons of data in most cases.

read_buffer_size
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_read_buffer_size

  button: 2M
  default: 128K

  plan: 2M

  Apparently matters only for sequential scans. Hopefully we aren't
  doing many sequential scans!

read_rnd_buffer_size
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_read_rnd_buffer_size

  button: 8M
  mkanat: 4M
  default: 256k

  plan: 4M

  Mostly helps for reading *small* tables in sorted order. Buffering
  will be ineffective for larger tables, I think.

sort_buffer_size
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_sort_buffer_size

  button: 2M
  mkanat: 6M
  default: 2M

  plan: 6M

  There is some concern about memory use here if an URL with a sort is
  slashdotted, but memory usage should be limited in practice not by
  max_connections but by the maximum number of connections of one type
  (MaxClients on httpd on bugzilla.gnome.org or whatever) So, max usage
  should be less than max_connections * sort_buffer_size.

table_cache
http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

  button: 2048 (config file also sets it to 512 first)
  default: 64

  plan: 2048

  Doubt it matters much for performance; opening a file is really fast.
  Affects maximum number of FDs we use.

thread_cache_size:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_thread_cache_size

  button: 60
  mkanat: 500
  default: 0

  plan: 100

  Really shouldn't be necessary at all with good Linux threading. 
  Thread creation numbers may be high without caching lots of threads,
  but that doesn't mean it is slow.

wait_timeout
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout

  button: 120
  mkanat: 820
  default: 28800

  plan: 820
   
  Comment from button's my.conf is 
   
  # Default is 28800, which is insane for pconnect-using apps

  The logic here is that if you are using 'pconnect' with PHP then
  you'll have a connection sticking around for each httpd, but using a 
  timeout doesn't seem like a good way of dealing with this in the
  face of load. You need to handle it through max_connections instead.
  The only legitimate reason for keeping this small seems to be client
  bugs - if you get a stuck process with a connection open.

Other tuning variables [not planned to set]
===========================================

back_log:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_back_log

  button: 200
  default 50

  plan: <unset>

  Comment in button's by.conf is:

  # Increase the size of the listen queue for incoming TCP/IP connections
  # An attempt to speed up performance of POST operations on blogs.gnome.org

  But a too small TCP backlog would result in unreliability not performance
  problems.

concurrent_insert:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_concurrent_insert

  button: 2
  default: 1

  plan: <unset>

  I don't like turning on non-default behavior here, which is likely not
  that well tested. If there is a table where this really matters, we
  should by using InnoDB for it, not MyISAM.

innodb_file_io_threads
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_file_io_threads

  mkanat: 4
  default: 4
 
  plan: <unset>

innodb_flush_log_at_trx_commit
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
 
  mkanat: 1
  default: 1

  plan: <unset>

innodb_lock_wait_timeout
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout
 
 mkanat: 50
 default: 50

 plan: <unset>

innodb_log_archive
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_log_archive

 mkanat: set
 default: -

 plan: <unset>

 "This variable is present for historical reasons, but is unused."

interactive_timeout:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_interactive_timeout

  mkanat: 600
  default: 28800

  plan: <unset>

  Timing out mysql console sessions is just annoying.

myisam_sort_buffer_size
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_myisam_sort_buffer_size

  default: 8M
  button: 48M

  plan: <unset>

  Only matters for CREATE INDEX / REPAIR TABLE. Doesn't seem worth
  deviating from defaults.

skip_external_locking
http://dev.mysql.com/doc/refman/5.0/en/external-locking.html

  mkanat: set
  default: set

  plan: <unset>

skip_name_resolve
http://dev.mysql.com/doc/refman/5.0/en/server-options.html

  mkanat: set
  default: unset
  
  plan: <unset>

  Don't want IP's in our users table if we can avoid it. We generally
  use back-channel hostnames (window-back, etc), which are in
  /etc/hosts, so DNS outages shouldn't cause problems.

thread_concurrency:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_thread_concurrency

  button: 8
  default: 10

  plan: <unset>

  Documented to be Solaris-specific

thread_stack
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_thread_stack

  mkanat: 128K
  default: 192K

  plan: <unset>

  Don't see point in whacking it down 1/3rd

tmp_table_size
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_tmp_table_size

  mkanat: 32M
  default: dynamic (32M on button)

  plan: <unset>  

  Rather let MySQL use smarts than fixing it at the default value.
  Unless bugzilla uses 'create temporary table', it's probably not
  performance critical.