Difference between revisions of "MySQL Tuning Parameters"
From AgileApps Support Wiki
imported>Aeric |
imported>Aeric |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
<includeonly>=== MySQL Tuning Parameters ===</includeonly> | <includeonly>==== MySQL Tuning Parameters ====</includeonly> | ||
These section provides some typical MySQL configuration values that are appropriate for a production system. These values assume: | These section provides some typical MySQL configuration values that are appropriate for a production system. These values assume: | ||
:* 100Mb Primary Ethernet Card | :* 100Mb Primary Ethernet Card | ||
| Line 8: | Line 8: | ||
:* 64-bit Red Hat Enterprise Linux ES 4 | :* 64-bit Red Hat Enterprise Linux ES 4 | ||
==== my.cnf / my.ini Options ==== | {{Important| | ||
:* ''Before changing parameters, take the server and MySQL down.''<br>Do not adjust parameters while MySQL is running. Doing so can potentially cause the database to be corrupted. | |||
:* The settings shown here are an example. Consult the latest MySQL document for information on the best settings to use. | |||
}} | |||
===== my.cnf / my.ini Options ===== | |||
:{| | :{| | ||
<pre> | <pre> | ||
| Line 32: | Line 37: | ||
|} | |} | ||
==== InnoDB-Specific Options ==== | ===== InnoDB-Specific Options ===== | ||
:{| | :{| | ||
<pre> | <pre> | ||
| Line 49: | Line 54: | ||
</pre> | </pre> | ||
|} | |} | ||
<noinclude> | |||
[[Category:Installation]] | |||
</noinclude> | |||
Latest revision as of 00:20, 15 November 2014
These section provides some typical MySQL configuration values that are appropriate for a production system. These values assume:
- 100Mb Primary Ethernet Card
- 32 GB Memory
- 2 Processors
- 2 Cores per Processor
- Switched Ethernet Port
- 64-bit Red Hat Enterprise Linux ES 4
Important:
- Before changing parameters, take the server and MySQL down.
Do not adjust parameters while MySQL is running. Doing so can potentially cause the database to be corrupted. - The settings shown here are an example. Consult the latest MySQL document for information on the best settings to use.
- Before changing parameters, take the server and MySQL down.
my.cnf / my.ini Options
interactive-timeout = 7200 wait-timeout = 7200 back_log = 200 max_connections = 350 max_connect_errors = 10 table_cache = 8192 max_allowed_packet = 48M binlog_cache_size = 8M max_heap_table_size = 120M sort_buffer_size = 2M join_buffer_size = 2M thread_cache = 200 thread_concurrency = 24 query_cache_size = 500M query_cache_limit = 1M ft_min_word_len = 4 thread_stack = 256K tmp_table_size = 120M
InnoDB-Specific Options
innodb_additional_mem_pool_size = 48M innodb_buffer_pool_size = 23G innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 24 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M odb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 #innodb_flush_method=O_DSYNC innodb_lock_wait_timeout = 300