pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17755: database queries get stuck for certain IDs
3+ messages / 3 participants
[nested] [flat]
* BUG #17755: database queries get stuck for certain IDs
@ 2023-01-20 10:26 PG Bug reporting form <noreply@postgresql.org>
2023-01-20 10:42 ` Re: BUG #17755: database queries get stuck for certain IDs Pavol Sekeres <pavol.sekeres8@gmail.com>
2023-01-20 17:19 ` Re: BUG #17755: database queries get stuck for certain IDs Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2023-01-20 10:26 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: pavol.sekeres8@gmail.com
The following bug has been logged on the website:
Bug reference: 17755
Logged by: Pavol Sekeres
Email address: pavol.sekeres8@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system: CentOS Linux 7 (Core)
Description:
Hello,
we are using PostgreSQL version 9.6.8 on our production environment which we
operate ourselves. We are facing issue with the PostgreSQL database.
When we use select/insert query on a specific table it runs fine 99.99% of
the time. But we are doing many inserts per day from our app (around
100.000) and it happens to us almost every week that some
query to this table gets stuck. Then you can list it using pg_stat_activity
and see there that it is running long(hours, days, even weeks). From this
point the only option we have is to kill this process in which the query
runs.
Unless we do this, the database gets busy by this process infinitely running
query and the cpu usage rises dramatically causing database drop-outs.
The problem is just with this table and we are using many in our database.
Some further information is: this problematic table is called request_datas
and has just two columns : 1. request_id - is unlimited varchar containing
the string representation of uuid, 2. request_data - is again unlimited
varchar containing string representation of JSON object
We noticed that the insert/select queries which get stuck and run infinitely
have specific request_id - starting with 'a6bda' for example
'a6bda748-0e31-4da6-8fed-331c6c4a7cdb'.
Also we are not always querying the database directly but we are using java
app with JDBC connections. However the select query gets stuck even when
triggered from the psql console.
From our point of view we are using the database normally and there is no
reason why simple select query where id is equal to some value should get
stuck and run infinitely.
Do you know what could be the cause for our problems and possibly suggest
any solution?
Thank you,
Best regards,
Pavol
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17755: database queries get stuck for certain IDs
2023-01-20 10:26 BUG #17755: database queries get stuck for certain IDs PG Bug reporting form <noreply@postgresql.org>
@ 2023-01-20 10:42 ` Pavol Sekeres <pavol.sekeres8@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Pavol Sekeres @ 2023-01-20 10:42 UTC (permalink / raw)
To: pavol.sekeres8@gmail.com; pgsql-bugs@lists.postgresql.org
Hello,
I would like to add one more info. We are using database replication and
this issue is happening only on primary server. If we try select on our
stand by copy (exact replica of our database) the query doesn't get stuck
and runs quickly.
Thank you,
Best regards,
Pavol
On Fri, Jan 20, 2023 at 11:27 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17755
> Logged by: Pavol Sekeres
> Email address: pavol.sekeres8@gmail.com
> PostgreSQL version: Unsupported/Unknown
> Operating system: CentOS Linux 7 (Core)
> Description:
>
> Hello,
>
> we are using PostgreSQL version 9.6.8 on our production environment which
> we
> operate ourselves. We are facing issue with the PostgreSQL database.
> When we use select/insert query on a specific table it runs fine 99.99% of
> the time. But we are doing many inserts per day from our app (around
> 100.000) and it happens to us almost every week that some
> query to this table gets stuck. Then you can list it using pg_stat_activity
> and see there that it is running long(hours, days, even weeks). From this
> point the only option we have is to kill this process in which the query
> runs.
> Unless we do this, the database gets busy by this process infinitely
> running
> query and the cpu usage rises dramatically causing database drop-outs.
> The problem is just with this table and we are using many in our database.
> Some further information is: this problematic table is called request_datas
> and has just two columns : 1. request_id - is unlimited varchar containing
> the string representation of uuid, 2. request_data - is again unlimited
> varchar containing string representation of JSON object
> We noticed that the insert/select queries which get stuck and run
> infinitely
> have specific request_id - starting with 'a6bda' for example
> 'a6bda748-0e31-4da6-8fed-331c6c4a7cdb'.
> Also we are not always querying the database directly but we are using java
> app with JDBC connections. However the select query gets stuck even when
> triggered from the psql console.
>
> From our point of view we are using the database normally and there is no
> reason why simple select query where id is equal to some value should get
> stuck and run infinitely.
> Do you know what could be the cause for our problems and possibly suggest
> any solution?
>
> Thank you,
> Best regards,
> Pavol
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17755: database queries get stuck for certain IDs
2023-01-20 10:26 BUG #17755: database queries get stuck for certain IDs PG Bug reporting form <noreply@postgresql.org>
@ 2023-01-20 17:19 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2023-01-20 17:19 UTC (permalink / raw)
To: pavol.sekeres8@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> we are using PostgreSQL version 9.6.8 on our production environment which we
> operate ourselves.
You realize, I hope, that you are running a very old minor release
of a branch that's been out of support entirely for more than a year.
You might consider consulting our release note archives
https://www.postgresql.org/docs/release/
to see if your problem matches up with any of the large number of
post-9.6.8 fixes.
> We noticed that the insert/select queries which get stuck and run infinitely
> have specific request_id - starting with 'a6bda' for example
> 'a6bda748-0e31-4da6-8fed-331c6c4a7cdb'.
Smells a little bit like index corruption, perhaps. I'd try reindexing
whatever index(es) exist on that column. But if that doesn't fix it ...
> Do you know what could be the cause for our problems and possibly suggest
> any solution?
Running ancient minor releases is fundamentally unsound database
administration practice. At minimum you should be running the last
9.6.x minor release (9.6.24); but really now would be a good time
to be thinking about upgrading to some still-in-support branch.
If there is an actual Postgres problem here, and it wasn't fixed
by 9.6.24, we aren't ever going to fix it in that branch.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-01-20 17:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-20 10:26 BUG #17755: database queries get stuck for certain IDs PG Bug reporting form <noreply@postgresql.org>
2023-01-20 10:42 ` Pavol Sekeres <pavol.sekeres8@gmail.com>
2023-01-20 17:19 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox