Managing strings is still slow in large components

[burner1024]

Describe the issue

#6207
I tried with Weblate 4.9 and unfortunately the issues persist.

1. Each new variant takes about 0.5 seconds more to add than the previous one.

uwsgi stderr | [pid: 304|app: 0|req: 8/89] 172.19.0.1 () {66 vars in 1322 bytes} [Sat Nov 13 17:44:11 2021] POST /new-unit/fallout/rp/ru/ => generated 0 bytes in 1871 msecs (HTTP/1.1 302) 8 headers in 403 bytes (1 switches on core 0)
uwsgi stderr | [pid: 304|app: 0|req: 10/91] 172.19.0.1 () {66 vars in 1322 bytes} [Sat Nov 13 17:44:29 2021] POST /new-unit/fallout/rp/ru/ => generated 0 bytes in 2228 msecs (HTTP/1.1 302) 8 headers in 403 bytes (1 switches on core 0)
uwsgi stderr | [pid: 1015|app: 0|req: 16/94] 172.19.0.1 () {66 vars in 1322 bytes} [Sat Nov 13 17:44:43 2021] POST /new-unit/fallout/rp/ru/ => generated 0 bytes in 2734 msecs (HTTP/1.1 302) 8 headers in 403 bytes (1 switches on core 0)
uwsgi stderr | [pid: 1015|app: 0|req: 17/96] 172.19.0.1 () {66 vars in 1322 bytes} [Sat Nov 13 17:45:00 2021] POST /new-unit/fallout/rp/ru/ => generated 0 bytes in 3191 msecs (HTTP/1.1 302) 8 headers in 403 bytes (1 switches on core 0)
uwsgi stderr | [pid: 1015|app: 0|req: 19/99] 172.19.0.1 () {66 vars in 1322 bytes} [Sat Nov 13 17:45:18 2021] POST /new-unit/fallout/rp/ru/ => generated 0 bytes in 3675 msecs (HTTP/1.1 302) 8 headers in 402 bytes (1 switches on core 0)
uwsgi stderr | [pid: 1015|app: 0|req: 23/106] 172.19.0.1 () {66 vars in 1322 bytes} [Sat Nov 13 17:48:05 2021] POST /new-unit/fallout/rp/ru/ => generated 0 bytes in 4209 msecs (HTTP/1.1 302) 8 headers in 403 bytes (1 switches on core 0)

2. Deleting a string takes a minute

wsgi stderr | [2021-11-13 17:36:12,517: INFO/885] fallout/rp: skipped push: push on commit disabled
uwsgi stderr | [2021-11-13 17:36:20,143: INFO/885] fallout/rp: skipped push: push on commit disabled
uwsgi stderr | [2021-11-13 17:36:20,276: INFO/885] fallout/rp/ru: processing data/text/po/russian.po, content changed
celery-celery stderr | [2021-11-13 17:36:26,093: INFO/MainProcess] Task weblate.utils.tasks.heartbeat[c6364899-3062-401b-bec5-09e56e5e88b7] received
uwsgi stderr | [pid: 400|app: 0|req: 35/65] 127.0.0.1 () {32 vars in 353 bytes} [Sat Nov 13 17:36:35 2021] GET /healthz/ => generated 2 bytes in 19 msecs (HTTP/1.1 200) 8 headers in 304 bytes (1 switches o
n core 0)
nginx stdout | 127.0.0.1 - - [13/Nov/2021:17:36:35 +0000] "GET /healthz/ HTTP/1.1" 200 12 "-" "curl/7.74.0"
uwsgi stderr | [pid: 301|app: 0|req: 1/66] 127.0.0.1 () {32 vars in 353 bytes} [Sat Nov 13 17:37:05 2021] GET /healthz/ => generated 2 bytes in 564 msecs (HTTP/1.1 200) 8 headers in 304 bytes (1 switches o
n core 0)
nginx stdout | 127.0.0.1 - - [13/Nov/2021:17:37:05 +0000] "GET /healthz/ HTTP/1.1" 200 12 "-" "curl/7.74.0"
uwsgi stderr | [2021-11-13 17:37:10,287: INFO/885] fallout/rp/ru: updating completed
celery-notify stderr | [2021-11-13 17:37:10,372: INFO/MainProcess] Task weblate.accounts.tasks.notify_change[6af6cf27-75e8-4c50-a410-56484c27a908] received
uwsgi stderr | [pid: 885|app: 0|req: 5/67] 172.19.0.1 () {66 vars in 1314 bytes} [Sat Nov 13 17:36:10 2021] POST /unit/1576647/delete/ => generated 0 bytes in 59692 msecs (HTTP/1.1 302) 8 headers in 376 bytes (1 switches on core 0)
uwsgi stderr | Sat Nov 13 17:37:10 2021 - ...The work of process 885 is done. Seeya!

