Bug 39471 - Create ucr template for mysql configuration
Create ucr template for mysql configuration
Status: CLOSED FIXED
Product: UCS
Classification: Unclassified
Component: MySQL
UCS 4.1
Other Linux
: P5 enhancement (vote)
: UCS 4.1-2-errata
Assigned To: Philipp Hahn
Janek Walkenhorst
:
Depends on: 40216
Blocks: 40857
  Show dependency treegraph
 
Reported: 2015-10-05 11:48 CEST by Erik Damrose
Modified: 2016-08-03 15:56 CEST (History)
4 users (show)

See Also:
What kind of report is it?: ---
What type of bug is this?: ---
Who will be affected by this bug?: ---
How will those affected feel about the bug?: ---
User Pain:
Enterprise Customer affected?:
School Customer affected?:
ISV affected?:
Waiting Support:
Ticket number:
Bug group (optional): External feedback, Roadmap discussion (moved), Usability
Max CVSS v3 score:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Erik Damrose univentionstaff 2015-10-05 11:48:05 CEST
UCS offers no ucr template for the mysql config. A partner requested this feature for an App. Several Apps use mysql in their backend.
Comment 1 Stefan Gohmann univentionstaff 2015-11-24 06:30:09 CET
Please provide a univention-mysql package which can be used by different Apps.
Comment 2 Philipp Hahn univentionstaff 2015-11-24 13:44:30 CET
# tail -n 5 /etc/mysql/my.cnf 
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

# printf '%s\n%s' '[mysqld]' 'bind-address = 0.0.0.0' >/etc/mysql/conf.d/insane.cnf
# /etc/init.d/mysql restart
# lsof -i :mysql
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  19329 mysql   10u  IPv4 467010      0t0  TCP *:mysql (LISTEN)
Comment 3 Stefan Gohmann univentionstaff 2015-11-24 21:20:09 CET
Sorry, that is not what I meant.

We need a mysql configuration which can be configured through UCR. That gives the opportunity to define LDAP policies and so on.

Another issue I've heard is that the performance with our default configuration is not good enough. So, we should provide a default mysql config which matches the App needs, for example for OX, Zarafa or SEP.

@Erik, maybe you can provide some config recommendations?
Comment 4 Erik Damrose univentionstaff 2015-11-26 17:31:53 CET
What i learned so far is, that recommends are not easily done. Basic setting depend on RAM, CPU-count and so on. I will give more input when i hear something.
Comment 5 Philipp Hahn univentionstaff 2015-11-30 09:45:02 CET
r65973 | Bug #39471 MySQL: Initial Release
 The rope is called mysql/config/$section/$key=$value
 autostart is already provided by univention-base-files / our patched MySQL; the old UCR definitions should be removed from u-b-f.

Package: univention-mysql
Version: 1.0.0-1.15.201511300938
Branch: ucs_4.1-0
Scope: errata4.1-0

r65974 | Bug #39471 MySQL: Initial Release YAML
 univention-mysql.yaml

r65975 | Bug #39471: univention-mysql
 Added to ucs_4.1-0.txt - will not be on DVD
