CODEWITHSUNDEEP
postgresqlgoogle-cloud-platformgoogle-cloud-sql
Dustin Michels
Dustin Michels

Reputation: 3226

Google Cloud SQL - Deciphering Postgres log & viewing temporary log file

I have a Postgres instance running on Google Cloud SQL. I am having some trouble deciphering messages in the online "Logs Viewer".

I'm getting lots of "info" level messages that look like this, repeated over and over again.

[i] 2019-06-12 16:19:05.376 MDT [119402]: [2-1] db=cleanchemidata,user=cleanchemiadmin STATEMENT: SELECT MAX("cc_systemdataview"."time_stamp") AS "time_stamp__max" FROM "cc_systemdataview" WHERE "cc_systemdataview"."job_id" = 72
[i] 2019-06-12 16:19:06.213 MDT [119401]: [1-1] db=cleanchemidata,user=cleanchemiadmin LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp119401.0", size 38330368

I created that user and database, and I know why my application is making that select statement.

I don't understand why this select statement is being printed in the log (but no other SQL statements from my application are). It doesn't look like an error?

The temporary log file, at path "base/pgsql_tmp/pgsql_tmp119401.0", might have answers but I cannot figure out how to access it. I've tried opening "cloud shell" and navigating the file system but cannot find this "base" or "pgsql_tmp" folder or any log files.

Any ideas on why this STATEMENT message is being displayed, or how to access this temporary log file?

Upvotes: 1

Views: 954

Answers (1)

Maxim
Maxim

Reputation: 4431

As per the following link for PostgreSQL performance monitoring:

This log event is emitted when a temporary file was written, used as part of normal operations, and then deleted again.

Commonly you would see this when a query could not allocate enough memory due to work_mem restrictions, and had to use a file on disk to perform a sort or hash operation.

You can find more details, as well as recommended actions to be taken in the link provided.

I’m unfamiliar with your DB constraints but, I’d look into optimizing said query, as queries that do not use temporary files usually perform better.

As for accessing the log file, unfortunately, you are currently not able to view the temp files, since, Cloud SQL is a fully-managed service. The only way to visualize the logs is through Stackdriver logging as described here.

It is important to understand that the Cloud Shell is a temporary VM instance on its own. Its main purpose is to make it easy for you to manage your projects and resources without having to install the Google Cloud SDK. Its content has no relation to the Cloud SQL's instance file-system, hence you are not seeing the directories you mention.

Upvotes: 3

Related Questions