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