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

Hi Monty,

Haidong, if you can create a set of files to replace the current ones, we can quickly get them review and add this to the MariaDB distribution.

Yes, I've created a set of files. Please look over. I did use sample files from http://datastrangler.com/windpipe/2011/02/24/custom-mysql-config-files-to-en... as template with slight modification (replication stuff removal, sync_log 1, and XA line removal). I can initiate discussion with the author to ask for permission and express appreciation. I came up my-sample.cnf.sh myself. Once I get your blessing or the blessing from people you designate, I will push for a merge. 5 files are attached: One is a generic my-sample.cnf.sh file. The other 4 are my-small.cnf.sh, my-medium.cnf.sh, my-large.cnf.sh, and my-huge.cnf.sh. Definition of small, medium, large, and huge can be arbitrary, so I made a decision: small: minimum of 4G memory medium: minimum of 16G memory large: minimum of 64G memory huge: minimum of 128G memory Now, one can argue the definition, but at this stage, I am tired of it. If I were benevolent dictator of the universe, I would just use the attached single, minimalist my-sample.cnf.sh. For my-sample.cnf.sh, I've gone through the build, compile, and testing phase, and can tell you it works. Let me know what you think. Haidong Ji

Sorry. The sh was from the source code for the make file. It will turn into the regular sh file after build, compiling, and packaging. Yeah, I was lazy :) On Wed, Mar 30, 2011 at 12:56 PM, Peter Laursen wrote:

Why a .sh file? This is not cross-platform (if I understand).

Peter Webyog On Wed, Mar 30, 2011 at 19:51, Haidong Ji wrote:

...

Hi Monty,

...

Haidong, if you can create a set of files to replace the current ones, we can quickly get them review and add this to the MariaDB distribution.

Yes, I've created a set of files. Please look over. I did use sample files from http://datastrangler.com/windpipe/2011/02/24/custom-mysql-config-files-to-en... as template with slight modification (replication stuff removal, sync_log 1, and XA line removal). I can initiate discussion with the author to ask for permission and express appreciation. I came up my-sample.cnf.sh myself.

Once I get your blessing or the blessing from people you designate, I will push for a merge.

5 files are attached:

One is a generic my-sample.cnf.sh file.

The other 4 are my-small.cnf.sh, my-medium.cnf.sh, my-large.cnf.sh, and my-huge.cnf.sh. Definition of small, medium, large, and huge can be arbitrary, so I made a decision:

small: minimum of 4G memory medium: minimum of 16G memory large: minimum of 64G memory huge: minimum of 128G memory

Now, one can argue the definition, but at this stage, I am tired of it. If I were benevolent dictator of the universe, I would just use the attached single, minimalist my-sample.cnf.sh.

For my-sample.cnf.sh, I've gone through the build, compile, and testing phase, and can tell you it works.

Let me know what you think.

Haidong Ji

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to     : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help   : https://help.launchpad.net/ListHelp

Hi Haidong, all As promised, I've fixed up a 5.2 branch to get rid of the old troublesome config files, and replaced them with the single baseline sample config - also any references in the build environment and in- tree documentation. See https://code.launchpad.net/~arjen-lentz/maria/5.2-zapoldsamplecnfs also proposed for merging. The baseline example I've used comes from the MariaDB DEB (Debian/ Ubuntu) packaging, and we can adjust it further. The new MariaDB packaging/build scripts should also be adjusted to use this file, replacing the old separate copy in the debian/ subdir. 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

Hi Vladislav thanks for your reply, excellent feedback! comments below.

...

As promised, I've fixed up a 5.2 branch to get rid of the old troublesome config files, and replaced them with the single baseline sample config - also any references in the build environment and in- tree documentation.

See https://code.launchpad.net/~arjen-lentz/maria/5.2- zapoldsamplecnfs also proposed for merging. The baseline example I've used comes from the MariaDB DEB (Debian/ Ubuntu) packaging, and we can adjust it further.

If this should be used more than just on Linux or Debian/Ubuntu, we need to adjust it further . The sample is not usable in this form on Windows (and neither on tar.gz distributions), mainly due to hardcoded paths +pid-file = /var/run/mysqld/mysqld.pid +basedir = /usr +tmpdir = /tmp +language = /usr/share/mysql/english +!includedir /etc/mysql/conf.d/ Yet, it is used on Windows as my-example.ini, and in tar.gz as my-example.cnf. My question is : why hardcoding paths, you can influence them anyway with compile-time settings. Also, tmpdir has already a sane default, or has not it?

I think so. To clarify, the origin of this baseline config is Open Query's daily work. We took to explicitly defining all these aspects, to unbreak things regardless of the build source (binary tarball, RPM, DEB) as they all do things slightly differently (see below) and some distro- builds (e.g. those by RedHat themselves, etc) are different again. For example, one particular aspect we ran in to was that the relay log files in a very indirect way derive their path from the configured socket directory setting, this made them end up on /var/run/mysqld on Ubuntu. The consequence was that on reboot, a replication slave would break as /var/run is a tmpfs. This was a bug (yuo can find it in the MySQL bug system and in Open Query's blog and quite a few other places), and the most effective way for resolving this was specifying all the paths explicitly, to not rely on an underlying code being fixed. Again, it's possible to fix this in the MariaDB code base, but it's not entirely trivial because of the indirectness when you look at the code. We'd need to ensure that all paths would indeed end up correct on all platforms we build for. That's entirely doable and I suppose we could have mysql-test cases that check the output of these aspects of SHOW GLOBAL VARIABLES.

