Mysql
EC2 T2 Small 2GB RAM MySQL 優化建議
我正在嘗試為我們的伺服器優化我們的 MySQL 配置。我認為 MySQL 需要進一步配置的原因是因為我的日誌顯示我們的 SELECT 查詢在某些類別中需要很長時間（10,000+ ms）。
我們的伺服器是在 Amazon EC2 t2.small 實例（具有 2 GB RAM）上執行 Ubuntu 14.04 的 Linux 64 位。
我執行 MySQLTuner 1.6.4 並得到以下結果
-------- Performance Metrics ------------------------------------------------- [--] Up for: 9d 2h 35m 38s (8M q [11.226 qps], 234K conn, TX: 63B, RX: 1B) [--] Reads / Writes: 95% / 5% [--] Binary logging is enabled (GTID MODE: OFF) [--] Total buffers: 540.0M global + 1.0M per thread (100 max threads) [OK] Maximum reached memory usage: 583.6M (29.18% of installed RAM) [OK] Maximum possible memory usage: 643.9M (32.19% of installed RAM) [OK] Slow queries: 0% (25/8M) [OK] Highest usage of available connections: 42% (42/100) [OK] Aborted connections: 0.00% (2/234145) [OK] Query cache efficiency: 57.9% (4M cached / 8M selects) [!!] Query cache prunes per day: 11915 [OK] Sorts requiring temporary tables: 0% (877 temp sorts / 288K sorts) [!!] Joins performed without indexes: 7884 [!!] Temporary tables created on disk: 39% (86K on disk / 221K total) [OK] Thread cache hit rate: 99% (42 created / 234K connections) [OK] Table cache hit rate: 67% (984 open / 1K opened) [OK] Open file limit used: 11% (250/2K) [OK] Table locks acquired immediately: 100% (5M immediate / 5M locks) [OK] Binlog cache memory access: 99.77% ( 178406 Memory / 178825 Total) -------- MyISAM Metrics ------------------------------------------------------ [!!] Key buffer used: 18.3% (12M used / 67M cache) [OK] Key buffer size / total MyISAM indexes: 64.0M/2.3M [OK] Read Key buffer hit rate: 100.0% (6M cached / 14 reads) [OK] Write Key buffer hit rate: 100.0% (431K cached / 73 writes) -------- InnoDB Metrics ------------------------------------------------------ [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 384.0M/202.2M [OK] InnoDB buffer pool instances: 1 [!!] InnoDB Used buffer: 62.73% (15416 used/ 24575 total) [OK] InnoDB Read buffer efficiency: 100.00% (38241101927 hits/ 38241112309 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 343581 writes) -------- ThreadPool Metrics -------------------------------------------------- [--] ThreadPool stat is disabled. -------- AriaDB Metrics ------------------------------------------------------ [--] AriaDB is disabled. -------- TokuDB Metrics ------------------------------------------------------ [--] TokuDB is disabled. -------- Galera Metrics ------------------------------------------------------ [--] Galera is disabled. -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] This is a standalone server.. -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Variables to adjust: query_cache_size (> 40M) join_buffer_size (> 296.0K, or always use indexes with joins) tmp_table_size (> 40M) max_heap_table_size (> 130M)我的
my.cnf配置如下[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # GENERAL # user = mysql default_storage_engine = InnoDB pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking # MyISAM # key_buffer_size = 64M myisam_recover = FORCE,BACKUP # DATA STORAGE # datadir = /var/lib/mysql/ # BINARY LOGGING # log_bin = /var/lib/mysql/mysql-bin expire_logs_days = 14 # CACHES AND LIMITS # tmp_table_size = 40M max_heap_table_size = 130M query_cache_type = 1 query_cache_size = 40M query_cache_limit = 10M thread_cache_size = 50 open_files_limit = 1500 table_definition_cache = 400 table_open_cache = 1000 max_allowed_packet = 16M thread_cache_size = 50 max_connections = 100 join_buffer_size = 296k sort_buffer_size = 256k read_buffer_size = 128k read_rnd_buffer_size = 128k # INNODB # innodb_flush_method = O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 64M innodb_flush_log_at_trx_commit = 1 innodb_file_per_table = 1 innodb_buffer_pool_size = 384M innodb_buffer_pool_instances = 1 innodb_log_buffer_size = 4M innodb_read_io_threads = 16 innodb_write_io_threads = 16 # LOGGING # log_error = /var/log/mysql/error.log log_slow_queries = /var/log/mysql/mysql-slow.log log_warnings = 0 long_query_time = 10 expire_logs_days = 10 max_binlog_size = 100M binlog_format = MIXED [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/我只是想知道我是否遺漏了可以進一步配置的任何內容。
我們伺服器的最佳調整是什麼？
您可以增加 innodb_buffer_pool_size 和 innodb_log_file_size 以加快查詢處理速度。
您也可以使用https://tools.percona.com/wizard 根據硬體配置和工作負載找出 Mysql 引擎的最佳配置。
我建議增加記憶體限制和相關參數以處理更快的選擇操作。
雖然 Mysqltuner 腳本足夠高效，可以為您提供基於伺服器配置的參數調整的最佳建議。
如果你有一個伺服器就需要增加授權包的數量並且那個會為你服務的很多應用一起puedad發送資訊，如果要使用斷開連接的程序再發送’replication’需要修改這個選項。max_allowed_packet = 512M