Bug #43914 delayed-file param / a function to execute group of statements in another thread
Submitted: 27 Mar 2009 15:30 Modified: 30 Mar 2009 13:21
Reporter: Serdar S. Kacar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[27 Mar 2009 15:30] Serdar S. Kacar
Description:
When starting the server, one might wish to warmup data. As opposed to "init-file", warmup characteristics are
- server can start serving clients before warmup sompletes, and
- warmup can take a long long time - several minutes to hours.

Therefor, placing warmup SQL into the "init-file" may not be an option.

How to repeat:
N/A

Suggested fix:
A. "delayed-file" initialization parameter
where read SQL executed in a temporary thread and this temporary thread is created after all the other initialization tasks have been completed - at a point where server is ready to accept connections.

B. A general purpose function - "SPAWN(Options, Statements)"
This functions would start a temporary thread to run the statements and return immediately. Security context (i.e. user) of the temporary thread is the CALL-TIME user of the caller. 
(By CALL-TIME user I mean, if it is called from a stored procedure, and "SQL SECURITY DEFINER" is in effect, then the CALL-TIME user is the definer of the stored procedure.)
The "Options" Parameter :
- DISABLE LOG : do not log any warning/error to "log-error" file
- DISABLE LOG WARNING : do not log warnings to "log-error" file (but errors)
- GROUP=groupname : only one spawned thread per groupname can execute at a time

Then something like this in "init-file" would be used for warmup tasks :
DO SPAWN('DISABLE LOG, GROUP=warm1', 
  'SET @@sort_buffer_size=10000000000; CALL abc.warmup();');
DO SPAWN('DISABLE LOG, GROUP=warm1', 'CALL cde.warmup();');
DO SPAWN('DISABLE LOG, GROUP=warm2', 'CALL lookup.warmup();');
[29 Mar 2009 11:05] Valeriy Kravchuk
I think all these (and more!) can be done using events in 5.1+. Please, check http://dev.mysql.com/doc/refman/5.1/en/events.html.
[30 Mar 2009 13:20] Serdar S. Kacar
> I think all these (and more!) can be done using events in 5.1+
Right. But there are some uncertainaties also. Excerpt from "Event_scheduler::execute_top()" :
  /*
    TODO: should use thread pool here, preferably with an upper limit
    on number of threads: if too many events are scheduled for the
    same time, starting all of them at once won't help them run truly
    in parallel (because of the great amount of synchronization), so
    we may as well execute them in sequence, keeping concurrency at a
    reasonable level.
  */
If someone decides to "execute them in sequence" , tasks that can take long times could block other events.
Otherwise, I concur that function I mentioned before is fully covered by EVENTs.
[30 Mar 2009 13:21] Serdar S. Kacar
.