MySQL对EC2 T2小2GB内存的优化建议

Question

我正在尝试优化我们的服务器的MySQL配置。我认为需要进一步配置MySQL的原因是我的日志显示我们的SELECT查询在某些类别中花费了很长的时间(10,000+ ms)。

我们的服务器是一个运行Ubuntu14.04的Linux64位服务器，运行在亚马逊EC2 t2.小实例上(内存为2GB)。

我运行了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/

我只是想知道我是否遗漏了任何我可以进一步配置的东西。

我们的服务器最好的调优是什么？

Answer 1

您可以增加innodb_buffer_pool_size和innodb_log_file_size，以加快查询处理。

此外，您还可以使用https://tools.percona.com/wizard来根据H/W配置和工作负载找到Mysql引擎的最佳配置。

我建议增加缓存限制和相关参数，以处理更快的选择操作。

尽管Mysqltuner脚本足够高效，可以为您提供基于服务器配置的参数调整的最佳建议。

Answer 2

如果您有一个服务器是必要的，以增加授权包的数量，这将为您的许多应用程序一起提供puedad发送信息，如果他们想使用断开连接的程序，然后发送‘复制’需要修改此选项。max_allowed_packet =512米