TableA ( Column1, Column 2....Column N, timestamp) Partition by times
I was able to see a weird behavior recently on this table where querying specific timestamps ended in an issue (Query getting stuck more than expected time - 10min and block IO utilization which inturn lead to service degradation).
127.0.0.1:3307>select * from TableA where cloumn1=v1 and cloumn2=v2 and cloumn3=v3 and cloumn4=v4 and cloumn5=v5 and (timestamp between 1673319600 and 1673319600);
^CCtrl-C -- query killed. Continuing normally.
ERROR 1317 (70009): Query execution was interrupted
127.0.0.1:3307> analyze select * from TableA where cloumn1=v1 and cloumn2=v2 and cloumn3=v3 and cloumn4=v4 and cloumn5=v5 and (timestamp between 1673319600 and 1673319600);
^CCtrl-C -- query killed. Continuing normally.
ERROR 1317 (70100): Query execution was interrupted
127.0.0.1:3307> explain select * from TableA where cloumn1=v1 and cloumn2=v2 and cloumn3=v3 and cloumn4=v4 and cloumn5=v5 and (timestamp between 1673319600 and 1673319600);
+------+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | TableA | ALL | PRIMARY | NULL | NULL | NULL | 14 | Using where |
+------+-------------+--------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.042 sec)
while the same was working under different timestamp
127.0.0.1:3307> explain select * from TableA where cloumn1=v1 and cloumn2=v2 and cloumn3=v3 and cloumn4=v4 and cloumn5=v5 and (timestamp between 1673319600+3600 and 1673319600+7200+7200+7200+7200+7200+7200);
+------+-------------+--------------+------+---------------+---------+---------+-------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+------+---------------+---------+---------+-------------------------------+------+-------------+
| 1 | SIMPLE | TableA | ref | PRIMARY | PRIMARY | 7 | const,const,const,const,const | 12 | Using where |
+------+-------------+--------------+------+---------------+---------+---------+-------------------------------+------+-------------+
1 row in set (0.002 sec)
127.0.0.1:3307> analyze select * from TableA where cloumn1=v1 and cloumn2=v2 and cloumn3=v3 and cloumn4=v4 and cloumn5=v5 and (timestamp between 1673319600+3600 and 1673319600+7200+7200+7200+7200+7200+7200);
+------+-------------+--------------+------+---------------+---------+---------+-------------------------------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+--------------+------+---------------+---------+---------+-------------------------------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | TableA | ref | PRIMARY | PRIMARY | 7 | const,const,const,const,const | 12 | 600.00 | 100.00 | 91.67 | Using where |
+------+-------------+--------------+------+---------------+---------+---------+-------------------------------+------+--------+----------+------------+-------------+
1 row in set (0.003 sec)
Suspecting something might get corrupted in the DB entry for a specific time but not sure how to verify it.
a. Tried to check via mysqlcheck but it consumes more time and IO utilization.
b. Do we have any other option to check the same in a better way?
a. Either restarting the active DB or other ways to repair the data to solve the issue like mysqlcheck repair.