Mysql uses 100% of CPU

  • Hi,

    I started having a problem with mysqld.exe using 100% of CPU.

    I ran ‘SHOW FULL PROCESSLIST;’ and I got below query running and never ending:

    SELECT SQL_CALC_FOUND_ROWS  kconl_posts.ID FROM kconl_posts  INNER JOIN kconl_postmeta ON ( kconl_posts.ID = kconl_postmeta.post_id )  INNER JOIN kconl_postmeta AS mt1 ON ( kconl_posts.ID = mt1.post_id )  INNER JOIN kconl_postmeta AS mt2 ON ( kconl_posts.ID = mt2.post_id )  INNER JOIN kconl_postmeta AS mt3 ON ( kconl_posts.ID = mt3.post_id )  INNER JOIN kconl_postmeta AS mt4 ON ( kconl_posts.ID = mt4.post_id )  INNER JOIN kconl_postmeta AS mt5 ON ( kconl_posts.ID = mt5.post_id )  INNER JOIN kconl_postmeta AS mt6 ON ( kconl_posts.ID = mt6.post_id )  INNER JOIN kconl_postmeta AS mt7 ON ( kconl_posts.ID = mt7.post_id )  INNER JOIN kconl_postmeta AS mt8 ON ( kconl_posts.ID = mt8.post_id )  INNER JOIN kconl_postmeta AS mt9 ON ( kconl_posts.ID = mt9.post_id )  INNER JOIN kconl_postmeta AS mt10 ON ( kconl_posts.ID = mt10.post_id )  INNER JOIN kconl_postmeta AS mt11 ON ( kconl_posts.ID = mt11.post_id )  INNER JOIN kconl_postmeta AS mt12 ON ( kconl_posts.ID = mt12.post_id )  INNER JOIN kconl_postmeta AS mt13 ON ( kconl_posts.ID = mt13.post_id ) WHERE 1=1  AND ( 
  ( kconl_postmeta.meta_key = 'routine_action' AND kconl_postmeta.meta_value IN ('task_scheduler_action_delete_task_log') ) 
  AND 
  ( mt1.meta_key = 'occurrence' AND mt1.meta_value IN ('constant') ) 
  AND 
  ( mt2.meta_key = '_max_root_log_count' AND mt2.meta_value IN ('0') ) 
  AND 
  ( mt3.meta_key = '_target_task_id' AND mt3.meta_value IN ('3277') ) 
  AND 
  ( mt4.meta_key = '_max_root_log_count_of_the_target' AND mt4.meta_value IN ('10') ) 
  AND 
  ( mt5.meta_key = 'owner_task_id' AND mt5.meta_value IN ('3277') ) 
  AND 
  ( mt6.meta_key = 'parent_routine_log_id' AND mt6.meta_value IN ('0') ) 
  AND 
  ( mt7.meta_key = '_max_execution_time' AND mt7.meta_value IN ('120') ) 
  AND 
  ( mt8.meta_key = '_force_execution' AND mt8.meta_value IN ('0') ) 
  AND 
  ( mt9.meta_key = '_hung_routine_handle_type' AND mt9.meta_value IN ('0') ) 
  AND 
  ( mt10.meta_key = '_yoast_wpseo_content_score' AND mt10.meta_value IN ('30') ) 
  AND 
  ( mt11.meta_key = 'custom_action' AND mt11.meta_value IN ('my_custom_action') ) 
  AND 
  ( mt12.meta_key = '_qts_slug_da' AND mt12.meta_value IN ('rz_test_task') ) 
  AND 
  ( mt13.meta_key = '_qts_slug_en' AND mt13.meta_value IN ('rz_test_task') )
) AND kconl_posts.post_type IN ('ts_task', 'ts_routine', 'ts_thread') AND ((kconl_posts.post_status = 'publish' OR kconl_posts.post_status = 'private')) GROUP BY kconl_posts.ID ORDER BY kconl_posts.post_date DESC, kconl_posts.ID DESC LIMIT 0, 1

    This is on the local server. CPU first is at around 30%, then it gets higher and higher. It reaches 100% and then my programs crash.

    I believe this query is run by Task Scheduler plug in.

    What step can I take to find the problem?

    Thanks

