I have 2 main tables on the server. They have the same structure, but hold different data.
Table #1: 214 million rows, size 40GB (25gb indexes)
Table #2: 26 million rows, size 5.5GB (3.5gb indexes)
My OS is Debian 8 Jessie.
The first problem. Everything works very quickly when there is a cache in memory. If I clean cache or reboot the server, then MySQL queries are very slow. MySQL stores indexes in memory and takes it as a cache, always? Because after some manipulations, the select queries is very fast. What manipulations need to be done to make the server fly:
If I using duplicate of table #1, then during its execution, as I understand it, a read operation occurs and at the same time the information is cached into memory. Here is a free -m screen at the moment of launching a duplicate table.
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
The result, when the duplicate table was created:
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
50GB of RAM under the cache. Execution of the query before the duplicate table 105 seconds:
# 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;
After the duplicate table, it's 0.2 seconds instead 105 secs.
I am thinking of changing the disk from SSHD to NVMe M2 (5x faster). But I will have another 4-5 tables of 50GB each, in this case, I just do not have enough RAM.
I have 128GB RAM. In addition to this project, there is one more project. On which 50GB are allocated. The rest is used for this project and MySQL. I use MyISAM and not InnoDB, because on the site only selects and inserts operations. InnoDB is 20 times slower, checked on a home machine, too, with an M.2 disk....
Here is the mysql config file:
[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/
Table 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;
Query explain:
+------+--------------+--------------------------+------+---------------+--------------+---------+-------+--------+----------------+
| 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 |
+------+--------------+--------------------------+------+---------------+--------------+---------+-------+--------+----------------+
I have same values in p1-p9 , its a player IDs. One player can have 20 rows, or can have 2 million rows. Most often I get the data exactly by p1-p9 or p1-p9 and handLimit. Sometimes a date is involved, but it’s in every row and I don’t see any reason to index date. How to be?