Other MySQL Setting Worth Considering
Prev | Chapter 29. Prepare the MySQL Database for the Tigase Server | Next

## Other MySQL Setting Worth Considering

There is a number of other options useful, especially for a performance reasons. Please note, you 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

I am certainly not a database expert nor MySQL expert and I do not pretend to be one. So any comments or suggestions you may have are very welcome and appreciated.

Prev | Up | Next
Configuring MySQL for UTF-8 Support | Home | Chapter 30. Hashed User Passwords in Database