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 secur...@mariadb.org _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
