[opensuse-buildservice] mysqld consuming 100% CPU since updating to 2.6.5
Hi, since I updated to 2.6.5, mysql is running amok. /srv/www/obs/api/log/delayed_job.log containing many entries and some like this: E, [2015-10-13T08:32:45.780268 #5420] ERROR -- : 2015-10-13T08:32:45+0200: [Worker(delayed_job.2 host:buildservice pid:5420)] Job SendEventEmails (id=499046) FAILED (0 prior attempts) with ActiveRecord::StatementInvalid: Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction: DELETE FROM `delayed_jobs` WHERE `delayed_jobs`.`id` = 499046 I, [2015-10-13T08:04:15.614209 #5414] INFO -- : 2015-10-13T08:04:15+0200: [Worker(delayed_job.1 host:buildservice pid:5414)] Error while reserving job: Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction: UPDATE `delayed_jobs` SET `delayed_jobs`.`locked_at` = '2015-10-13 06:04:11', `delayed_jobs`.`locked_by` = 'delayed_job.1 host:buildservice pid:5414' WHERE ((run_at <= '2015-10-13 06:04:11' AND (locked_at IS NULL OR locked_at < '2015-10-13 02:04:11') OR locked_by = 'delayed_job.1 host:buildservice pid:5414') AND failed_at IS NULL) AND `delayed_jobs`.`queue` IN ('quick') ORDER BY priority ASC, run_at ASC LIMIT 1 buildservice:~ # grep "Deadlock found when" /srv/www/obs/api/log/delayed_job.log | wc -l 1081 Did something went wrong on my system or is that a general problem? MariaDB [(none)]> SHOW FULL PROCESSLIST; +-----+------+-----------+----------------+---------+------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-----+------+-----------+----------------+---------+------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+ | 90 | obs | localhost | api_production | Sleep | 46 | | NULL | 0.000 | | 91 | obs | localhost | api_production | Query | 1 | init | UPDATE `delayed_jobs` SET `delayed_jobs`.`locked_at` = '2015-10-13 06:48:37', `delayed_jobs`.`locked_by` = 'delayed_job.0 host:buildservice pid:5408' WHERE ((run_at <= '2015-10-13 06:48:37' AND (locked_at IS NULL OR locked_at < '2015-10-13 02:48:37') OR locked_by = 'delayed_job.0 host:buildservice pid:5408') AND failed_at IS NULL) AND `delayed_jobs`.`queue` IN ('quick') ORDER BY priority ASC, run_at ASC LIMIT 1 | 0.000 | | 92 | obs | localhost | api_production | Query | 4 | init | UPDATE `delayed_jobs` SET `delayed_jobs`.`locked_at` = '2015-10-13 06:48:34', `delayed_jobs`.`locked_by` = 'delayed_job.1 host:buildservice pid:5414' WHERE ((run_at <= '2015-10-13 06:48:34' AND (locked_at IS NULL OR locked_at < '2015-10-13 02:48:34') OR locked_by = 'delayed_job.1 host:buildservice pid:5414') AND failed_at IS NULL) AND `delayed_jobs`.`queue` IN ('quick') ORDER BY priority ASC, run_at ASC LIMIT 1 | 0.000 | | 93 | obs | localhost | api_production | Query | 0 | init | UPDATE `delayed_jobs` SET `delayed_jobs`.`locked_at` = '2015-10-13 06:48:38', `delayed_jobs`.`locked_by` = 'delayed_job.2 host:buildservice pid:5420' WHERE ((run_at <= '2015-10-13 06:48:38' AND (locked_at IS NULL OR locked_at < '2015-10-13 02:48:38') OR locked_by = 'delayed_job.2 host:buildservice pid:5420') AND failed_at IS NULL) AND `delayed_jobs`.`queue` IN ('quick') ORDER BY priority ASC, run_at ASC LIMIT 1 | 0.000 | | 94 | obs | localhost | api_production | Sleep | 2 | | NULL | 0.000 | | 95 | obs | localhost | api_production | Sleep | 3 | | NULL | 0.000 | | 96 | obs | localhost | api_production | Query | 0 | updating | DELETE FROM `delayed_jobs` WHERE `delayed_jobs`.`id` = 499660 | 0.000 | | 97 | obs | localhost | api_production | Sleep | 5 | | NULL | 0.000 | | 98 | obs | localhost | api_production | Sleep | 1620 | | NULL | 0.000 | | 99 | obs | localhost | api_production | Sleep | 10 | | NULL | 0.000 | | 100 | obs | localhost | api_production | Sleep | 441 | | NULL | 0.000 | | 101 | root | localhost | NULL | Query | 0 | init | SHOW FULL PROCESSLIST | 0.000 | +-----+------+-----------+----------------+---------+------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+ 12 rows in set (0.00 sec) -- mit freundlichen Gruessen/with best regards, Carsten Hoeger Open-Xchange GmbH -------------------------------------------------------------------------------- Open-Xchange AG, Rollnerstr. 14, 90408 Nürnberg, Amtsgericht Nürnberg HRB 24738 Vorstand: Rafael Laguna de la Vera, Carsten Dirks Aufsichtsratsvorsitzender: Richard Seibt European Office: Open-Xchange GmbH, Martinstr. 41, D-57462 Olpe, Germany Amtsgericht Siegen, HRB 8718, Geschäftsführer: Frank Hoberg, Martin Kauss US Office: Open-Xchange. Inc., 530 Lytton Avenue, Palo Alto, CA 94301, USA --------------------------------------------------------------------------------
Chöger! On 13.10.2015 08:50, Carsten Höger wrote:
since I updated to 2.6.5, mysql is running amok.
/srv/www/obs/api/log/delayed_job.log
containing many entries and some like this:
E, [2015-10-13T08:32:45.780268 #5420] ERROR -- : 2015-10-13T08:32:45+0200: [Worker(delayed_job.2 host:buildservice pid:5420)]
How old are your delayed_job processes? $ ps aux|grep delayed_job Henne -- Henne Vogelsang http://www.opensuse.org Everybody has a plan, until they get hit. - Mike Tyson -- To unsubscribe, e-mail: opensuse-buildservice+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-buildservice+owner@opensuse.org
Hi,
On 13 Oct 2015, at 13:12 , Henne Vogelsang
wrote: Chöger!
On 13.10.2015 08:50, Carsten Höger wrote:
since I updated to 2.6.5, mysql is running amok.
/srv/www/obs/api/log/delayed_job.log
containing many entries and some like this:
E, [2015-10-13T08:32:45.780268 #5420] ERROR -- : 2015-10-13T08:32:45+0200: [Worker(delayed_job.2 host:buildservice pid:5420)]
How old are your delayed_job processes?
$ ps aux|grep delayed_job
from this morning when I restarted it in hope to “fix” that: wwwrun 5408 0.7 0.9 567708 297888 ? Sl 08:03 2:33 delayed_job.0 wwwrun 5414 0.2 0.4 411540 146536 ? Sl 08:03 0:39 delayed_job.1 wwwrun 5420 0.2 0.4 411696 147624 ? Sl 08:03 0:46 delayed_job.2 wwwrun 5434 0.0 0.3 256840 128824 ? Sl 08:03 0:05 delayed_job.1000 wwwrun 5465 0.0 0.3 393040 130744 ? Sl 08:04 0:06 delayed_job.1010 wwwrun 5484 0.3 0.4 412088 147344 ? Sl 08:04 1:04 delayed_job.1020 it appears to be working on a long queue of things: buildservice:~> sudo mysql -pXXXXX api_production -e 'SELECT COUNT(*) FROM delayed_jobs' +----------+ | COUNT(*) | +----------+ | 455697 | +----------+ buildservice:~> sudo mysql -pXXXXX api_production -e 'SELECT COUNT(*) FROM delayed_jobs’ +----------+ | COUNT(*) | +----------+ | 455692 | +----------+ so I hope that this will be over somewhen... -- mit freundlichen Gruessen/with best regards, Carsten Hoeger Open-Xchange GmbH -------------------------------------------------------------------------------- Open-Xchange AG, Rollnerstr. 14, 90408 Nürnberg, Amtsgericht Nürnberg HRB 24738 Vorstand: Rafael Laguna de la Vera, Carsten Dirks Aufsichtsratsvorsitzender: Richard Seibt European Office: Open-Xchange GmbH, Martinstr. 41, D-57462 Olpe, Germany Amtsgericht Siegen, HRB 8718, Geschäftsführer: Frank Hoberg, Martin Kauss US Office: Open-Xchange. Inc., 530 Lytton Avenue, Palo Alto, CA 94301, USA --------------------------------------------------------------------------------
On Tuesday 2015-10-13 13:35, Carsten Höger wrote:
it appears to be working on a long queue of things:
buildservice:~> sudo mysql -pXXXXX api_production -e 'SELECT COUNT(*) FROM delayed_jobs' +----------+ | COUNT(*) | +----------+ | 455697 | +----------+
buildservice:~> sudo mysql -pXXXXX api_production -e 'SELECT COUNT(*) FROM delayed_jobs’ +----------+ | COUNT(*) | +----------+ | 455692 | +----------+
so I hope that this will be over somewhen...
Sounds like a case of https://github.com/openSUSE/open-build-service/issues/832 -- To unsubscribe, e-mail: opensuse-buildservice+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-buildservice+owner@opensuse.org
Hi,
On 13 Oct 2015, at 13:39 , Jan Engelhardt
wrote: it appears to be working on a long queue of things:
buildservice:~> sudo mysql -pXXXXX api_production -e 'SELECT COUNT(*) FROM delayed_jobs' +----------+ | COUNT(*) | +----------+ | 455697 | +----------+
buildservice:~> sudo mysql -pXXXXX api_production -e 'SELECT COUNT(*) FROM delayed_jobs’ +----------+ | COUNT(*) | +----------+ | 455692 | +----------+
so I hope that this will be over somewhen...
Sounds like a case of https://github.com/openSUSE/open-build-service/issues/832
Hm, yes, although I am not sure if I understand why. osc api /issue_trackers > trackers edit the file and change <issue-tracker> <name>bnc</name> <kind>bugzilla</kind> <description>openSUSE Bugzilla</description> <url>https://bugzilla.opensuse.org/</url> <show-url>https://bugzilla.opensuse.org/show_bug.cgi?id=@@@</show-url> <regex>(?:bnc|BNC|bsc|BSC|boo|BOO)\s*[#:]\s*(\d+)</regex> <label>boo#@@@</label> - <enable-fetch>true</enable-fetch> + <enable-fetch>false</enable-fetch> </issue-tracker> and run osc api -X PUT /issue_trackers -T trackers ? -- mit freundlichen Gruessen/with best regards, Carsten Hoeger Open-Xchange GmbH -------------------------------------------------------------------------------- Open-Xchange AG, Rollnerstr. 14, 90408 Nürnberg, Amtsgericht Nürnberg HRB 24738 Vorstand: Rafael Laguna de la Vera, Carsten Dirks Aufsichtsratsvorsitzender: Richard Seibt European Office: Open-Xchange GmbH, Martinstr. 41, D-57462 Olpe, Germany Amtsgericht Siegen, HRB 8718, Geschäftsführer: Frank Hoberg, Martin Kauss US Office: Open-Xchange. Inc., 530 Lytton Avenue, Palo Alto, CA 94301, USA --------------------------------------------------------------------------------
On Dienstag, 13. Oktober 2015, 13:52:22 CEST wrote Carsten Höger:
Hi,
On 13 Oct 2015, at 13:39 , Jan Engelhardt
wrote: it appears to be working on a long queue of things:
buildservice:~> sudo mysql -pXXXXX api_production -e 'SELECT COUNT(*) FROM delayed_jobs' +----------+ | COUNT(*) | +----------+ | 455697 | +----------+
buildservice:~> sudo mysql -pXXXXX api_production -e 'SELECT COUNT(*) FROM delayed_jobs’ +----------+ | COUNT(*) | +----------+ | 455692 | +----------+
so I hope that this will be over somewhen...
Sounds like a case of https://github.com/openSUSE/open-build-service/issues/832
not really, because the issue had no failing attempts like Carsten has.
Hm, yes, although I am not sure if I understand why.
osc api /issue_trackers > trackers
edit the file and change
<issue-tracker> <name>bnc</name> <kind>bugzilla</kind> <description>openSUSE Bugzilla</description> <url>https://bugzilla.opensuse.org/</url> <show-url>https://bugzilla.opensuse.org/show_bug.cgi?id=@@@</show-url> <regex>(?:bnc|BNC|bsc|BSC|boo|BOO)\s*[#:]\s*(\d+)</regex> <label>boo#@@@</label> - <enable-fetch>true</enable-fetch> + <enable-fetch>false</enable-fetch> </issue-tracker>
and run
osc api -X PUT /issue_trackers -T trackers
?
you need to use a POST here. -- Adrian Schroeter email: adrian@suse.de SUSE Linux GmbH, GF: Felix Imendörffer, Jane Smithard, Graham Norton, HRB 21284 (AG Nürnberg) Maxfeldstraße 5 90409 Nürnberg Germany -- To unsubscribe, e-mail: opensuse-buildservice+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-buildservice+owner@opensuse.org
On Dienstag, 13. Oktober 2015, 14:03:01 CEST wrote Adrian Schröter:
On Dienstag, 13. Oktober 2015, 13:52:22 CEST wrote Carsten Höger:
Hi,
On 13 Oct 2015, at 13:39 , Jan Engelhardt
wrote: it appears to be working on a long queue of things:
buildservice:~> sudo mysql -pXXXXX api_production -e 'SELECT COUNT(*) FROM delayed_jobs' +----------+ | COUNT(*) | +----------+ | 455697 | +----------+
buildservice:~> sudo mysql -pXXXXX api_production -e 'SELECT COUNT(*) FROM delayed_jobs’ +----------+ | COUNT(*) | +----------+ | 455692 | +----------+
so I hope that this will be over somewhen...
Sounds like a case of https://github.com/openSUSE/open-build-service/issues/832
not really, because the issue had no failing attempts like Carsten has.
I will disable opensuse bugzilla scanning by default in git now. It still works fine for us, but we use a different api, not the default one ... However, no of us is testing the official api it seems. -- Adrian Schroeter email: adrian@suse.de SUSE Linux GmbH, GF: Felix Imendörffer, Jane Smithard, Graham Norton, HRB 21284 (AG Nürnberg) Maxfeldstraße 5 90409 Nürnberg Germany -- To unsubscribe, e-mail: opensuse-buildservice+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-buildservice+owner@opensuse.org
Hi,
On 13 Oct 2015, at 14:03 , Adrian Schröter
wrote:
osc api /issue_trackers > trackers
edit the file and change
<issue-tracker> <name>bnc</name> <kind>bugzilla</kind> <description>openSUSE Bugzilla</description> <url>https://bugzilla.opensuse.org/</url> <show-url>https://bugzilla.opensuse.org/show_bug.cgi?id=@@@</show-url> <regex>(?:bnc|BNC|bsc|BSC|boo|BOO)\s*[#:]\s*(\d+)</regex> <label>boo#@@@</label> - <enable-fetch>true</enable-fetch> + <enable-fetch>false</enable-fetch> </issue-tracker>
and run
osc api -X PUT /issue_trackers -T trackers
?
you need to use a POST here.
Ah, yes, sure. But, doesn’t seem to work: buildservice:~> cat issue_trackers <issue-tracker> <name>bnc</name> <kind>bugzilla</kind> <description>openSUSE Bugzilla</description> <url>https://bugzilla.opensuse.org/</url> <show-url>https://bugzilla.opensuse.org/show_bug.cgi?id=@@@</show-url> <regex>(?:bnc|BNC|bsc|BSC|boo|BOO)\s*[#:]\s*(\d+)</regex> <label>boo#@@@</label> <enable-fetch>false</enable-fetch> </issue-tracker> buildservice:~> osc api -X POST /issue_trackers -T issue_trackers <issue-tracker> <name>bnc</name> <kind>bugzilla</kind> <description>openSUSE Bugzilla</description> <url>https://bugzilla.opensuse.org/</url> <show-url>https://bugzilla.opensuse.org/show_bug.cgi?id=@@@</show-url> <regex>(?:bnc|BNC|bsc|BSC|boo|BOO)\s*[#:]\s*(\d+)</regex> <label>boo#@@@</label> <enable-fetch>false</enable-fetch> </issue-tracker> buildservice:~> osc api /issue_trackers | grep -A 5 "openSUSE Bugzilla" <description>openSUSE Bugzilla</description> <url>https://bugzilla.opensuse.org/</url> <show-url>https://bugzilla.opensuse.org/show_bug.cgi?id=@@@</show-url> <regex>(?:bnc|BNC|bsc|BSC|boo|BOO)\s*[#:]\s*(\d+)</regex> <label>boo#@@@</label> <enable-fetch>true</enable-fetch> -- mit freundlichen Gruessen/with best regards, Carsten Hoeger Open-Xchange GmbH -------------------------------------------------------------------------------- Open-Xchange AG, Rollnerstr. 14, 90408 Nürnberg, Amtsgericht Nürnberg HRB 24738 Vorstand: Rafael Laguna de la Vera, Carsten Dirks Aufsichtsratsvorsitzender: Richard Seibt European Office: Open-Xchange GmbH, Martinstr. 41, D-57462 Olpe, Germany Amtsgericht Siegen, HRB 8718, Geschäftsführer: Frank Hoberg, Martin Kauss US Office: Open-Xchange. Inc., 530 Lytton Avenue, Palo Alto, CA 94301, USA --------------------------------------------------------------------------------
Carsten Höger
Hi,
On 13 Oct 2015, at 14:03 , Adrian Schröter
wrote: osc api /issue_trackers > trackers
edit the file and change
<issue-tracker> <name>bnc</name> <kind>bugzilla</kind> <description>openSUSE Bugzilla</description> <url>https://bugzilla.opensuse.org/</url> <show-url>https://bugzilla.opensuse.org/show_bug.cgi?id=@@@</show-url> <regex>(?:bnc|BNC|bsc|BSC|boo|BOO)\s*[#:]\s*(\d+)</regex> <label>boo#@@@</label> - <enable-fetch>true</enable-fetch> + <enable-fetch>false</enable-fetch> </issue-tracker>
and run
osc api -X PUT /issue_trackers -T trackers
?
you need to use a POST here.
Ah, yes, sure.
But, doesn’t seem to work:
buildservice:~> cat issue_trackers <issue-tracker> <name>bnc</name> <kind>bugzilla</kind> <description>openSUSE Bugzilla</description> <url>https://bugzilla.opensuse.org/</url> <show-url>https://bugzilla.opensuse.org/show_bug.cgi?id=@@@</show-url> <regex>(?:bnc|BNC|bsc|BSC|boo|BOO)\s*[#:]\s*(\d+)</regex> <label>boo#@@@</label> <enable-fetch>false</enable-fetch> </issue-tracker>
buildservice:~> osc api -X POST /issue_trackers -T issue_trackers
osc api -e /issue_trackers/bnc doesn't work either. Andreas. -- Andreas Schwab, SUSE Labs, schwab@suse.de GPG Key fingerprint = 0196 BAD8 1CE9 1970 F4BE 1748 E4D4 88E3 0EEA B9D7 "And now for something completely different." -- To unsubscribe, e-mail: opensuse-buildservice+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-buildservice+owner@opensuse.org
Hi,
On 13 Oct 2015, at 14:45 , Andreas Schwab
wrote: But, doesn’t seem to work:
buildservice:~> cat issue_trackers <issue-tracker> <name>bnc</name> <kind>bugzilla</kind> <description>openSUSE Bugzilla</description> <url>https://bugzilla.opensuse.org/</url> <show-url>https://bugzilla.opensuse.org/show_bug.cgi?id=@@@</show-url> <regex>(?:bnc|BNC|bsc|BSC|boo|BOO)\s*[#:]\s*(\d+)</regex> <label>boo#@@@</label> <enable-fetch>false</enable-fetch> </issue-tracker>
buildservice:~> osc api -X POST /issue_trackers -T issue_trackers
osc api -e /issue_trackers/bnc doesn't work either.
Meanwhile I removed it here /srv/www/obs/api/db/seeds.rb and in the mysqldb update issue_trackers set enable_fetch=0 where description like "openSUSE Bugzilla"; Btw.: What are these delayed_jobs meant for? Can we just delete all these 400k entries in that table? -- mit freundlichen Gruessen/with best regards, Carsten Hoeger Open-Xchange GmbH -------------------------------------------------------------------------------- Open-Xchange AG, Rollnerstr. 14, 90408 Nürnberg, Amtsgericht Nürnberg HRB 24738 Vorstand: Rafael Laguna de la Vera, Carsten Dirks Aufsichtsratsvorsitzender: Richard Seibt European Office: Open-Xchange GmbH, Martinstr. 41, D-57462 Olpe, Germany Amtsgericht Siegen, HRB 8718, Geschäftsführer: Frank Hoberg, Martin Kauss US Office: Open-Xchange. Inc., 530 Lytton Avenue, Palo Alto, CA 94301, USA --------------------------------------------------------------------------------
Hi,
On Thu, 15 Oct 2015 10:30:56 +0200
"Carsten Höger"
Meanwhile I removed it here /srv/www/obs/api/db/seeds.rb and in the mysqldb
update issue_trackers set enable_fetch=0 where description like "openSUSE Bugzilla";
Btw.: What are these delayed_jobs meant for? Can we just delete all these 400k entries in that table?
I did exactly this while updating a test machine copied from Packman's PMBS from 2.5 to 2.6 back in June. Seemed to introduce no harm. For the real update is stopped postfix while the delayed jobs tried to notify our users and deleted the mail storm once the job queue was empty, and restarted postfix. YMMV, but greetings, Stefan -- Stefan Botter zu Hause Bremen
Hey, On 15.10.2015 10:30, Carsten Höger wrote:
Btw.: What are these delayed_jobs meant for?
There are several types of jobs [1]. Some do OBS internal things, some create requests, some send mails etc. etc.
Can we just delete all these 400k entries in that table?
That would mean that those Jobs are not executed. If you can live with that (your instance can) then fine... Henne [1] https://github.com/openSUSE/open-build-service/tree/master/src/api/app/jobs -- Henne Vogelsang http://www.opensuse.org Everybody has a plan, until they get hit. - Mike Tyson -- To unsubscribe, e-mail: opensuse-buildservice+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-buildservice+owner@opensuse.org
participants (6)
-
Adrian Schröter
-
Andreas Schwab
-
Carsten Höger
-
Henne Vogelsang
-
Jan Engelhardt
-
Stefan Botter