Univention Bugzilla – Bug 39471
Create ucr template for mysql configuration
Last modified: 2016-08-03 15:56:30 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.
Please provide a univention-mysql package which can be used by different Apps.
# 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)
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?
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.
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
(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
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.
(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
(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
(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".
(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)
(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
<http://errata.software-univention.de/ucs/4.1/223.html>