Re: Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3/4?
От | Marco Boeringa |
---|---|
Тема | Re: Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3/4? |
Дата | |
Msg-id | f24e578d-3f05-3770-2ccd-64dbbe9814db@boeringa.demon.nl обсуждение исходный текст |
Ответ на | Re: Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3/4? (Bruce Momjian <bruce@momjian.us>) |
Список | pgsql-bugs |
Hi Bruce, As requested. Note that this is on very capable hardware in the form of an HP Z840 workstation with NVMe. I have processed the entire OpenStreetMap "Planet" file with this hardware and configuration with success, it actually fails on a much smaller Geofabrik "Italy" extract: version | ---------------------------------------------------------------------------------------------------------------------------------+ PostgreSQL 14.4 (Ubuntu 14.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit| |name |current_setting |source | |--------------------------------|-----------------------------------------------------------------|--------------------| |application_name |DBeaver 22.1.2 - SQLEditor <Script-2.sql> |session | |autovacuum_work_mem |8000kB |configuration file | |checkpoint_completion_target |0.9 |configuration file | |checkpoint_timeout |5min |configuration file | |client_encoding |UTF8 |client | |cluster_name |14/main |configuration file | |cursor_tuple_fraction |1 |configuration file | |DateStyle |ISO, DMY |client | |default_statistics_target |1000 |configuration file | |default_text_search_config |pg_catalog.english |configuration file | |dynamic_shared_memory_type |posix |configuration file | |effective_cache_size |100GB |configuration file | |effective_io_concurrency |500 |configuration file | |extra_float_digits |3 |session | |jit |on |configuration file | |lc_messages |en_US.UTF-8 |configuration file | |lc_monetary |nl_NL.UTF-8 |configuration file | |lc_numeric |nl_NL.UTF-8 |configuration file | |lc_time |nl_NL.UTF-8 |configuration file | |listen_addresses |localhost, |configuration file | |log_destination |stderr |configuration file | |log_line_prefix |%m [%p] %q%u@%d |configuration file | |log_rotation_age |1h |configuration file | |log_rotation_size |10000kB |configuration file | |log_statement |none |configuration file | |log_timezone |Europe/Amsterdam |configuration file | |log_truncate_on_rotation |on |configuration file | |logging_collector |off |configuration file | |maintenance_io_concurrency |500 |configuration file | |maintenance_work_mem |8000MB |configuration file | |max_connections |1000 |configuration file | |max_parallel_maintenance_workers|28 |configuration file | |max_parallel_workers |128 |configuration file | |max_parallel_workers_per_gather |56 |configuration file | |max_stack_depth |2MB |environment variable| |max_wal_senders |0 |configuration file | |max_wal_size |25GB |configuration file | |max_worker_processes |128 |configuration file | |min_wal_size |1GB |configuration file | |parallel_leader_participation |off |configuration file | |parallel_setup_cost |100 |configuration file | |parallel_tuple_cost |0.025 |configuration file | |password_encryption |md5 |configuration file | |port |5433 |configuration file | |random_page_cost |1 |configuration file | |search_path |osm, public, "$user" |session | |shared_buffers |75GB |configuration file | |ssl |off |configuration file | |ssl_cert_file |server.crt |configuration file | |ssl_key_file |server.key |configuration file | |synchronous_commit |off |configuration file | |temp_buffers |8000MB |configuration file | |temp_tablespaces |osm_i |configuration file | |TimeZone |Europe/Berlin |client | |track_activity_query_size |10000B |configuration file | |wal_compression |on |configuration file | |wal_level |minimal |configuration file | |work_mem |2000MB |configuration file | Op 22-7-2022 om 21:07 schreef Bruce Momjian: > On Fri, Jul 22, 2022 at 09:56:06AM +0200, Marco Boeringa wrote: >> Unfortunately, after more testing, it turns out this issue still persists in >> PostgreSQL 14.4. >> >> I have now encountered exactly the same problem as described in the original >> issue below: One autovacuum session that never finishes with no wait event (or >> is just incredibly slow and not finishing after many hours although it should >> in minutes considering the relative small dataset and normal operation), and >> the "client backend" and "parallel worker" stuck on the same wait events as >> listed below with the same "SELECT COUNT (*)" SQL statement. >> >> One thing to note as well, besides this being workstation level hardware with >> ECC RAM, is that I now also activated 'pg_checksums' on the PostgreSQL >> databases, and reloaded all data, so all data should now have checksums. No >> PostgreSQL error at all is generated via the ODBC connection I use to access >> and update the database when this happens and PostgreSQL appears stuck on the >> autovacuum. So I guess this now means I can now pretty much exclude a hardware >> error, and this must be some software issue, considering the checksums. > You might want to run these queries and show us the output, in case it > suggests a cause: > > SELECT version(); > > -- non-default server settings > SELECT name, current_setting(name), source > FROM pg_settings > WHERE source NOT IN ('default', 'override'); >
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Bruce MomjianДата:
Сообщение: Re: Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3/4?
Следующее
От: Marco BoeringaДата:
Сообщение: Re: Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3/4?