Disable tracking of project-tag counts to improve DB load
After deploying 8.17.0 RC1 to gitlab.com yesterday we have seen an increase in load for the database. After some digging @pcarranza found this:
- Example 1: 2017-02-09 14:35:24 - ERROR: deadlock detected
Detail: Process 9450 waits for ShareLock on transaction 466210367; blocked by process 11742.
Process 11742 waits for ShareLock on transaction 466211149; blocked by process 8442.
Process 8442 waits for ExclusiveLock on tuple (168,5) of relation 33863 of database 16385; blocked by process 9450.
Process 9450: UPDATE "tags" SET "taggings_count" = COALESCE("taggings_count", 0) - 1 WHERE "tags"."id" = $1
Process 11742: UPDATE "tags" SET "taggings_count" = COALESCE("taggings_count", 0) - 1 WHERE "tags"."id" = $1
Process 8442: UPDATE "tags" SET "taggings_count" = COALESCE("taggings_count", 0) - 1 WHERE "tags"."id" = $1
Context: while updating tuple (168,5) in relation "tags"
Hint: See server log for query details.
Statement: UPDATE "tags" SET "taggings_count" = COALESCE("taggings_count", 0) - 1 WHERE "tags"."id" = $1
Problem 1
It looks like we're updating tagging counts concurrently and this doesn't always work well (read: it's killing the db).
Problem 2
It seems like we're updating tagging counts every time and this bumped exclusive locks from aprox 20 to 1.8k.
References:
Slack conversation starting here.