Melhorar configuração deste my.cnf (com MySQLTuner)

[Genneral]

Olá povo, boa tarde.

Recentemente fiz um upgrade de memória em meu servidor e configurei um novo My.CNF de acordo co mas especificações do MySQLTuner, instalado no servidor.

Meu servidor:

• MySQL: 5.6
• PHP: 5.5
• RAM: 4GB
• CPU: 2 Cores de 2Ghz
• S.O: CentOS 7 x64
• Armazenamento: SSD 40GB
• Atualmente estou com 3 domínios neste servidor (média de 220k de sessões mês), ambos usando MySQL como BD, tenho varnish instalado também..varnish precisa de algumas configurações que estou buscando ajuda (caso queira ajudar o tópico é este)

Atualmente meu my.cnf está assim:

# ATUAL
[mysql]

# Cliente #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# Geral #
user                           = mysql
default-storage-engine         = MyISAM
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 48M
myisam-recover                 = FORCE,BACKUP
sort-buffer                    = 1M
join-buffer					   = 1M
max-join_size                  = 2M

# Armazenamento #
datadir                        = /var/lib/mysql/

# Logs binarios #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# Threads #
thread_cache_size              = 0
max_delayed_threads            = 20

# Tabelas #
tmp-table-size                 = 64M
max-heap-table-size            = 256M
table-definition-cache         = 400
table-open-cache               = 80
open-files-limit               = 65535

# Conections #
max-connections                = 500

# Cache de querys (desabilitado por padrao) #
query-cache-type               = 0
query-cache-size               = 0

# Timeouts #
interactive_timeout            = 45
wait_timeout                   = 45

# InnoDB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 32M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 16M
innodb_thread_concurrency      = 0
innodb_buffer_pool_instances   = 1
innodb_io_capacity             = 2000
innodb_read_io_threads         = 64
innodb_write_io_threads        = 64

# Criando logs #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

# Seguranca #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
open_files_limit               = 65535

[mysqld_safe]
open_files_limit               = 1024000

Como não sou especialista..acredito que tenha bastante coisa errada.

O resultado do MySQLTuner está assim:

# sudo perl mysqltuner.pl
 >>  MySQLTuner 1.6.13 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.30-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 12M (Tables: 38)
