Otimizar-Mysql

#Tutorial https://misaelgomes.com.br/otimizando-alocacao-de-memoria-no-mysql-com-jemalloc/

nano /lib/systemd/system/mysql.service
LimitNOFILE = 65535
systemctl daemon-reload

#Tuning Primer
git clone https://github.com/RootService/tuning-primer.git

#Backup
mysqldump -u root -p DATABASE > BK.NAME.SQL;

#Restaurar
mysql -u root -p DATABASE < BK.NAME.SQL;

mysql -u root -p -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -u root -p -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -u root -p -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

#mysqld.cnf

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
log-error	= /var/log/mysql/error.log

bind-address = 127.0.0.1
skip-external-locking
skip-name-resolve

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds = 259200
max_binlog_size         = 50M
sync_binlog             = 1

table_open_cache_instances = 8
table_open_cache = 15000
max_heap_table_size           = 256M
open_files_limit              = 16384
tmp_table_size                = 256M
table_definition_cache        = 15000

key_buffer_size = 128M
read_buffer_size = 64M
read_rnd_buffer_size = 64M
sort_buffer_size = 64M

bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 128M
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800
connect_timeout = 200
net_read_timeout = 200
net_write_timeout = 200

join_buffer_size = 8M
thread_stack            = 256K
;thread_cache_size       = 20

max_allowed_packet      = 128M

default_storage_engine = InnoDB

thread_handling = pool-of-threads
thread_pool_idle_timeout = 30
thread_pool_size = 2

;innodb_flush_method           = O_DIRECT
innodb_flush_method            = O_DIRECT_NO_FSYNC
innodb_log_files_in_group      = 2
innodb_log_file_size           = 128M
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 2000
innodb_file_per_table          = 1
innodb_open_files = 10000
innodb_buffer_pool_size        = 2G
innodb_thread_concurrency = 0
innodb_lock_wait_timeout = 120
innodb_log_buffer_size = 64M
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_buffer_pool_instances = 2
innodb_adaptive_flushing = 1
innodb_stats_persistent = 1
innodb_autoinc_lock_mode = 2

innodb_random_read_ahead = on
;innodb_buffer_pool_read_ahead = 35