I already tried

• I've read and searched the documentation.
• I've searched for similar issues in this repository.

Steps to reproduce the behavior

Add a variant, delete a string from a large PO component (a few MB).

Expected behavior

Adding and deleting strings in under 1 second.

Screenshots

No response

Exception traceback

No response

How do you run Weblate?

Docker container

Weblate versions

 * Weblate: 4.9
 * Django: 3.2.9
 * siphashc: 2.1
 * translate-toolkit: 3.5.1
 * lxml: 4.6.4
 * Pillow: 8.4.0
 * 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.13.0
 * oauthlib: 3.1.1
 * django-compressor: 2.4.1
 * djangorestframework: 3.12.4
 * django-filter: 21.1
 * django-appconf: 1.0.5
 * user-agents: 2.2.0
 * filelock: 3.3.2
 * setuptools: 58.5.3
 * jellyfish: 0.8.9
 * openpyxl: 3.0.9
 * celery: 5.2.0
 * kombu: 5.2.1
 * translation-finder: 2.10
 * weblate-language-data: 2021.5
 * html2text: 2020.1.16
 * pycairo: 1.16.2
 * pygobject: 3.38.0
 * diff-match-patch: 20200713
 * requests: 2.26.0
 * django-redis: 5.0.0
 * hiredis: 2.0.0
 * sentry_sdk: 1.4.3
 * Cython: 0.29.24
 * misaka: 2.1.1
 * GitPython: 3.1.24
 * borgbackup: 1.1.17
 * pyparsing: 3.0.5
 * pyahocorasick: 1.4.2
 * python-redis-lock: 3.7.0
 * Python: 3.9.2
 * Git: 2.30.2
 * psycopg2: 2.9.1
 * psycopg2-binary: 2.9.1
 * phply: 1.2.5
 * chardet: 4.0.0
 * ruamel.yaml: 0.17.17
 * tesserocr: 2.5.2
 * akismet: 1.1
 * boto3: 1.20.2
 * zeep: 4.1.0
 * aeidon: 1.10.1
 * iniparse: 0.5
 * mysqlclient: 2.0.3
 * Mercurial: 5.9.3
 * git-svn: 2.30.2
 * git-review: 2.1.0
 * Redis server: 6.2.4
 * PostgreSQL server: 13.3
 * Database backends: django.db.backends.postgresql
 * Cache backends: default:RedisCache, avatar:FileBasedCache
 * Email setup: django.core.mail.backends.smtp.EmailBackend: 127.0.0.1
 * OS encoding: filesystem=utf-8, default=utf-8
 * Celery: redis://cache:6379/1, redis://cache:6379/1, regular
 * Platform: Linux 5.11.0-38-generic (x86_64)

Weblate deploy checks

SystemCheckError: System check identified some issues:

CRITICALS:
?: (weblate.E003) Cannot send e-mail ([Errno 111] Connection refused), please check EMAIL_* settings.
	HINT: https://docs.weblate.org/en/weblate-4.9/admin/install.html#out-mail
?: (weblate.E012) The server e-mail address should be changed from its default value
	HINT: https://docs.weblate.org/en/weblate-4.9/admin/install.html#production-email
