Hi, Ragul,

Can you share the table definition? The result of SHOW CREATE TABLE TableA.

/Sergei

On Jan 27, ragul rangarajan wrote:

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

Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org