Viewing 15 replies - 1 through 15 (of 19 total)
  • Plugin Author miunosoft

    (@miunosoft)

    Hi,

    That query seems to be one to check duplicate routines. Every time a routine of a task is finished, the plugin tries to truncate log items of the task by creating an internal routine. In that process, a duplicate routine is checked and the query seems to be that one.

    Can you try setting 0 to the task Max Count of Log Entries option?

    I guess you have lots of routines running. I’ll see if the log truncation mechanism can be simplified to reduce database queries.

    Plugin Author miunosoft

    (@miunosoft)

    The development version 1.5.0b04 may fix your problem. Please try that and let me know how it goes.

    Thread Starter RolandasZa

    (@rolandasza)

    Hi, @miunosoft

    thanks for your reply.

    I updated the plugin to the development version, and set ‘Max Count of Log Entries’ to 0, as you instructed, but I still have the same problem.

    Today I found that table ‘psninja_amdd’ was corrupt, so I ran ‘REPAIR TABLE psninja_amdd;’ and this table is OK now.

    But I still get these same queries(when I run ‘SHOW FULL PROCESSLIST’), I send you before, running with high CPU.

    What else can I try?

    Plugin Author miunosoft

    (@miunosoft)

    The latest version 1.5.0 includes changed made in 1.5.0b04 (development version). So you can simply update the plugin to 1.5.0 in case if you haven’t.

    Regarding the query which shows up with SHOW FULL PROCESSLIST, that posted query is only performed when there is a value other than 0 is set to the Max Count of Log Entries` task option. If you have multiple tasks, please check the option in all of them.

    Also restarting the MySQL daemon might help.

    Thread Starter RolandasZa

    (@rolandasza)

    I have installed the latest version- 1.5.0

    I checked Query Monitor and found this:

    https://xxx.local/
    ?doing_server_heartbeat=1600675525.7753
    &id=5f685ec5bd31b
    &context=beat
    &last_beat_time=1600675505.9413

    cURL error 28: Operation timed out after 1000 milliseconds with 0 bytes received

    apply_filters(‘http_request_args’)

    Could this be a problem?

    Plugin Author miunosoft

    (@miunosoft)

    Not sure. You can post a screenshot of it.

    Also, please update the plugin to v1.5.1 which does not use the internal routine to delete log items. So the query like you posted should not occur.

    Thread Starter RolandasZa

    (@rolandasza)

    I wanted to delete Routines and got this:
    Catchable fatal error: Object of class TaskScheduler_Event_Action_DeleteLogItems could not be converted to string
    in C:\laragon\www\xxx\wp-content\plugins\task-scheduler\include\class\event\action\TaskScheduler_Event_Action_DeleteLogItems.php on line 77

    Thread Starter RolandasZa

    (@rolandasza)

    Screenshot of Query Monitor: https://i.imgur.com/Q0TIWQi.png

    Plugin Author miunosoft

    (@miunosoft)

    To get rid of the error of “Catchable fatal error: Object of class…”, try the development version 1.5.2b01.

    As for the cURL error, it times out with 0.01 seconds so it should not be related to the stuck MySQL process. To check if the server heartbeat is running properly, go to Dashboard -> Task Scheduler -> Settings. Then you can see when is the last beat time in the Status field.

    Same problem, 1.5.2b01 fixed the problem for me.

    Thread Starter RolandasZa

    (@rolandasza)

    Updated to latest development version.

    Heartbeat looks ok. I wait for Heartbeat to run, refresh page and ‘Last Beat Time’ and ‘Next Check’ get updated.

    I was able to remove Routines, then I clicked on ‘Run task’ and CPU is at 90% by mysqld.exe again.

    Plugin Author miunosoft

    (@miunosoft)

    @rolandasza Sounds like you have a heavy routine. By clicking on the Run Now action link, do you see threads that belong to the created routine? If yes, tell me how many.

    Also, what action is associated with your task? The action name should be indicated in the Details column of the task table accessed via Dashboard -> Task Scheduler -> Tasks.

    Thread Starter RolandasZa

    (@rolandasza)

    @miunosoft

    I see no Threads: https://prntscr.com/ul5th8

    You can see action in screenshot too.

    Sorry, my PC keeps crashing because of this. I stop Mysql, then start and that never-ending query starts again.

    Plugin Author miunosoft

    (@miunosoft)

    So you assigned a custom action to your task.

    Can you create a test task with the Debug action? Then run it and let me know if the same problem occurs.

    Thread Starter RolandasZa

    (@rolandasza)

    I’ll do that first thing in the morning tomorrow

Viewing 15 replies - 1 through 15 (of 19 total)
  • The topic ‘Mysql uses 100% of CPU’ is closed to new replies.

Tags