API/WebUIで VACUUM ANALYZE などのデータベースメインテナンス機能

eternal-flame-AD commented 3 hours ago

Summary

SQLを書かなくても API/WebUI/定期的に　VACUUM ANALYZE　などよく使われるデータベースメインテナンスコマンドをできるように欲しい。

例えば

POST /api/admin/maintenance/db/vacuum -> VACUUM (ANALYZE, TRUNCATE $1, PARALLEL $2)　サブミットする。（数分かかります） GET /api/admin/maintenance/db/history -> 前回実行したタイムスタンプ

Purpose

Postgresは定期的にVACUUM ANALYZEコマンドが必要と思います。VACUUMが行われない限り削除したノートなどのデータは再利用恐らくできません、ベンチマークはまだしていないでも多少なりともパフォーマンスも関わると思います。

VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables.

https://www.postgresql.org/docs/current/sql-vacuum.html

MisskeyはDocker composeで内部ネットワークで専用データベースを使っているので、データベースのメインテナンスは docker exec psql / mTLS/SSH プロキシしてSQLを書くのは一般的な管理者に対しては操作が困難だと思います。

自分のインスタンス（~5人）のstats:

> ANALYZE VERBOSE "user", "note", "__chart__per_user_notes", "drive_file";

analyzing "public.user"
"user": scanned 30000 of 34855 pages, containing 170087 live rows and 26678 dead rows; 30000 rows in sample, 197613 estimated total rows
analyzing "public.note"
"note": scanned 30000 of 420323 pages, containing 431026 live rows and 16665 dead rows; 30000 rows in sample, 6039005 estimated total rows
analyzing "public.__chart__per_user_notes"
"__chart__per_user_notes": scanned 25392 of 25392 pages, containing 2275948 live rows and 517 dead rows; 30000 rows in sample, 2275948 estimated total rows
analyzing "public.drive_file"
"drive_file": scanned 30000 of 134271 pages, containing 318773 live rows and 6738 dead rows; 30000 rows in sample, 1426732 estimated total rows
ANALYZE
Time: 5.416s (5 seconds), executed in: 5.416s (5 seconds)

Vacuum後（ディスクスペースが少なくないためTRUNCATEオプションが使っていないTotal pagesは減らない　それでも削除したページは再利用できなりました）：

VACUUM (ANALYZE, PARALLEL 4);

VACUUM
Time: 95.214s (1 minute 35 seconds), executed in: 95.214s (1 minute 35 seconds)

ANALYZE VERBOSE "user", "note", "__chart__per_user_notes", "drive_file";

analyzing "public.user"
"user": scanned 30000 of 34855 pages, containing 170035 live rows and 7 dead rows; 30000 rows in sample, 197552 estimated total rows
analyzing "public.note"
"note": scanned 30000 of 420332 pages, containing 429723 live rows and 4 dead rows; 30000 rows in sample, 6020878 estimated total rows
analyzing "public.__chart__per_user_notes"
"__chart__per_user_notes": scanned 25392 of 25392 pages, containing 2275948 live rows and 478 dead rows; 30000 rows in sample, 2275948 estimated total rows
analyzing "public.drive_file"
"drive_file": scanned 30000 of 134273 pages, containing 318284 live rows and 1 dead rows; 30000 rows in sample, 1424565 estimated total rows
ANALYZE
Time: 5.894s (5 seconds), executed in: 5.894s (5 seconds)

実装決定されたら幸いです。Backendは自信あるけどFrontend経験ほぼないので、Frontend部分は公式チームにお願いいただけます。

Do you want to implement this feature yourself?

[x] Yes, I will implement this by myself and send a pull request

fruitriin commented 3 hours ago

もしかしたら、Managed DBを使っている場合は Auto VACUUM が有効でしょうし、個人が管理するpostgresにしても、auto VACUUMの設定を有効にすることができるという点で、Misskeyというソフトウェアの責任の範疇ではないかもしれません。（あったらあったで便利だと思います！）

個人的には「とりあえずMisskeyを稼働させるドキュメント」は公式に案内されていますが、「ステップアップしてスケールさせていくときのドキュメント」が公開されるととてもとても良いと思います。

syuilo commented 3 hours ago

「ステップアップしてスケールさせていくときのドキュメント」が公開されるととてもとても良いと思います。