Comment 6 Janek Walkenhorst univentionstaff 2015-12-08 18:11:57 CET
(In reply to Stefan Gohmann from comment #3)
> Another issue I've heard is that the performance with our default
> configuration is not good enough. So, we should provide a default mysql
> config which matches the App needs, for example for OX, Zarafa or SEP.
Is this part of this bug?


(In reply to Philipp Hahn from comment #5)
> r65973 | Bug #39471 MySQL: Initial Release
>  The rope is called mysql/config/$section/$key=$value
>  autostart is already provided by univention-base-files / our patched MySQL;
> the old UCR definitions should be removed from u-b-f.
> 
> Package: univention-mysql
OK except:
There is a syntax to be followed in MySQL option files, documented e.g. here:
<https://dev.mysql.com/doc/refman/5.5/en/option-files.html>
(Although there seems to be no way to escape a »'« or a »"« character?, )the UCR template should do what is possible to correctly transfer all values from UCR to MySQL.

> r65974 | Bug #39471 MySQL: Initial Release YAML
>  univention-mysql.yaml
OK

> r65975 | Bug #39471: univention-mysql
>  Added to ucs_4.1-0.txt - will not be on DVD
OK
Comment 7 Janek Walkenhorst univentionstaff 2015-12-08 18:14:31 CET
Additionally: Maybe a different UCR warning would make more sense, as there is no need to manually edit the template, if every possible configuration can be made through UCR.
Comment 8 Stefan Gohmann univentionstaff 2015-12-09 07:06:56 CET
(In reply to Janek Walkenhorst from comment #6)
> (In reply to Stefan Gohmann from comment #3)
> > Another issue I've heard is that the performance with our default
> > configuration is not good enough. So, we should provide a default mysql
> > config which matches the App needs, for example for OX, Zarafa or SEP.
> Is this part of this bug?

Yes, we should provide a better default configuration which meets the requirements of the Apps. See for example here https://doc.zarafa.com/6.40/Administrator_Manual/en-US/html/_memory_usage_setup.html
Comment 9 Philipp Hahn univentionstaff 2015-12-09 10:32:06 CET
(In reply to Stefan Gohmann from comment #8)
> (In reply to Janek Walkenhorst from comment #6)
> > (In reply to Stefan Gohmann from comment #3)
> > > Another issue I've heard is that the performance with our default
> > > configuration is not good enough. So, we should provide a default mysql
> > > config which matches the App needs, for example for OX, Zarafa or SEP.
> > Is this part of this bug?
> 
> Yes, we should provide a better default configuration which meets the
> requirements of the Apps. See for example here
> <https://doc.zarafa.com/6.40/Administrator_Manual/en-US/html/_memory_usage_setup.html>

What is *the* Apps?
If Zarafa want to tune their DB for their specific needs, the can do so now using UCR, but others want to tune other parameters. It even gets worse when two Apps would share one MySQL server, as one would overwrite the settings specified by the other.
If you want a fast DB, use a dedicated VM and give it lots(!) of RAM, fast SSDs or many(!) disks.

To give you an idea; these are the Zarafa recommendations:
> innodb_buffer_pool_size: around 25% of total RAM size
> mysql_query_cache: 32Mb
> innodb_log_file_size: 25% of the innodb_buffer_pool_size
> innodb_log_buffer_size: 32M

Linux Magazin 12/2015.p34 has an article about MySQL tuning, which recommends something other:
> query_cache: "Some consider it a waste and recommend deactivating it completely"
> innodb_buffer_pool: "80% of RAM" (which is good on a dedicated server, but would cripple any other process on a shared host)
> innodb_log_file_size: "48M", but "choosing the wrong value could cripple your server, which will no longer start then".

Unless you have a concrete requirement, picking a value is as good as picking any other value. So I won't do that!



(In reply to Janek Walkenhorst from comment #6)
> (In reply to Philipp Hahn from comment #5)
> > r65973 | Bug #39471 MySQL: Initial Release
> >  The rope is called mysql/config/$section/$key=$value
> >  autostart is already provided by univention-base-files / our patched MySQL;
> > the old UCR definitions should be removed from u-b-f.
> > 
> > Package: univention-mysql
> OK except:
> There is a syntax to be followed in MySQL option files, documented e.g. here:
> <https://dev.mysql.com/doc/refman/5.5/en/option-files.html>
> (Although there seems to be no way to escape a »'« or a »"« character?, )the
> UCR template should do what is possible to correctly transfer all values
> from UCR to MySQL.

Using either ' or " should be possible; otherwise the template prints an error.
Please note that due to Bug #40202 is is now impossible to set a value to a string consisting only of white space characters, as \ is now escaped to \\.
This is also why I had to add the hack for trailing '/' to distinguish between empty value and no value at all.

r66195 | Bug #39471 MySQL: Allow options without value
 Adds some escaping.
r66196 | Bug #39471 MySQL: Fix version fiasco
 Source package revision 1.0.0-2 of univention-mysql has already been imported.

Package: univention-mysql
Version: 3.0.0-1.16.201512091026
Branch: ucs_4.1-0
Scope: errata4.1-0

r66197 | Bug #39471 MySQL: YAML
 univention-mysql.yaml
Comment 10 Erik Damrose univentionstaff 2015-12-09 11:39:45 CET
(In reply to Philipp Hahn from comment #9)
> What is *the* Apps?
> If Zarafa want to tune their DB for their specific needs, the can do so now
> using UCR, but others want to tune other parameters. It even gets worse when
> two Apps would share one MySQL server, as one would overwrite the settings
> specified by the other.
> If you want a fast DB, use a dedicated VM and give it lots(!) of RAM, fast
> SSDs or many(!) disks.

I agree with Philipps statement. Mysql does have a basic configuration with default values. If there are no universally applicable parameters we should not configure something just because we can.

If an ISV wants to ship mysql parameters they could do that now. In support cases the customer can be told to set some UCRvs and configure mysql "the univention-way".
Comment 11 Janek Walkenhorst univentionstaff 2015-12-09 12:15:14 CET
(In reply to Philipp Hahn from comment #9)
> (In reply to Stefan Gohmann from comment #8)
> What is *the* Apps?
> If Zarafa want to tune their DB for their specific needs, the can do so now
> using UCR, but others want to tune other parameters. It even gets worse when
> two Apps would share one MySQL server, as one would overwrite the settings
> specified by the other.
> If you want a fast DB, use a dedicated VM and give it lots(!) of RAM, fast
> SSDs or many(!) disks.
> 
> To give you an idea; these are the Zarafa recommendations:
> > innodb_buffer_pool_size: around 25% of total RAM size
> > mysql_query_cache: 32Mb
> > innodb_log_file_size: 25% of the innodb_buffer_pool_size
> > innodb_log_buffer_size: 32M
> 
> Linux Magazin 12/2015.p34 has an article about MySQL tuning, which
> recommends something other:
> > query_cache: "Some consider it a waste and recommend deactivating it completely"
> > innodb_buffer_pool: "80% of RAM" (which is good on a dedicated server, but would cripple any other process on a shared host)
> > innodb_log_file_size: "48M", but "choosing the wrong value could cripple your server, which will no longer start then".
> 
> Unless you have a concrete requirement, picking a value is as good as
> picking any other value. So I won't do that!
I agree.
This also seems rather cargo-cult-y, consider e.g. that for innodb_log_buffer_size "[a] large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. If big transactions are present, making the log buffer larger will save disk I/O."
For a system with 1 GiB of RAM it would be innodb_log_buffer_size: 16 MiB
For a system with 128 GiB of RAM it would be innodb_log_buffer_size: 2048 MiB
But the size of the transactions are application dependent and not machine RAM dependent. This setting is therefore either much too low or much to high. (And most transactions are probably less than a few MB so it seems rather excessive)
I would suggest an approach based on the typical transaction size and typical database size of the application, any "% of physical RAM"-rule is wrong for a shared host. (See also swap size cargo-cult)
Comment 12 Janek Walkenhorst univentionstaff 2015-12-10 16:06:36 CET
(In reply to Philipp Hahn from comment #9)
> Using either ' or " should be possible; otherwise the template prints an
> error.
> Please note that due to Bug #40202 is is now impossible to set a value to a
> string consisting only of white space characters, as \ is now escaped to \\.
> This is also why I had to add the hack for trailing '/' to distinguish
> between empty value and no value at all.
> 
> r66195 | Bug #39471 MySQL: Allow options without value
>  Adds some escaping.
OK

> r66196 | Bug #39471 MySQL: Fix version fiasco
>  Source package revision 1.0.0-2 of univention-mysql has already been
> imported.
OK

> Package: univention-mysql
> Version: 3.0.0-1.16.201512091026
> Branch: ucs_4.1-0
> Scope: errata4.1-0
OK

> r66197 | Bug #39471 MySQL: YAML
>  univention-mysql.yaml
OK
Comment 13 Janek Walkenhorst univentionstaff 2016-08-03 15:56:30 CEST
<http://errata.software-univention.de/ucs/4.1/223.html>