Mattermost Notifications flooding the services table
Summary
On our own hosted Gitlab server we are using the Mattermost Notifications service, and after using it some time, it floods the services table with records currently we are at more then 48247441 records. And it is causing timeout statement errors when a pipeline finishes. Even via gitlab-rails dbconsole I get time outs on SELECT statements on the services table.
Steps to reproduce
Activate mattermost notifications and use it for a long time or fill the services table with a lot of records.
What is the current bug behavior?
Background jobs notifications keep failing and die-ing with errors like these:
PG::QueryCanceled: ERROR: canceling statement due to statement timeout : SELECT "services".* FROM "services" WHERE "services"."project_id" = 174 AND "services"."job_events" = TRUE AND "services"."active" = TRUE
When to much commits are made after each other the server slows down to a halt, due too a lot of statement timeouts.
Relevant logs and/or screenshots
PG::QueryCanceled: ERROR: canceling statement due to statement timeout : SELECT "services".* FROM "services" WHERE "services"."project_id" = 174 AND "services"."job_events" = TRUE AND "services"."active" = TRUE
Output of checks
This Gitlab instances runs on a own hosted server. With 2 cores and 4gb ram. Used by less then 8 people at a time.
Results of GitLab environment info
Expand for output related to GitLab environment info
System information System: Ubuntu 18.04 Current User: git Using RVM: no Ruby Version: 2.6.3p62 Gem Version: 2.7.9 Bundler Version:1.17.3 Rake Version: 12.3.3 Redis Version: 3.2.12 Git Version: 2.22.0 Sidekiq Version:5.2.7 Go Version: unknownGitLab information
Version: 12.4.3
Revision: 4d477238500
Directory: /opt/gitlab/embedded/service/gitlab-rails
DB Adapter: PostgreSQL
DB Version: 10.9
URL: https://gitlab..com
HTTP Clone URL: https://gitlab..com/some-group/some-project.git
SSH Clone URL: git@gitlab.***********.com:some-group/some-project.git
Using LDAP: no
Using Omniauth: yes
Omniauth Providers:GitLab Shell
Version: 10.2.0
Repository storage paths:
default: /var/opt/gitlab/git-data/repositories
GitLab Shell path: /opt/gitlab/embedded/service/gitlab-shell
Git: /opt/gitlab/embedded/bin/git
Results of GitLab application Check
Expand for output related to the GitLab application check
Checking GitLab subtasks ...Checking GitLab Shell ...
GitLab Shell: ... GitLab Shell version >= 10.2.0 ? ... OK (10.2.0) Running /opt/gitlab/embedded/service/gitlab-shell/bin/check Internal API available: OK Redis available via internal API: OK gitlab-shell self-check successful
Checking GitLab Shell ... Finished
Checking Gitaly ...
Gitaly: ... default ... OK
Checking Gitaly ... Finished
Checking Sidekiq ...
Sidekiq: ... Running? ... yes Number of Sidekiq processes ... 1
Checking Sidekiq ... Finished
Checking Incoming Email ...
Incoming Email: ... Reply by email is disabled in config/gitlab.yml
Checking Incoming Email ... Finished
Checking LDAP ...
LDAP: ... LDAP is disabled in config/gitlab.yml
Checking LDAP ... Finished
Checking GitLab App ...
Git configured correctly? ... yes Database config exists? ... yes All migrations up? ... yes Database contains orphaned GroupMembers? ... no GitLab config exists? ... yes GitLab config up to date? ... yes Log directory writable? ... yes Tmp directory writable? ... yes Uploads directory exists? ... yes Uploads directory has correct permissions? ... yes Uploads directory tmp has correct permissions? ... yes Init script exists? ... skipped (omnibus-gitlab has no init script) Init script up-to-date? ... skipped (omnibus-gitlab has no init script) Projects have namespace: ... 7/1 ... yes 7/2 ... yes 8/3 ... yes 5/4 ... yes 5/5 ... yes 2/6 ... yes 8/7 ... yes 5/8 ... yes 8/11 ... yes 8/12 ... yes 8/13 ... yes 11/14 ... yes 8/15 ... yes 8/16 ... yes 8/17 ... yes 8/18 ... yes 8/19 ... yes 8/20 ... yes 8/21 ... yes 8/22 ... yes 8/23 ... yes 8/24 ... yes 8/25 ... yes 8/26 ... yes 8/27 ... yes 8/28 ... yes 8/29 ... yes 8/30 ... yes 8/31 ... yes 8/32 ... yes 8/33 ... yes 8/34 ... yes 8/35 ... yes 8/36 ... yes 8/37 ... yes 8/38 ... yes 8/39 ... yes 8/40 ... yes 8/41 ... yes 8/42 ... yes 8/43 ... yes 8/44 ... yes 8/45 ... yes 8/46 ... yes 8/47 ... yes 8/48 ... yes 8/49 ... yes 8/50 ... yes 8/51 ... yes 8/52 ... yes 8/53 ... yes 8/54 ... yes 8/55 ... yes 8/56 ... yes 8/57 ... yes 8/58 ... yes 8/59 ... yes 8/60 ... yes 8/61 ... yes 8/62 ... yes 8/63 ... yes 8/64 ... yes 8/65 ... yes 8/66 ... yes 8/67 ... yes 8/68 ... yes 8/69 ... yes 8/70 ... yes 8/71 ... yes 8/72 ... yes 8/73 ... yes 8/74 ... yes 8/75 ... yes 8/76 ... yes 8/77 ... yes 8/78 ... yes 8/79 ... yes 8/80 ... yes 8/81 ... yes 8/82 ... yes 8/83 ... yes 8/84 ... yes 8/85 ... yes 8/86 ... yes 8/87 ... yes 8/88 ... yes 8/89 ... yes 8/90 ... yes 8/91 ... yes 8/92 ... yes 8/93 ... yes 8/94 ... yes 8/95 ... yes 8/96 ... yes 8/97 ... yes 8/98 ... yes 8/99 ... yes 8/100 ... yes 8/101 ... yes 8/102 ... yes 8/103 ... yes 8/104 ... yes 8/105 ... yes 8/106 ... yes 8/107 ... yes 8/108 ... yes 8/109 ... yes 8/110 ... yes 8/111 ... yes 8/112 ... yes 8/113 ... yes 8/114 ... yes 8/115 ... yes 8/116 ... yes 8/117 ... yes 8/118 ... yes 8/119 ... yes 8/120 ... yes 8/121 ... yes 8/122 ... yes 8/123 ... yes 8/124 ... yes 8/125 ... yes 8/126 ... yes 8/127 ... yes 8/128 ... yes 8/129 ... yes 8/130 ... yes 8/131 ... yes 8/132 ... yes 8/133 ... yes 8/134 ... yes 8/135 ... yes 8/136 ... yes 8/137 ... yes 8/138 ... yes 8/139 ... yes 8/140 ... yes 8/141 ... yes 8/142 ... yes 8/143 ... yes 8/144 ... yes 8/145 ... yes 8/146 ... yes 8/147 ... yes 8/148 ... yes 8/149 ... yes 8/150 ... yes 8/151 ... yes 8/152 ... yes 8/153 ... yes 8/154 ... yes 8/155 ... yes 8/156 ... yes 8/157 ... yes 8/158 ... yes 8/159 ... yes 8/160 ... yes 8/161 ... yes 8/162 ... yes 8/163 ... yes 8/165 ... yes 8/166 ... yes 8/167 ... yes 7/168 ... yes 8/169 ... yes 8/170 ... yes 8/171 ... yes 8/172 ... yes 8/173 ... yes 8/174 ... yes 8/175 ... yes 8/176 ... yes 8/177 ... yes 8/178 ... yes 8/179 ... yes 8/180 ... yes 8/181 ... yes 8/182 ... yes 8/183 ... yes 8/184 ... yes 8/185 ... yes 8/186 ... yes 8/187 ... yes 8/188 ... yes 8/189 ... yes 13/190 ... yes 8/191 ... yes 8/192 ... yes 8/193 ... yes 8/194 ... yes 8/195 ... yes 8/196 ... yes 8/197 ... yes 8/198 ... yes 8/199 ... yes 8/200 ... yes 8/201 ... yes 8/202 ... yes 8/203 ... yes 8/204 ... yes 8/205 ... yes 8/206 ... yes 8/207 ... yes 8/208 ... yes 8/209 ... yes 8/210 ... yes 8/211 ... yes 8/212 ... yes 8/213 ... yes 8/214 ... yes 8/215 ... yes 8/216 ... yes 13/217 ... yes 13/218 ... yes 13/219 ... yes 13/220 ... yes 13/221 ... yes 13/222 ... yes 13/223 ... yes 13/224 ... yes 13/225 ... yes 13/226 ... yes 13/227 ... yes 14/228 ... yes 14/229 ... yes 15/230 ... yes 15/231 ... yes 15/232 ... yes 15/233 ... yes 8/234 ... yes 8/235 ... yes 8/236 ... yes 8/237 ... yes 8/238 ... yes 8/239 ... yes 8/240 ... yes 15/241 ... yes 8/242 ... yes 15/243 ... yes 8/244 ... yes 8/245 ... yes 8/246 ... yes 8/247 ... yes 8/248 ... yes 15/249 ... yes 15/250 ... yes 8/251 ... yes 15/252 ... yes 15/253 ... yes 8/254 ... yes 8/255 ... yes 8/256 ... yes 8/257 ... yes 15/258 ... yes 8/259 ... yes 8/260 ... yes 8/261 ... yes 8/262 ... yes 8/263 ... yes 8/264 ... yes 15/265 ... yes 8/266 ... yes 8/267 ... yes 8/268 ... yes 15/269 ... yes 8/270 ... yes 8/271 ... yes 8/272 ... yes 8/273 ... yes 8/274 ... yes 8/275 ... yes 15/276 ... yes 8/277 ... yes 8/278 ... yes 15/279 ... yes 8/280 ... yes 15/281 ... yes 8/282 ... yes 16/283 ... yes 8/284 ... yes 8/285 ... yes 8/286 ... yes 8/287 ... yes 8/288 ... yes 8/290 ... yes 8/292 ... yes 17/293 ... yes 8/294 ... yes 8/295 ... yes 8/296 ... yes 8/297 ... yes 8/298 ... yes 8/299 ... yes 8/300 ... yes 8/301 ... yes 8/302 ... yes 8/303 ... yes 8/304 ... yes 8/305 ... yes 8/306 ... yes 8/307 ... yes 8/308 ... yes 8/309 ... yes 8/310 ... yes 8/311 ... yes 8/312 ... yes 8/313 ... yes 17/314 ... yes 17/315 ... yes 17/316 ... yes 17/317 ... yes 3/318 ... yes 7/319 ... yes 7/320 ... yes 5/321 ... yes 5/322 ... yes 5/323 ... yes 5/324 ... yes 7/325 ... yes 5/326 ... yes 7/327 ... yes 7/328 ... yes 7/329 ... yes 7/330 ... yes 5/331 ... yes 2/332 ... yes 8/334 ... yes 5/335 ... yes 8/337 ... yes 5/338 ... yes 5/339 ... yes 5/340 ... yes 8/343 ... yes 8/344 ... yes 8/345 ... yes 8/346 ... yes 8/347 ... yes 8/348 ... yes 5/349 ... yes 5/350 ... yes 5/351 ... yes 5/352 ... yes 8/353 ... yes 5/354 ... yes 5/355 ... yes 5/356 ... yes 5/357 ... yes 21/358 ... yes 10/359 ... yes 5/360 ... yes 10/361 ... yes 8/362 ... yes 5/363 ... yes 5/364 ... yes 5/365 ... yes 8/366 ... yes 5/367 ... yes 24/368 ... yes 5/369 ... yes 5/370 ... yes 8/371 ... yes 5/372 ... yes 5/373 ... yes 8/374 ... yes 8/375 ... yes 8/376 ... yes 8/377 ... yes 8/378 ... yes 5/379 ... yes 24/380 ... yes 8/381 ... yes 7/382 ... yes 8/383 ... yes 5/384 ... yes 8/385 ... yes 8/386 ... yes 8/390 ... yes 8/391 ... yes 8/392 ... yes 8/393 ... yes 8/394 ... yes 8/395 ... yes 8/396 ... yes 8/398 ... yes 5/399 ... yes 5/400 ... yes 8/401 ... yes 8/402 ... yes 8/403 ... yes 8/406 ... yes 8/407 ... yes 5/408 ... yes 25/409 ... yes 8/412 ... yes 8/413 ... yes Redis version >= 2.8.0? ... yes Ruby version >= 2.5.3 ? ... yes (2.6.3) Git version >= 2.22.0 ? ... yes (2.22.0) Git user has default SSH configuration? ... yes Active users: ... 6 Is authorized keys file accessible? ... yes
Checking GitLab App ... Finished
Checking GitLab subtasks ... Finished
Possible fixes
Clear services table after a job is done but no idea how to do that.