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. _______________________________________________ gnome-infrastructure mailing list gnome-infrastructure@gnome.org http://mail.gnome.org/mailman/listinfo/gnome-infrastructure
