Peertube: High CPU usage from Postgres

[Nutomic]

Recently my instance has very high CPU usage from the postgres process. I dont remember seeing this before the Peertube 2.0 upgrade, so I believe it was a change from that update which caused the increased CPU usage. Especially the idle processes are strange. I am not familiar with postgres performance analysis tools, but it shouldnt be too hard to find the queries responsible for this.

Peertube version: 2.0.0
Postgres version: 10.11

[Nutomic]

This is how it looks within minutes of rebooting, literally all the CPU is taken up by Postgres. Any idea how I can find the cause for this? I didnt find any good instructions online.

[tcitworld]

You can log queries and inspect them to try to get a hint.

The following query also shows the current running queries:

SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

A tool like PgBadger may come in handy as well.

[Nutomic]

Here is the output from your query:

pid  |       age       | usename  |                                                                                                                                                  query                                                                                                                                                                                 
------+-----------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
26 |                 | peertube | 
24 |                 |          | 
22 |                 |          | 
21 |                 |          | 
23 |                 |          | 
1194 | 00:00:00.006865 | peertube | SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1195 | 00:00:00.101169 | peertube | SELECT "VideoModel".*, "Thumbnails"."id" AS "Thumbnails.id", "Thumbnails"."filename" AS "Thumbnails.filename", "Thumbnails"."height" AS "Thumbnails.height", "Thumbnails"."width" AS "Thumbnails.width", "Thumbnails"."type" AS "Thumbnails.type", "Thumbnails"."fileUrl" AS "Thumbnails.fileUrl", "Thumbnails"."automaticallyGenerated" AS "Thumbnails.automaticallyGenerated", "Thumbnails"."videoId" AS "Thumbnails.videoId", "Thumbnails"."videoPlaylistId" AS "Thumbnails.videoPlaylistId", "Thumbnails"."createdAt" AS "Thumbnails.createdAt", "Thumbnails"."updatedAt" AS "Thumbnails.updatedAt" FROM (SELECT "VideoModel"."id", "VideoModel"."uuid", "VideoModel"."name", "VideoModel"."category", "VideoModel"."licence", "VideoModel"."language", "VideoModel"."privacy", "VideoModel"."nsfw", "VideoModel"."description", "VideoModel"."support", "VideoModel"."duration", "VideoModel"."views", "VideoModel"."likes", "VideoModel"."dislikes", "VideoModel"."remote", "VideoModel"."url", "VideoModel"."commentsEnabled", "VideoModel"."downloadEna
1196 | 00:00:00.195913 | peertube | SELECT "id", "filename", "fileUrl", "onDisk", "createdAt", "updatedAt" FROM "avatar" AS "AvatarModel" WHERE "AvatarModel"."filename" = 'c396cba0-e7b8-4ef4-bd18-622b33e6cafc.jpg' LIMIT 1;
1203 | 00:00:00.396939 | peertube | SELECT "id", "uuid", "name", "category", "licence", "language", "privacy", "nsfw", "description", "support", "duration", "views", "likes", "dislikes", "remote", "url", "commentsEnabled", "downloadEnabled", "waitTranscoding", "state", "publishedAt", "originallyPublishedAt", "channelId", "createdAt", "updatedAt", 0 as similarity FROM "video" AS "VideoModel" WHERE ("VideoModel"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND "VideoModel"."channelId" NOT IN (SELECT id FROM "videoChannel" WHERE "accountId" IN (SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (1) UNION ALL SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" WHERE "serverBlocklist"."accountId" IN (1))) AND (("VideoModel"."state" = 1 OR ("VideoModel"."state" = 2 AND "VideoModel"."waitTranscoding" = false)) AND "VideoModel"."privacy" = 1) AND "VideoModel"."id" IN (SELE
1209 | 00:00:00.396942 | peertube | SELECT "id", "uuid", "name", "category", "licence", "language", "privacy", "nsfw", "description", "support", "duration", "views", "likes", "dislikes", "remote", "url", "commentsEnabled", "downloadEnabled", "waitTranscoding", "state", "publishedAt", "originallyPublishedAt", "channelId", "createdAt", "updatedAt", 0 as similarity FROM "video" AS "VideoModel" WHERE ("VideoModel"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND "VideoModel"."channelId" NOT IN (SELECT id FROM "videoChannel" WHERE "accountId" IN (SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (1) UNION ALL SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" WHERE "serverBlocklist"."accountId" IN (1))) AND (("VideoModel"."state" = 1 OR ("VideoModel"."state" = 2 AND "VideoModel"."waitTranscoding" = false)) AND "VideoModel"."privacy" = 1) AND "VideoModel"."id" IN (SELE
1208 | 00:00:00.396944 | peertube | SELECT "id", "uuid", "name", "category", "licence", "language", "privacy", "nsfw", "description", "support", "duration", "views", "likes", "dislikes", "remote", "url", "commentsEnabled", "downloadEnabled", "waitTranscoding", "state", "publishedAt", "originallyPublishedAt", "channelId", "createdAt", "updatedAt", 0 as similarity FROM "video" AS "VideoModel" WHERE ("VideoModel"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND "VideoModel"."channelId" NOT IN (SELECT id FROM "videoChannel" WHERE "accountId" IN (SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (1) UNION ALL SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" WHERE "serverBlocklist"."accountId" IN (1))) AND (("VideoModel"."state" = 1 OR ("VideoModel"."state" = 2 AND "VideoModel"."waitTranscoding" = false)) AND "VideoModel"."privacy" = 1) AND "VideoModel"."id" IN (SELE
1202 | 00:00:00.396946 | peertube | SELECT "id", "uuid", "name", "category", "licence", "language", "privacy", "nsfw", "description", "support", "duration", "views", "likes", "dislikes", "remote", "url", "commentsEnabled", "downloadEnabled", "waitTranscoding", "state", "publishedAt", "originallyPublishedAt", "channelId", "createdAt", "updatedAt", 0 as similarity FROM "video" AS "VideoModel" WHERE ("VideoModel"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND "VideoModel"."channelId" NOT IN (SELECT id FROM "videoChannel" WHERE "accountId" IN (SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (1) UNION ALL SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" WHERE "serverBlocklist"."accountId" IN (1))) AND (("VideoModel"."state" = 1 OR ("VideoModel"."state" = 2 AND "VideoModel"."waitTranscoding" = false)) AND "VideoModel"."privacy" = 1) AND "VideoModel"."id" IN (SELE
1169 | 00:00:00.396949 | peertube | SELECT "id", "uuid", "name", "category", "licence", "language", "privacy", "nsfw", "description", "support", "duration", "views", "likes", "dislikes", "remote", "url", "commentsEnabled", "downloadEnabled", "waitTranscoding", "state", "publishedAt", "originallyPublishedAt", "channelId", "createdAt", "updatedAt", 0 as similarity FROM "video" AS "VideoModel" WHERE ("VideoModel"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND "VideoModel"."channelId" NOT IN (SELECT id FROM "videoChannel" WHERE "accountId" IN (SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (1) UNION ALL SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" WHERE "serverBlocklist"."accountId" IN (1))) AND (("VideoModel"."state" = 1 OR ("VideoModel"."state" = 2 AND "VideoModel"."waitTranscoding" = false)) AND "VideoModel"."privacy" = 1) AND "VideoModel"."id" IN (SELE
1197 | 00:00:01.209776 | peertube | SET client_min_messages TO warning;SET TIME ZONE INTERVAL '+00:00' HOUR TO MINUTE;
(14 rows)

Though right now the load has gone down, maybe someone was crawling my instance or something. Still that shouldnt cause so much load. I will look into the log analyzer later.

[Nutomic]

Okay here is a report from the last day, around 400.000 queries. The thing I noticed is that there are tons of SELECT queries. Some of the individual queries were executed more than 20.000 times! I'm sure some of this could be avoided by caching the query results.

http://nextcloud.nutomic.com/index.php/s/Qm7YrFNZ23tRoqL

| times executed | query
|-|-|
| 42,422 | select "AccountModel"."id", "AccountModel"."name", "AccountModel"."description", "AccountModel"."actorId", "AccountModel"."userId", "AccountModel"."applicationId", "AccountModel"."createdAt", "AccountModel"."updatedAt", "Actor"."id" AS "Actor.id", "Actor"."type" AS "Actor.type", "Actor"."preferredUsername" AS "Actor.preferredUsername", "Actor"."url" AS "Actor.url", "Actor"."publicKey" AS "Actor.publicKey", "Actor"."privateKey" AS "Actor.privateKey", "Actor"."followersCount" AS "Actor.followersCount", "Actor"."followingCount" AS "Actor.followingCount", "Actor"."inboxUrl" AS "Actor.inboxUrl", "Actor"."outboxUrl" AS "Actor.outboxUrl", "Actor"."sharedInboxUrl" AS "Actor.sharedInboxUrl", "Actor"."followersUrl" AS "Actor.followersUrl", "Actor"."followingUrl" AS "Actor.followingUrl", "Actor"."avatarId" AS "Actor.avatarId", "Actor"."serverId" AS "Actor.serverId", "Actor"."createdAt" AS "Actor.createdAt", "Actor"."updatedAt" AS "Actor.updatedAt", "Actor->Server"."id" AS "Actor.Server.id", "Actor->Server"."host" AS "Actor.Server.host", "Actor->Server"."redundancyAllowed" AS "Actor.Server.redundancyAllowed", "Actor->Server"."createdAt" AS "Actor.Server.createdAt", "Actor->Server"."updatedAt" AS "Actor.Server.updatedAt", "Actor->Avatar"."id" AS "Actor.Avatar.id", "Actor->Avatar"."filename" AS "Actor.Avatar.filename", "Actor->Avatar"."fileUrl" AS "Actor.Avatar.fileUrl", "Actor->Avatar"."onDisk" AS "Actor.Avatar.onDisk", "Actor->Avatar"."createdAt" AS "Actor.Avatar.createdAt", "Actor->Avatar"."updatedAt" AS "Actor.Avatar.updatedAt" from "account" AS "AccountModel" inner join "actor" AS "Actor" on "AccountModel"."actorId" = "Actor"."id" and "Actor"."preferredUsername" = ? left outer join "server" AS "Actor->Server" on "Actor"."serverId" = "Actor->Server"."id" left outer join "avatar" AS "Actor->Avatar" on "Actor"."avatarId" = "Actor->Avatar"."id" where ("AccountModel"."userId" is not null or "AccountModel"."applicationId" is not null) limit ?; |
| 37,854 | select "ActorModel"."id", "ActorModel"."type", "ActorModel"."preferredUsername", "ActorModel"."url", "ActorModel"."publicKey", "ActorModel"."privateKey", "ActorModel"."followersCount", "ActorModel"."followingCount", "ActorModel"."inboxUrl", "ActorModel"."outboxUrl", "ActorModel"."sharedInboxUrl", "ActorModel"."followersUrl", "ActorModel"."followingUrl", "ActorModel"."avatarId", "ActorModel"."serverId", "ActorModel"."createdAt", "ActorModel"."updatedAt", "Account"."id" AS "Account.id", "Account"."name" AS "Account.name", "Account"."description" AS "Account.description", "Account"."actorId" AS "Account.actorId", "Account"."userId" AS "Account.userId", "Account"."applicationId" AS "Account.applicationId", "Account"."createdAt" AS "Account.createdAt", "Account"."updatedAt" AS "Account.updatedAt", "VideoChannel"."id" AS "VideoChannel.id", "VideoChannel"."name" AS "VideoChannel.name", "VideoChannel"."description" AS "VideoChannel.description", "VideoChannel"."support" AS "VideoChannel.support", "VideoChannel"."actorId" AS "VideoChannel.actorId", "VideoChannel"."accountId" AS "VideoChannel.accountId", "VideoChannel"."createdAt" AS "VideoChannel.createdAt", "VideoChannel"."updatedAt" AS "VideoChannel.updatedAt", "VideoChannel->Account"."id" AS "VideoChannel.Account.id", "VideoChannel->Account"."name" AS "VideoChannel.Account.name", "VideoChannel->Account"."description" AS "VideoChannel.Account.description", "VideoChannel->Account"."actorId" AS "VideoChannel.Account.actorId", "VideoChannel->Account"."userId" AS "VideoChannel.Account.userId", "VideoChannel->Account"."applicationId" AS "VideoChannel.Account.applicationId", "VideoChannel->Account"."createdAt" AS "VideoChannel.Account.createdAt", "VideoChannel->Account"."updatedAt" AS "VideoChannel.Account.updatedAt", "VideoChannel->Account->Actor"."id" AS "VideoChannel.Account.Actor.id", "VideoChannel->Account->Actor"."type" AS "VideoChannel.Account.Actor.type", "VideoChannel->Account->Actor"."preferredUsername" AS "VideoChannel.Account.Actor.preferredUsername", "VideoChannel->Account->Actor"."url" AS "VideoChannel.Account.Actor.url", "VideoChannel->Account->Actor"."publicKey" AS "VideoChannel.Account.Actor.publicKey", "VideoChannel->Account->Actor"."privateKey" AS "VideoChannel.Account.Actor.privateKey", "VideoChannel->Account->Actor"."followersCount" AS "VideoChannel.Account.Actor.followersCount", "VideoChannel->Account->Actor"."followingCount" AS "VideoChannel.Account.Actor.followingCount", "VideoChannel->Account->Actor"."inboxUrl" AS "VideoChannel.Account.Actor.inboxUrl", "VideoChannel->Account->Actor"."outboxUrl" AS "VideoChannel.Account.Actor.outboxUrl", "VideoChannel->Account->Actor"."sharedInboxUrl" AS "VideoChannel.Account.Actor.sharedInboxUrl", "VideoChannel->Account->Actor"."followersUrl" AS "VideoChannel.Account.Actor.followersUrl", "VideoChannel->Account->Actor"."followingUrl" AS "VideoChannel.Account.Actor.followingUrl", "VideoChannel->Account->Actor"."avatarId" AS "VideoChannel.Account.Actor.avatarId", "VideoChannel->Account->Actor"."serverId" AS "VideoChannel.Account.Actor.serverId", "VideoChannel->Account->Actor"."createdAt" AS "VideoChannel.Account.Actor.createdAt", "VideoChannel->Account->Actor"."updatedAt" AS "VideoChannel.Account.Actor.updatedAt", "VideoChannel->Account->Actor->Server"."id" AS "VideoChannel.Account.Actor.Server.id", "VideoChannel->Account->Actor->Server"."host" AS "VideoChannel.Account.Actor.Server.host", "VideoChannel->Account->Actor->Server"."redundancyAllowed" AS "VideoChannel.Account.Actor.Server.redundancyAllowed", "VideoChannel->Account->Actor->Server"."createdAt" AS "VideoChannel.Account.Actor.Server.createdAt", "VideoChannel->Account->Actor->Server"."updatedAt" AS "VideoChannel.Account.Actor.Server.updatedAt", "VideoChannel->Account->Actor->Avatar"."id" AS "VideoChannel.Account.Actor.Avatar.id", "VideoChannel->Account->Actor->Avatar"."filename" AS "VideoChannel.Account.Actor.Avatar.filename", "VideoChannel->Account->Actor->Avatar"."fileUrl" AS "VideoChannel.Account.Actor.Avatar.fileUrl", "VideoChannel->Account->Actor->Avatar"."onDisk" AS "VideoChannel.Account.Actor.Avatar.onDisk", "VideoChannel->Account->Actor->Avatar"."createdAt" AS "VideoChannel.Account.Actor.Avatar.createdAt", "VideoChannel->Account->Actor->Avatar"."updatedAt" AS "VideoChannel.Account.Actor.Avatar.updatedAt", "Server"."id" AS "Server.id", "Server"."host" AS "Server.host", "Server"."redundancyAllowed" AS "Server.redundancyAllowed", "Server"."createdAt" AS "Server.createdAt", "Server"."updatedAt" AS "Server.updatedAt", "Avatar"."id" AS "Avatar.id", "Avatar"."filename" AS "Avatar.filename", "Avatar"."fileUrl" AS "Avatar.fileUrl", "Avatar"."onDisk" AS "Avatar.onDisk", "Avatar"."createdAt" AS "Avatar.createdAt", "Avatar"."updatedAt" AS "Avatar.updatedAt" from "actor" AS "ActorModel" left outer join "account" AS "Account" on "ActorModel"."id" = "Account"."actorId" left outer join ("videoChannel" AS "VideoChannel" inner join "account" AS "VideoChannel->Account" on "VideoChannel"."accountId" = "VideoChannel->Account"."id" inner join "actor" AS "VideoChannel->Account->Actor" on "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id" left outer join "server" AS "VideoChannel->Account->Actor->Server" on "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id" left outer join "avatar" AS "VideoChannel->Account->Actor->Avatar" on "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id") on "ActorModel"."id" = "VideoChannel"."actorId" left outer join "server" AS "Server" on "ActorModel"."serverId" = "Server"."id" left outer join "avatar" AS "Avatar" on "ActorModel"."avatarId" = "Avatar"."id" where "ActorModel"."preferredUsername" = ? and "ActorModel"."serverId" is null limit ?; |
| 20,529 | select "ActorModel"."id", "ActorModel"."type", "ActorModel"."preferredUsername", "ActorModel"."url", "ActorModel"."publicKey", "ActorModel"."privateKey", "ActorModel"."followersCount", "ActorModel"."followingCount", "ActorModel"."inboxUrl", "ActorModel"."outboxUrl", "ActorModel"."sharedInboxUrl", "ActorModel"."followersUrl", "ActorModel"."followingUrl", "ActorModel"."avatarId", "ActorModel"."serverId", "ActorModel"."createdAt", "ActorModel"."updatedAt", "Account"."id" AS "Account.id", "VideoChannel"."id" AS "VideoChannel.id" from "actor" AS "ActorModel" left outer join "account" AS "Account" on "ActorModel"."id" = "Account"."actorId" left outer join "videoChannel" AS "VideoChannel" on "ActorModel"."id" = "VideoChannel"."actorId" where "ActorModel"."url" = ? limit ?; |

Thanks @tcitworld for suggesting this tool!

[houdini69]

I have got the same behaviour of postgres on my instance (100% CPU).
Laurent

[houdini69]

[Chocobozzz]

Please check what are the queries that are running

[Nutomic]

@Chocobozzz Check my comment above, I linked a complete report.

[Chocobozzz]

my comment was addressed to @houdini69

[houdini69]

  6635 |                 |          | <insufficient privilege>
  6637 |                 | postgres | <insufficient privilege>
 10769 |                 |          | <insufficient privilege>
  6633 |                 |          | <insufficient privilege>
  6632 |                 |          | <insufficient privilege>
  6634 |                 |          | <insufficient privilege>
 10730 | 00:00:39.730571 | peertube | SELECT "id" FROM "video" AS "VideoModel" WHERE ("VideoModel"."id" NOT IN (SELECT "vi
deoBlacklist"."videoId" FROM "videoBlacklist") AND "VideoModel"."channelId" NOT IN (SELECT id FROM "videoChannel" WHERE "a
ccountId" IN (SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (1) UNION ALL SELECT "account"
."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id INNER JOIN "serverBlocklist"
 ON "actor"."serverId" = "serverBlocklist"."targetServerId" WHERE "serverBlocklist"."accountId" IN (1))) AND (("VideoModel"."state" = 1 OR ("VideoModel"."state" = 2 AND "VideoModel"."waitTranscoding" = false)) AND "VideoModel"."privacy" = 1) AND "VideoModel"."id" IN (SELECT "videoShare"."videoId" AS "id" FROM "videoShare" INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" WHERE "actorFollow"."actorId" = 1 UNION ALL SELECT "video"."id" AS "id" FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" INNER JOIN "account" ON "a
 10731 | 00:00:40.053201 | peertube | SELECT "id" FROM "video" AS "VideoModel" WHERE ("VideoModel"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND "VideoModel"."channelId" NOT IN (SELECT id FROM "videoChannel" WHERE "accountId" IN (SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (1) UNION ALL SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" WHERE "serverBlocklist"."accountId" IN (1))) AND (("VideoModel"."state" = 1 OR ("VideoModel"."state" = 2 AND "VideoModel"."waitTranscoding" = false)) AND "VideoModel"."privacy" = 1) AND "VideoModel"."id" IN (SELECT "videoShare"."videoId" AS "id" FROM "videoShare" INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" WHERE "actorFollow"."actorId" = 1 UNION ALL SELECT "video"."id" AS "id" FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" INNER JOIN "account" ON "a
 10344 | 00:14:11.317086 | peertube | SELECT "id" FROM "video" AS "VideoModel" WHERE ("VideoModel"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND "VideoModel"."channelId" NOT IN (SELECT id FROM "videoChannel" WHERE "accountId" IN (SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (1) UNION ALL SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" WHERE "serverBlocklist"."accountId" IN (1))) AND (("VideoModel"."state" = 1 OR ("VideoModel"."state" = 2 AND "VideoModel"."waitTranscoding" = false)) AND "VideoModel"."privacy" = 1) AND "VideoModel"."id" IN (SELECT "videoShare"."videoId" AS "id" FROM "videoShare" INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" WHERE "actorFollow"."actorId" = 1 UNION ALL SELECT "video"."id" AS "id" FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" INNER JOIN "account" ON "a
(9 lignes)

[houdini69]

I couldn't install PgBadger as my server run on armhf platform.

[houdini69]

I note this cpu activity at 100% as soon as I open the web browser.

[houdini69]

Ok after checking CPU activity of Postgres It seems normal to get high CPU activity peaks especially as my instance is only been running for a short time!

[Chocobozzz]

I made several SQL query improvements and cached some other ones. We'll see if it's better in the next peertube release. I also think it could be related to https://github.com/silicium14/peertube_index/issues/5 that crawls all instance videos.

[filmaidykai]

Hey just upgrade 2.0.0 to 2.1.0 and came same problem cpu working wit 100% any one find solution how to kill cpu?

[shirshak55]

@Chocobozzz no still the cpu usage is 100%

[shirshak55]

@Chocobozzz still not solved :(

[shirshak55]

@Chocobozzz is it due to postgres 9.6 or something like that?

[Chocobozzz]

What is the query that takes 100% of CPU of PostgreSQL?

[shirshak55]

is there any proper way to debug such stuff? I am not that pro is database stuff to be honest.

[Chocobozzz]

@shirshak55 To check running queries, see: https://github.com/Chocobozzz/PeerTube/issues/2326#issuecomment-565567356

The pgbadger file has expired.

[shirshak55]

@Chocobozzz here is link again. https://send.firefox.com/download/84044d16f54cb7f1/#zdnrCnInGluLrwYNYdgpAA

[filmaidykai]

Same isue problem and here https://github.com/YunoHost-Apps/peertube_ynh/issues/143

[shirshak55]

downgraded and works :(

[shirshak55]

I think this is every serious issue to be honest. It breaks everything in production and there is no even easy way to detect whats the problem. I checked queries and it looks fine . Doesn't seem to have deadlocks. So confused and tired with this issue. If chockbuzz wants i can give access to system to know the problem in depth.

[Chocobozzz]

Please send me an email (in my github profile)

[Chocobozzz]

I found the query that causes the issue. I don't understand why some postgresql don't like this query.

I rewrited the query using UNION ALL instead of OR, and that should fix the bug: https://github.com/Chocobozzz/PeerTube/commit/49be0fd3255db54cf9b038bed792eb0de0faf591

Can some people confirm it fixes your bug with peertube 2.1.0? Just replace the file in peertube-latest/dist/server/models/video/video.js by the file in this zip archive

[shirshak55]

@Chocobozzz thanks you :)

[shirshak55]

will report u once i upgrade it and try it..

[shirshak55]

@Chocobozzz i confirm this patch fixed the issue.

Upgraded to latest version
CPU usage was 90+%
Applied this patched
Restart peertube, nginx and postgres
Cpu usages below 10%

Omg didn't realize one query can create so much problem :(

[shirshak55]

@Nutomic can u also confirm ?

[Nutomic]

@shirshak55 I'm running with that Docker, its not that easy to replace a random file. Will definitely try once it gets in to a release.

[Chocobozzz]

Hello,

I'm rewriting the videos list SQL query to plain SQL (instead of using our ORM). Admins that had a CPU issue with PostgreSQL, could you run some of/all these queries and paste the output on pastebin (or send it by email) please?

Trending count

explain analyze SELECT COUNT(*) as "total" FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"INNER JOIN "actor" "accountActor" ON "account"."actorId" = "accountActor"."id" WHERE "video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND NOT EXISTS (  SELECT 1 FROM "accountBlocklist"   WHERE "accountBlocklist"."accountId" IN ('1', '2')   AND "accountBlocklist"."targetAccountId" = "account"."id" )AND NOT EXISTS (  SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN ('1', '2')   AND "serverBlocklist"."targetServerId" = "accountActor"."serverId") AND ("video"."state" = 1 OR ("video"."state" = 2 AND "video"."waitTranscoding" IS false)) AND ("video"."privacy" = 1 OR "video"."privacy" = 4) AND (  EXISTS (    SELECT 1 FROM "videoShare"     INNER JOIN "actorFollow" "actorFollowShare" ON "actorFollowShare"."targetActorId" = "videoShare"."actorId"     AND "actorFollowShare"."actorId" = 1 WHERE "videoShare"."videoId" = "video"."id"  )  OR  EXISTS (    SELECT 1 from "actorFollow"     WHERE "actorFollow"."targetActorId" = "videoChannel"."actorId" AND "actorFollow"."actorId" = 1  )  OR "video"."remote" IS FALSE)

Trending results

explain analyze SELECT "video".*, "VideoChannel"."id" AS "VideoChannel.id", "VideoChannel"."name" AS "VideoChannel.name", "VideoChannel"."description" AS "VideoChannel.description", "VideoChannel"."actorId" AS "VideoChannel.actorId", "VideoChannel->Actor"."id" AS "VideoChannel.Actor.id", "VideoChannel->Actor"."preferredUsername" AS "VideoChannel.Actor.preferredUsername", "VideoChannel->Actor"."url" AS "VideoChannel.Actor.url", "VideoChannel->Actor"."serverId" AS "VideoChannel.Actor.serverId", "VideoChannel->Actor"."avatarId" AS "VideoChannel.Actor.avatarId", "VideoChannel->Account"."id" AS "VideoChannel.Account.id", "VideoChannel->Account"."name" AS "VideoChannel.Account.name", "VideoChannel->Account->Actor"."id" AS "VideoChannel.Account.Actor.id", "VideoChannel->Account->Actor"."preferredUsername" AS "VideoChannel.Account.Actor.preferredUsername", "VideoChannel->Account->Actor"."url" AS "VideoChannel.Account.Actor.url", "VideoChannel->Account->Actor"."serverId" AS "VideoChannel.Account.Actor.serverId", "VideoChannel->Account->Actor"."avatarId" AS "VideoChannel.Account.Actor.avatarId", "VideoChannel->Actor->Server"."id" AS "VideoChannel.Actor.Server.id", "VideoChannel->Actor->Server"."host" AS "VideoChannel.Actor.Server.host", "VideoChannel->Actor->Avatar"."id" AS "VideoChannel.Actor.Avatar.id", "VideoChannel->Actor->Avatar"."filename" AS "VideoChannel.Actor.Avatar.filename", "VideoChannel->Actor->Avatar"."fileUrl" AS "VideoChannel.Actor.Avatar.fileUrl", "VideoChannel->Actor->Avatar"."onDisk" AS "VideoChannel.Actor.Avatar.onDisk", "VideoChannel->Actor->Avatar"."createdAt" AS "VideoChannel.Actor.Avatar.createdAt", "VideoChannel->Actor->Avatar"."updatedAt" AS "VideoChannel.Actor.Avatar.updatedAt", "VideoChannel->Account->Actor->Server"."id" AS "VideoChannel.Account.Actor.Server.id", "VideoChannel->Account->Actor->Server"."host" AS "VideoChannel.Account.Actor.Server.host", "VideoChannel->Account->Actor->Avatar"."id" AS "VideoChannel.Account.Actor.Avatar.id", "VideoChannel->Account->Actor->Avatar"."filename" AS "VideoChannel.Account.Actor.Avatar.filename", "VideoChannel->Account->Actor->Avatar"."fileUrl" AS "VideoChannel.Account.Actor.Avatar.fileUrl", "VideoChannel->Account->Actor->Avatar"."onDisk" AS "VideoChannel.Account.Actor.Avatar.onDisk", "VideoChannel->Account->Actor->Avatar"."createdAt" AS "VideoChannel.Account.Actor.Avatar.createdAt", "VideoChannel->Account->Actor->Avatar"."updatedAt" AS "VideoChannel.Account.Actor.Avatar.updatedAt", "Thumbnails"."id" AS "Thumbnails.id", "Thumbnails"."type" AS "Thumbnails.type", "Thumbnails"."filename" AS "Thumbnails.filename", "userVideoHistory"."id" AS "userVideoHistory.id", "userVideoHistory"."currentTime" AS "userVideoHistory.currentTime" FROM (SELECT "video"."id", COALESCE(SUM("videoView"."views"), 0) AS "videoViewsSum", 0 as similarity FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"INNER JOIN "actor" "accountActor" ON "account"."actorId" = "accountActor"."id" LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= '2020-03-03 10:30:26.046 +01:00' WHERE "video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND NOT EXISTS (  SELECT 1 FROM "accountBlocklist"   WHERE "accountBlocklist"."accountId" IN ('1', '2')   AND "accountBlocklist"."targetAccountId" = "account"."id" )AND NOT EXISTS (  SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN ('1', '2')   AND "serverBlocklist"."targetServerId" = "accountActor"."serverId") AND ("video"."state" = 1 OR ("video"."state" = 2 AND "video"."waitTranscoding" IS false)) AND ("video"."privacy" = 1 OR "video"."privacy" = 4) AND (  EXISTS (    SELECT 1 FROM "videoShare"     INNER JOIN "actorFollow" "actorFollowShare" ON "actorFollowShare"."targetActorId" = "videoShare"."actorId"     AND "actorFollowShare"."actorId" = 1 WHERE "videoShare"."videoId" = "video"."id"  )  OR  EXISTS (    SELECT 1 from "actorFollow"     WHERE "actorFollow"."targetActorId" = "videoChannel"."actorId" AND "actorFollow"."actorId" = 1  )  OR "video"."remote" IS FALSE) GROUP BY "video"."id"  ORDER BY "videoViewsSum" DESC, "video"."views" DESC LIMIT 25 OFFSET 0) AS "tmp" INNER JOIN "video" ON "tmp"."id" = "video"."id" INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id" INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id" INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id" INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id" LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id" LEFT OUTER JOIN "avatar" AS "VideoChannel->Actor->Avatar" ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id" LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id" LEFT OUTER JOIN "avatar" AS "VideoChannel->Account->Actor->Avatar" ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id" LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId" LEFT OUTER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = 1 ORDER BY "videoViewsSum" DESC, "video"."views" DESC

Recently added count

explain analyze SELECT COUNT(*) as "total" FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"INNER JOIN "actor" "accountActor" ON "account"."actorId" = "accountActor"."id" WHERE "video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND NOT EXISTS (  SELECT 1 FROM "accountBlocklist"   WHERE "accountBlocklist"."accountId" IN ('1', '2')   AND "accountBlocklist"."targetAccountId" = "account"."id" )AND NOT EXISTS (  SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN ('1', '2')   AND "serverBlocklist"."targetServerId" = "accountActor"."serverId") AND ("video"."state" = 1 OR ("video"."state" = 2 AND "video"."waitTranscoding" IS false)) AND ("video"."privacy" = 1 OR "video"."privacy" = 4) AND (  EXISTS (    SELECT 1 FROM "videoShare"     INNER JOIN "actorFollow" "actorFollowShare" ON "actorFollowShare"."targetActorId" = "videoShare"."actorId"     AND "actorFollowShare"."actorId" = 1 WHERE "videoShare"."videoId" = "video"."id"  )  OR  EXISTS (    SELECT 1 from "actorFollow"     WHERE "actorFollow"."targetActorId" = "videoChannel"."actorId" AND "actorFollow"."actorId" = 1  )  OR "video"."remote" IS FALSE)

Recently added results

explain analyze SELECT "video".*, "VideoChannel"."id" AS "VideoChannel.id", "VideoChannel"."name" AS "VideoChannel.name", "VideoChannel"."description" AS "VideoChannel.description", "VideoChannel"."actorId" AS "VideoChannel.actorId", "VideoChannel->Actor"."id" AS "VideoChannel.Actor.id", "VideoChannel->Actor"."preferredUsername" AS "VideoChannel.Actor.preferredUsername", "VideoChannel->Actor"."url" AS "VideoChannel.Actor.url", "VideoChannel->Actor"."serverId" AS "VideoChannel.Actor.serverId", "VideoChannel->Actor"."avatarId" AS "VideoChannel.Actor.avatarId", "VideoChannel->Account"."id" AS "VideoChannel.Account.id", "VideoChannel->Account"."name" AS "VideoChannel.Account.name", "VideoChannel->Account->Actor"."id" AS "VideoChannel.Account.Actor.id", "VideoChannel->Account->Actor"."preferredUsername" AS "VideoChannel.Account.Actor.preferredUsername", "VideoChannel->Account->Actor"."url" AS "VideoChannel.Account.Actor.url", "VideoChannel->Account->Actor"."serverId" AS "VideoChannel.Account.Actor.serverId", "VideoChannel->Account->Actor"."avatarId" AS "VideoChannel.Account.Actor.avatarId", "VideoChannel->Actor->Server"."id" AS "VideoChannel.Actor.Server.id", "VideoChannel->Actor->Server"."host" AS "VideoChannel.Actor.Server.host", "VideoChannel->Actor->Avatar"."id" AS "VideoChannel.Actor.Avatar.id", "VideoChannel->Actor->Avatar"."filename" AS "VideoChannel.Actor.Avatar.filename", "VideoChannel->Actor->Avatar"."fileUrl" AS "VideoChannel.Actor.Avatar.fileUrl", "VideoChannel->Actor->Avatar"."onDisk" AS "VideoChannel.Actor.Avatar.onDisk", "VideoChannel->Actor->Avatar"."createdAt" AS "VideoChannel.Actor.Avatar.createdAt", "VideoChannel->Actor->Avatar"."updatedAt" AS "VideoChannel.Actor.Avatar.updatedAt", "VideoChannel->Account->Actor->Server"."id" AS "VideoChannel.Account.Actor.Server.id", "VideoChannel->Account->Actor->Server"."host" AS "VideoChannel.Account.Actor.Server.host", "VideoChannel->Account->Actor->Avatar"."id" AS "VideoChannel.Account.Actor.Avatar.id", "VideoChannel->Account->Actor->Avatar"."filename" AS "VideoChannel.Account.Actor.Avatar.filename", "VideoChannel->Account->Actor->Avatar"."fileUrl" AS "VideoChannel.Account.Actor.Avatar.fileUrl", "VideoChannel->Account->Actor->Avatar"."onDisk" AS "VideoChannel.Account.Actor.Avatar.onDisk", "VideoChannel->Account->Actor->Avatar"."createdAt" AS "VideoChannel.Account.Actor.Avatar.createdAt", "VideoChannel->Account->Actor->Avatar"."updatedAt" AS "VideoChannel.Account.Actor.Avatar.updatedAt", "Thumbnails"."id" AS "Thumbnails.id", "Thumbnails"."type" AS "Thumbnails.type", "Thumbnails"."filename" AS "Thumbnails.filename", "userVideoHistory"."id" AS "userVideoHistory.id", "userVideoHistory"."currentTime" AS "userVideoHistory.currentTime" FROM (SELECT "video"."id", 0 as similarity FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"INNER JOIN "actor" "accountActor" ON "account"."actorId" = "accountActor"."id" WHERE "video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND NOT EXISTS (  SELECT 1 FROM "accountBlocklist"   WHERE "accountBlocklist"."accountId" IN ('1', '2')   AND "accountBlocklist"."targetAccountId" = "account"."id" )AND NOT EXISTS (  SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN ('1', '2')   AND "serverBlocklist"."targetServerId" = "accountActor"."serverId") AND ("video"."state" = 1 OR ("video"."state" = 2 AND "video"."waitTranscoding" IS false)) AND ("video"."privacy" = 1 OR "video"."privacy" = 4) AND (  EXISTS (    SELECT 1 FROM "videoShare"     INNER JOIN "actorFollow" "actorFollowShare" ON "actorFollowShare"."targetActorId" = "videoShare"."actorId"     AND "actorFollowShare"."actorId" = 1 WHERE "videoShare"."videoId" = "video"."id"  )  OR  EXISTS (    SELECT 1 from "actorFollow"     WHERE "actorFollow"."targetActorId" = "videoChannel"."actorId" AND "actorFollow"."actorId" = 1  )  OR "video"."remote" IS FALSE)   ORDER BY "video"."publishedAt" DESC, "video"."id" ASC LIMIT 25 OFFSET 0) AS "tmp" INNER JOIN "video" ON "tmp"."id" = "video"."id" INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id" INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id" INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id" INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id" LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id" LEFT OUTER JOIN "avatar" AS "VideoChannel->Actor->Avatar" ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id" LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id" LEFT OUTER JOIN "avatar" AS "VideoChannel->Account->Actor->Avatar" ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id" LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId" LEFT OUTER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = 1 ORDER BY "video"."publishedAt" DESC, "video"."id" ASC

Search count

explain analyze WITH "trigramSearch" AS (  SELECT "video"."id",   similarity(lower(immutable_unaccent("video"."name")), lower(immutable_unaccent('tte'))) as similarity   FROM "video"   WHERE lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent('tte')) OR         lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent('%tte%'))) SELECT COUNT(*) as "total" FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"INNER JOIN "actor" "accountActor" ON "account"."actorId" = "accountActor"."id" LEFT JOIN "trigramSearch" ON "video"."id" = "trigramSearch"."id" WHERE "video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND NOT EXISTS (  SELECT 1 FROM "accountBlocklist"   WHERE "accountBlocklist"."accountId" IN ('1', '2')   AND "accountBlocklist"."targetAccountId" = "account"."id" )AND NOT EXISTS (  SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN ('1', '2')   AND "serverBlocklist"."targetServerId" = "accountActor"."serverId") AND ("video"."state" = 1 OR ("video"."state" = 2 AND "video"."waitTranscoding" IS false)) AND ("video"."privacy" = 1 OR "video"."privacy" = 4) AND (  EXISTS (    SELECT 1 FROM "videoShare"     INNER JOIN "actorFollow" "actorFollowShare" ON "actorFollowShare"."targetActorId" = "videoShare"."actorId"     AND "actorFollowShare"."actorId" = 1 WHERE "videoShare"."videoId" = "video"."id"  )  OR  EXISTS (    SELECT 1 from "actorFollow"     WHERE "actorFollow"."targetActorId" = "videoChannel"."actorId" AND "actorFollow"."actorId" = 1  )  OR "video"."remote" IS FALSE) AND (  "trigramSearch"."id" IS NOT NULL OR   EXISTS (    SELECT 1 FROM "videoTag"     INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId"     WHERE lower("tag"."name") = 'tte'     AND "video"."id" = "videoTag"."videoId"  ))

Search results

explain analyze  SELECT "video".*, "VideoChannel"."id" AS "VideoChannel.id", "VideoChannel"."name" AS "VideoChannel.name", "VideoChannel"."description" AS "VideoChannel.description", "VideoChannel"."actorId" AS "VideoChannel.actorId", "VideoChannel->Actor"."id" AS "VideoChannel.Actor.id", "VideoChannel->Actor"."preferredUsername" AS "VideoChannel.Actor.preferredUsername", "VideoChannel->Actor"."url" AS "VideoChannel.Actor.url", "VideoChannel->Actor"."serverId" AS "VideoChannel.Actor.serverId", "VideoChannel->Actor"."avatarId" AS "VideoChannel.Actor.avatarId", "VideoChannel->Account"."id" AS "VideoChannel.Account.id", "VideoChannel->Account"."name" AS "VideoChannel.Account.name", "VideoChannel->Account->Actor"."id" AS "VideoChannel.Account.Actor.id", "VideoChannel->Account->Actor"."preferredUsername" AS "VideoChannel.Account.Actor.preferredUsername", "VideoChannel->Account->Actor"."url" AS "VideoChannel.Account.Actor.url", "VideoChannel->Account->Actor"."serverId" AS "VideoChannel.Account.Actor.serverId", "VideoChannel->Account->Actor"."avatarId" AS "VideoChannel.Account.Actor.avatarId", "VideoChannel->Actor->Server"."id" AS "VideoChannel.Actor.Server.id", "VideoChannel->Actor->Server"."host" AS "VideoChannel.Actor.Server.host", "VideoChannel->Actor->Avatar"."id" AS "VideoChannel.Actor.Avatar.id", "VideoChannel->Actor->Avatar"."filename" AS "VideoChannel.Actor.Avatar.filename", "VideoChannel->Actor->Avatar"."fileUrl" AS "VideoChannel.Actor.Avatar.fileUrl", "VideoChannel->Actor->Avatar"."onDisk" AS "VideoChannel.Actor.Avatar.onDisk", "VideoChannel->Actor->Avatar"."createdAt" AS "VideoChannel.Actor.Avatar.createdAt", "VideoChannel->Actor->Avatar"."updatedAt" AS "VideoChannel.Actor.Avatar.updatedAt", "VideoChannel->Account->Actor->Server"."id" AS "VideoChannel.Account.Actor.Server.id", "VideoChannel->Account->Actor->Server"."host" AS "VideoChannel.Account.Actor.Server.host", "VideoChannel->Account->Actor->Avatar"."id" AS "VideoChannel.Account.Actor.Avatar.id", "VideoChannel->Account->Actor->Avatar"."filename" AS "VideoChannel.Account.Actor.Avatar.filename", "VideoChannel->Account->Actor->Avatar"."fileUrl" AS "VideoChannel.Account.Actor.Avatar.fileUrl", "VideoChannel->Account->Actor->Avatar"."onDisk" AS "VideoChannel.Account.Actor.Avatar.onDisk", "VideoChannel->Account->Actor->Avatar"."createdAt" AS "VideoChannel.Account.Actor.Avatar.createdAt", "VideoChannel->Account->Actor->Avatar"."updatedAt" AS "VideoChannel.Account.Actor.Avatar.updatedAt", "Thumbnails"."id" AS "Thumbnails.id", "Thumbnails"."type" AS "Thumbnails.type", "Thumbnails"."filename" AS "Thumbnails.filename", "userVideoHistory"."id" AS "userVideoHistory.id", "userVideoHistory"."currentTime" AS "userVideoHistory.currentTime" FROM (WITH "trigramSearch" AS (  SELECT "video"."id",   similarity(lower(immutable_unaccent("video"."name")), lower(immutable_unaccent('tte'))) as similarity   FROM "video"   WHERE lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent('tte')) OR         lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent('%tte%'))) SELECT "video"."id", COALESCE("trigramSearch"."similarity", 0) as similarity FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"INNER JOIN "actor" "accountActor" ON "account"."actorId" = "accountActor"."id" LEFT JOIN "trigramSearch" ON "video"."id" = "trigramSearch"."id" WHERE "video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND NOT EXISTS (  SELECT 1 FROM "accountBlocklist"   WHERE "accountBlocklist"."accountId" IN ('1', '2')   AND "accountBlocklist"."targetAccountId" = "account"."id" )AND NOT EXISTS (  SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN ('1', '2')   AND "serverBlocklist"."targetServerId" = "accountActor"."serverId") AND ("video"."state" = 1 OR ("video"."state" = 2 AND "video"."waitTranscoding" IS false)) AND ("video"."privacy" = 1 OR "video"."privacy" = 4) AND (  EXISTS (    SELECT 1 FROM "videoShare"     INNER JOIN "actorFollow" "actorFollowShare" ON "actorFollowShare"."targetActorId" = "videoShare"."actorId"     AND "actorFollowShare"."actorId" = 1 WHERE "videoShare"."videoId" = "video"."id"  )  OR  EXISTS (    SELECT 1 from "actorFollow"     WHERE "actorFollow"."targetActorId" = "videoChannel"."actorId" AND "actorFollow"."actorId" = 1  )  OR "video"."remote" IS FALSE) AND (  "trigramSearch"."id" IS NOT NULL OR   EXISTS (    SELECT 1 FROM "videoTag"     INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId"     WHERE lower("tag"."name") = 'tte'     AND "video"."id" = "videoTag"."videoId"  ))   ORDER BY "similarity" DESC, "video"."id" ASC LIMIT 10 OFFSET 0) AS "tmp" INNER JOIN "video" ON "tmp"."id" = "video"."id" INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id" INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id" INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id" INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id" LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id" LEFT OUTER JOIN "avatar" AS "VideoChannel->Actor->Avatar" ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id" LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id" LEFT OUTER JOIN "avatar" AS "VideoChannel->Account->Actor->Avatar" ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id" LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId" LEFT OUTER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = 1 ORDER BY "similarity" DESC, "video"."id" ASC

With languages

explain analyze SELECT "video".*, "VideoChannel"."id" AS "VideoChannel.id", "VideoChannel"."name" AS "VideoChannel.name", "VideoChannel"."description" AS "VideoChannel.description", "VideoChannel"."actorId" AS "VideoChannel.actorId", "VideoChannel->Actor"."id" AS "VideoChannel.Actor.id", "VideoChannel->Actor"."preferredUsername" AS "VideoChannel.Actor.preferredUsername", "VideoChannel->Actor"."url" AS "VideoChannel.Actor.url", "VideoChannel->Actor"."serverId" AS "VideoChannel.Actor.serverId", "VideoChannel->Actor"."avatarId" AS "VideoChannel.Actor.avatarId", "VideoChannel->Account"."id" AS "VideoChannel.Account.id", "VideoChannel->Account"."name" AS "VideoChannel.Account.name", "VideoChannel->Account->Actor"."id" AS "VideoChannel.Account.Actor.id", "VideoChannel->Account->Actor"."preferredUsername" AS "VideoChannel.Account.Actor.preferredUsername", "VideoChannel->Account->Actor"."url" AS "VideoChannel.Account.Actor.url", "VideoChannel->Account->Actor"."serverId" AS "VideoChannel.Account.Actor.serverId", "VideoChannel->Account->Actor"."avatarId" AS "VideoChannel.Account.Actor.avatarId", "VideoChannel->Actor->Server"."id" AS "VideoChannel.Actor.Server.id", "VideoChannel->Actor->Server"."host" AS "VideoChannel.Actor.Server.host", "VideoChannel->Actor->Avatar"."id" AS "VideoChannel.Actor.Avatar.id", "VideoChannel->Actor->Avatar"."filename" AS "VideoChannel.Actor.Avatar.filename", "VideoChannel->Actor->Avatar"."fileUrl" AS "VideoChannel.Actor.Avatar.fileUrl", "VideoChannel->Actor->Avatar"."onDisk" AS "VideoChannel.Actor.Avatar.onDisk", "VideoChannel->Actor->Avatar"."createdAt" AS "VideoChannel.Actor.Avatar.createdAt", "VideoChannel->Actor->Avatar"."updatedAt" AS "VideoChannel.Actor.Avatar.updatedAt", "VideoChannel->Account->Actor->Server"."id" AS "VideoChannel.Account.Actor.Server.id", "VideoChannel->Account->Actor->Server"."host" AS "VideoChannel.Account.Actor.Server.host", "VideoChannel->Account->Actor->Avatar"."id" AS "VideoChannel.Account.Actor.Avatar.id", "VideoChannel->Account->Actor->Avatar"."filename" AS "VideoChannel.Account.Actor.Avatar.filename", "VideoChannel->Account->Actor->Avatar"."fileUrl" AS "VideoChannel.Account.Actor.Avatar.fileUrl", "VideoChannel->Account->Actor->Avatar"."onDisk" AS "VideoChannel.Account.Actor.Avatar.onDisk", "VideoChannel->Account->Actor->Avatar"."createdAt" AS "VideoChannel.Account.Actor.Avatar.createdAt", "VideoChannel->Account->Actor->Avatar"."updatedAt" AS "VideoChannel.Account.Actor.Avatar.updatedAt", "Thumbnails"."id" AS "Thumbnails.id", "Thumbnails"."type" AS "Thumbnails.type", "Thumbnails"."filename" AS "Thumbnails.filename", "userVideoHistory"."id" AS "userVideoHistory.id", "userVideoHistory"."currentTime" AS "userVideoHistory.currentTime" FROM (SELECT "video"."id", COALESCE(SUM("videoView"."views"), 0) AS "videoViewsSum", 0 as similarity FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"INNER JOIN "actor" "accountActor" ON "account"."actorId" = "accountActor"."id" LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= '2020-03-03 11:47:51.802 +01:00' WHERE "video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND NOT EXISTS (  SELECT 1 FROM "accountBlocklist"   WHERE "accountBlocklist"."accountId" IN ('1', '2')   AND "accountBlocklist"."targetAccountId" = "account"."id" )AND NOT EXISTS (  SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN ('1', '2')   AND "serverBlocklist"."targetServerId" = "accountActor"."serverId") AND ("video"."state" = 1 OR ("video"."state" = 2 AND "video"."waitTranscoding" IS false)) AND ("video"."privacy" = 1 OR "video"."privacy" = 4) AND (  EXISTS (    SELECT 1 FROM "videoShare"     INNER JOIN "actorFollow" "actorFollowShare" ON "actorFollowShare"."targetActorId" = "videoShare"."actorId"     AND "actorFollowShare"."actorId" = 1 WHERE "videoShare"."videoId" = "video"."id"  )  OR  EXISTS (    SELECT 1 from "actorFollow"     WHERE "actorFollow"."targetActorId" = "videoChannel"."actorId" AND "actorFollow"."actorId" = 1  )  OR "video"."remote" IS FALSE) AND ("video"."language" IN ('en', 'fr') OR   EXISTS (    SELECT 1 FROM "videoCaption" WHERE "videoCaption"."language"     IN ('en', 'fr') AND     "videoCaption"."videoId" = "video"."id"  )) GROUP BY "video"."id"  ORDER BY "videoViewsSum" DESC, "video"."views" DESC LIMIT 25 OFFSET 0) AS "tmp" INNER JOIN "video" ON "tmp"."id" = "video"."id" INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id" INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id" INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id" INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id" LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id" LEFT OUTER JOIN "avatar" AS "VideoChannel->Actor->Avatar" ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id" LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id" LEFT OUTER JOIN "avatar" AS "VideoChannel->Account->Actor->Avatar" ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id" LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId" LEFT OUTER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = 1 ORDER BY "videoViewsSum" DESC, "video"."views" DESC

[shirshak55]

@Chocobozzz do u want to run it on old version of peertube or latest one?

[Chocobozzz]

@Chocobozzz do u want to run it on old version of peertube or latest one?

Whatever suits you :)