FYI: https://misskey-hub.net/ja/docs/for-admin/install/resources/scale-out/

fruitriin commented 2 hours ago

おお、そのドキュメントはすでにあるのですね！ではこちらを拡張させていくのが良いと思います。

ところで、これは完全にオフトピックですが、 Postgresのレプリケーションは確かに有効にできるのですが、私の環境ではファイルのアップロードのときにアップロードの完了しなくなる、あるいはエラーがでるようになる副作用があるので（これはSQLのInsertが完了するよりも早くSelectしてしまうからだと思うのですが）一番最初にある項目にもかかわらず私はそれを有効にして運用することができません…… https://github.com/misskey-dev/misskey/issues/10897

eternal-flame-AD commented 2 hours ago

Docker composeはプライベートネットワーク（SQL環境は複数のコマンドが必要）ので Misskeyが全部自動的にしてマニュアルvacuum必要がないと思いました。　

appで実装の代わりに公式サイトにdocker-compose/local deployment環境に必要なメインテナンスを記入してのも良いと思います。

「MisskeyがDBのメインテナンス機能がない　docker exec psql ... VACUUM ANALYZE;　は定期的に実行するを勧めます。」などの言葉では明確に述べるできたら良いとおもいます。

VACUUMはScalingの領域ではないので https://misskey-hub.net/ja/docs/for-admin/install/guides/docker/ には適切と思います？

eternal-flame-AD commented 2 hours ago

誤解された

AUTO VACUUMはDockerized Postgresのが難しいとおもいます ("internal_network"でPostgresサーバーアクセスできるAuto vacuum daemonが必要） https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM

fruitriin commented 2 hours ago

Docker固有の事情はよくわからないのですが、 dockerコンテナにsshして postgres.conf の auto_vacuumを enableにすればよいのでは、と思いましたが永続化保証がないですね。ううむ。

eternal-flame-AD commented 2 hours ago

Autovacuumは既にonけど何故か"note"をVacuumしていない。。 (Transaction locks?　自動的なVacuumは使用中のテーブルはLockしません)

misskey@localhost:misskey> show autovacuum;
+------------+
| autovacuum |
|------------|
| on         |
+------------+
SHOW 1
Time: 0.142s
misskey@localhost:misskey> show autovacuum_vacuum_threshold;
+-----------------------------+
| autovacuum_vacuum_threshold |
|-----------------------------|
| 50                          |
+-----------------------------+
SHOW 1
Time: 0.143s
misskey@localhost:misskey> show autovacuum_vacuum_insert_threshold;
+------------------------------------+
| autovacuum_vacuum_insert_threshold |
|------------------------------------|
| 1000                               |
+------------------------------------+
SHOW 1
Time: 0.143s
misskey@localhost:misskey> select relname, last_autovacuum from pg_stat_all_tables where schemaname = 'public' and relname = 'note';
+---------+-----------------+
| relname | last_autovacuum |
|---------+-----------------|
| note    | <null>          |
+---------+-----------------+
SELECT 1
Time: 0.144s
misskey@localhost:misskey> select relname, last_autovacuum from pg_stat_all_tables where last_autovacuum is not null and schemaname = 'public' order by last_autovacuum desc;
+-------------------------+-------------------------------+
| relname                 | last_autovacuum               |
|-------------------------+-------------------------------|
| __chart__active_users   | 2024-11-16 00:00:23.928789+00 |
| user                    | 2024-11-15 12:19:32.058+00    |
| note_unread             | 2024-11-14 18:31:58.262931+00 |
| __chart__federation     | 2024-11-14 16:57:55.838571+00 |
| user_note_pining        | 2024-11-14 12:03:49.898469+00 |
| user_profile            | 2024-11-14 11:30:55.466137+00 |
| retention_aggregation   | 2024-11-14 00:00:57.582109+00 |
| __chart_day__federation | 2024-11-13 02:57:27.227732+00 |
| hashtag                 | 2024-11-11 19:29:18.180407+00 |
| instance                | 2024-11-10 18:54:25.823239+00 |
| user_publickey          | 2024-11-07 15:09:11.314558+00 |
| access_token            | 2024-11-07 08:22:54.832434+00 |
+-------------------------+-------------------------------+

syuilo commented 1 hour ago

前vacuumするAPIあってコントロールパネルから叩けたけどなんで廃止したんだっけ

misskey-dev / misskey