There are a number of other useful options, especially for performance improvements. Please note, you will have to review them as some of them may impact data reliability and are useful for performance or load tests installations only.
# InnoDB seems to be a better choice # so lets make it a default DB engine default-storage-engine = innodb
Some the general MySQL settings which mainly affect performance:
key_buffer = 64M max_allowed_packet = 32M sort_buffer_size = 64M net_buffer_length = 64K read_buffer_size = 16M read_rnd_buffer_size = 16M thread_stack = 192K thread_cache_size = 8 query_cache_limit = 10M query_cache_size = 64M
InnoDB specific settings:
# Keep data in a separate file for each table innodb_file_per_table = 1 # Allocate memory for data buffers innodb_buffer_pool_size = 1000M innodb_additional_mem_pool_size = 100M # A location of the MySQL database innodb_data_home_dir = /home/databases/mysql/ innodb_log_group_home_dir = /home/databases/mysql/ # The main thing here is the 'autoextend' property # without it your data file may reach maximum size and # no more records can be added to the table. innodb_data_file_path = ibdata1:10M:autoextend innodb_log_file_size = 10M innodb_log_buffer_size = 32M # Some other performance affecting settings innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 50 innodb_thread_concurrency = 16
These settings may not be fully optimized for your system, and have been only tested on our systems. If you have found better settings for your systems, feel free to let us know.