[Maria-developers] Default parameter settings / Windows MSI installer
Hello, After recent discussion about appropriate or not appropriate default setting on in my.cnf , I have created a prototype minimal settings for Windows installer. Here is the picture of this attempt http://cid-ff0c950417b4f8a4.office.live.com/self.aspx/share/dbsettings.png For those who have familiar the 5.2 MSI installer already, what I have added is a checkbox "Standard configuration", explanation text, and input field for innodb bufferpool size (preset with RAM/8, in my case it is 8191/8=1023). "Standard configuration" is on by default, if it is "on", then 4 parameters are added to the my.ini file 1) default_storage_engine=innodb 2) innodb_buffer_pool_size=[VALUE_IN_EDIT_BOX]M 3) innodb_log_file_size= min(innodb_buffer_pool_size/4, 50) I made this formula almost out of thin air and using some blog reading and comments in the templates. It is likely that 50M is more than the average user needs, however it only takes some disk space, and If user has to increase from default 5MB , the procedure is awkward. I checked, MySQL Config Wizard set this parameter to 54M on my box, so it is in the same range. 4) sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION If "Standard configuration" is off, hen input field for bufferpool size is disabled, and none of those 4 parameters is added to my.ini Do you think it is useful in this form? So far I tried to avoid "calibration" topics altogether since it is a controversial topic where people never have same opinions. Also I tried to avoid it because I cannot write GUIs . However, this one change is small in and it does not introduce additional dialogs. It makes some important defaults the same as "standard configuration" in existing MySQL installers. I think it was PeterL who already asked for non-default buffer pool size already, also Monty also spoke about it in Lisbon. Comments welcome, also those on the picture above - I'm not sure if wording and use of brackets and quotes is correct. Wlad
I don't know if the following comments are useful but here goes...
Here is the picture of this attempt
Looks nice.
For those who have familiar the 5.2 MSI installer already, what I have added is a checkbox "Standard configuration", explanation text, and
For those that "aren't" familiar or are new to the installer I'm thinking a "small question mark surrounded by a circle" next to the 'Standard Configuration' textbox that when hovered over popups a more detailed description of what "standard" means for MariaDB. This would be in lieu of the text below the checkbox. This might make sense to have for the other fields too. Just a thought.
1) default_storage_engine=innodb
I think the storage engine that replaced innodb is now technically xtradb but I believe the configuration option "alias" innodb still works for compatibility. Would it make sense to point this out?
XtraDB identifies itself as 'InnoDB' (in I_S tables and SHOW ENGINES). And X is in every respect backwards compatible with I. Even in CREATE TABLE statments you name it 'InnoDB'. For those users not familiar with the 'political situation' around MySQL 'InnoDB' would be familiar and 'XtraDB' unknown for many. It could be written like 'InnoBD (XtraDB)' though. -- Peter On Tue, Apr 5, 2011 at 20:47, Adam M. Dutko <dutko.adam@gmail.com> wrote:
I don't know if the following comments are useful but here goes...
Here is the picture of this attempt
Looks nice.
For those who have familiar the 5.2 MSI installer already, what I have added is a checkbox "Standard configuration", explanation text, and
For those that "aren't" familiar or are new to the installer I'm thinking a "small question mark surrounded by a circle" next to the 'Standard Configuration' textbox that when hovered over popups a more detailed description of what "standard" means for MariaDB. This would be in lieu of the text below the checkbox. This might make sense to have for the other fields too. Just a thought.
1) default_storage_engine=innodb
I think the storage engine that replaced innodb is now technically xtradb but I believe the configuration option "alias" innodb still works for compatibility. Would it make sense to point this out?
1) As I have already told I request a max_allowed_packet setting larger than 1M (in both [mysqld] and [mysqldump] sections). Oracle-MySQL sets 1M in [mysqld] and 16M in [mysqldump]. I prefer 64M in both actually. The reason is that BLOBs are commonly used to store images/photos and with recent digital cameras (10-18 Mpixel) it is not even possible to store an image from such cam in a single INSERT statement. MySQL server will 'go away' or 'receive a packet too large'. 2) Oracle-MySQL sets STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION if user select 'strict mode' during configuration. NO_AUTO_CREATE_USER just means that GRANT will not create user - only CREATE USER + GRANT will. This I do not find important. So I am OK with 'strict wlad mode'. But I am not sure that strict mode should be default as long as it is not in *nix. 3) I agree that if InnoDB is default engine then the 'pool size' should be raised from default. Looks OK for a test/development server as typically used in Windows (but still a very low setting for a production server) -- Peter On Tue, Apr 5, 2011 at 20:30, Vladislav Vaintroub <wlad@montyprogram.com>wrote:
Hello, After recent discussion about appropriate or not appropriate default setting on in my.cnf , I have created a prototype minimal settings for Windows installer. Here is the picture of this attempt http://cid-ff0c950417b4f8a4.office.live.com/self.aspx/share/dbsettings.png
For those who have familiar the 5.2 MSI installer already, what I have added is a checkbox "Standard configuration", explanation text, and input field for innodb bufferpool size (preset with RAM/8, in my case it is 8191/8=1023). "Standard configuration" is on by default,
if it is "on", then 4 parameters are added to the my.ini file 1) default_storage_engine=innodb 2) innodb_buffer_pool_size=[VALUE_IN_EDIT_BOX]M 3) innodb_log_file_size= min(innodb_buffer_pool_size/4, 50)
I made this formula almost out of thin air and using some blog reading and comments in the templates. It is likely that 50M is more than the average user needs, however it only takes some disk space, and If user has to increase from default 5MB , the procedure is awkward. I checked, MySQL Config Wizard set this parameter to 54M on my box, so it is in the same range.
4) sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
If "Standard configuration" is off, hen input field for bufferpool size is disabled, and none of those 4 parameters is added to my.ini
Do you think it is useful in this form? So far I tried to avoid "calibration" topics altogether since it is a controversial topic where people never have same opinions. Also I tried to avoid it because I cannot write GUIs . However, this one change is small in and it does not introduce additional dialogs. It makes some important defaults the same as "standard configuration" in existing MySQL installers. I think it was PeterL who already asked for non-default buffer pool size already, also Monty also spoke about it in Lisbon.
Comments welcome, also those on the picture above - I'm not sure if wording and use of brackets and quotes is correct.
Wlad
From: Peter Laursen [mailto:peter_laursen@webyog.com] Sent: Dienstag, 5. April 2011 20:52 To: Vladislav Vaintroub Cc: maria-developers@lists.launchpad.net; Kazuhisa Ichikawa; Philip Stoev; Arjen Lentz; haidong.ji@gmail.com Subject: Re: Default parameter settings / Windows MSI installer 1) As I have already told I request a max_allowed_packet setting larger than 1M (in both [mysqld] and [mysqldump] sections). Oracle-MySQL sets 1M in [mysqld] and 16M in [mysqldump]. I prefer 64M in both actually. The reason is that BLOBs are commonly used to store images/photos and with recent digital cameras (10-18 Mpixel) it is not even possible to store an image from such cam in a single INSERT statement. MySQL server will 'go away' or 'receive a packet too large'. This one belongs to the domain of “well defined compiled-in defaults”.. Increased compiled-in default value for max_allowed_packet has no potential to break an existing application. Myself, I do not see any harm in allowing 64MB by default, however I also do not fully understand denial-of-service potential , how big is a memory usage will be when 64MB statement is parsed, optimized etc. Maybe someone would know the story behind it better. I suggest to handle it in additional post , on this specific topic. 2) Oracle-MySQL sets STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION if user select 'strict mode' during configuration. NO_AUTO_CREATE_USER just means that GRANT will not create user - only CREATE USER + GRANT will. This I do not find important. So I am OK with 'strict wlad mode'. But I am not sure that strict mode should be default as long as it is not in *nix. Hmm, right. Just checked what 5.5 installation created. This is „strict Arjen mode” to be exactJ In his Debian examples the comment IIRC was “just like on Windows, set strict mode”, and it had STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION. NO_AUTO_CREATE_USER looks rather exotic, makes me wonder what was the reason to include it here.
I am not sure that strict mode should be default as long as it is not in *nix. Should be default I think. MySQL Windows installer invented the strict-by-default schema, I guess we have to follow to be the “drop-in replacement” . Everyone loves “strict” anyway, right?
3) I agree that if InnoDB is default engine then the 'pool size' should be raised from default. Looks OK for a test/development server as typically used in Windows (but still a very low setting for a production server) Agree, the proposed default settings of RAM/8 is not a for a dedicated server. In that case, DBA will correct this proposed number in the edit box during the install, or later, in my.ini -- Peter On Tue, Apr 5, 2011 at 20:30, Vladislav Vaintroub <wlad@montyprogram.com> wrote: Hello, After recent discussion about appropriate or not appropriate default setting on in my.cnf , I have created a prototype minimal settings for Windows installer. Here is the picture of this attempt http://cid-ff0c950417b4f8a4.office.live.com/self.aspx/share/dbsettings.png For those who have familiar the 5.2 MSI installer already, what I have added is a checkbox "Standard configuration", explanation text, and input field for innodb bufferpool size (preset with RAM/8, in my case it is 8191/8=1023). "Standard configuration" is on by default, if it is "on", then 4 parameters are added to the my.ini file 1) default_storage_engine=innodb 2) innodb_buffer_pool_size=[VALUE_IN_EDIT_BOX]M 3) innodb_log_file_size= min(innodb_buffer_pool_size/4, 50) I made this formula almost out of thin air and using some blog reading and comments in the templates. It is likely that 50M is more than the average user needs, however it only takes some disk space, and If user has to increase from default 5MB , the procedure is awkward. I checked, MySQL Config Wizard set this parameter to 54M on my box, so it is in the same range. 4) sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION If "Standard configuration" is off, hen input field for bufferpool size is disabled, and none of those 4 parameters is added to my.ini Do you think it is useful in this form? So far I tried to avoid "calibration" topics altogether since it is a controversial topic where people never have same opinions. Also I tried to avoid it because I cannot write GUIs . However, this one change is small in and it does not introduce additional dialogs. It makes some important defaults the same as "standard configuration" in existing MySQL installers. I think it was PeterL who already asked for non-default buffer pool size already, also Monty also spoke about it in Lisbon. Comments welcome, also those on the picture above - I'm not sure if wording and use of brackets and quotes is correct. Wlad
Hi Vlad On 06/04/2011, at 4:30 AM, Vladislav Vaintroub wrote:
3) innodb_log_file_size= min(innodb_buffer_pool_size/4, 50)
I made this formula almost out of thin air and using some blog reading and comments in the templates. It is likely that 50M is more than the average user needs, however it only takes some disk space, and If user has to increase from default 5MB , the procedure is awkward. I checked, MySQL Config Wizard set this parameter to 54M on my box, so it is in the same range.
InnoDB also wants to have its log files at least 3x size of largest BLOB. Given - Peter Laursen's sensible request to have 64M as a default max_packet_size; - combined witht the small yet fixed amount of disk space the InnoDB log files use; and - the nuisance resizing the logfiles is, I would suggest just fixing the size to either 50M or 64M, and not applying a magic formula to try and minimise it. Given the little disk space it uses it doesn't serve that much of a purpose, and given that it's not relative to the buffer pool but also the transaction size, a simple formula just doesn't do the trick. Yes I do realise that if Peter puts in a 50M BLOB, then the iblog should be >150M. But the packet size is all of a query, not just the one column, and it'll be escaped also. Regular BLOBs (images, etc) tend to not exceed 10M or so, which is well within the iblog/3 range for the size values I suggest. 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
I think we need to avoid a situation where there is yet another set of mariadb defaults located in yet another place (in this case, I assume in the WiX XML description file). Previously, we already had a situation with the old MySQL windows config wizard where an option would be deprecated and removed by the server whereas the config wizard will still try to configure it. Right now we have 3 separate things: - server defaults in the code, I belive mysqld.cc - various example .cnf files that are no longer current - windows-specific settings It would be nice if we can get those down to 1 or 2. For example, have the windows installer use one of the example .cnf files (once those are updated to be current to existing hardware). I also think that "Standard configuration" is a bit misleading here, since the standard configuration created by this option will be different from the default configuration. Maybe something like "Configuration optimized for transactions" would be more descriptive and better alligned with setting a transactional storage engine and a restrictive SQL mode. Also, I believe "bufferpool" from Vlad's PNG should be written "Buffer pool". Dbart can provide the best wording. Philip Stoev ----- Original Message ----- From: "Vladislav Vaintroub" <wlad@montyprogram.com> To: <maria-developers@lists.launchpad.net>; "Kazuhisa Ichikawa" <kahz@home.email.ne.jp>; "Peter Laursen" <peter_laursen@webyog.com>; "Philip Stoev" <pstoev@askmonty.org>; "Arjen Lentz" <arjen@openquery.com>; <haidong.ji@gmail.com> Sent: Tuesday, April 05, 2011 9:30 PM Subject: Default parameter settings / Windows MSI installer
Hello, After recent discussion about appropriate or not appropriate default setting on in my.cnf , I have created a prototype minimal settings for Windows installer. Here is the picture of this attempt http://cid-ff0c950417b4f8a4.office.live.com/self.aspx/share/dbsettings.png
For those who have familiar the 5.2 MSI installer already, what I have added is a checkbox "Standard configuration", explanation text, and input field for innodb bufferpool size (preset with RAM/8, in my case it is 8191/8=1023). "Standard configuration" is on by default,
if it is "on", then 4 parameters are added to the my.ini file 1) default_storage_engine=innodb 2) innodb_buffer_pool_size=[VALUE_IN_EDIT_BOX]M 3) innodb_log_file_size= min(innodb_buffer_pool_size/4, 50)
I made this formula almost out of thin air and using some blog reading and comments in the templates. It is likely that 50M is more than the average user needs, however it only takes some disk space, and If user has to increase from default 5MB , the procedure is awkward. I checked, MySQL Config Wizard set this parameter to 54M on my box, so it is in the same range.
4) sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
If "Standard configuration" is off, hen input field for bufferpool size is disabled, and none of those 4 parameters is added to my.ini
Do you think it is useful in this form? So far I tried to avoid "calibration" topics altogether since it is a controversial topic where people never have same opinions. Also I tried to avoid it because I cannot write GUIs . However, this one change is small in and it does not introduce additional dialogs. It makes some important defaults the same as "standard configuration" in existing MySQL installers. I think it was PeterL who already asked for non-default buffer pool size already, also Monty also spoke about it in Lisbon.
Comments welcome, also those on the picture above - I'm not sure if wording and use of brackets and quotes is correct.
Wlad
-----Original Message----- From: Philip Stoev [mailto:pstoev@askmonty.org] Sent: Mittwoch, 6. April 2011 13:10 To: Vladislav Vaintroub Cc: maria-developers@lists.launchpad.net; Kazuhisa Ichikawa; Peter Laursen; Arjen Lentz; haidong.ji@gmail.com Subject: Re: Default parameter settings / Windows MSI installer
I think we need to avoid a situation where there is yet another set of mariadb defaults located in yet another place (in this case, I assume in
Hi Philip, the
WiX XML description file). Previously, we already had a situation with the old MySQL windows config wizard where an option would be deprecated and removed by the server whereas the config wizard will still try to configure it.
Right, we will not catch errors if we do not test packages. It was a problem within old MySQL /Sun/Oracle, as packages were produced after all development was done , and buildteam black magic was required to do it. But here, we can produce/test packages per-push or nightly, to avoid regressions. The number of parameters is kept to minimum, and those are unlikely to disappear (sql_mode, default_storage_engine, innodb_buffer_pool_size, innodb_log_buffer_size, port,datadir).
Right now we have 3 separate things: - server defaults in the code, I belive mysqld.cc - various example .cnf files that are no longer current - windows-specific settings
It would be nice if we can get those down to 1 or 2. For example, have the windows installer use one of the example .cnf files (once those are updated to be current to existing hardware).
I also think that "Standard configuration" is a bit misleading here, since the standard configuration created by this option will be different from
There is a problem with .cnfs, they do not scale with RAM. Also, Arjen wants to get the number of example .cnf down to 1, with hardcoded values and also make it Unix (with O_DIRECTS , paths with /usr and what else). Maintaining a copy "master" my-example.cnf for Windows, and selectively applying changes to my-example.ini, is not something I'm very keen on doing. Currently, I'm trying to avoid a situation, where people install Oracle's MySQL with all defaults, try it out, then install MariaDB with all defaults, try it out, and find out that ours sucks in comparison performance-wise, or their application would not work as expected due to some weirdness in sql_mode. the
default configuration. Maybe something like "Configuration optimized for transactions" would be more descriptive and better alligned with setting a transactional storage engine and a restrictive SQL mode.
I tried ConfigWizard yesterday, they have "standard" (one click) and "detailed" (many clicks). So I took "standard" as label. "Optimized for transactions" sounds also fine with me.
Also, I believe "bufferpool" from Vlad's PNG should be written "Buffer pool". Dbart can provide the best wording.
Will ask him.
Philip Stoev
Hi Vlad On 06/04/2011, at 10:51 PM, Vladislav Vaintroub wrote:
From: Philip Stoev [mailto:pstoev@askmonty.org] Right now we have 3 separate things: - server defaults in the code, I belive mysqld.cc - various example .cnf files that are no longer current - windows-specific settings
It would be nice if we can get those down to 1 or 2. For example, have the windows installer use one of the example .cnf files (once those are updated to be current to existing hardware).
There is a problem with .cnfs, they do not scale with RAM. Also, Arjen wants to get the number of example .cnf down to 1, with hardcoded values and also make it Unix (with O_DIRECTS , paths with /usr and what else).
Yes i want the number of example cnf files down to 1, and I've provided extensive reasoning for it which is also backed up by Philip's comments. However, to state that "Arjen wants ... hardcoded values and also make it Unix ..." is a misrepresentation of my position, not backed by any email here. I'm fine with removing most or even all such lines where possible, as long as it produces a functional result. O_DIRECT can probably be commented out "by default", removing that particular Unix-ism.
Maintaining a copy "master" my-example.cnf for Windows, and selectively applying changes to my-example.ini, is not something I'm very keen on doing.
If we mark Unix and Windows specifics in a standard way, the build system can take care of the rest and we can keep the single config. It may not be possible to completely eliminate platform dependent settings. This could for instance be done with a special tag in a comment line above the relevant option line, or even with a comment/tag on the line itself - that can then be cut off during processing both on Unix and on Windows. I don't see it as an "exception for Windows" situation, but rather a "fork in the road, we need one of two things here".
Currently, I'm trying to avoid a situation, where people install Oracle's MySQL with all defaults, try it out, then install MariaDB with all defaults, try it out, and find out that ours sucks in comparison performance- wise
I don't think making settings RAM-dependent really fixes this. Benchmarks will always be done badly and sometimes by uninformed people, no amount of preemptive handholding will prevent that. I called my config a baseline, not a default. It provides a sane starting point for further tuning necessary for production environments. So, many other commonly necesasry options will be present but commented out, with minimal yet relevant detail. You can't create a production/benchmark optimal config through formulas based on RAM and perhaps a few questions, so please let's not waste time on that. It is of course possible to create a config that will be optimal for some benchmarks, so if someone were wanting to deliver some misdirection out of the box, I'm sure their marketing department will make that happen at some point - I'm actually surprised it hasn't happened yet ;-) The one thing we should aim for is to have InnoDB enabled as the default engine, because being "ACID out of the box" is vital - it's been like that on Windows for years, and Oracle/MySQL has also moved to that for Unix from 5.5. This was a longer overdue change of default, and the only reason it wasn't done before was the old "MySQL AB does not own InnoDB" political situation (but for some reason it was ok on Windows, anyway ;-) 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
participants (5)
-
Adam M. Dutko -
Arjen Lentz -
Peter Laursen -
Philip Stoev -
Vladislav Vaintroub