maria-discuss team mailing list archive

[Arjen Lentz <arjen@xxxxxxxxxxxxx>]

Hi Haidong

> Haidong> http://datastrangler.com/windpipe/2011/02/24/custom-mysql-config-files-to-ensure-maximum-performance/


some comments:
> innodb_data_file_path	 = ibdata1:128M;ibdata2:10M:autoextend
> innodb_file_per_table	 #enable always
What's the benefit of having two global table space files?
The file-per-table is good for default, then you need a moderately  
sized autoextending ibdata1, that's all; it just needs to contain the  
table definitions, foreign key info, etc... 10M:autoextend is probably  
fine.


> innodb_flush_log_at_trx_commit	= 2    #2/0 = perf, 1 = ACID
> sync_binlog	 = 0
Please don't create non-ACID defaults. I really don't want to re- 
battle topics that were thoroughly dealt with over 10 years ago (as in  
making sure MySQL can be ACID "out of the box". The default has to be  
1 for InnoDB.
For sync_binlog, we find that =10 has a good performance/reliability  
trade-off so that might be an acceptable default there.

> innodb_support_xa	 = 0	 #recommend 0, disable xa to negate extra  
disk flush
So then InnoDB and the binary log no longer do a two-phase commit  
process?
That's not a winner, Haidong. For reasons similar to above.


> innodb_log_file_size	 = 256M	#64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM 
+ = 256, 2G_RAM+ = 128
It's been found that a log file size in the order of 50M or so works  
fine for most cases. Larger is not beneficial and significantly delays  
recovery time.

> innodb_log_files_in_group	= 4	#combined size of all logs <4GB.  
<2G_RAM = 2, >2G_RAM = 4
why?

> skip-innodb-doublewrite
you can't just disable that by default, it depends on the situation  
and filesystem/IO environment.


I severely dislike having multiple sample config files. History has  
proven that as multiple files are a pest to maintain, they will fall  
behind over time instead of getting updated. Since most settings can  
be given a sane default, with additional settings highly specific to  
each environment, I find that having a single baseline config can be  
quite sufficient.
We've been using that approach for a few years now with good results.


Regards,
Arjen.
--
Arjen Lentz, Exec.Director @ Open Query (http://openquery.com)
Remote expertise & maintenance for MySQL/MariaDB server environments.

Follow us at http://openquery.com/blog/ & http://twitter.com/openquery