SQL查询在新服务器（5.7.19），vs旧（5.5.52）

我正在将我的网站从旧服务器移到新服务器。 因此，我们正在从MySQL 5.5.52移到5.7.19 。 但是，速度差异很大。 我只是偶然注意到它是我们的脚本之一，通常需要2秒钟来build立一个类别，新的一个是7！

新服务器是Linode 12GB RAM，6核心服务器。

testing查询：

SELECT 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，glinks_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.GoogleLong，glinks_Category.CatRoot，glinks_Category.Has_New_Links，glinks_Category.ClosestLinkIDs，glinks_Category.Payment_Mode，GL inks_Category.Number_of_Links，glinks_Category.Multilang_onlinereservation_Name，glinks_Category.Payment_Description，glinks_Category.Template_Set_BAK，glinks_CatLinks.CategoryID FROM glinks_CatLinks，glinks_Category，glinks_Links WHERE glinks_CatLinks.LinkID = glinks_Links.ID AND glinks_CatLinks.CategoryID = glinks_Category.ID AND（glinks_CatLinks.LinkID = '139134' ）

• MySQL表校验和 – MySQL转储之前还是之后？
• 最高命令 – 整体CPU使用率比进程的CPU使用率低
• 将本地MySQL表导出到远程数据库
• 不能摆脱：错误2002（HY000）：不能通过套接字'/var/run/mysqld/mysqld.sock'连接到本地MySQL服务器（2）
• 根据数据库redirect所有电子邮件

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

更简单的事情：

SELECT * FROM glinks_Link_Images WHERE link_id_fk = 32934 ORDER BY sort_order ASC

新服务器需要0.0006 ，新服务器需要0.0004 。 新的服务器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 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="" 

我错过了什么？ 我认为在一个更好的spec机器上使用更新版本的mySQL会快很多，所以我们的configuration中必须有一些东西 – 我只是不太确定从哪里开始！

顺便说一句：这些表格是MyISAM格式。 我已经尝试了它作为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. 

25/9/17 ：根据要求，这里是更新的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/