MySQL性能

Question

我在服务器上有两个主表。它们的结构相同，但数据却不同。

表1:2.14亿行，大小为40 214 (25 214索引)

表2:2600万行，大小为5.5GB (3.5gb索引)

我的操作系统是Debian 8 Jessie。

第一个问题。当内存中有缓存时，一切都会非常迅速地工作。如果我清理缓存或重新启动服务器，那么MySQL查询将非常缓慢。MySQL总是将索引存储在内存中并将其作为缓存使用？因为经过一些操作后，select查询非常快。要使服务器正常运行，需要做哪些操作：

如果我使用表#1的副本，那么在它执行期间，正如我所理解的，会发生读取操作，同时将信息缓存到内存中。这是一个免费的-m屏幕，此时启动一个重复的表。

root@ns344370:~# free -m
             total       used       free     shared    buffers     cached
Mem:        128965      76802      52163         31         61      21714
-/+ buffers/cache:      55026      73939
Swap:        56141          0      56141

创建重复表时的结果：

root@ns344370:~# free -m
             total       used       free     shared    buffers     cached
Mem:        128965     126414       2551         31         49      65426
-/+ buffers/cache:      60938      68027
Swap:        56141          0      56141

缓存下的50 of内存。在重复表105秒之前执行查询：

# Query_time: 105.469931  Lock_time: 0.000180 Rows_sent: 41041  Rows_examined: 2097994
SET timestamp=1539135133;
SELECT SQL_CACHE `id`, `currency`, `handLimit`, `date`, `pp1` AS `profit`,`psd1` AS `isSD` FROM `ps_hands` WHERE `p1` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10') 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp2` AS `profit`, `psd2` AS `isSD` FROM `ps_hands` WHERE `p2` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp3` AS `profit`, `psd3` AS `isSD` FROM `ps_hands` WHERE `p3` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp4` AS `profit`, `psd4` AS `isSD` FROM `ps_hands` WHERE `p4` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp5` AS `profit`, `psd5` AS `isSD` FROM `ps_hands` WHERE `p5` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp6` AS `profit`, `psd6` AS `isSD` FROM `ps_hands` WHERE `p6` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp7` AS `profit`, `psd7` AS `isSD` FROM `ps_hands` WHERE `p7` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp8` AS `profit`, `psd8` AS `isSD` FROM `ps_hands` WHERE `p8` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp9` AS `profit`, `psd9` AS `isSD` FROM `ps_hands` WHERE `p9` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')) 
ORDER BY `id` ASC;

在重复表之后，是0.2秒，而不是105秒。

我正在考虑将磁盘从SSHD更改为NVMe M2 (快5倍)。但我将有另外4-5个表，每个50 in，在这种情况下，我只是没有足够的RAM。

我有128 RAM内存。除了这个项目，还有一个项目。其中分配了50 On。其余的用于此项目和MySQL。我使用MyISAM而不是InnoDB，因为在站点上只选择和插入操作。在一台带有M.2磁盘的家用机器上，InnoDB也慢了20倍.

下面是mysql配置文件：

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

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

