Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lK6t9-0005sS-3i for pgsql-hackers@arkaria.postgresql.org; Wed, 10 Mar 2021 22:01:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lK6t8-0007PN-1u for pgsql-hackers@arkaria.postgresql.org; Wed, 10 Mar 2021 22:01:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lK5LI-0003qv-AS for pgsql-hackers@lists.postgresql.org; Wed, 10 Mar 2021 20:22:04 +0000 Received: from mail-qk1-x72d.google.com ([2607:f8b0:4864:20::72d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lK5LD-0006L0-GV for pgsql-hackers@postgresql.org; Wed, 10 Mar 2021 20:22:03 +0000 Received: by mail-qk1-x72d.google.com with SMTP id a9so18251815qkn.13 for ; Wed, 10 Mar 2021 12:21:59 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=discourse.org; s=google; h=from:subject:to:message-id:date:user-agent:mime-version :content-transfer-encoding:content-language; bh=LgTs+4C9M34O8h3mccZ/UOr2dio5vzBNo/1jkBOVFiA=; b=HvfZjPMReoWUsEMdM/KZRDtPXX6AOi8vUW0uU9mywukxmAdWKAU6GU3bQr38J6AYdy 9HJmBjUfS+4ENyjvJqo2GMp1MmopPefcE1XEh0Pcqgk8LCpUYwGQ7tUVbH9WG6ORzK2T 2442tzPzNnDHOe/IpG8NffbZwa+Govx+Lc7+A= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:subject:to:message-id:date:user-agent :mime-version:content-transfer-encoding:content-language; bh=LgTs+4C9M34O8h3mccZ/UOr2dio5vzBNo/1jkBOVFiA=; b=YxUwLU7sWIHGFK/18C9pqW/rinHixGEkCEKuvhhxbdUMXwnhkN+aKhG7OA4Yc3du7t D98uHKUtQHqygQrToOErpRlpLV1V4EIxh6nzKlB7J3PXRRKzXcnQ7f5LwrinR9NezEqu MQwvfDaT+yM6sNApR5DRo0sBaDI/5UNNvG6T6reQM9qST6Qdf1/MVocZO1E1m7ksZCI6 X2dkP4E+q+HgH2Ch5beTqP01NL6zTk2XIKwR4zIWOBMNvdAoF0szKd/G7Esqa78dPKbr g+4+dwRfEfnYKkOfn0nWpnA31BOyEFePvM5l9yBFXsygb4ySHHUAwzYq9gASWKfWuzMc 386w== X-Gm-Message-State: AOAM531+ulHsv9AAjoe7aXwDcXk7htnUTtdnF5m1Lsyw0ItD90A1JNa7 J4S6GkuF8bA+nLTfJisYeD216415vzCz6mOF X-Google-Smtp-Source: ABdhPJxKEywIKJxTxxeZbDu0cZoXLwM81petwZtEPjd1Q/21pPwZ4UzBU5od2zLL3omnXIZN0FZz8w== X-Received: by 2002:a37:a74e:: with SMTP id q75mr4437138qke.165.1615407718035; Wed, 10 Mar 2021 12:21:58 -0800 (PST) Received: from [192.168.1.37] (dhcp-24-53-242-66.cable.user.start.ca. [24.53.242.66]) by smtp.gmail.com with ESMTPSA id 7sm339726qkm.64.2021.03.10.12.21.57 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 10 Mar 2021 12:21:57 -0800 (PST) From: Michael Brown Subject: fdatasync performance problem with large number of DB files To: pgsql-hackers@postgresql.org X-Clacks-Overhead: GNU Terry Pratchett Message-ID: <11bc2bb7-ecb5-3ad0-b39f-df632734cd81@discourse.org> Date: Wed, 10 Mar 2021 15:21:54 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.8.1 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit Content-Language: en-CA List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk I initially posted this on the pgsql-general mailing list [5] but was advised to also post this to the -hackers list as it deals with internals. We've encountered a production performance problem with pg13 related to how it fsyncs the whole data directory in certain scenarios, related to what Paul (bcc'ed) described in a post to pgsql-hackers [1]. Background: We've observed the full recursive fsync is triggered when * pg_basebackup receives a streaming backup (via [2] fsync_dir_recurse or fsync_pgdata) unless --no-sync is specified * postgres starts up unclean (via [3] SyncDataDirectory) We run multiple postgres clusters and some of those clusters have many (~450) databases (one database-per-customer) meaning that the postgres data directory has around 700,000 files. On one of our less loaded servers this takes ~7 minutes to complete, but on another [4] this takes ~90 minutes. Obviously this is untenable risk. We've modified our process that bootstraps a replica via pg_basebackup to instead do "pg_basebackup --no-sync…" followed by a "sync", but we don't have any way to do the equivalent for the postgres startup. I presume the reason postgres doesn't blindly run a sync() is that we don't know what other I/O is on the system and it'd be rude to affect other services. That makes sense, except for our environment the work done by the recursive fsync is orders of magnitude more disruptive than a sync(). My questions are: * is there a knob missing we can configure? * can we get an opt-in knob to use a single sync() call instead of a recursive fsync()? * would you be open to merging a patch providing said knob? * is there something else we missed? Thanks! [1]: https://www.postgresql.org/message-id/flat/CAEET0ZHGnbXmi8yF3ywsDZvb3m9CbdsGZgfTXscQ6agcbzcZAw@mail.gmail.com [2]: https://github.com/postgres/postgres/blob/master/src/bin/pg_basebackup/pg_basebackup.c#L2181 [3]: https://github.com/postgres/postgres/blob/master/src/backend/access/transam/xlog.c#L6495 [4]: It should be identical config-wise. It isn't starved for IO but does have other regular write workloads [5]: https://www.postgresql-archive.org/fdatasync-performance-problem-with-large-number-of-DB-files-td6184094.html -- Michael Brown Civilized Discourse Construction Kit, Inc. https://www.discourse.org/