XF 2.0 Job Manager Gone Crazy

Ghan_04

Active member
For some reason, my database has been getting slammed recently by something to do with the xf_job table and scheduled jobs. The cron jobs themselves don't seem to be the source of the problem. What I see is this kind of thing filling up my database process list:

Ghan-28-05-18_19:57:30.png


I see queries like this constantly as I refresh the processlist. They don't seem to take long to execute individually, but it's like every page load submits a ton of them at a time. I'm averaging about 2,000 queries per second on the database with only ~250 people online. MariaDB is using up 3.5 of the server's 4 cores and is driving the load above 30. This is with enableListeners=false to disable all addons. Where can I look to pinpoint what's going on here?

Thanks!
 

Xon

Well-known member
@Ghan_04 my suggestion would be to rename deferred.php away so you can see the contents of xf_job table before it gets drained by deferred.php

If this still happens without deferred.php being called, then there is something buggy with XenForo's job dispatching.

:edit: Remember to rename deferred.php back after you are done!
 

Ghan_04

Active member
@Ghan_04 my suggestion would be to rename deferred.php away so you can see the contents of xf_job table before it gets drained by deferred.php

If this still happens without deferred.php being called, then there is something buggy with XenForo's job dispatching.

:edit: Remember to rename deferred.php back after you are done!

When I was watching xf_job before, I noticed that the last_run_date field was being updated every second on I think 11 of the entries. After renaming deferred.php, it looks like only a couple of them are updating now. But I'm not sure what I'm supposed to be looking for here.

Ghan-28-05-18_21:01:07.png
 

Xon

Well-known member
XF:Notifier suggests it is trying to push a ton of new post alert/emails, and it is only doing a few at a time. But it is weird those aren't terminating reasonably quickly.

How are you sending email? To a local postfix instance which then spools out? Or some 3rd party service?
 

Ghan_04

Active member
XF:Notifier suggests it is trying to push a ton of new post alert/emails, and it is only doing a few at a time. But it is weird those aren't terminating reasonably quickly.

How are you sending email? To a local postfix instance which then spools out? Or some 3rd party service?

We're using SMTP via Amazon to send emails. This does seem to be something to do with alerts (the xf_user_alert table has 186,000 entries). We have the Alert Improvements addon, but like I mentioned, I disabled all addons while troubleshooting this and it is still doing what is shown in the screenshot.
 

Xon

Well-known member
Meant job.php rather than deferreed.php!


One thing you could try is editing a renamed `job.php` so it dumps database query tracing (warning can be a lot!) and get the stacktrace of whatever is happening.

After
PHP:
$app->setup();
Add:
PHP:
\XF::db()->logQueries(true, false);

Replace
PHP:
header('Content-Type: application/json; charset=UTF-8');
header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: no-store, no-cache, must-revalidate, max-age=0");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");
echo json_encode($output);
with
PHP:
echo $app->debugger()->getDebugPageHtml($app);

Then run it manually.

Back up the file before editing, and ensure it isn't named job.php so it isn't hit by mistake by someone else.
 

Ghan_04

Active member
Meant job.php rather than deferreed.php!


One thing you could try is editing a renamed `job.php` so it dumps database query tracing (warning can be a lot!) and get the stacktrace of whatever is happening.

After
PHP:
$app->setup();
Add:
PHP:
\XF::db()->logQueries(true, false);

Replace
PHP:
header('Content-Type: application/json; charset=UTF-8');
header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: no-store, no-cache, must-revalidate, max-age=0");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");
echo json_encode($output);
with
PHP:
echo $app->debugger()->getDebugPageHtml($app);

Then run it manually.

Back up the file before editing, and ensure it isn't named job.php so it isn't hit by mistake by someone else.

I have done this, and these look like the queries I'm seeing that stack up on the database constantly. Looking through the includes, it seems to have all the addons listed. Page times bounce around anywhere between 0.08 and 0.5 seconds depending on how busy the server is at that moment.

It seems like this is being run on every page load because I see these queries constantly in the database processlist, but they are always time=0 so it does not take long to run them, it's just that there is a constant load of them (running around 2500 queries per second). Is it normal that this is run on every page load? If not, how can I tell what is triggering it to do so? What calls job.php? If it is supposed to run on every page load, then how can I tell what is causing it to fire off so many queries at once? This has just started happening fairly recently over the past few weeks. Are the entries in the xf_job database of any significance? Is the fact that xf_user_alert has over 180,000 entries significant?
 

Xon

Well-known member
Is the fact that xf_user_alert has over 180,000 entries significant?
Nah, that is just the total number of alerts in the last 7 days(read) or 30 days(unread) or so. Sufficient Velocity has 557k in that table :p

I have done this, and these look like the queries I'm seeing that stack up on the database constantly. Looking through the includes, it seems to have all the addons listed. Page times bounce around anywhere between 0.08 and 0.5 seconds depending on how busy the server is at that moment.
It looks like you have a post reply notification which is just spinning.

That this is happening with enableListeners=false, when all add-on class extensions are disabled, points to something wonky with XenForo itself.
 
Top