Re: Performance degradation on concurrent COPY into a single relation in PG16.

[Masahiko Sawada]

On Mon, Jul 3, 2023 at 11:55 AM Masahiko Sawada <sawada.m...@gmail.com> wrote:
>
> After further investigation, the performance degradation comes from
> calling posix_fallocate() (called via FileFallocate()) and pwritev()
> (called via FileZero) alternatively depending on how many blocks we
> extend by. And it happens only on the xfs filesystem.

FYI, the attached simple C program proves the fact that calling
alternatively posix_fallocate() and pwrite() causes slow performance
on posix_fallocate():

$ gcc -o test test.c
$ time ./test test.1 1
total   200000
fallocate       200000
filewrite       0

real    0m1.305s
user    0m0.050s
sys     0m1.255s

$ time ./test test.2 2
total   200000
fallocate       100000
filewrite       100000

real    1m29.222s
user    0m0.139s
sys     0m3.139s

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

#include <stdio.h>
#include <stdlib.h>
#include <fcntl.h>
#include <unistd.h>

int
main(int argc, char **argv)
{
    char *filename = argv[1];
    int ratio = atoi(argv[2]);
    char block[8192] = {0};
    int fd;
    int total_len = 0;
    int n_fallocate = 0;
    int n_filewrite = 0;
    int i;

    fd = open(filename, O_RDWR | O_CREAT, S_IRWXU);
    if (fd < 0)
    {
        fprintf(stderr, "could not open file %s: %m\n", filename);
        return 1;
    }

    for (i = 0; i < 200000; i++)
    {
        int ret;

        if (ratio != 0 && i % ratio == 0)
        {
            posix_fallocate(fd, total_len, 8192);
            n_fallocate++;
        }
        else
        {
            pwrite(fd, block, 8192, total_len);
            n_filewrite++;
        }
        total_len += 8192;
    }

    printf("total\t%d\n", i);
    printf("fallocate\t%d\n", n_fallocate);
    printf("filewrite\t%d\n", n_filewrite);

    close(fd);
    return 0;
}