Sorting out mysqld options

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

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

- Owen

Variables with behavioral effects


  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.


  button: 3
  mkanat: 2
  default: 4

  plan: 2

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


  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 ???


  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


  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.


  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

  Needs special procedure to change, with the default value of 
  innodb_fast_shutdown, since log files are not cleaned up on
  shutdown. See:

  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


  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


  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,, etc, then we need more. Main concern
  is probably per-client memory overhead. Affects the maximum number
  of FDs we need.


  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)


  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]


  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.


  button: 2M
  default: 128K

  plan: 2M

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


  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.


  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 or whatever) So, max usage
  should be less than max_connections * sort_buffer_size.


  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.


  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.


  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]


  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

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


  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.


  mkanat: 4
  default: 4
  plan: <unset>

  mkanat: 1
  default: 1

  plan: <unset>

 mkanat: 50
 default: 50

 plan: <unset>


 mkanat: set
 default: -

 plan: <unset>

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


  mkanat: 600
  default: 28800

  plan: <unset>

  Timing out mysql console sessions is just annoying.


  default: 8M
  button: 48M

  plan: <unset>

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


  mkanat: set
  default: set

  plan: <unset>


  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.


  button: 8
  default: 10

  plan: <unset>

  Documented to be Solaris-specific


  mkanat: 128K
  default: 192K

  plan: <unset>

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


  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.

[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]