MySQL Tunning - Parte 2 - Config

Condig_MySQLTunning-Parte2.txt

[client]

###
### client section - used for "mysql", "mysqladmin" and similar command line
### clients.
###

#port = 3306
#socket = /tmp/mysql.sock
### Just in case your current configuration is not using default values.

[mysqld]

###
### mysqld section - used by MySQL Server (also applies to Percona Server,
### MariaDB etc.)
###

#port = 3306
#socket = /tmp/mysql.sock
#user = mysql
### Just in case your current configuration is not using default values.

datadir = /var/lib/mysql
### This must point to the main MySQL data directory.


###
### General Server Options:
###

max_allowed_packet = 32M
### Default packet limit is almost always too small.

max_connections = 2000
### Max connections as well (sleeping threads are okay to have)

#table_open_cache = 2000
### Table open cache - defaults only good in MySQL 5.7 and 5.6
### On 5.1 or 5.5 set the above.

#table_open_cache_instances = 16
### Table open cache instances - be sure to set this on MySQL 5.6.
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#table_open_cache_instances

open_files_limit = 10000
### Increase the number of file descriptors, we're not in stone age now.

tmp_table_size = 64M
max_heap_table_size = 64M
### Incease limits for implicit and explicit temporary tables.

tmpdir = /tmp
### Don't use tmpfs for tmpdir, or AIO will be disabled. And make sure
### There's enough room for temporary tables and files (usually 2-5GB)

#thread_cache_size = 100
### Thread cache - this is now autosized in MySQL 5.6 and 5.7
### But on MySQL 5.1 and 5.5 do set the above

default_storage_engine = InnoDB
### Default storage engine in most cases should be InnoDB. If in doubt:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#default_storage_engine

skip_name_resolve
### Disabling DNS resolution - DNS based logins will stop working:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#skip_name_resolve

query_cache_type=0
query_cache_size=0
### Explicitly disabling the query cache. If you have a light workload, you
### may reconsider:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#query_cache_type

#back_log = 512
### Consider increasing this if you have a high number of new connections
### (> 1000/sec) and you are running on MySQL 5.6 or older
### https://www.percona.com/blog/2011/04/19/mysql-connection-timeouts/

#thread_concurrency = 0
### Do not tune this. This does nothing. And I have included it here only
### because I've seen too many people obsess over it.

#join_buffer_size = 256k
#sort_buffer_size = 256k
#read_buffer_size = 128k
#read_rnd_buffer_size = 256k
### Leave these at their defaults, do not change server-wide settings for them
### Instead, use session variable when you really need it like that:
### mysql> SET session read_buffer_size = 2 * 1024 * 1024;
### mysql> RUN YOUR QUERY;


###
### Binary logging section
###

log_bin
server_id = 1
### Enabling binlog as well as unique server_id for
### point in time recovery and, potentially, replication.

max_binlog_size = 100M
### Don't have large binary logs, with file systems like ext3 this could
### cause stalls.

#master_info_repository = TABLE
#relay_log_info_repository = TABLE
### If you're on MySQL 5.6 or MySQL 5.7, store the binlog position
### to TABLE rather than FILE (that way position is in sync with db)

expire_logs_days = 7
### Control the amount of binary logs server is allowed to keep (in days)

sync_binlog = 0
### Disabling sync-binlog for better performance, but do consider the
### durability issues:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#sync_binlog

binlog_format = MIXED
### For the binary log format, I recommend MIXED, but it's up to you.

###
### MyISAM Specific Options:
###

### Assuming MyISAM is not going to be used, therefore defaults used for MyISAM
### Still if you do plan to use it, this is probably the only variable you
### want to tune:
#key_buffer_size = 128M


###
### InnoDB Specific Options:
###

#innodb_buffer_pool_size = 96G
### Set the innodb buffer pool size to 75-80% of total memory (dedicated):
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_buffer_pool_size
### The example above would be used on a dedicated 128GB MySQL server.

#innodb_log_file_size = 2047M
### Allow 1-2h worth of writes to be buffered in transaction logs:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_log_file_size
### Helper command:
### mysql> pager grep seq
### mysql> show engine innodb status\G select sleep(60); show engine innodb status\G

innodb_log_buffer_size = 16M
### Don't sweat about about this, just set it to 16M.

innodb_flush_log_at_trx_commit = 0
### This, OTOH is really important. See:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_flush_log_at_trx_commit

innodb_flush_method = O_DIRECT
### On Linux, just leave it set to O_DIRECT.
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_flush_method

#innodb_buffer_pool_instances = 4
### Set this depending on MySQL version. Available since 5.6
### In 5.6, set to 4
### In 5.7, set to 8
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_buffer_pool_instances

innodb_thread_concurrency = 8
### Yes, this is not an error. Use 0 only for benchmarks and if you're nowhere
### near saturating your server.
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_thread_concurrency

#innodb_io_capacity = 1000
#innodb_io_capacity_max = 3000
### Base these on your server radom write IO capabilities
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_io_capacity

innodb_stats_on_metadata = 0
### On 5.6 and 5.7 this is already turned off by default.
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_stats_on_metadata

#innodb_buffer_pool_dump_at_shutdown = 1 # MySQL 5.6+
#innodb_buffer_pool_load_at_startup  = 1 # MySQL 5.6+
#innodb_buffer_pool_dump_pct = 75        # MySQL 5.7 only
### Enable these for faster warm-up
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_buffer_pool_dump_at_shutdown

#innodb_adaptive_hash_index_parts = 16      # MySQL 5.7 only
#innodb_adaptive_hash_index_partitions = 16 # Percona Server only
### AHI is a common bottle-neck, however few versions of MySQL
### support AHI partitions:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_adaptive_hash_index_parts

#innodb_checksum_algorithm = crc32     # MySQL 5.6 or newer
### Hardware acceleratorion for checksums
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_checksum_algorithm

innodb_read_io_threads = 16
innodb_write_io_threads = 16
### This isn't super important to fine tune, but it's good to aling
### it to number of availble read / write spindles
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_read_io_threads

innodb_file_per_table
### There's a number of reasons to use file per table, but beware that
### it doesn't convert tables automatically, and ibdata1 will never shrink anyways

#innodb_open_files = 3000
### Set this on MySQL 5.1 and 5.5. On 5.6 and beyond it's autotuned
### specifies max number of .ibd files that MySQL can keep open at one time

#innodb_flush_neighbors = 0
### Set to 0 if you're using SSD. For magnetic drives, keep it at 1
### http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_neighbors

#innodb_flushing_avg_loops = 100
### Use this if you don't like how flushing activity behaves
### (e.g. if you get stalls)
### http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flushing_avg_loops

#innodb_page_cleaners = 8 # MySQL 5.7 only
### On a write-heavy environment, use this to gain even more control
### over the flushing activity. This is not yet tested too wildly.
### http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_cleaners

#innodb_old_blocks_time = 1000
### Use this only on 5.1 and 5.5 since 5.6 and 5.7 already have 1000 as a default
### This basically protects your buffer pool from occasional scans
### Although the way it works is quite complex, definitely not for my.cnf

#innodb_max_dirty_pages_pct
### If you're in a write-heavy environment, but want to limit how much of it
### you use for dirty pages, this is the variable to configure. Defaults are
### version specific, but range in 75-90 %