public inbox for [email protected]
help / color / mirror / Atom feedGoogle Cloud PostgreSQL Upgrade v11 to 15
2+ messages / 2 participants
[nested] [flat]
* Google Cloud PostgreSQL Upgrade v11 to 15
@ 2024-10-10 10:06 SOzcn <[email protected]>
2024-10-10 11:58 ` Re: Google Cloud PostgreSQL Upgrade v11 to 15 Miroslav Mladenov Ivanov <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: SOzcn @ 2024-10-10 10:06 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Google Cloud PostgreSQL Upgrade v11 to 15
2024-10-10 10:06 Google Cloud PostgreSQL Upgrade v11 to 15 SOzcn <[email protected]>
@ 2024-10-10 11:58 ` Miroslav Mladenov Ivanov <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Miroslav Mladenov Ivanov @ 2024-10-10 11:58 UTC (permalink / raw)
To: SOzcn <[email protected]>; +Cc: Pgsql-admin <[email protected]>
Hello ,
Maybe you should try pg_hint_plan to control the execution plan for your
problematic queries. You should check for execution plans for queries
before the upgrade and try to generate similar plans to these .
On Thu, Oct 10, 2024 at 1:07 PM SOzcn <[email protected]> wrote:
> 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.
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-10-10 11:58 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-10 10:06 Google Cloud PostgreSQL Upgrade v11 to 15 SOzcn <[email protected]>
2024-10-10 11:58 ` Miroslav Mladenov Ivanov <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox