Re: Sorting out mysqld options

[Max Kanat-Alexander <mkanat everythingsolved com>]

Owen Taylor wrote:
> 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.

	It's best to keep it on, for Bugzilla. It won't have any significant 
performance impact, and it will allow InnoDB to more effectively reclaim 
space in its data files if needed.

	If you had a heavy-write application with 1000 tables, I'd suggest 
keeping it off, because the tiny tiny overhead of the extra fsync calls 
would possibly add up if you were looking at 2500 transactions per 
second or something like that.


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

	You might want to set slow_query_time (or whatever the var is called) 
down to 6--that will be more likely to show up truly slow queries.

> 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

	Bugzilla of course won't use all that, but I'm assuming you're setting 
it that high because there will be other applications using InnoDB on 
this server.

> 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

	I'm doing a test upgrade right now, and once it's done I'll let you 
know how large the indexes are on the bugs_fulltext table.


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

	If you search short_desc for a substring, you're likely to do one, 
because MySQL can't use indexes in that case.

	Note this, however:
	http://www.mysqlperformanceblog.com/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/

	-Max
--
Max Kanat-Alexander
Chief Engineer
http://www.everythingsolved.com/
Everything Solved: Complete Computer Management