Fediver

NHKニュースのRSSに対して直接curl叩くと200返ってくるのでNHK側の問題ではないはず

jj1guj@yuuki ~/misskey (master)> curl https://www.nhk.or.jp/rss/news/cat0.xml -o /dev/null -w "%{http_code}\n"
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  5240  100  5240    0     0  41838      0 --:--:-- --:--:-- --:--:-- 41920
200

藍ちゃんから出てるエラー

app-1  | Uncaught exception: 
app-1  | RequestError
app-1  |     at ClientRequest.<anonymous> (file:///ai/node_modules/got/dist/source/core/index.js107)
app-1  |     at Object.onceWrapper (node639:26)
app-1  |     at ClientRequest.emit (node536:35)
app-1  |     at emitErrorEvent (node104:11)
app-1  |     at TLSSocket.socketErrorListener (node512:5)
app-1  |     at TLSSocket.emit (node524:28)
app-1  |     at emitErrorNT (node:internal/streams/destroy8)
app-1  |     at emitErrorCloseNT (node:internal/streams/destroy3)
app-1  |     at process.processTicksAndRejections (node:internal/process/task_queues:90:21)AggregateError [ETIMEDOUT]: 
app-1  |     at internalConnectMultiple (node1128:18)
app-1  |     at afterConnectMultiple (node1693:7) {
app-1  |   input: undefined,
app-1  |   code: 'ETIMEDOUT',
app-1  |   timings: {
app-1  |     start: 1736517638497,
app-1  |     socket: 1736517638497,
app-1  |     lookup: 1736517638504,
app-1  |     connect: undefined,
app-1  |     secureConnect: undefined,
app-1  |     upload: undefined,
app-1  |     response: undefined,
app-1  |     end: undefined,
app-1  |     error: 1736517775311,
app-1  |     abort: undefined,
app-1  |     phases: {
app-1  |       wait: 0,
app-1  |       dns: 7,
app-1  |       tcp: undefined,
app-1  |       tls: undefined,
app-1  |       request: undefined,
app-1  |       firstByte: undefined,
app-1  |       download: undefined,
app-1  |       total: 136814
app-1  |     }
app-1  |   },

そして制約が欠けてる、、、
以上が起きてるdb↓

misskey=# \d "__chart_day__per_user_notes"
                                         Table "public.__chart_day__per_user_notes"
      Column       |          Type          | Collation | Nullable |                         Default                         
-------------------+------------------------+-----------+----------+---------------------------------------------------------
 id                | integer                |           | not null | nextval('__chart_day__per_user_notes_id_seq'::regclass)
 date              | integer                |           | not null | 
 group             | character varying(128) |           | not null | 
 ___total          | integer                |           | not null | '0'::bigint
 ___inc            | smallint               |           | not null | '0'::bigint
 ___dec            | smallint               |           | not null | '0'::bigint
 ___diffs_normal   | smallint               |           | not null | '0'::bigint
 ___diffs_reply    | smallint               |           | not null | '0'::bigint
 ___diffs_renote   | smallint               |           | not null | '0'::bigint
 ___diffs_withFile | smallint               |           | not null | '0'::smallint
Indexes:
    "PK_58bab6b6d3ad9310cbc7460fd28" PRIMARY KEY, btree (id)

misskey=#  \d "__chart_day__per_user_notes"
                                         Table "public.__chart_day__per_user_notes"
      Column       |          Type          | Collation | Nullable |                         Default                         
-------------------+------------------------+-----------+----------+---------------------------------------------------------
 id                | integer                |           | not null | nextval('__chart_day__per_user_notes_id_seq'::regclass)
 date              | integer                |           | not null | 
 group             | character varying(128) |           | not null | 
 ___total          | integer                |           | not null | '0'::bigint
 ___inc            | smallint               |           | not null | '0'::bigint
 ___dec            | smallint               |           | not null | '0'::bigint
 ___diffs_normal   | smallint               |           | not null | '0'::bigint
 ___diffs_reply    | smallint               |           | not null | '0'::bigint
 ___diffs_renote   | smallint               |           | not null | '0'::bigint
 ___diffs_withFile | smallint               |           | not null | '0'::smallint
Indexes:
    "PK_58bab6b6d3ad9310cbc7460fd28" PRIMARY KEY, btree (id)
    "IDX_c5545d4b31cdc684034e33b81c" UNIQUE, btree (date, "group")
    "UQ_c5545d4b31cdc684034e33b81c3" UNIQUE CONSTRAINT, btree (date, "group")

hmm
なんかweb側からこんなログが出てたぞ…

query is slow: SELECT "ChartXinstance"."id" AS "ChartXinstance_id", "ChartXinstance"."date" AS "ChartXinstance_date", "ChartXinstance"."group" AS "ChartXinstance_group", "ChartXinstance"."___requests_failed" AS "ChartXinstance____requests_failed", "ChartXinstance"."___requests_succeeded" AS "ChartXinstance____requests_succeeded", "ChartXinstance"."___requests_received" AS "ChartXinstance____requests_received", "ChartXinstance"."___notes_total" AS "ChartXinstance____notes_total", "ChartXinstance"."___notes_inc" AS "ChartXinstance____notes_inc", "ChartXinstance"."___notes_dec" AS "ChartXinstance____notes_dec", "ChartXinstance"."___notes_diffs_normal" AS "ChartXinstance____notes_diffs_normal", "ChartXinstance"."___notes_diffs_reply" AS "ChartXinstance____notes_diffs_reply", "ChartXinstance"."___notes_diffs_renote" AS "ChartXinstance____notes_diffs_renote", "ChartXinstance"."___notes_diffs_withFile" AS "ChartXinstance____notes_diffs_withFile", "ChartXinstance"."___users_total" AS "ChartXinstance____users_total", "ChartXinstance"."___users_inc" AS "ChartXinstance____users_inc", "ChartXinstance"."___users_dec" AS "ChartXinstance____users_dec", "ChartXinstance"."___following_total" AS "ChartXinstance____following_total", "ChartXinstance"."___following_inc" AS "ChartXinstance____following_inc", "ChartXinstance"."___following_dec" AS "ChartXinstance____following_dec", "ChartXinstance"."___followers_total" AS "ChartXinstance____followers_total", "ChartXinstance"."___followers_inc" AS "ChartXinstance____followers_inc", "ChartXinstance"."___followers_dec" AS "ChartXinstance____followers_dec", "ChartXinstance"."___drive_totalFiles" AS "ChartXinstance____drive_totalFiles", "ChartXinstance"."___drive_incFiles" AS "ChartXinstance____drive_incFiles", "ChartXinstance"."___drive_decFiles" AS "ChartXinstance____drive_decFiles", "ChartXinstance"."___drive_incUsage" AS "ChartXinstance____drive_incUsage", "ChartXinstance"."___drive_decUsage" AS "ChartXinstance____drive_decUsage" FROM "__chart__instance" "ChartXinstance" WHERE (("ChartXinstance"."date" = $1) AND ("ChartXinstance"."group" = $2)) LIMIT 1

うーむ、、、、
残るログ出力がこれだけになってしまった、、、
もうわからん、、、

db-1  | 2024-11-10 11:19:41.166 UTC [28] LOG:  checkpoint starting: time
db-1  | 2024-11-10 11:21:36.140 UTC [28] LOG:  checkpoint complete: wrote 315 buffers (1.9%); 0 WAL file(s) added, 1 removed, 0 recycled; write=114.910 s, sync=0.058 s, total=114.974 s; sync files=153, longest=0.008 s, average=0.001 s; distance=8561 kB, estimate=9781 kB

HIT!! ​​​​​​​​​​​​
Total:6
#BlobCrash
https://misskey.gg/play/9ptjiw953i

重くなるときこれの直後に重くなるんだよなあ

db-1  | 2024-10-11 19:16:23.786 UTC [27] LOG:  checkpoint starting: time
db-1  | 2024-10-11 19:16:54.442 UTC [27] LOG:  checkpoint complete: wrote 305 buffers (1.9%); 0 WAL file(s) added, 0 removed, 0 recycled; write=30.633 s, sync=0.019 s, total=30.656 s; sync files=72, longest=0.006 s, average=0.001 s; distance=1939 kB, estimate=2839 kB

@morotesovtannu@msk.ilnk.info 確かにそのとおりです.
そこそこ大きい企業ですし,事前に対策は取れたように感じます.
私の引用投稿もGMOペパボを援護する意図は全くありません.

また, https://misskey.pepabo.com/about#charts 内,Total number of notesより
ここ最近あまり投稿されていませんし,登録時のCaptchaなど
基本的な対策すらとられていなさそうなので,まともに管理されていないのかもしれないですね.

HIT!! ​​​​​​​​​​
Total:5
#BlobCrash
https://misskey.gg/play/9ptjiw953i