?: (weblate.E013) The "From" e-mail address should be changed from its default value
	HINT: https://docs.weblate.org/en/weblate-4.9/admin/install.html#production-email
?: (weblate.E027) The path /app/data/secret is owned by a different user, check your DATA_DIR settings.
	HINT: https://docs.weblate.org/en/weblate-4.9/admin/install.html#file-permissions

ERRORS:
?: (weblate.E011) E-mail addresses for site admins is misconfigured
	HINT: https://docs.weblate.org/en/weblate-4.9/admin/install.html#production-admins

WARNINGS:
?: (security.W004) You have not set a value for the SECURE_HSTS_SECONDS setting. If your entire site is served only over SSL, you may want to consider setting a value and enabling HTTP Strict Transport Security. Be sure to read the documentation first; enabling HSTS carelessly can cause serious, irreversible problems.
?: (security.W008) Your SECURE_SSL_REDIRECT setting is not set to True. Unless your site should be available over both SSL and non-SSL connections, you may want to either set this setting True or configure a load balancer or reverse-proxy server to redirect all connections to HTTPS.
?: (security.W012) SESSION_COOKIE_SECURE is not set to True. Using a secure-only session cookie makes it more difficult for network traffic sniffers to hijack user sessions.
?: (security.W018) You should not have DEBUG set to True in deployment.

INFOS:
?: (weblate.I021) Error collection is not set up, it is highly recommended for production use
	HINT: https://docs.weblate.org/en/weblate-4.9/admin/install.html#collecting-errors
?: (weblate.I028) Backups are not configured, it is highly recommended for production use
	HINT: https://docs.weblate.org/en/weblate-4.9/admin/backup.html

System check identified 11 issues (1 silenced).

Additional context

Attached is an example of one of several sql queries ran during deletion, which seems pretty crazy to me (just query text is 0.5Mb) sql.txt

[nijel]

Deleting strings should be implemented in future similarly as adding - the strings would be deleted in the database and removed from the file later. Until that, the performance for deleting will suffer. See #1993

[github-actions]

Thank you for your report; the issue you have reported has just been fixed.

• In case you see a problem with the fix, please comment on this issue.
• 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.

[burner1024]

Adding is in 1.3s area now, not super fast, but stable now, thanks!

uwsgi stderr | [pid: 300|app: 0|req: 10/18] 172.19.0.1 () {66 vars in 1322 bytes} [Mon Nov 15 12:39:17 2021] POST /new-unit/fallout/rp/ru/ => generated 0 bytes in 1354 msecs (HTTP/1.1 302) 8 headers in 403 bytes (2 switches on core 0)
uwsgi stderr | [pid: 300|app: 0|req: 11/20] 172.19.0.1 () {66 vars in 1322 bytes} [Mon Nov 15 12:39:32 2021] POST /new-unit/fallout/rp/ru/ => generated 0 bytes in 1227 msecs (HTTP/1.1 302) 8 headers in 403 bytes (1 switches on core 0)
uwsgi stderr | [pid: 297|app: 0|req: 7/23] 172.19.0.1 () {66 vars in 1322 bytes} [Mon Nov 15 12:39:46 2021] POST /new-unit/fallout/rp/ru/ => generated 0 bytes in 1302 msecs (HTTP/1.1 302) 8 headers in 402 bytes (2 switches on core 0)
uwsgi stderr | [pid: 300|app: 0|req: 14/26] 172.19.0.1 () {66 vars in 1322 bytes} [Mon Nov 15 12:40:04 2021] POST /new-unit/fallout/rp/ru/ => generated 0 bytes in 1304 msecs (HTTP/1.1 302) 8 headers in 403 bytes (2 switches on core 0)

In PO case, maybe not necessary to actually delete strings from file, just mark obsolete? The translations could come in handy again later.

[nijel]

It doesn't matter whether they are removed or marked obsolete, the round trip to the file makes it expensive.