Jump to content

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


Genneral

Recommended Posts

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.

×
×
  • Create New...

Important Information

Do you agree with our terms?