Genneral Posted June 29, 2016 Share Posted June 29, 2016 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! 0 Quote Link to comment Share on other sites More sharing options...
Joel Emanoel Posted July 2, 2016 Share Posted July 2, 2016 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. 0 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
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.