maria-discuss team mailing list archive

[ragul rangarajan <ragulrangarajan@xxxxxxxxx>]

Hi all,


I have a TableA with data around 1.4Billion which was partitioned by
timestamp.
<https://stackoverflow.com/posts/75231474/timeline>

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.

   1. Is there any way to check the Data corruption issue in the specific
   table?

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?

   1. If we have a problem with DB table entries do we have options to
   solve this

a. Either restarting the active DB or other ways to repair the data to
solve the issue like mysqlcheck repair.

   1. Not sure why we see NULL in key, Key_len, and possible_key for the
   first explain statement for the same table.


Regards,
Ragul R