maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Dan,

On Mar 03, mariadb@xxxxxxxxxxxxxx wrote:
> With this table:
> 
> CREATE TABLE t1 (
>   label varchar(100) NOT NULL,
>   dt datetime NOT NULL,
>   success tinyint(1) NOT NULL,
>   PRIMARY KEY (label,dt),
>   KEY dt (dt),
>   KEY success (success,dt)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
> 
> I have a DELETE query that has been stuck in Statistics for nearly an 
> hour now:
> 
> DELETE
>      MyDB.t1
> FROM
>      MyDB.t1
> WHERE
>      dt <= '2023-02-21 08:06:46'
>      AND dt >= '2023-02-21 08:03:48'
> 
> This statement runs several times per hour to trim the table (and has 
> for years). It has run fine against my 10.6.12 DB (this is a downstream 
> replica DB) for a couple weeks now(when I upgraded the replica); but 
> suddenly today produced the hang. Any ideas why? The table has nearly 15 
> million rows; but the rows are more or less evenly spaced across datetimes.

Is there any chance you could run https://poormansprofiler.org/ (or one
of the alternatives) when this happens again?

> Also I find that I cannot kill the query using KILL 12345; the KILL 
> statement just comes back immediately without reporting an error and the 
> statement continues. Is there no way to kill this statement without 
> crashing the DB? Again, this is a replica statement that is executing. I 
> had planned to stop the query, execute an ANALYZE TABLE t1 and then try 
> it again, but I don't seem to be able to kill the query.

KILL always returns immediately. Killing in MariaDB is cooperative, it
simply tells the thread "abort execution at your earliest convenience"
and it's up to the executing thread to decide when to abort. KILL
doesn't wait for it.

Regards,
Sergei
VP of MariaDB Server Engineering
and security@xxxxxxxxxxx