maria-developers team mailing list archive

["Vladislav Vaintroub" <wlad@xxxxxxxxxxxxxxxx>]

From: Peter Laursen [mailto:peter_laursen@xxxxxxxxxx] 
Sent: Dienstag, 5. April 2011 20:52
To: Vladislav Vaintroub
Cc: maria-developers@xxxxxxxxxxxxxxxxxxx; Kazuhisa Ichikawa; Philip Stoev; Arjen Lentz; haidong.ji@xxxxxxxxx
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@xxxxxxxxxxxxxxxx> 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