About this question
I have a powerful dedicated server.
Intel I7-6700K -
64GB DDR4 2400 MHz
1x480GB SSD
running mysql server along with nginx,php
innodb-ft-min-token-size = 1
innodb-ft-enable-stopword = 0
innodb_buffer_pool_size = 40G
max_connections = 2000
[deploy@ns540545 ~]$ free -h
total used free shared buff/cache available
Mem: 62G 45G 11G 107M 6.4G 16G
Swap: 2.0G 1.4G 640M
it was expensive so i got another dedicated server for cost cutting let's call it not-so powerful dedicated server
Intel i3-2130
8GB DDR3 1333 MHz
2TB
running mysql server along with nginx,php
innodb-ft-min-token-size = 1
innodb-ft-enable-stopword = 0
innodb_buffer_pool_size = 4G
max_connections = 2000
[root@privateserver deploy]# free -h
total used free shared buff/cache available
Mem: 7.7G 7.5G 73M 24M 150M 79M
Swap: 39G 7.8G 32G
I moved the database from a powerful server to a not-so-powerful server. I can feel slight performance degradation while running simple queries which is fine, but this one query which used to take 2 minutes on a powerful server now it takes around 26.6525 hours and counting on a not-so powerful server.
UPDATE content a JOIN peers_data b ON a.hash = b.hash SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated
More info about tables which are exactly same on both the dedicated server
CREATE TABLE `peers_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`hash` char(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`seeders` int(11) NOT NULL DEFAULT '0',
`leechers` int(11) NOT NULL DEFAULT '0',
`is_updated` int(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`hash`),
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `content` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`content_id` int(11) unsigned NOT NULL DEFAULT '0',
`hash` char(40) CHARACTER SET ascii NOT NULL DEFAULT '',
`title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`tags` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`category` smallint(3) unsigned NOT NULL DEFAULT '0',
`category_name` varchar(50) CHARACTER SET ascii COLLATE ascii_bin DEFAULT '',
`sub_category` smallint(3) unsigned NOT NULL DEFAULT '0',
`sub_category_name` varchar(50) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`size` bigint(20) unsigned NOT NULL DEFAULT '0',
`seeders` int(11) unsigned NOT NULL DEFAULT '0',
`leechers` int(11) unsigned NOT NULL DEFAULT '0',
`upload_date` datetime DEFAULT NULL,
`uploader` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
`uploader_level` varchar(10) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
`comments_count` int(11) unsigned NOT NULL DEFAULT '0',
`is_updated` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`content_id`),
UNIQUE KEY `unique` (`id`) USING BTREE,
KEY `hash` (`hash`),
KEY `uploader` (`uploader`),
KEY `sub_category` (`sub_category`),
KEY `category` (`category`),
KEY `title_index` (`title`),
KEY `category_sub_category` (`category`,`sub_category`),
KEY `seeders` (`seeders`),
KEY `uploader_sub_category` (`uploader`,`sub_category`),
KEY `upload_date` (`upload_date`),
KEY `uploader_upload_date` (`uploader`,`upload_date`),
KEY `leechers` (`leechers`),
KEY `size` (`size`),
KEY `uploader_seeders` (`uploader`,`seeders`),
KEY `uploader_size` (`uploader`,`size`),
FULLTEXT KEY `title` (`title`),
FULLTEXT KEY `tags` (`tags`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
mysql> explain UPDATE content a JOIN peers_data b ON a.hash = b.hash SET a.seeders = b.seeders, a.leechers = b.leechers, a.is_updated = b.is_updated ;
+----+-------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | UPDATE | a | NULL | ALL | NULL | NULL | NULL | NULL | 4236260 | 100.00 | NULL |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 160 | func | 1 | 100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
2 rows in set (0.00 sec)
records in peers_data 6,367,417
records in content 4,236,268
How can i speed up the above update join query ? i was expecting about 1 hour on not-so powerful server, but 26 hours+ is too much.
what am I doing wrong ? or missing here ?
I have tried to compensate for RAM on a not-so-powerful server by setting 32 GB + swap space. Is innodb buffer pool 4 gb too much ?