MySQL DELETE LOW PRIORITY ?

[PHPFAN10]

Hello,

 

I basically have a cron job that runs once everyday at 12 midnight to find records that are 7 days or older and deletes them. I determine the age of record using php as when the user account is requested to be deleted i insert a timestamp into the database and set the account status to delete and then in seven days time it will get deleted.

 

Now as deleting these records are not a high priority as i don't really want them to be i have seen the term LOW PRIORITY. Thing is i have searched all over the internet and cannot find anywhere on how it works exactly and what effects it has etc.

 

Could anyone tell me how low priority works exactly and can it work with a DELETE statement?

 

Thank you,

PHPLOVER

 

 

[PFMaBiSmAd]

There's always the original documentation - http://dev.mysql.com/doc/refman/5.0/en/delete.html

[PHPFAN10]

Hi,

 

I was meant to say it's a MyISAM DB.

 

Thanks for the doc but the problem is it only says this about LOW PRIORITY:

If you specify LOW_PRIORITY, the server delays execution of the DELETE until no other clients are reading from the table. This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).

 

Now what i would like to know is; when the cron job is run and someone was updating there profile for example and/or registering on the website would MySQL say in err human terms (excuse the expression): ?

 

oh hello delete query i see your low priority, i have a select/insert/update query that needs processing, i see your low priority so i will first do my select/insert/update queries and when i got time and nobody else is doing any other queries i will come back to you and delete what you want me to delete, but for now i am storing you away until i have some free time to spare.

 

I am sorry for the expression but i guess it's easier to explain and understand that way.

 

My next question is, what about if MySQL starts deleting records and someone comes along and updates profile or registers on site for example, does MySQL pause the low priority delete query to give for example the INSERT/UPDATE query priority?

 

 

Thanks

PHPFAN10

[fenway]

I don't understand your question -- the only thing LOW_PRIORITY does is change read/write lock acquisition.

[Muddy_Funster]

ok, so you want to know if, while a low priority query is being run, it can be interupted by another query being initiated at a normal priority?

In my limited knowledge, I am fairly sure that once a query has been started it can't be stoped under normal running conditions untill it has completed.

I think this will be a problem however because:

A) your delete query is likely to run quick enough that it won't even overlap with any other activity

B) even if it did overlap, MySQL can run much more than 1 querie at a time.

 

So it's not really much of an issue as I see it.  I think you are totaly missing the mark with what low_priority actualy does.

[PHPFAN10]

Thanks i understand now i was just thinking in terms of how it could effect other queries but you have answered my question.

 

Thank you

PHPFAN

[Adam]

In my limited knowledge, I am fairly sure that once a query has been started it can't be stoped under normal running conditions untill it has completed.

 

MySQL operations can be stopped, but that's at a DBA-level.

 

I think this will be a problem however because:

A) your delete query is likely to run quick enough that it won't even overlap with any other activity

B) even if it did overlap, MySQL can run much more than 1 querie at a time.

 

The time the query would take is completely relative to the size of the table, current server usage, server capabilities, etc. MySQL does support simultaneous queries, but table-level locking on MyISAM tables prevents the same table being written to by 2 queries at the same time.

 

Using LOW PRIORITY will do exactly what it says, delay execution until the table isn't being read/written from. If your table is frequently being accessed, then that's not a good idea as the query could be waiting infinitely. Once the DELETE has started though, it won't get interrupted by another query as it has table-lock.