One more DB performance report/question. I am seeing some UPDATE queries that only change one row but take much, much longer than they ought to. Is anyone else seeing this?
For example, the following type of query is getting killed from time to time in the p_enwp10 database on sql-s1. The query killers says it ran for over 400 seconds before being killed. The update is on a primary key, and I don't see any way to optimize it. At the time this is running the database connection is inside a transaction (AutoCommit = 0) if that matters.
UPDATE tmpcategories
SET c_category = 'A-Class_Water_supply_and_sanitation_articles', c_ranking = '425', c_replacement = 'A-Class'
WHERE c_project = 'Water_supply_and_sanitation' and c_rating= 'A-Class' and c_type = 'quality'
- Carl
mysql> show indexes from tmpcategories;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tmpcategories | 0 | PRIMARY | 1 | c_project | A | 4055 | NULL | NULL | | BTREE | |
| tmpcategories | 0 | PRIMARY | 2 | c_type | A | 8110 | NULL | NULL | | BTREE | |
| tmpcategories | 0 | PRIMARY | 3 | c_rating | A | 40553 | NULL | NULL | | BTREE | |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+