← Back to team overview

maria-discuss team mailing list archive

Re: Bundle more relevant cnf files in MariaDB package


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

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

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

Follow ups