29 Mar
2011
29 Mar
'11
7:47 a.m.
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