[--] Data in InnoDB tables: 6M (Tables: 39)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[--] There are 605 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3s (74 q [24.667 qps], 11 conn, TX: 293K, RX: 10K)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 3.7G
[--] Max MySQL memory    : 1.9G
[--] Other process memory: 515.4M
[--] Total buffers: 144.0M global + 2.6M per thread (500 max threads)
[--] P_S Max memory usage: 489M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 641.0M (16.91% of installed RAM)
[OK] Maximum possible memory usage: 1.9G (51.32% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 5% (4/74)
[OK] Highest usage of available connections: 0% (3/500)
[OK] Aborted connections: 0.00%  (0/11)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 30 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 20% (1 on disk / 5 total)
[OK] Table cache hit rate: 91% (72 open / 79 opened)
[OK] Open file limit used: 0% (35/65K)
[OK] Table locks acquired immediately: 100% (139 immediate / 139 locks)
[OK] Binlog cache memory access: 0% ( 0 Memory / 0 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is enabled.
[--] Memory used by P_S: 489.1M
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.9% (9M used / 50M cache)
[OK] Key buffer size / total MyISAM indexes: 48.0M/1.9M
[!!] Read Key buffer hit rate: 94.5% (1K cached / 79 reads)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 16.0M/7.0M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 40.37% (413 used/ 1023 total)
[!!] InnoDB Read buffer efficiency: 86.96% (2762 hits/ 3176 total)
[!!] InnoDB Write Log efficiency: 0% (1 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1

Principalmente no bloco InnoDB eu não consegui uma configuração ideal que se adeque aos requisitos do MySQLTuner.

Caso alguém tenha uma dica mágica e experiente pra me passar agradeço!

[Joel Emanoel]

Em 29/06/2016 at 16:45, Genneral disse:

Olá povo, boa tarde.

Recentemente fiz um upgrade de memória em meu servidor e configurei um novo My.CNF de acordo co mas especificações do MySQLTuner, instalado no servidor.

Meu servidor:

• MySQL: 5.6
• PHP: 5.5
• RAM: 4GB
• CPU: 2 Cores de 2Ghz
• S.O: CentOS 7 x64
• Armazenamento: SSD 40GB
• Atualmente estou com 3 domínios neste servidor (média de 220k de sessões mês), ambos usando MySQL como BD, tenho varnish instalado também..varnish precisa de algumas configurações que estou buscando ajuda (caso queira ajudar o tópico é este)

Atualmente meu my.cnf está assim:

# ATUAL
[mysql]

# Cliente #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# Geral #
user                           = mysql
default-storage-engine         = MyISAM
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 48M
myisam-recover                 = FORCE,BACKUP
sort-buffer                    = 1M
join-buffer					   = 1M
max-join_size                  = 2M

# Armazenamento #
datadir                        = /var/lib/mysql/

# Logs binarios #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# Threads #
thread_cache_size              = 0
max_delayed_threads            = 20

# Tabelas #
tmp-table-size                 = 64M
max-heap-table-size            = 256M
table-definition-cache         = 400
table-open-cache               = 80
open-files-limit               = 65535

# Conections #
max-connections                = 500

# Cache de querys (desabilitado por padrao) #
query-cache-type               = 0
query-cache-size               = 0

# Timeouts #
interactive_timeout            = 45
wait_timeout                   = 45

# InnoDB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 32M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 16M
innodb_thread_concurrency      = 0
innodb_buffer_pool_instances   = 1
innodb_io_capacity             = 2000
innodb_read_io_threads         = 64
innodb_write_io_threads        = 64

# Criando logs #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

# Seguranca #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
open_files_limit               = 65535

[mysqld_safe]
open_files_limit               = 1024000

Como não sou especialista..acredito que tenha bastante coisa errada.

O resultado do MySQLTuner está assim:

# sudo perl mysqltuner.pl
 >>  MySQLTuner 1.6.13 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.30-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 12M (Tables: 38)
[--] Data in InnoDB tables: 6M (Tables: 39)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[--] There are 605 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3s (74 q [24.667 qps], 11 conn, TX: 293K, RX: 10K)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 3.7G
[--] Max MySQL memory    : 1.9G
[--] Other process memory: 515.4M
[--] Total buffers: 144.0M global + 2.6M per thread (500 max threads)
[--] P_S Max memory usage: 489M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 641.0M (16.91% of installed RAM)
[OK] Maximum possible memory usage: 1.9G (51.32% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 5% (4/74)
[OK] Highest usage of available connections: 0% (3/500)
[OK] Aborted connections: 0.00%  (0/11)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 30 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 20% (1 on disk / 5 total)
[OK] Table cache hit rate: 91% (72 open / 79 opened)
[OK] Open file limit used: 0% (35/65K)
[OK] Table locks acquired immediately: 100% (139 immediate / 139 locks)
[OK] Binlog cache memory access: 0% ( 0 Memory / 0 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is enabled.
[--] Memory used by P_S: 489.1M
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.9% (9M used / 50M cache)
[OK] Key buffer size / total MyISAM indexes: 48.0M/1.9M
[!!] Read Key buffer hit rate: 94.5% (1K cached / 79 reads)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 16.0M/7.0M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 40.37% (413 used/ 1023 total)
[!!] InnoDB Read buffer efficiency: 86.96% (2762 hits/ 3176 total)
[!!] InnoDB Write Log efficiency: 0% (1 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1

Principalmente no bloco InnoDB eu não consegui uma configuração ideal que se adeque aos requisitos do MySQLTuner.

Caso alguém tenha uma dica mágica e experiente pra me passar agradeço!

Acredito que no seu caso seria bom a utilização de MariaDB melhora muito, da uma olhada no Google tem vários tutoriais para instalação, utilizo o MariaDB na maioria dos meus sites e no Servidor da empresa e funcionando muito bem.