public inbox for [email protected]
help / color / mirror / Atom feedFrom: SOzcn <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: Google Cloud PostgreSQL Upgrade v11 to 15
Date: Thu, 10 Oct 2024 13:06:40 +0300
Message-ID: <CAJyV5AZ=PdsWJtNfFy6tZ+WuvLd76muH2qfKLpMOW+qGDp0Ckw@mail.gmail.com> (raw)
Hello All,
I hope everything is well with you. I have some issues after upgrading the
GCP.
We performed an upgrade on Google Cloud PostgreSQL from version 11 to 15.
Initially, we executed this in our test environment, and there were no
major issues observed.
However, we unfortunately encountered a performance issue with one
particular query after the upgrade of Production. Below is my detailed
analysis, and I would appreciate your comments on the matter.
When we carried out the upgrade in the Production environment, we faced
several major problems. First and foremost, there was a relentless disk
issue and high CPU consumption. We realized that the root cause of these
two problems was the activation of the log_duration parameter, which was
generating thousands of logs per second, and we resolved it.
We also discovered that the execution plans had changed after the upgrade,
which was expected. Since it was performing a table scan, retrieving a
total of 200 records through paging took minutes. By setting the
random_page_cost parameter to 4, I managed to direct it towards an Index
Scan. As a result, the 200 records were fetched within 3-5 seconds.
At this point, the query is running consistently on both version 11 and
version 15, taking 3-5 seconds to retrieve 200 records. However, while
version 11 processed 20,000 records in 15-20 seconds, version 15 is taking
1.5-2 minutes for the same amount.
The query contains many joins and subqueries. Based on this, I maximized
the values of the work_mem parameter and ran tests, but no improvement was
observed.
In my research on similar cases, I found that some cases reported success
by disabling the jit parameter, but it didn’t help in my case.
I also tried standard analyze and index maintenance, including rebuilding
the indexes. Additionally, I took the problematic database to my local
environment and conducted tests on versions 15 and 16. However, there was
no change in performance.
After analyzing the query, I tried certain indexing methods on the
identified costly parts, but they didn’t help. Additionally, I tested by
disabling the subqueries and using temporary tables, but that also did not
lead to a solution.
I look forward to your comments on this matter.
view thread (2+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Google Cloud PostgreSQL Upgrade v11 to 15
In-Reply-To: <CAJyV5AZ=PdsWJtNfFy6tZ+WuvLd76muH2qfKLpMOW+qGDp0Ckw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox