与旧服务器 (5.5.52) 相比，新服务器 (5.7.19) 上的 SQL 查询速度慢得多

Question

我正在将我的网站从旧服务器迁移到新服务器。因此，我们将从 mySQL 迁移5.5.52到5.7.19。但是，速度差异很大。我只是偶然注意到了这一点，因为我们的一个脚本通常需要 2 秒钟才能建立一个类别，但在新脚本上却需要 7 秒钟！

新的服务器是 Linode12GBRAM，6 核服务器。

测试查询：

选择 glinks_Links.*、glinks_Category.linking、glinks_Category.LngNew、glinks_Category.Multilang_espanol_Name、glinks_Category.GoogleLat、glinks_Category.Town_Name、glinks_Category.HeaderFrench、glinks_Category.Category_Template、glinks_Category.GoogleMapZoom、glinks_Category.Multilang_english_Name、glinks _Category.Newest_Link、glinks_Category.CategoryFind、glinks_Category.newURLFormat、glinks_Category.FooterFrench、glinks_Category.Meta_Keywords、glinks_Category.Header、glinks_Category.Region_Cat_IDs_for_Map、glinks_Category.RelatedVilleLinks、glinks_Category.Meta_Description、glinks_Category.Footer、g links_Category.isRegion、glinks_Category.Multilang_mobi_Name、glinks_Category.ShowMap、glinks_Category.Full_Name、glinks_Category.Direct_Links、glinks_Category.LatNew、glinks_Category.FatherID、glinks_Category.CatDepth、glinks_Category.Has_Changed_Links、glinks_Category.Name、glinks_Category.GoogleLon g,glinks_Category.CatRoot,glinks_Category.Has_New_Links,glinks_Category.ClosestLinkIDs,glinks_Category.Payment_Mode,glinks_Category.Number_of_Links,glinks_Category.Multilang_onlinereservation_Name,glinks_Category.Payment_Description,glinks_Category.Template_Set_BAK,glinks_CatLinks.CategoryID 来自 glinks_CatLinks,glinks_Category,glinks_Links 其中 glinks_CatLinks.LinkID = glinks_Links.ID AND glinks_CatLinks.CategoryID = glinks_Category.ID AND (glinks_CatLinks.LinkID = '139134')

需要0.0015在新服务器上，0.0010在旧的那个上。

更简单的事情：

从 glinks_Link_Images 中选择 *，其中 link_id_fk = 32934 按 sort_order ASC 排序

需要0.0006在新服务器上，0.0004在新的服务器上。我的cnf文件如下所示：

[client]
port=3306
socket=/var/run/mysqld/mysqld.sock

[mysqld_safe]
socket=/var/run/mysqld/mysqld.sock

[mysqld]
user=mysql
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
log_error=/var/log/mysql/error.log

#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

symbolic-links=0

skip-external-locking
#key_buffer_size = 256M
tmp_table_size=256M
key_buffer_size=2G
max_allowed_packet = 64M
#table_open_cache = 256
max_heap_table_size=256M
table_open_cache=1024
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
innodb_buffer_pool_size=134217728

join_buffer_size=512k

#innodb_use_native_aio = 0
innodb_file_per_table

max_connections=500
max_user_connections=50
wait_timeout=500
interactive_timeout=500
long_query_time=5

!includedir /etc/mysql/conf.d/

...还有旧的：

[mysqld]
innodb_file_per_table=1
local-infile=0
thread_cache_size=4
max_tmp_tables=32
query_cache_size=8M
max_delayed_threads=20
#table_open_cache=256
#skip-networking
query_cache_type=1
max_connections=500
default-storage-engine=MyISAM
open_files_limit=20558
sql-mode=""

# ones i've added
key_buffer_size=2G
tmp_table_size=256M
max_heap_table_size=256M
table_open_cache=1024
join_buffer_size=512k
interactive_timeout=500
wait_timeout=500
max_allowed_packet=1073741824
innodb_buffer_pool_size=134217728

#[mysqld]
#sql-mode=""

我遗漏了什么？我原本以为在规格更好的机器上使用较新版本的 mySQL 会快得多，所以我们的配置中一定出了问题 - 我只是不太确定从哪里开始！

顺便提一句：表格如下：数据库管理系统格式。我曾尝试过数据库引擎InnoDB，但似乎并没有什么区别。

更新：以下是 mysqltuner 运行的输出：

 >>  MySQLTuner 1.7.4 - 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] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.7.19-0ubuntu0.16.04.1
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(169K)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[!!] /var/log/mysql/error.log contains 18 warning(s).
[OK] /var/log/mysql/error.log doesn't contain any error.
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 45M (Tables: 61)
[--] Data in MyISAM tables: 8G (Tables: 206)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'admin_default@%' hasn't specific host restriction.
[!!] User 'chambres_links@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 18h 24m 23s (36M q [153.362 qps], 5K conn, TX: 189G, RX: 6G)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is disabled
[--] Physical Memory     : 11.7G
[--] Max MySQL memory    : 2.7G
[--] Other process memory: 491.1M
[--] Total buffers: 2.4G global + 1.4M per thread (214 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 2.4G (20.55% of installed RAM)
[OK] Maximum possible memory usage: 2.7G (22.96% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/36M)
[OK] Highest usage of available connections: 1% (3/214)
[!!] Aborted connections: 82.87%  (4722/5698)
[!!] 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.
[!!] Query cache efficiency: 0.0% (0 cached / 34M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 4M sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 14% (32K on disk / 223K total)
[OK] Thread cache hit rate: 99% (3 created / 5K connections)
[!!] Table cache hit rate: 16% (342 open / 2K opened)
[OK] Open file limit used: 23% (240/1K)
[OK] Table locks acquired immediately: 99% (41M immediate / 41M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 21.9% (470M used / 2B cache)
[OK] Key buffer size / total MyISAM indexes: 2.0G/1.6G
[OK] Read Key buffer hit rate: 100.0% (562M cached / 53K reads)
[!!] Write Key buffer hit rate: 71.3% (8M cached / 5M writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/45.9M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.99% (2525768 hits/ 2526144 total)
[!!] InnoDB Write Log efficiency: 66.67% (2 hits/ 3 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5 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.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysql/error.log file
    Restrict Host for user@% to user@SpecificDNSorIp
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: 
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache (400)
    Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: 
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    table_open_cache (> 400)
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

2017 年 9 月 25 日：根据要求，这是更新的 my.cnf 文件：

[client]
port=3306
socket=/var/run/mysqld/mysqld.sock

[mysqld_safe]
socket=/var/run/mysqld/mysqld.sock

[mysqld]
user=mysql
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
log_error=/var/log/mysql/error.log

#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

symbolic-links=0

skip-external-locking
#key_buffer_size = 256M
tmp_table_size=256M
key_buffer_size=2G
max_allowed_packet = 64M
#table_open_cache = 256
max_heap_table_size=256M
table_open_cache=1024
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
innodb_buffer_pool_size=134217728

join_buffer_size=512k

#innodb_use_native_aio = 0
innodb_file_per_table

max_connections=500
max_user_connections=50
wait_timeout=500
interactive_timeout=500
long_query_time=5

!includedir /etc/mysql/conf.d/

Answer 1

您添加的 sort_buffer_size、read_buffer_size、read_rnd_buffer_size 需要删除。这样才能让更高性能的默认值为您工作。考虑添加 join_buffer_size = 512K，这在历史上对您很有帮助。我会查看更多详细信息，以后可能会有更多建议。

Answer 2

要调整的变量：是需要考虑的事情。在手册中搜索列出的名称，看看变量起什么作用。即使您不在“生产”中，您的“构建类别”现在是否比 7 秒更好？期待重新发布 MySQLTuner 结果，即使是来自预生产的结果。