My.cnf: Difference between revisions
From Open-Xchange
No edit summary |
|||
Line 1: | Line 1: | ||
== Introduction == | |||
This page lists some parameters (MySQL 5.1) which might be worth to adjust on a MySQL instance running the Open-Xchange databases. | This page lists some parameters (MySQL 5.1) which might be worth to adjust on a MySQL instance running the Open-Xchange databases. | ||
This is NOT a reference configuration but shows some possible settings. Please check the MySQL documentation for your version to understand the settings and test local changes carefully. | This is NOT a reference configuration but shows some possible settings. Please check the MySQL documentation for your version to understand the settings and test local changes carefully. | ||
You should adjust the <code>innodb_buffer_pool_size</code> parameter for better memory usage. | === Configuration items === | ||
* You should adjust the <code>innodb_buffer_pool_size</code> parameter for better memory usage. This is where all the memory of large DB machines should go. For example, on a 32 GB RAM machine, this can go up to 24 GB. | |||
* On bigger installations you should use <code>innodb_file_per_table = 1</code>, which is creating single files instead of one big blob. If you change this parameter after the database initialization you have to recreate (like dump/drop and re-import) the tables. | |||
* Some benchmarks indicate that actually switching off the query cache helps performance. You may want to experiment with this. To switch it off, use <code>query_cache_size=0</code>, <code>query_cache_type=0</code>. | |||
* It can help to put different parts of the mysql datadir (iblog, ibdata) on different filesystems / storage devices. This depends on your infrastructure. Settings herefore are <code>datadir</code>, <code>innodb_data_home_dir</code>, <code>innodb_log_group_home_dir</code>. | |||
=== Sample my.cnf file === | |||
# | # |
Revision as of 08:53, 15 January 2014
Introduction
This page lists some parameters (MySQL 5.1) which might be worth to adjust on a MySQL instance running the Open-Xchange databases.
This is NOT a reference configuration but shows some possible settings. Please check the MySQL documentation for your version to understand the settings and test local changes carefully.
Configuration items
- You should adjust the
innodb_buffer_pool_size
parameter for better memory usage. This is where all the memory of large DB machines should go. For example, on a 32 GB RAM machine, this can go up to 24 GB.
- On bigger installations you should use
innodb_file_per_table = 1
, which is creating single files instead of one big blob. If you change this parameter after the database initialization you have to recreate (like dump/drop and re-import) the tables.
- Some benchmarks indicate that actually switching off the query cache helps performance. You may want to experiment with this. To switch it off, use
query_cache_size=0
,query_cache_type=0
.
- It can help to put different parts of the mysql datadir (iblog, ibdata) on different filesystems / storage devices. This depends on your infrastructure. Settings herefore are
datadir
,innodb_data_home_dir
,innodb_log_group_home_dir
.
Sample my.cnf file
# [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld] socket = /var/run/mysqld/mysqld.sock port = 3306 user = mysql # applies only when running as root #memlock = 1 table_open_cache = 3072 table_definition_cache = 4096 max_heap_table_size = 64M tmp_table_size = 64M max_connections = 505 max_user_connections = 500 max_allowed_packet = 16M thread_cache_size = 32 query_cache_size = 64M # InnoDB default_table_type = InnoDB # 80% of ram that is dedicated for the database (this needs to be adjusted to your system) innodb_buffer_pool_size = 14G # number of CPU cores dedicated to the MySQL InnoDB backend innodb_buffer_pool_instances = 16 innodb_data_file_path = ibdata1:128M:autoextend innodb_file_per_table = 1 innodb_log_file_size = 512M innodb_log_files_in_group = 2 # MyISAM myisam_recover = backup,force # Logging log_warnings = 2 log_error = /var/log/mysql/error.log slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 log_queries_not_using_indexes = 1 min_examined_row_limit = 20 # Binary Log / Replication server_id = 1 log-bin = mysql-bin binlog_cache_size = 1M sync_binlog = 8 binlog_format = row expire_logs_days = 7 max_binlog_size = 128M relay-log = /var/log/mysql/slave-relay.log relay-log-index = /var/log/mysql/slave-relay-log.index [mysqldump] quick single-transaction max_allowed_packet = 16M [mysql] no_auto_rehash [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqld_safe] open-files-limit = 8192 log-error = /var/log/mysql/error.log