Table metrics_metric is growing constantly

[rob006]

Describe the issue

4 days ago I updated Weblate from ancient 3.9.1 to most recent 4.8.1. Since then I'm seeing significant increase in number of queries, especially in terms of inserts.

Number of queries grows with number of pageviews, and they generates inserts even if they're just reads without modifying anything (so I can see bots visits on this graph). It looks like these write queries affects almost exclusively metrics_metric, which also seems to grow significantly since the upgrade (since yesterday its size increased from 200 MB to 400 MB) and now it is almost 70% of the whole database size. Is it normal that this table is so big and generates some many inserts? Is there any way to disable this behavior?

Server configuration and status

• Weblate: weblate-4.8.1
• Django: 3.2.7
• siphashc: 2.1
• translate-toolkit: 3.4.1
• lxml: 4.6.3
• Pillow: 8.3.2
• bleach: 4.1.0
• python-dateutil: 2.8.2
• social-auth-core: 4.1.0
• social-auth-app-django: 5.0.0
• django-crispy-forms: 1.12.0
• oauthlib: 3.1.0
• django-compressor: 2.4.1
• djangorestframework: 3.12.4
• django-filter: 2.4.0
• django-appconf: 1.0.5
• user-agents: 2.2.0
• filelock: 3.0.12
• setuptools: 58.1.0
• jellyfish: 0.8.2
• openpyxl: 3.0.9
• celery: 5.1.2
• kombu: 5.1.0
• translation-finder: 2.10
• weblate-language-data: 2021.5
• html2text: 2020.1.16
• pycairo: 1.20.1
• pygobject: 3.42.0
• diff-match-patch: 20200713
• requests: 2.26.0
• django-redis: 5.0.0
• hiredis: 2.0.0
• sentry_sdk: 1.3.1
• Cython: 0.29.24
• misaka: 2.1.1
• GitPython: 3.1.24
• borgbackup: 1.1.17
• pyparsing: 2.4.7
• pyahocorasick: 1.4.2
• python-redis-lock: 3.7.0
• Python: 3.8.10
• Git: 2.25.1
• psycopg2-binary: 2.9.1
• phply: 1.2.5
• chardet: 4.0.0
• ruamel.yaml: 0.17.16
• tesserocr: 2.5.2
• akismet: 1.1
• boto3: 1.18.48
• zeep: 4.1.0
• aeidon: 1.9
• iniparse: 0.5
• mysqlclient: 2.0.3
• MySQL sever: 8.0.26
• Database backends: django.db.backends.mysql
• Cache backends: default:MemcachedCache, avatar:FileBasedCache
• Email setup: django.core.mail.backends.smtp.EmailBackend: localhost
• OS encoding: filesystem=utf-8, default=utf-8
• Celery: redis://localhost:6379/0, redis://localhost:6379/0, regular
• Platform: Linux 5.4.0-86-generic (x86_64)

Weblate deploy checks

SystemCheckError: System check identified some issues:

CRITICALS:
?: (weblate.E034) The Celery process is outdated or misconfigured. Following items differ: vcs
	HINT: https://docs.weblate.org/en/weblate-4.8.1/admin/install.html#celery

System check identified 1 issue (7 silenced).

[nijel]

The upgrade to 4.6 introduced this and missing metrics are calculated on the fly, that's why you are seeing inserts for read-only access. That should stop once all past metrics are calculated. That's also why the table grows fast in the beginning, but it won't grow that much after that.

[rob006]

How big table should I expect? Metrics taking twice as much space as the rest the data already seems to be a bit excessive.

[github-actions]

This issue looks more like a support question than an issue. We strive to answer these reasonably fast, but purchasing the support subscription is not only more responsible and faster for your business but also makes Weblate stronger.

In case your question is already answered, making a donation is the right way to say thank you!

[nijel]

Looking at our production sites, it has roughly the same size as the translation data. We run PostgreSQL, though.

There will be some improvements to that once #6616 is merged.

[github-actions]

The issue you have reported is now resolved. If you don’t feel it’s right, please follow its labels to get a clue for further steps.

• In case you see a similar problem, please open a separate issue.
• If you are happy with the outcome, don’t hesitate to support Weblate by making a donation.

[drzraf]

I also encountered this scenario where this table reached several hundreds of MB (even with little use of Weblate). There should definitely exist some kind of pruning (or at least document whether it's safe to TRUNCATE this table or not)

[nijel]

4.14.1 fixed pruning of unused data. You might want to vacuum the table then if there were a lot of pruned entries

[rob006]

@nijel This table is still massive in my case (96% of database size):

Over 300k records are added to this table every day.

[nijel]

Please follow up to this topic in #8119 only.