Extremely slow SQL query

[yukirina]

There's one particular query that is stalling my entire MySQL and forcing it to a 100 CPU load, but I have no idea what it is.

To give context, I have recently transferred from Kimsufi dedicated server to Hetzner's. There has been no problem with moving the files and importing the database, but I put up the forum today and there's just one query that keeps getting marked by the server slow log.

Time Id Command Argument
# Time: 140920 7:30:27
# User@Host: himeuta_saya[himeuta_saya] @ localhost []
# Query_time: 17.132885 Lock_time: 0.000068 Rows_sent: 0 Rows_examined: 32544817
use himeuta_kancolle;
SET timestamp=1411191027;
UPDATE activitystream AS a
INNER JOIN attachment AS at ON (a.contentid = at.attachmentid)
SET
a.score = (1 + (at.counter / 1000))
WHERE
a.typeid = 3;
/usr/sbin/mysqld, Version: 5.5.37-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock

Help would be much appreciated.

[Lynne]

Try going to AdminCP > Settings > Options > Activity Stream > Content Tracked and disable "Album Photo" and "Group Photo" See if you still get the same slow queries.