Menu
We have noticed some inconsistencies in our MySQL performance for query times that we feel cannot be explained by just server load. Some queries seem to be much efficient than others, despite having a similar setup.
Edit: Since opening this question, our database crashed (for unknown reasons at this moment, RDS teams are investigating), and after a reboot the issue is no longer reproducible and the queries are the same speed. I still would like to know what was wrong because the issue may come back, but perhaps we will never find out...
Continue original question: To test this, I made a near-copy of a table. The performance (query time) against the copied table is dramatically worse than the source, despite the same execution path, same data, and very similar table schematics.
In this example, we have the following 3 tables:
offers_clicks
offers
offers_new
"offers_new" is a test table that has a limited number of field (only "id" and 'status" fields), but is otherwise having the same structure as "offers" table (which is also having "id" and "status" field, but also many other fields). Here are the table schematics:
Offers Table:
| offers | CREATE TABLE `offers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`status` tinyint(4) NOT NULL DEFAULT '0',
`user_id` int(11) NOT NULL DEFAULT '0',
.....many_other_fields.....
KEY `ix_public_view_key` (`public_view_key`),
FULLTEXT KEY `name` (`name`,`internal_name`)
) ENGINE=InnoDB AUTO_INCREMENT=18425582 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
Offers New table:
| offers_new | CREATE TABLE `offers_new` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`status` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18423831 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
We then did an INSERT SELECT from "offers" table to "offers_new" table:
INSERT INTO offers_new (id, status) SELECT id, status FROM offers;
After this, we started to run some test queries. As you can see, the query times to "offers_new" table is about 10 times slower than "offers" table.
mysql> SELECT COUNT(*) FROM offers_clicks, offers_new WHERE offers_new.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00';
+----------+
| COUNT(*) |
+----------+
| 15472 |
+----------+
1 row in set (26.04 sec)
mysql> SELECT COUNT(*) FROM offers_clicks, offers WHERE offers.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00';
+----------+
| COUNT(*) |
+----------+
| 15472 |
+----------+
1 row in set (2.90 sec)
mysql> SELECT COUNT(*) FROM offers_clicks, offers_new WHERE offers_new.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00';
+----------+
| COUNT(*) |
+----------+
| 15472 |
+----------+
1 row in set (28.07 sec)
mysql> SELECT COUNT(*) FROM offers_clicks, offers WHERE offers.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00';
+----------+
| COUNT(*) |
+----------+
| 15472 |
+----------+
1 row in set (2.26 sec)
Please note that the execution path is exactly the same for both queries:
mysql> explain SELECT COUNT(*) FROM offers_clicks, offers_new WHERE offers_new.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00';
+----+-------------+---------------+-----------------------------------------------------------------------+--------+--------------------+---------+---------+--------------------------------------------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+
| 1 | SIMPLE | offers_clicks | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19 | range | pts_id,date,date_2 | date | 5 | NULL | 15472 | 100.00 | Using index condition |
| 1 | SIMPLE | offers_new | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dejong_pointstoshop.offers_clicks.offer_id | 1 | 100.00 | Using index |
+----+
2 rows in set, 1 warning (0.00 sec)
mysql> explain SELECT COUNT(*) FROM offers_clicks, offers WHERE offers.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00';
+----+-------------+---------------+-----------------------------------------------------------------------+--------+--------------------+---------+---------+--------------------------------------------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+-----------------------------------------------------------------------+--------+--------------------+---------+---------+--------------------------------------------+-------+----------+-----------------------+
| 1 | SIMPLE | offers_clicks | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19 | range | pts_id,date,date_2 | date | 5 | NULL | 15472 | 100.00 | Using index condition |
| 1 | SIMPLE | offers | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dejong_pointstoshop.offers_clicks.offer_id | 1 | 100.00 | Using index |
+----+-------------+---------------+-----------------------------------------------------------------------+--------+--------------------+---------+---------+--------------------------------------------+-------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)
Steps we have taken after this:
We are using MySQL 8.0.15
It is worth noting that our production system is under high CPU load. However, this should not explain different query times for queries that are pretty much identical. We ran it dozens of times now with he same results.
It is also worth noting that "offers" table is a production table, so it's being updated / queried against frequently. "offers_new", which is the slow table, is not.
Edits based on comments:
mysql> SELECT COUNT(*) FROM offers_new;
+----------+
| COUNT(*) |
+----------+
| 5093127 |
+----------+
1 row in set (0.13 sec)
mysql> SELECT COUNT(*) FROM offers;
+----------+
| COUNT(*) |
+----------+
| 5107742 |
+----------+
1 row in set (2.54 sec)
The counts are very close. Offers is a live table, so since my tests yesterday, new entries have been added to the table. The counts are still close though. Interestingly, the COUNT(*) on "offers_new" is significantly faster than "offers", so the opposite from the query results we made this question about! Count on "offers_new" is about 0.1/0.2 seconds, count on "offers" table is a few seconds (ranges between 2-6s). I ran it about 10 times each to make sure it wasn't just server load, and it was consistently slower. I assume that this has something to do with the number of columns which is much higher for "offers" table. Either way, it's interesting to see the opposite results of the other query...
Here is the profiling:
mysql> SHOW PROFILES;
+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+---+
| 1 | 26.03997750 | SELECT COUNT(*) FROM offers_clicks, offers_new WHERE offers_new.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00' |
| 2 | 2.89890600 | SELECT COUNT(*) FROM offers_clicks, offers WHERE offers.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00' |
| 3 | 28.07228225 | SELECT COUNT(*) FROM offers_clicks, offers_new WHERE offers_new.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00' |
| 4 | 2.25160675 | SELECT COUNT(*) FROM offers_clicks, offers WHERE offers.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00' |
+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql> SHOW PROFILE ALL FOR QUERY 1;
+---+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+---+
| starting | 0.000364 | 0.016000 | 0.000000 | 3 | 3 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | NULL | NULL | NULL |
| Executing hook on transaction | 0.000134 | 0.008000 | 0.000000 | 6 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1100 |
| starting | 0.000128 | 0.008000 | 0.000000 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1102 |
| checking permissions | 0.000173 | 0.008000 | 0.000000 | 3 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 1899 |
| checking permissions | 0.000182 | 0.012000 | 0.000000 | 65 | 33 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 1899 |
| Opening tables | 0.003432 | 0.176000 | 0.012000 | 556 | 224 | 0 | 0 | 0 | 0 | 0 | 20 | 0 | open_tables | sql_base.cc | 5586 |
| init | 0.000235 | 0.012000 | 0.000000 | 25 | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 555 |
| System lock | 0.000151 | 0.008000 | 0.000000 | 62 | 17 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 332 |
| optimizing | 0.000171 | 0.008000 | 0.000000 | 32 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 212 |
| statistics | 0.001255 | 0.068000 | 0.000000 | 230 | 125 | 0 | 24 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 425 |
| preparing | 0.000166 | 0.012000 | 0.000000 | 43 | 19 | 0 | 0 | 0 | 0 | 0 | 10 | 0 | optimize | sql_optimizer.cc | 499 |
| executing | 0.000134 | 0.004000 | 0.000000 | 27 | 16 | 0 | 0 | 0 | 0 | 0 | 10 | 0 | exec | sql_executor.cc | 197 |
| Sending data | 26.032492 | 999.999999 | 67.940000 | 5361975 | 1420548 | 227416 | 1584568 | 0 | 0 | 0 | 299459 | 0 | exec | sql_executor.cc | 273 |
| end | 0.000325 | 0.012000 | 0.000000 | 7 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 608 |
| query end | 0.000115 | 0.004000 | 0.000000 | 11 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4581 |
| waiting for handler commit | 0.000118 | 0.008000 | 0.000000 | 34 | 13 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ha_commit_trans | handler.cc | 1533 |
| closing tables | 0.000118 | 0.004000 | 0.000000 | 23 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4627 |
| freeing items | 0.000163 | 0.008000 | 0.000000 | 4 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5256 |
| cleaning up | 0.000125 | 0.008000 | 0.000000 | 5 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 2108 |
+---+
19 rows in set, 1 warning (0.00 sec)
mysql> SHOW PROFILE ALL FOR QUERY 2;
+---+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+---+
| starting | 0.000364 | 0.012000 | 0.000000 | 41 | 24 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
| Executing hook on transaction | 0.000137 | 0.008000 | 0.000000 | 40 | 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1100 |
| starting | 0.000135 | 0.004000 | 0.000000 | 21 | 6 | 0 | 16 | 0 | 0 | 0 | 2 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1102 |
| checking permissions | 0.000124 | 0.008000 | 0.000000 | 26 | 7 | 0 | 16 | 0 | 0 | 0 | 2 | 0 | check_access | sql_authorization.cc | 1899 |
| checking permissions | 0.000139 | 0.008000 | 0.000000 | 19 | 9 | 32 | 24 | 0 | 0 | 0 | 4 | 0 | check_access | sql_authorization.cc | 1899 |
| Opening tables | 0.000152 | 0.004000 | 0.000000 | 30 | 14 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5586 |
| init | 0.000125 | 0.004000 | 0.008000 | 25 | 19 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | execute | sql_select.cc | 555 |
| System lock | 0.000237 | 0.004000 | 0.004000 | 26 | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 332 |
| optimizing | 0.000150 | 0.008000 | 0.000000 | 24 | 7 | 0 | 0 | 0 | 0 | 0 | 4 | 0 | optimize | sql_optimizer.cc | 212 |
| statistics | 0.001082 | 0.048000 | 0.004000 | 192 | 59 | 0 | 0 | 0 | 0 | 0 | 17 | 0 | optimize | sql_optimizer.cc | 425 |
| preparing | 0.000162 | 0.008000 | 0.000000 | 19 | 22 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 499 |
| executing | 0.000136 | 0.008000 | 0.000000 | 33 | 32 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 197 |
| Sending data | 2.894895 | 120.524000 | 6.512000 | 551014 | 158606 | 43632 | 125120 | 0 | 0 | 0 | 34154 | 0 | exec | sql_executor.cc | 273 |
| end | 0.000359 | 0.012000 | 0.000000 | 28 | 27 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 608 |
| query end | 0.000130 | 0.004000 | 0.004000 | 51 | 33 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4581 |
| waiting for handler commit | 0.000135 | 0.004000 | 0.000000 | 58 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ha_commit_trans | handler.cc | 1533 |
| closing tables | 0.000144 | 0.008000 | 0.000000 | 27 | 15 | 0 | 16 | 0 | 0 | 0 | 2 | 0 | mysql_execute_command | sql_parse.cc | 4627 |
| freeing items | 0.000165 | 0.008000 | 0.000000 | 23 | 4 | 0 | 8 | 0 | 0 | 0 | 3 | 0 | mysql_parse | sql_parse.cc | 5256 |
| cleaning up | 0.000137 | 0.008000 | 0.000000 | 6 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 2108 |
+---+
19 rows in set, 1 warning (0.00 sec)
mysql> show create table offers \G
*************************** 1. row ***************************
Table: offers
Create Table: CREATE TABLE `offers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`status` tinyint(4) NOT NULL DEFAULT '0',
`field_a` int(11) NOT NULL DEFAULT '0',
`field_b` text COLLATE utf8_unicode_ci NOT NULL,
`field_c` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
`field_d` text COLLATE utf8_unicode_ci NOT NULL,
`field_e` text COLLATE utf8_unicode_ci NOT NULL,
`field_f` text COLLATE utf8_unicode_ci,
`field_g` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
`field_h` text COLLATE utf8_unicode_ci,
`field_i` mediumint(9) NOT NULL DEFAULT '0',
`field_j` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`field_k` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`field_l` decimal(10,4) NOT NULL,
`field_m` decimal(10,4) NOT NULL DEFAULT '0.0000',
`field_n` decimal(10,4) NOT NULL DEFAULT '0.0000',
`field_o` decimal(10,4) NOT NULL DEFAULT '0.0000',
`field_p` decimal(5,2) NOT NULL DEFAULT '0.00',
`field_q` text COLLATE utf8_unicode_ci NOT NULL,
`field_r` text COLLATE utf8_unicode_ci NOT NULL,
`field_s` int(11) NOT NULL,
`field_t` mediumint(9) NOT NULL DEFAULT '0',
`field_u` tinyint(4) NOT NULL DEFAULT '0',
`field_v` tinyint(4) NOT NULL DEFAULT '0',
`field_w` tinyint(4) NOT NULL DEFAULT '0',
`field_x` tinyint(4) NOT NULL,
`field_y` tinyint(4) DEFAULT NULL,
`field_z` tinyint(4) NOT NULL DEFAULT '0',
`field_aa` tinyint(1) NOT NULL DEFAULT '0',
`field_ab` tinyint(1) NOT NULL,
`field_ac` tinyint(4) NOT NULL,
`field_ad` tinyint(1) NOT NULL DEFAULT '0',
`field_ae` tinyint(1) NOT NULL,
`field_af` int(10) unsigned DEFAULT '0',
`field_ag` int(10) unsigned NOT NULL,
`field_ah` int(10) unsigned NOT NULL,
`field_ai` int(11) NOT NULL,
`field_aj` tinyint(1) NOT NULL DEFAULT '0',
`field_ak` decimal(6,3) DEFAULT '0.000',
`field_al` tinyint(1) NOT NULL,
`field_am` decimal(8,3) NOT NULL,
`field_an` decimal(8,3) NOT NULL,
`field_ao` decimal(5,2) NOT NULL,
`field_ap` decimal(8,3) NOT NULL,
`field_aq` tinyint(3) unsigned DEFAULT NULL,
`field_ar` int(11) NOT NULL,
`field_as` mediumint(9) NOT NULL DEFAULT '0',
`field_at` mediumint(9) NOT NULL,
`field_au` mediumint(9) NOT NULL,
`field_av` mediumint(9) NOT NULL,
`field_aw` mediumint(9) NOT NULL,
`field_ax` mediumint(9) NOT NULL DEFAULT '8388607',
`field_ay` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`field_az` tinyint(1) NOT NULL DEFAULT '0',
`field_ba` smallint(5) unsigned DEFAULT NULL,
`field_bb` tinyint(1) NOT NULL DEFAULT '0',
`field_bc` tinyint(1) NOT NULL DEFAULT '0',
`field_bd` tinyint(1) NOT NULL DEFAULT '1',
`field_be` tinyint(1) NOT NULL,
`field_bf` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`field_bg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`field_bh` tinyint(1) NOT NULL DEFAULT '0',
`field_bi` text COLLATE utf8_unicode_ci NOT NULL,
`field_bj` tinyint(1) NOT NULL DEFAULT '0',
`field_bk` date DEFAULT NULL,
`field_bl` date DEFAULT NULL,
`field_bm` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`field_bn` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`field_bo` tinyint(1) NOT NULL DEFAULT '0',
`field_bp` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`field_bq` smallint(5) unsigned DEFAULT NULL,
`field_br` tinyint(3) unsigned NOT NULL DEFAULT '0',
`field_bs` decimal(10,6) DEFAULT NULL,
`field_bt` decimal(10,6) DEFAULT NULL,
`field_bu` tinyint(1) NOT NULL DEFAULT '0',
`field_bv` tinyint(1) NOT NULL DEFAULT '0',
`field_bw` tinyint(1) NOT NULL DEFAULT '0',
`field_bx` tinyint(1) NOT NULL DEFAULT '0',
`field_by` tinyint(1) NOT NULL DEFAULT '0',
`field_bz` datetime DEFAULT NULL,
`field_ca` datetime DEFAULT NULL,
`field_cb` datetime DEFAULT NULL,
`field_cc` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`field_cd` datetime DEFAULT NULL,
`field_ce` varchar(155) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`field_cf` int(11) DEFAULT NULL,
`field_cg` decimal(10,4) NOT NULL DEFAULT '0.0000',
`field_ch` tinyint(1) NOT NULL DEFAULT '0',
`field_ci` decimal(5,4) NOT NULL DEFAULT '0.0000',
`field_cj` mediumint(9) NOT NULL DEFAULT '0',
`field_ck` datetime DEFAULT NULL,
`field_cl` tinyint(1) NOT NULL DEFAULT '0',
`field_cm` datetime DEFAULT NULL,
`field_cn` smallint(5) unsigned NOT NULL DEFAULT '0',
`field_co` smallint(5) unsigned NOT NULL DEFAULT '0',
`field_cp` tinyint(1) NOT NULL DEFAULT '0',
`field_cq` tinyint(1) NOT NULL DEFAULT '0',
`field_cr` tinyint(1) NOT NULL DEFAULT '0',
`field_cs` tinyint(1) NOT NULL DEFAULT '0',
`field_ct` tinyint(1) NOT NULL DEFAULT '0',
`field_cu` tinyint(1) NOT NULL DEFAULT '0',
`field_cv` tinyint(1) NOT NULL DEFAULT '0',
`field_cw` tinyint(1) NOT NULL DEFAULT '0',
`field_cx` decimal(5,2) NOT NULL,
`field_cy` tinyint(1) NOT NULL DEFAULT '0',
`field_cz` tinyint(1) NOT NULL DEFAULT '0',
`field_da` int(11) NOT NULL DEFAULT '0',
`field_db` tinyint(1) NOT NULL DEFAULT '0',
`field_dc` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`field_dd` tinyint(3) unsigned DEFAULT NULL,
`field_de` tinyint(1) DEFAULT '0',
`field_df` datetime DEFAULT NULL,
`field_dg` tinyint(1) NOT NULL DEFAULT '0',
`field_dh` tinyint(1) NOT NULL DEFAULT '0',
`field_di` bigint(20) DEFAULT NULL,
`field_dj` datetime DEFAULT NULL,
`field_dk` tinyint(1) NOT NULL DEFAULT '0',
`field_dl` int(1) DEFAULT NULL,
`field_dm` tinyint(1) NOT NULL DEFAULT '0',
`field_dn` int(11) NOT NULL DEFAULT '0',
`field_do` tinyint(1) NOT NULL DEFAULT '0',
`field_dp` tinyint(3) unsigned NOT NULL DEFAULT '0',
`field_dq` int(11) NOT NULL DEFAULT '0',
`field_dr` json DEFAULT NULL,
`field_ds` int(11) NOT NULL DEFAULT '0',
`field_dt` datetime DEFAULT NULL,
`field_du` tinyint(1) NOT NULL DEFAULT '0',
`field_dv` json NOT NULL,
`field_dw` decimal(8,5) DEFAULT '0.00000',
`field_dx` tinyint(1) NOT NULL DEFAULT '0',
`field_dy` smallint(6) NOT NULL DEFAULT '14',
PRIMARY KEY (`id`),
UNIQUE KEY `field_i_field_j` (`field_i`,`field_j`),
UNIQUE KEY `field_j_field_i` (`field_k`,`field_i`),
KEY `status_field_bf` (`status`,`field_bf`),
KEY `field_bm` (`field_bm`),
KEY `status_field_i_field_bo` (`status`,`field_i`,`field_bo`),
KEY `field_j` (`field_j`),
KEY `field_bz` (`field_bz`),
KEY `field_br` (`field_br`),
KEY `field_bh` (`field_bh`),
KEY `field_ba` (`field_ba`),
KEY `field_cm` (`status`,`field_cm`),
KEY `field_cc` (`field_cc`),
KEY `field_i_field_cc` (`field_i`,`field_cc`),
KEY `field_g` (`field_g`),
FULLTEXT KEY `field_c` (`field_c`,`field_ce`)
) ENGINE=InnoDB AUTO_INCREMENT=18425582 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
526
A guess (pending more information requested in Comments)...
innodb_buffer_pool_size is smaller than the Data_length (see SHOW TABLE SIZE offers), and the 15K rows are scattered around the table. This could lead to hitting the disk instead of finding the desired id in the cache (buffer_pool).A partial solution may be to increase the buffer_pool. However, since you are using a Cloud service, the only way to do that increase may be to pay for bigger RAM.
An alternative may be to force or trick it into using a different index. But first I need to see the rest of the indexes, plus the datatypes of the indexed columns.
Meanwhile, I would really like to critique offers_clicks; what is the partition key?
On to another approach... Is this a common query? It seems to be "how many clicks happened in the hour?"
Why does it need to look at offers at all? Can't it get the right answer from SELECT COUNT(*) FROM offers_clicks WHERE date ...?
If you do need the JOIN, then let's talk about building and maintaining a summary table that holds the counts for every hour.
Side issues
FROM offers_clicks, offers
WHERE offers.id = offers_clicks.offer_id AND offers_clicks.date
should be written
FROM offers_clicks
JOIN offers ON offers.id = offers_clicks.offer_id -- how the tables relate
WHERE offers_clicks.date ...
The former is the old "commalist" style of JOIN, the latter is the preferred syntax. The two syntaxes generate identical code, so this is not a performance issue. The former looks like a "cross join", but the WHERE makes it effectively not.
Also... This is only 3599 seconds, not a full hour. The convention is to include the first midnight.
date > '2019-05-30 00:00:00'
and date < '2019-05-30 01:00:00'
I prefer this style since it avoids leap year issues, etc:
date >= '2019-05-30 00:00:00'
and date < '2019-05-30 00:00:00' + INTERVAL 1 HOUR
Page faults...
When a program (MySQL or any other) has allocated more memory (RAM) than the Operating System is willing to give it, some pages (usually 4KB in size) are "swapped" out to disk. When a page is referenced by the program, a "page fault" occurs. This is a hardware interrupt that says "panic! the page you asked for is no in RAM". Then the OS steps in to copy that page back into RAM so the program can continue.
InnoDB would much prefer to lay out a lot of RAM, then have free access to all of it whenever it likes. That is, InnoDB pretends that page faults will never occur. Hence, page faults hurt performance a lot. Do not configure MySQL to use "too much" RAM, else you get into page faults.
InnoDB does need to swap blocks of data and indexes (and table definitions, etc) into RAM and free up space for other blocks. But InnoDB is carefully designed and coded to minimize this. The "buffer pool" is a large chunk of RAM that InnoDB uses as an LRU (Least Recently Used) cache of its 16KB blocks. That is probably what is slowing down the processing, not the CPU, not page faults, etc. (I await some info so I can confirm this.)
The first time is usually slower, which is when it shows major page faults. Follow up queries are usually fast.
That means that there was I/O to bring in pages needed for the query (page fault major). Then subsequent queries could be run without I/O (faster).
And, since offers is much bigger, there would be more I/O. However, that is the opposite of what you are seeing.
190
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
