MySQL Tuning Parameter

I haven't posted anything in a while.  The holiday season has been busy and I have been complete upgrade projects.  Before I start belated Happy New Year to you.

Whilst working on a small client database recently and made a list the MySQL parameters we used to tune the database.  Hopefully it may give you some pointers into database tuning and parameters to look at.  I should give you a couple of more details the database is fairly large about 3TB.  It's a mix of online and archive data.  It's main transaction types are insert and select.  The database version is 5.5.11 (soon to be upgraded to 5.6.14).  The databases within the instance are mainly innodb.


MySQL Parameters



innodb_stats_on_metadata=off (off as default in MySQL 5.6)
max_connections = 751 (default 151) - you may want to increases this - simultaneous connections
max_connect_errors = 751 (default 10 pre 5.6.  Default is 100 post 5.5) - If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections.
table_open_cache = 2048 (default 400 pre 5.6.  Default in 5.6 is 2000 ) - open tables for all threads
max_allowed_packet = 64M - (default 16MB) - The maximum size of one packet or any generated/intermediate string, or any parameter sent by the mysql_stmt_send_long_data() C API function.  The default is 4MB in 5.6 and above and 1MB in 5.5 and below
binlog_cache_size = 1M - (default 32KB) - The size of the cache to hold changes to the binary log during a transaction.
max_heap_table_size = 2G (default 16MB) - This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values
sort_buffer_size = 2M (default) - Each session that needs to do a sort allocates a buffer of this size.   This is thread dependant.
join_buffer_size = 128M (default 128KB pre 5.6) -The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.
thread_cache_size = 100 (default 0) - How many threads the server should cache for reuse. When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there.
tmp_table_size = 2G (default 16MB) - The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.)
innodb_additional_mem_pool_size = 16M (default 8MB) - Additional memory pool that is used by InnoDB to store metadata  information.  If InnoDB requires more memory for this purpose it will start to allocate it from the OS.  As this is fast enough on most recent operating systems, you normally do not need to change this value. SHOW INNODB STATUS will display the current amount used.
innodb_buffer_pool_size = 120G (default 128MB) - The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data.  Dependant on physical memory.
innodb_write_io_threads = 32 (default 4) - The number of I/O threads for read operations in InnoDB.
innodb_read_io_threads = 32 (default 4) - The number of I/O threads for write operations in InnoDB.
innodb_log_buffer_size = 32M (default 8) - The size in bytes of the buffer that InnoDB uses to write to the log files on disk.
innodb_max_dirty_pages_pct = 90 (default 75) - InnoDB tries to flush data from the buffer pool so that the percentage of dirty pages does not exceed this value.
innodb_lock_wait_timeout = 120 (default 50) -The length of time in seconds an InnoDB transaction waits for a row lock before giving up
innodb_io_capacity = 10000 (default 200) - The innodb_io_capacity parameter sets an upper limit on the I/O activity performed by the InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the insert buffer.
innodb_buffer_pool_instances = 4 (default 1) - The number of regions that the InnoDB buffer pool is divided into.
innodb_sync_spin_loops = 100 (default 30) - The number of times a thread waits for an InnoDB mutex to be freed before the thread is suspended.
open-files-limit = 8192 (default 0)  - Changes the number of file descriptors available to mysqld.  Set to -1 (autosized) in 5.6,

Another parameter to test is innodb_flush_method, this can give good performance gain depending on the disk subsystem.  We kept this as default but is worth testing.

MyISAM Specific options
key_buffer_size = 64M (default 8MB)- Index blocks for MyISAM tables are buffered and are shared by all threads.
bulk_insert_buffer_size = 64M (default 8MB) -  MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables.
myisam_sort_buffer_size = 128M (default 8MB)  - The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
myisam_max_sort_file_size = 10G (default 2GB) -  The maximum size of the temporary file that MySQL is permitted to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE).

No comments:

Post a Comment