# curl -L http://mysqltuner.pl/ | perl
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LC_CTYPE = "UTF-8",
LANG = "en_US.UTF-8"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 211k 100 211k 0 0 29678 0 0:00:07 0:00:07 --:--:-- 120k
>> MySQLTuner 1.7.9 - Major Hayden <[email protected]>
>> 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.5.58-cll
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/sal.sallyfashion.com.my.err(1M)
[OK] Log file /var/lib/mysql/sal.sallyfashion.com.my.err exists
[OK] Log file /var/lib/mysql/sal.sallyfashion.com.my.err is readable.
[OK] Log file /var/lib/mysql/sal.sallyfashion.com.my.err is not empty
[OK] Log file /var/lib/mysql/sal.sallyfashion.com.my.err is smaller than 32 Mb
[!!] /var/lib/mysql/sal.sallyfashion.com.my.err contains 42 warning(s).
[!!] /var/lib/mysql/sal.sallyfashion.com.my.err contains 4497 error(s).
[--] 75 start(s) detected in /var/lib/mysql/sal.sallyfashion.com.my.err
[--] 1) 180416 14:40:24 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 180416 14:39:09 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 180416 14:38:01 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 180416 14:37:41 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 180416 14:36:32 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 180416 14:36:22 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 180416 14:36:21 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 180416 14:36:19 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 180416 14:36:17 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 180416 14:36:16 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8 shutdown(s) detected in /var/lib/mysql/sal.sallyfashion.com.my.err
[--] 1) 180416 14:40:22 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 180416 14:39:06 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 180416 14:38:03 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 180416 14:37:46 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 180416 14:36:29 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 180416 14:34:26 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 180416 13:15:01 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 180416 13:14:23 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 13G (Tables: 1763)
[--] Data in InnoDB tables: 90M (Tables: 1783)
[--] Data in MEMORY tables: 3M (Tables: 77)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 20h 35m 29s (81M q [1K qps], 403K conn, TX: 302G, RX: 14G)
[--] Reads / Writes: 71% / 29%
[--] Binary logging is disabled
[--] Physical Memory : 15.6G
[--] Max MySQL memory : 3586.2G
[--] Other process memory: 2.1G
[--] Total buffers: 1.6G global + 3.5G per thread (1024 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 2795.0G (17945.76% of installed RAM)
[!!] Maximum possible memory usage: 3586.2G (23025.23% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (307K/81M)
[OK] Highest usage of available connections: 77% (798/1024)
[OK] Aborted connections: 0.03% (101/403957)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 95.8% (75M cached / 78M selects)
[!!] Query cache prunes per day: 1018432
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 559K sorts)
[!!] Joins performed without indexes: 2211
[!!] Temporary tables created on disk: 40% (142K on disk / 353K total)
[OK] Thread cache hit rate: 99% (2K created / 403K connections)
[!!] Table cache hit rate: 19% (5K open / 25K opened)
[OK] Open file limit used: 44% (4K/11K)
[!!] Table locks acquired immediately: 90%
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (67M used / 67M cache)
[OK] Key buffer size / total MyISAM indexes: 64.0M/4.2G
[OK] Read Key buffer hit rate: 99.6% (3B cached / 14M reads)
[!!] Write Key buffer hit rate: 3.0% (28M cached / 848K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 16
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 512.0M/90.5M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (1.953125 %): 5.0M * 2/512.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 96.23% (290632 hits/ 302029 total)
[!!] InnoDB Write Log efficiency: 86.35% (253 hits/ 293 total)
[OK] InnoDB log waits: 0.00% (0 waits / 40 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.
-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.
-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/lib/mysql/sal.sallyfashion.com.my.err file
Control error line(s) into /var/lib/mysql/sal.sallyfashion.com.my.err file
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Enable the slow query log to troubleshoot bad queries
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (11034) variable
should be greater than table_open_cache (5000)
Optimize queries and/or use InnoDB to reduce lock wait
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Read this before changing innodb_log_file_size and/or innodb_log_files_in_group:
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (=0)
query_cache_type (=0)
query_cache_size (> 512M) [see warning above]
join_buffer_size (> 3.0G, or always use indexes with joins)
table_open_cache (> 5000)
innodb_log_file_size should be (=64M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
O Linux reportará o uso da CPU no topo em termos de porcentagem de um núcleo da CPU. Portanto, se o seu servidor de banco de dados tiver 4 núcleos, você poderá ver até 400% do uso da CPU (4 x 100%). O MySQL é escalável para usar todas as CPUs / Núcleos disponíveis para aumentar a escala. Parece que o único problema real observado é a pegada de memória e a saída fornece algumas opções, embora a chave seja adicionar RAM adicional ao sistema.
você deve estar feliz - ele é dimensionado para uma única unidade de execução. Algumas pessoas estão procurando desesperadamente…)
Sugestões a considerar para sua seção my.cnf-ini [mysqld]
Mysqltuner indica que você pode usar ~ 3Terabytes de RAM e você tem 16G
Leve o seguinte com # ou REMOVE para permitir que os padrões funcionem para você
sort_buffer_size . read_buffer_size . read_rnd_buffer_size . join_buffer_size
todos os acima são pedidos de RAM de conexão.
thread_cache_size=100 # to CAP of 100 suggested in REFMAN to avoid OOM
key_cache_division_limit=50 # from 100 percent for HOT/WARM use split
key_cache_age_threshold=64800 # from 300 seconds to reduce key_reads of 14M
key_cache_block_size=16384 # from 1024 to free more space when needed
query_cache_min_res_unit=512 # from 4096 to store more QC results, reduce prunes
table_open_cache=10000 # from 5000 to support more of 25K opened_tables
innodb_purge_threads=2 # from 1 to reduce contention for purge activities
tmp_table_size AND max_heap_table_size aumenta em 10% para reduzir 40% do disco.
Tags performance mysql