[Maria-discuss] Suggested sample my.cnf for MariaDB package
All, I proposed updating the packaged my.cnf sample files before. After some thinking and deliberation, here are my thoughts on the sample my.cnf: 1. There are so many factors that affect settings in my.cnf so it is impossible to please everybody. There is a lot of debates on this. 2. There are values in having just ONE sample my.cnf to get beginners started 3. It should be short 4. It should incorporate best practices, to the extent that best practices can be agreed 5. It should benefit as many people as possible My proposal is pasted below. Comments welcome. At the same time, constant back-and-forth bickering won't benefit much either, in my opinion. Thanks, Haidong "Alex" Ji # MariaDB/Percona/Oracle MySQL programs look for option files in a set of # locations which depend on the deployment platform. # You can copy this option file to one of those # locations. For information about these locations, see: # http://dev.mysql.com/doc/mysql/en/option-files.html # # For detailed definition of settings, see URL below: # http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # Please do your own research, consult various resources # and experts for optimal settings for your own needs. # Some basic ones are provided below to get you started. # The following options will be passed to all MySQL clients [client] port = 3306 socket = /var/lib/mysql/mysql.sock # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock user=mysql log-bin = mysql-bin log-slow-queries sync_binlog = 1 expire_logs_days= 10 wait_timeout = 30 #server-id = last octet of IP address #character-set-server = utf8 [mysqldump] quick
I personally would be more in favor of a version that has _all_ options in it, with explanatory comments. Many options should not be set, but they could be commented out. That would make it much easier for users to get a grasp of available options and recommended settings. For instance, zabbix' config file looks like this: # This is a config file for Zabbix Agent (Unix) # To get more information about Zabbix, visit http://www.zabbix.com ############ GENERAL PARAMETERS ################# ### Option: PidFile # Name of PID file. # # Mandatory: no # Default: PidFile=/var/run/zabbix_agentd.pid ### Option: LogFile # Name of log file. # If not set, syslog is used. # # Mandatory: no # Default: # LogFile= LogFile=/var/log/zabbix/zabbix_agentd.log ### Option: LogFileSize # Maximum size of log file in MB. # 0 - disable automatic log rotation. # # Mandatory: no # Range: 0-1024 # Default: LogFileSize=1 ### Option: DebugLevel # Specifies debug level # 0 - no debug # 1 - critical information # 2 - error information # 3 - warnings # 4 - for debugging (produces lots of information) # # Mandatory: no # Range: 0-4 # Default: DebugLevel=3 ### Option: SourceIP # Source IP address for outgoing connections. # # Mandatory: no # Default: # SourceIP= On Fri, Mar 18, 2011 at 18:16, Haidong Ji <haidong.ji@gmail.com> wrote:
All,
I proposed updating the packaged my.cnf sample files before. After some thinking and deliberation, here are my thoughts on the sample my.cnf:
1. There are so many factors that affect settings in my.cnf so it is impossible to please everybody. There is a lot of debates on this. 2. There are values in having just ONE sample my.cnf to get beginners started 3. It should be short 4. It should incorporate best practices, to the extent that best practices can be agreed 5. It should benefit as many people as possible
My proposal is pasted below. Comments welcome. At the same time, constant back-and-forth bickering won't benefit much either, in my opinion.
Thanks, Haidong "Alex" Ji
# MariaDB/Percona/Oracle MySQL programs look for option files in a set of # locations which depend on the deployment platform. # You can copy this option file to one of those # locations. For information about these locations, see: # http://dev.mysql.com/doc/mysql/en/option-files.html # # For detailed definition of settings, see URL below: # http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
# Please do your own research, consult various resources # and experts for optimal settings for your own needs. # Some basic ones are provided below to get you started.
# The following options will be passed to all MySQL clients [client] port = 3306 socket = /var/lib/mysql/mysql.sock
# The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock user=mysql
log-bin = mysql-bin log-slow-queries sync_binlog = 1 expire_logs_days= 10 wait_timeout = 30
#server-id = last octet of IP address #character-set-server = utf8
[mysqldump] quick
_______________________________________________ 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
-- Walter Heck -- follow @walterheck on twitter to see what I'm up to! -- Check out my new startup: Server Monitoring as a Service @ http://tribily.com Follow @tribily on Twitter and/or 'Like' our Facebook page at http://www.facebook.com/tribily
I request setting max_allowed_packet 16M (at least - actually I'd prefer 64M) in both [mysqld] and [mysqldump] sections for all templates. For the background for this request see http://www.webyog.com/blog/2009/08/10/mysql-server-has-gone-away-part-1-max_... We have reports every month where we have to explain users this setting ("help - I can dump my database but I cannot restore it"). 16/64M would eliminate the problem for most users. And even for a single (MEDIUM/LONG)BLOB used to store an image from a modern 10Mpixel+ Digicam 1M is not enough - not even with JPG-compression (and of course not at all with RAW or TIFF formats used by professionals) Peter Webyog On Fri, Mar 18, 2011 at 21:33, Walter Heck <walterheck@gmail.com> wrote:
I personally would be more in favor of a version that has _all_ options in it, with explanatory comments. Many options should not be set, but they could be commented out. That would make it much easier for users to get a grasp of available options and recommended settings. For instance, zabbix' config file looks like this:
# This is a config file for Zabbix Agent (Unix) # To get more information about Zabbix, visit http://www.zabbix.com
############ GENERAL PARAMETERS #################
### Option: PidFile # Name of PID file. # # Mandatory: no # Default: PidFile=/var/run/zabbix_agentd.pid
### Option: LogFile # Name of log file. # If not set, syslog is used. # # Mandatory: no # Default: # LogFile=
LogFile=/var/log/zabbix/zabbix_agentd.log
### Option: LogFileSize # Maximum size of log file in MB. # 0 - disable automatic log rotation. # # Mandatory: no # Range: 0-1024 # Default: LogFileSize=1
### Option: DebugLevel # Specifies debug level # 0 - no debug # 1 - critical information # 2 - error information # 3 - warnings # 4 - for debugging (produces lots of information) # # Mandatory: no # Range: 0-4 # Default: DebugLevel=3
### Option: SourceIP # Source IP address for outgoing connections. # # Mandatory: no # Default: # SourceIP=
On Fri, Mar 18, 2011 at 18:16, Haidong Ji <haidong.ji@gmail.com> wrote:
All,
I proposed updating the packaged my.cnf sample files before. After some thinking and deliberation, here are my thoughts on the sample my.cnf:
1. There are so many factors that affect settings in my.cnf so it is impossible to please everybody. There is a lot of debates on this. 2. There are values in having just ONE sample my.cnf to get beginners started 3. It should be short 4. It should incorporate best practices, to the extent that best practices can be agreed 5. It should benefit as many people as possible
My proposal is pasted below. Comments welcome. At the same time, constant back-and-forth bickering won't benefit much either, in my opinion.
Thanks, Haidong "Alex" Ji
# MariaDB/Percona/Oracle MySQL programs look for option files in a set of # locations which depend on the deployment platform. # You can copy this option file to one of those # locations. For information about these locations, see: # http://dev.mysql.com/doc/mysql/en/option-files.html # # For detailed definition of settings, see URL below: # http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
# Please do your own research, consult various resources # and experts for optimal settings for your own needs. # Some basic ones are provided below to get you started.
# The following options will be passed to all MySQL clients [client] port = 3306 socket = /var/lib/mysql/mysql.sock
# The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock user=mysql
log-bin = mysql-bin log-slow-queries sync_binlog = 1 expire_logs_days= 10 wait_timeout = 30
#server-id = last octet of IP address #character-set-server = utf8
[mysqldump] quick
_______________________________________________ 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
-- Walter Heck
-- follow @walterheck on twitter to see what I'm up to! -- Check out my new startup: Server Monitoring as a Service @ http://tribily.com Follow @tribily on Twitter and/or 'Like' our Facebook page at http://www.facebook.com/tribily
_______________________________________________ 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 Walter, I think a sample file with ALL options will drown out the important ones and causes confusion. Like I mentioned in the reply to Monty, personally I think the target of sample my.cnf is for lower end users (no disrespect intended whatsoever). My 0.02c Haidong On Fri, Mar 18, 2011 at 3:33 PM, Walter Heck <walterheck@gmail.com> wrote:
I personally would be more in favor of a version that has _all_ options in it, with explanatory comments. Many options should not be set, but they could be commented out. That would make it much easier for users to get a grasp of available options and recommended settings. For instance, zabbix' config file looks like this:
# This is a config file for Zabbix Agent (Unix) # To get more information about Zabbix, visit http://www.zabbix.com
############ GENERAL PARAMETERS #################
### Option: PidFile # Name of PID file. # # Mandatory: no # Default: PidFile=/var/run/zabbix_agentd.pid
### Option: LogFile # Name of log file. # If not set, syslog is used. # # Mandatory: no # Default: # LogFile=
LogFile=/var/log/zabbix/zabbix_agentd.log
### Option: LogFileSize # Maximum size of log file in MB. # 0 - disable automatic log rotation. # # Mandatory: no # Range: 0-1024 # Default: LogFileSize=1
### Option: DebugLevel # Specifies debug level # 0 - no debug # 1 - critical information # 2 - error information # 3 - warnings # 4 - for debugging (produces lots of information) # # Mandatory: no # Range: 0-4 # Default: DebugLevel=3
### Option: SourceIP # Source IP address for outgoing connections. # # Mandatory: no # Default: # SourceIP=
On Fri, Mar 18, 2011 at 18:16, Haidong Ji <haidong.ji@gmail.com> wrote:
All,
I proposed updating the packaged my.cnf sample files before. After some thinking and deliberation, here are my thoughts on the sample my.cnf:
1. There are so many factors that affect settings in my.cnf so it is impossible to please everybody. There is a lot of debates on this. 2. There are values in having just ONE sample my.cnf to get beginners started 3. It should be short 4. It should incorporate best practices, to the extent that best practices can be agreed 5. It should benefit as many people as possible
My proposal is pasted below. Comments welcome. At the same time, constant back-and-forth bickering won't benefit much either, in my opinion.
Thanks, Haidong "Alex" Ji
# MariaDB/Percona/Oracle MySQL programs look for option files in a set of # locations which depend on the deployment platform. # You can copy this option file to one of those # locations. For information about these locations, see: # http://dev.mysql.com/doc/mysql/en/option-files.html # # For detailed definition of settings, see URL below: # http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
# Please do your own research, consult various resources # and experts for optimal settings for your own needs. # Some basic ones are provided below to get you started.
# The following options will be passed to all MySQL clients [client] port = 3306 socket = /var/lib/mysql/mysql.sock
# The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock user=mysql
log-bin = mysql-bin log-slow-queries sync_binlog = 1 expire_logs_days= 10 wait_timeout = 30
#server-id = last octet of IP address #character-set-server = utf8
[mysqldump] quick
_______________________________________________ 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
-- Walter Heck
-- follow @walterheck on twitter to see what I'm up to! -- Check out my new startup: Server Monitoring as a Service @ http://tribily.com Follow @tribily on Twitter and/or 'Like' our Facebook page at http://www.facebook.com/tribily
I request setting max_allowed_packet 16M (at least - actually I'd prefer 64M) in both [mysqld] and [mysqldump] sections for all templates. For the background for this request see http://www.webyog.com/blog/2009/08/10/mysql-server-has-gone-away-part-1-max_...
Excellent! Thanks Peter. Your explanation is clear and I learned something from it! Haidong On Fri, Mar 18, 2011 at 4:28 PM, Peter Laursen <peter_laursen@webyog.com> wrote:
I request setting max_allowed_packet 16M (at least - actually I'd prefer 64M) in both [mysqld] and [mysqldump] sections for all templates. For the background for this request see http://www.webyog.com/blog/2009/08/10/mysql-server-has-gone-away-part-1-max_...
We have reports every month where we have to explain users this setting ("help - I can dump my database but I cannot restore it"). 16/64M would eliminate the problem for most users. And even for a single (MEDIUM/LONG)BLOB used to store an image from a modern 10Mpixel+ Digicam 1M is not enough - not even with JPG-compression (and of course not at all with RAW or TIFF formats used by professionals) Peter Webyog On Fri, Mar 18, 2011 at 21:33, Walter Heck <walterheck@gmail.com> wrote:
I personally would be more in favor of a version that has _all_ options in it, with explanatory comments. Many options should not be set, but they could be commented out. That would make it much easier for users to get a grasp of available options and recommended settings. For instance, zabbix' config file looks like this:
# This is a config file for Zabbix Agent (Unix) # To get more information about Zabbix, visit http://www.zabbix.com
############ GENERAL PARAMETERS #################
### Option: PidFile # Name of PID file. # # Mandatory: no # Default: PidFile=/var/run/zabbix_agentd.pid
### Option: LogFile # Name of log file. # If not set, syslog is used. # # Mandatory: no # Default: # LogFile=
LogFile=/var/log/zabbix/zabbix_agentd.log
### Option: LogFileSize # Maximum size of log file in MB. # 0 - disable automatic log rotation. # # Mandatory: no # Range: 0-1024 # Default: LogFileSize=1
### Option: DebugLevel # Specifies debug level # 0 - no debug # 1 - critical information # 2 - error information # 3 - warnings # 4 - for debugging (produces lots of information) # # Mandatory: no # Range: 0-4 # Default: DebugLevel=3
### Option: SourceIP # Source IP address for outgoing connections. # # Mandatory: no # Default: # SourceIP=
On Fri, Mar 18, 2011 at 18:16, Haidong Ji <haidong.ji@gmail.com> wrote:
All,
I proposed updating the packaged my.cnf sample files before. After some thinking and deliberation, here are my thoughts on the sample my.cnf:
1. There are so many factors that affect settings in my.cnf so it is impossible to please everybody. There is a lot of debates on this. 2. There are values in having just ONE sample my.cnf to get beginners started 3. It should be short 4. It should incorporate best practices, to the extent that best practices can be agreed 5. It should benefit as many people as possible
My proposal is pasted below. Comments welcome. At the same time, constant back-and-forth bickering won't benefit much either, in my opinion.
Thanks, Haidong "Alex" Ji
# MariaDB/Percona/Oracle MySQL programs look for option files in a set of # locations which depend on the deployment platform. # You can copy this option file to one of those # locations. For information about these locations, see: # http://dev.mysql.com/doc/mysql/en/option-files.html # # For detailed definition of settings, see URL below: # http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
# Please do your own research, consult various resources # and experts for optimal settings for your own needs. # Some basic ones are provided below to get you started.
# The following options will be passed to all MySQL clients [client] port = 3306 socket = /var/lib/mysql/mysql.sock
# The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock user=mysql
log-bin = mysql-bin log-slow-queries sync_binlog = 1 expire_logs_days= 10 wait_timeout = 30
#server-id = last octet of IP address #character-set-server = utf8
[mysqldump] quick
_______________________________________________ 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
-- Walter Heck
-- follow @walterheck on twitter to see what I'm up to! -- Check out my new startup: Server Monitoring as a Service @ http://tribily.com Follow @tribily on Twitter and/or 'Like' our Facebook page at http://www.facebook.com/tribily
_______________________________________________ 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 On 19/03/2011, at 3:16 AM, Haidong Ji wrote:
I proposed updating the packaged my.cnf sample files before.
The Debian/Ubuntu packages already contain a "baseline config" that Open Query created earlier for the OurDelta packages. I believe all of the options suggested by you are already incorporated in there. It definitely merits some extensions and adjustments based on more recent information, and should remain up to date. In addition - the RPM packages should use it also, making all packages consistent - the old my-*.cnf samples should disappear from the source tree as they're a great source of misinformation, confusion and ridicule. 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've been busy preparing for a SQL Server presentation I will do this weekend, therefore the lack of activity. Arjen,
The Debian/Ubuntu packages already contain a "baseline config" that Open Query created earlier for the OurDelta packages. I believe all of the options suggested by you are already incorporated in there. It definitely merits some extensions and adjustments based on more recent information, and should remain up to date.
Thanks! I've installed OurDelta MariaDb5.1 package on a Ubuntu box, and got the sample my.cnf out of /etc/mysql/my.cnf. I will sift through and decide, to the best of my abilities, what to take and not to take. Yes, it should remain up to date and subject to constant input and suggestions.
In addition - the RPM packages should use it also, making all packages consistent - the old my-*.cnf samples should disappear from the source tree as they're a great source of misinformation, confusion and ridicule.
Agree. I should be able to work on this in the next few days. Thanks, Haidong Ji
participants (4)
-
Arjen Lentz -
Haidong Ji -
Peter Laursen -
Walter Heck