[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
skip-external-locking
query_cache_type = DEMAND

key_buffer_size = 55G
sort_buffer_size = 512M
read_buffer_size = 128M

max_allowed_packet = 32M
thread_stack        = 192K
thread_cache_size       = 4096

#MySQL Tuner
max_heap_table_size = 128M
tmp_table_size = 128M
table_open_cache = 4096

myisam-recover         = BACKUP
max_connections        = 2000
table_cache            = 2048
thread_concurrency     = 17
query_cache_limit   = 128M
query_cache_size  = 256M

log_slow_queries  = /var/log/mysql/mysql-slow.log
long_query_time   = 1
#log-queries-not-using-indexes

expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name

innodb_buffer_pool_size = 512M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 256M
!includedir /etc/mysql/conf.d/

表DDL：

CREATE TABLE `ps_hands` (
  `id` bigint(14) NOT NULL DEFAULT '0',
  `currency` tinyint(1) NOT NULL,
  `handLimit` smallint(5) NOT NULL DEFAULT '0',
  `date` date NOT NULL,
  `maxPlayers` tinyint(1) NOT NULL,
  `p1` mediumint(7) NOT NULL DEFAULT '0',
  `p2` mediumint(7) NOT NULL DEFAULT '0',
  `p3` mediumint(7) NOT NULL DEFAULT '0',
  `p4` mediumint(7) NOT NULL DEFAULT '0',
  `p5` mediumint(7) NOT NULL DEFAULT '0',
  `p6` mediumint(7) NOT NULL DEFAULT '0',
  `p7` mediumint(7) NOT NULL DEFAULT '0',
  `p8` mediumint(7) NOT NULL DEFAULT '0',
  `p9` mediumint(7) NOT NULL DEFAULT '0',
  `pp1` mediumint(7) NOT NULL,
  `pp2` mediumint(7) NOT NULL,
  `pp3` mediumint(7) NOT NULL,
  `pp4` mediumint(7) NOT NULL,
  `pp5` mediumint(7) NOT NULL,
  `pp6` mediumint(7) NOT NULL,
  `pp7` mediumint(7) NOT NULL,
  `pp8` mediumint(7) NOT NULL,
  `pp9` mediumint(7) NOT NULL,
  `psd1` tinyint(1) NOT NULL,
  `psd2` tinyint(1) NOT NULL,
  `psd3` tinyint(1) NOT NULL,
  `psd4` tinyint(1) NOT NULL,
  `psd5` tinyint(1) NOT NULL,
  `psd6` tinyint(1) NOT NULL,
  `psd7` tinyint(1) NOT NULL,
  `psd8` tinyint(1) NOT NULL,
  `psd9` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `maxPlayers` (`maxPlayers`),
  KEY `p1_handLimit` (`p1`,`handLimit`),
  KEY `p2_handLimit` (`p2`,`handLimit`),
  KEY `p3_handLimit` (`p3`,`handLimit`),
  KEY `p4_handLimit` (`p4`,`handLimit`),
  KEY `p5_handLimit` (`p5`,`handLimit`),
  KEY `p6_handLimit` (`p6`,`handLimit`),
  KEY `p7_handLimit` (`p7`,`handLimit`),
  KEY `p8_handLimit` (`p8`,`handLimit`),
  KEY `p9_handLimit` (`p9`,`handLimit`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

查询解释：

+------+--------------+--------------------------+------+---------------+--------------+---------+-------+--------+----------------+
| id   | select_type  | table                    | type | possible_keys | key          | key_len | ref   | rows   | Extra          |
+------+--------------+--------------------------+------+---------------+--------------+---------+-------+--------+----------------+
|    1 | PRIMARY      | ps_hands                 | ref  | p1_handLimit  | p1_handLimit | 3       | const | 182239 | Using where    |
|    2 | UNION        | ps_hands                 | ref  | p2_handLimit  | p2_handLimit | 3       | const | 290077 | Using where    |
|    3 | UNION        | ps_hands                 | ref  | p3_handLimit  | p3_handLimit | 3       | const | 273151 | Using where    |
|    4 | UNION        | ps_hands                 | ref  | p4_handLimit  | p4_handLimit | 3       | const | 248191 | Using where    |
|    5 | UNION        | ps_hands                 | ref  | p5_handLimit  | p5_handLimit | 3       | const | 255685 | Using where    |
|    6 | UNION        | ps_hands                 | ref  | p6_handLimit  | p6_handLimit | 3       | const | 362813 | Using where    |
|    7 | UNION        | ps_hands                 | ref  | p7_handLimit  | p7_handLimit | 3       | const | 358672 | Using where    |
|    8 | UNION        | ps_hands                 | ref  | p8_handLimit  | p8_handLimit | 3       | const | 264515 | Using where    |
|    9 | UNION        | ps_hands                 | ref  | p9_handLimit  | p9_handLimit | 3       | const | 221512 | Using where    |
| NULL | UNION RESULT | <union1,2,3,4,5,6,7,8,9> | ALL  | NULL          | NULL         | NULL    | NULL  | NULL   | Using filesort |
+------+--------------+--------------------------+------+---------------+--------------+---------+-------+--------+----------------+

我在p1-p9中有相同的值，它是一个玩家ID。一个玩家可以有20行，也可以有2百万行。通常情况下，我完全通过p1-p9或p1-p9和handLimit获得数据。有时涉及日期，但它在每一行，我不认为有任何理由索引日期。怎么做？

Answer 1

关于您的my.cnf 米舍尔德分段费率按Second=RPS计算的建议

20181011 05 00 from mysqlservertuning.com
review your code for presence of CLOSE() at end of session to release threads_connected
innodb_log_file_size=32M  # from 5M should NEVER be less than buffer size
    special handling required on this VERY old v5.5.39 see REFMAN
max_connections=200  # from 500 until you NEED more connections
innodb_io_capacity=15000  # from 200 to use more of your IOPS capacity
read_buffer_size=128K  # from 128M to reduce handler_read_rnd_next RPS dramatically
sort_buffer_size=2M  # from ~ 512M to reduce PER connection RAM requirement
max_write_lock_count=16  # from ~4 billion to allow RD after nn lock requests
table_open_cache=10000  # from 2048 to reduce opened_tables count
table_definition_cache=3000  # from 400 to reduce opened_table_definitions count
open_files_limit=24000  # from 10000 to reduce opened_files RPS
key_buffer_size=12G  # from 24G because less than 10% used
key_cache_age_threshold=7200  # from 300 seconds to minimize AGE OUT data
key_cache_division_limit=50  # from 100 percent for Hot/Warm caches
key_cache_block_size=16384  # from 1024 to reduce CPU cycles for mgmt
query_cache_type=0  # from DEMAND for OFF less than 1% are CACHED
query_cache_size=0  # from 256M to conserve RAM and CPU cycles for mgmt

其他观察到的困难

这一天每45秒回滚一次；每小时299次的慢速查询应该被清除。

如需更多建议，请查看我的个人资料，网络资料，包括我的Skype ID和联系，请。