merge: Add configuration option for the "query is slow" warning (!1061)

View MR for information: https://activitypub.software/TransFem-org/Sharkey/-/merge_requests/1061

Approved-by: Marie <github@yuugi.dev>
Approved-by: Julia <julia@insertdomain.name>

1 files changed, 31 insertions, 16 deletions

diff --git a/packages/backend/src/postgres.ts b/packages/backend/src/postgres.ts
index 34fca6dd92..b4bd934972 100644
--- a/packages/backend/src/postgres.ts
+++ b/packages/backend/src/postgres.ts
@@ -98,9 +98,12 @@ pg.types.setTypeParser(20, Number);
 export const dbLogger = new MisskeyLogger('db');
 
 const sqlLogger = dbLogger.createSubLogger('sql', 'gray');
+const sqlMigrateLogger = sqlLogger.createSubLogger('migrate');
+const sqlSchemaLogger = sqlLogger.createSubLogger('schema');
 
 export type LoggerProps = {
 	disableQueryTruncation?: boolean;
+	enableQueryLogging?: boolean;
 	enableQueryParamLogging?: boolean;
 	printReplicationMode?: boolean,
 };
@@ -112,7 +115,7 @@ function highlightSql(sql: string) {
 }
 
 function truncateSql(sql: string) {
-	return sql.length > 100 ? `${sql.substring(0, 100)}...` : sql;
+	return sql.length > 100 ? `${sql.substring(0, 100)} [truncated]` : sql;
 }
 
 function stringifyParameter(param: any) {
@@ -136,7 +139,7 @@ class MyCustomLogger implements Logger {
 			modded = truncateSql(modded);
 		}
 
-		return highlightSql(modded);
+		return this.props.enableQueryLogging ? highlightSql(modded) : modded;
 	}
 
 	@bindThis
@@ -150,6 +153,8 @@ class MyCustomLogger implements Logger {
 
 	@bindThis
 	public logQuery(query: string, parameters?: any[], queryRunner?: QueryRunner) {
+		if (!this.props.enableQueryLogging) return;
+
 		const prefix = (this.props.printReplicationMode && queryRunner)
 			? `[${queryRunner.getReplicationMode()}] `
 			: undefined;
@@ -161,7 +166,8 @@ class MyCustomLogger implements Logger {
 		const prefix = (this.props.printReplicationMode && queryRunner)
 			? `[${queryRunner.getReplicationMode()}] `
 			: undefined;
-		sqlLogger.error(this.transformQueryLog(query, { prefix }), this.transformParameters(parameters));
+		const transformed = this.transformQueryLog(query, { prefix });
+		sqlLogger.error(`Query error (${error}): ${transformed}`, this.transformParameters(parameters));
 	}
 
 	@bindThis
@@ -169,22 +175,32 @@ class MyCustomLogger implements Logger {
 		const prefix = (this.props.printReplicationMode && queryRunner)
 			? `[${queryRunner.getReplicationMode()}] `
 			: undefined;
-		sqlLogger.warn(this.transformQueryLog(query, { prefix }), this.transformParameters(parameters));
+		const transformed = this.transformQueryLog(query, { prefix });
+		sqlLogger.warn(`Query is slow (${time}ms): ${transformed}`, this.transformParameters(parameters));
 	}
 
 	@bindThis
 	public logSchemaBuild(message: string) {
-		sqlLogger.info(message);
+		sqlSchemaLogger.debug(message);
 	}
 
 	@bindThis
-	public log(message: string) {
-		sqlLogger.info(message);
+	public log(level: 'log' | 'info' | 'warn', message: string) {
+		switch (level) {
+			case 'log':
+			case 'info': {
+				sqlLogger.info(message);
+				break;
+			}
+			case 'warn': {
+				sqlLogger.warn(message);
+			}
+		}
 	}
 
 	@bindThis
 	public logMigration(message: string) {
-		sqlLogger.info(message);
+		sqlMigrateLogger.debug(message);
 	}
 }
 
@@ -314,14 +330,13 @@ export function createPostgresDataSource(config: Config) {
 			},
 		} : false,
 		logging: log,
-		logger: log
-			? new MyCustomLogger({
-				disableQueryTruncation: config.logging?.sql?.disableQueryTruncation,
-				enableQueryParamLogging: config.logging?.sql?.enableQueryParamLogging,
-				printReplicationMode: !!config.dbReplications,
-			})
-			: undefined,
-		maxQueryExecutionTime: 300,
+		logger: new MyCustomLogger({
+			disableQueryTruncation: config.logging?.sql?.disableQueryTruncation,
+			enableQueryLogging: log,
+			enableQueryParamLogging: config.logging?.sql?.enableQueryParamLogging,
+			printReplicationMode: !!config.dbReplications,
+		}),
+		maxQueryExecutionTime: config.db.slowQueryThreshold,
 		entities: entities,
 		migrations: ['../../migration/*.js'],
 	});