This won't do it either on Windows: +innodb_flush_method = O_DIRECT

Also, lot of unixism is in the comments +# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf. +# InnoDB is enabled by default with a 10MB datafile in /var/lib/ mysql/. +# The files must end with '.cnf', otherwise they'll be ignored. +# Read the manual, too, if you want chroot! +# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

true since it was derived from that environment. It's a vast improvement over what was there before, but I won't pretend for a minute that it's perfect. The above is just comment text and thus harmless - naturally further improvements would be great!

Is there a need to repeat compiled-in defaults? +socket = @MYSQL_UNIX_ADDR@ +port = @MYSQL_TCP_PORT@

Not as such. I did the above based on the earlier examples. My socket was hardcoded to the Debian/Ubuntu layout, which unfortunately is different from the RPM layout which is different from the binary tarball layout. And guess what, the source build is different again ;-) Socket you may find in /var/run/mysqld/ or /var/lib/mysql or /tmp, and elsewhere. Indeed if the compiled-in default is correct, it'll work ok. Unfortunately, in the past we've had the situation where the defaults in the RPMs from mysql.com and Red Hat themselves did not match up with each other and the expected config on Red Hat environments, so a config file was essential to not break things in case of an upgrade in any direction.

And a merely provocative question : if those parameters(mainly numbers) are the sane universally useful baseline values, why not have them compiled into mysqld instead of current defaults? Than we won't need a config file at all.

I can agree with that logic, and since MariaDB does its own packaging now that could be an excellent solution that also resolves many if not all of the other issues you raised with the baseline config. So, Vlad, I'm all for what you suggest! excellent stuff. But here's the thing. What you suggest requires quite a bit more work by various people, and that'll take time. So I'm suggesting a multi-step process. The old my-*.cnf files are nasty and cause a lot of hassle, so I believe it's very important to get rid of them. We can certainly strip or comment out items in the new my-example.cnf to make sure it works on all platforms, or find a way to have a single source file from which *different* platform specific examples are generated. I was actually quite surprised to find that a .sh shell script is still used for preparing the Windows build environment! Of course, in the stock mysql.com you have the config wizard that takes care of creating a baseline config. I'm not sure what the current setup is for MariaDB and its Windows installer... but perhaps somewhere along there we can resolve that separately. Since Windows is not a *nix, all the paths are different. All other platforms are *nix based. Right? 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

Hi Vlad On 02/04/2011, at 7:05 AM, Vladislav Vaintroub wrote:

In current (5.2-main and 5.3-main MSI-based) setup, user can specify the datadirectory, port and root password, when database is created. Config file is tiny and typically has only datadir and port here and in no way "optimized". Windows packages are relocatable, hence I do not need either basedir nor language. Except missing sizes parameters, there is a difference to default MySQL installation in that sql_mode is not set (default in MySQL installations is strict) and storage engine is not set (default in MySQL installations is innodb). For me, it sounds like we could make many users happy, if we had a pre-selected "Quick configuration" checkbox (nothing wizard-driven), that would

-set sql_mode to strict -set default storage engine to innodb - give 12.5% or so of RAM to Innodb buffer pool (clearly not suitable for dedicated machines, but dedicated installs will need tweaking parameters anyway) . 12.5%RAM is something I borrowed from the ConfigWizard's default - set innodb log file size to a reasonable value, because changing this parameter afterwards is cumbersome.

Ideally there would be an integer input field with "how much memory (in MB) do you want to dedicate to this MariaDB instance" with 12.5%RAM being default.

My initial thought when the whole discussion about improved templates has started started was to reuse the "one true template" for windows. But now I'm not really sure, anything I have seen so far would not run on Windows when used as-is, and (for my own taste) overspecified, often repeating defaults. I personally would prefer a minimal OS-agnostic template, but I think way forward is to go on and roll out my own solution for those 4 extra parameters.

...

somewhere along there we can resolve that separately. Since Windows is not a *nix, all the paths are different. All other platforms are *nix based. Right?

*nix is not *nix, its standards can't agree with each other. MySQL packaging legacy adds yet another dimension to it. RPM package layout is different from DEB package layout and different from tar.gz. On Solaris, packages are getting installed into /opt , on OSX and after "make install" to /usr/local/mysql. And so on. The hardcoded paths will still be issue there.

So I think we agree that fixing up the build-time defaults would work best then, since that that point we know what we're building for, right? The problem with changing compiled-in defaults is that if an existing installation relies on a certain old incorrect setting, it'll break on upgrade. Since people tend to not simply replace running configs with example configs in a production system, having changes in a (sample/ default) config file is sufficiently safe. So, what do we do? 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