From: ess.bee59@gmail.com
To: pgsql-bugs@lists.postgresql.org
Subject: BUG #18005: PSQL Process hangs in parallel mode / complement information
Date: Fri, 30 Jun 2023 15:19:02 +0200
Message-ID: <003e01d9ab55$704b4050$50e1c0f0$@gmail.com> (raw)
Hello,
As I hope that the problem reported in BUG # 18005 can be reproduced on
your site, I will now attach some script-files to start the SQL´s and my
"postgresql.conf" file.
Prerequisites:
-A server with:
Ubuntu-2204-jammy-amd64-base (if possible)
At least 128 GB RAM,
At least 8 CPUs
At least 2 TB NVME
Of course Postgresql (14.8)
OSM2PGSQL (version 1.7.2 if possible, the attached LUA file is not
compatible with 1.6.x versions)
Fast Internet connection to download the OSM maps (pbf format)
Please find attached some shell and SQL scripts, that should be stored in
the same directory.
A sub-directories "pbf" should be created, the 2 log files are created
during the run in background (nohup)
-rw-r--r-- 1 postgres postgres 26278 Jun 17 20:49 all.sql
-rwxrwxr-x 1 postgres postgres 256 Jun 15 16:48 create-tags.sh
-rw-r--r-- 1 postgres postgres 51 Jun 15 16:47 extend.sql
-rw-rw-r-- 1 postgres postgres 183568 Jun 27 07:33 gen-europe.log
-rwxrwxr-x 1 postgres postgres 46 Jun 15 16:48 gen-europe.sh
-rw-rw-r-- 1 postgres postgres 164758 Jun 26 16:34 gen-rest.log
-rwxrwxr-x 1 postgres postgres 354 Jun 26 14:14 gen-rest.sh
-rwxrwxr-x 1 postgres postgres 163 Jun 15 16:49 get-pbf.sh
-rw-r--r-- 1 postgres postgres 5646 Jun 15 16:49 mycfg.lua
-rwxrwxr-x 1 postgres postgres 252 Jun 16 13:20 noh-gen-all.sh
drwxrwxr-x 2 postgres postgres 4096 Jun 27 07:33 pbf
Grant execution rights and start with
./noh-gen-all.sh
After 2 / 3 hours I could see in the log "gen-rest.log" the described
hanging SELECT!
I had 3 times by 3 tests:
(
-- create tags for noise
-- create raw data
-- when several highways-segments are producing noise, aggregate the
noises using the "ST_Union" of the segments!
-- (better as using "sum" or "max" that do not deliver good factors)
SELECT
m.osm_id losmid, m.highway lhighway, q.highway as qhighway,
q.maxspeed_class,
case............
)
The select needs in non-parallel mode nearly 55 minutes for "asia" and 45
minutes for "north-america, less for the other continents.
To be sure that the SELECT hangs, consider to look at the ps_stat_activity:
the time stamps of the hanging SQL´s (1 or 3 processes) are very strange
(as if the SQL never started at all)
Regards
EssBee
Attachments:
noh-gen-all.sh (application/octet-stream, 86B) download
all.sql (application/octet-stream, 25.7K) download
create-tags.sh (application/octet-stream, 257B) download
extend.sql (application/octet-stream, 52B) download
gen-europe.log (application/octet-stream, 179.3K) download
gen-europe.sh (application/octet-stream, 47B) download
gen-rest.log (application/octet-stream, 160.9K) download
gen-rest.sh (application/octet-stream, 355B) download
get-pbf.sh (application/octet-stream, 164B) download
mycfg.lua (application/octet-stream, 5.5K) download
postgresql.conf (application/octet-stream, 28.4K) download
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: pgsql-bugs@postgresql.org
Cc: ess.bee59@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18005: PSQL Process hangs in parallel mode / complement information
In-Reply-To: <003e01d9ab55$704b4050$50e1c0f0$@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox