MySQL optimization

[Laurent Blume via buildfarm]

Hi all,

I had a quick look at the MySQL configuration for the buildfarm, and I 
can give you the magic parameters we got last year from a consultant to 
improve MySQL performance:

innodb_file_per_table = 1
innodb_doublewrite    = 0
innodb_buffer_pool_size        = 256M
innodb_log_buffer_size         = 64M
innodb_flush_log_at_trx_commit = 2

innodb_file_per_table = 1
-> This splits the tables by file for InnoDB, generally better, only 
applies to newly created tables.

innodb_doublewrite    = 0
innodb_flush_log_at_trx_commit = 2
-> those two require reliable underlying hardware (BBW, and so on). ZFS 
helps. On the other hand, they make things tremendously faster.

innodb_buffer_pool_size        = 256M
innodb_log_buffer_size         = 64M
-> The space allocated in memory on start and on disk. Those are lowish 
values for my small system, it can be set to 1GB memory and 256MB logs.
NB: The log buffer size must be set with mysqld stopped first, and *the 
old log files deleted before restart*, else mysqld will complain.

For us, it helped a lot to reduce the number of slow queries.

HTH,

Laurent