maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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@xxxxxxxxxxx