RE: COPY performance on Windows

Hi, I compared the performance between 94f9c08 (previous commit of 82a4edabd2) and 82a4edabd2 with nclient = 8. The performance is degraded by 8%. * 94f9c08 (previous commit of 82a4edabd2) 75sec * 82a4edabd2 81sec I looked the pg_stat_io view after COPY completed. * 94f9c08 (previous com

RE: COPY performance on Windows

Hi, I did more investigation on the COPY performance on Windows. By using community distributed binaries, the COPY performance of PG16.6 and PG17.0 is worse than PG16.4. However, by using the binaries build by myself, there are no difference. So, it is not the problem about the source code afte

Re: COPY performance on Windows

Ryohei Takahashi (Fujitsu) писал(а) 2024-12-19 16:13: Hi Thank you for your advice and testing. I think, it could be checked, if table has text fields instead of numeric - we could exclude numeric conversion and have the same input-output operations (really more IO-operation, but we need to

RE: COPY performance on Windows

Hi Thank you for your advice and testing. > I think, it could be checked, if table has text fields instead of > numeric - we could exclude numeric conversion > and have the same input-output operations (really more IO-operation, but > we need to compare) I changed the column from int to text

Re: COPY performance on Windows

Ryohei Takahashi (Fujitsu) писал(а) 2024-12-16 15:10: Hi Please use the "test.sh" in the following e-mail. https://www.postgresql.org/message-id/flat/TY3PR01MB11891C0FD066F069B113A2376823E2%40TY3PR01MB11891.jpnprd01.prod.outlook.com#8455c9f7b66780a356511f5cfe029d57 I cannot reproduce your results

Re: COPY performance on Windows

Ryohei Takahashi (Fujitsu) писал(а) 2024-12-16 15:10: Hi According to your advice, I created RAM disk and put input files and data directory on RAM disk. But the result changed only a few seconds. In this test case, the table is unlogged table and shared_buffers is enough. So, I think the dis

RE: COPY performance on Windows

Hi, Thank you for your interest in this thread. > You could create database on RAM disk and make benchmarks. According to your advice, I created RAM disk and put input files and data directory on RAM disk. But the result changed only a few seconds. In this test case, the table is unlogged ta

RE: COPY performance on Windows

Hi, Thank you for your reply. I tried your patch and report in this e-mail. > 1. Those code paths finish up in pg_pwritev(), but it has a loop over > 8kb writes on Windows. Does it help if we just make "zbuffer" bigger? > How big? This patch improves the performance. I applied 0001-Use-bigg

Re: COPY performance on Windows

Ryohei Takahashi (Fujitsu) писал(а) 2024-12-11 15:18: Hi, I continuously investigate the performance problem of COPY on Windows. I noticed that not only PG17.0 but also PG16.6 have performance problem compared to PG16.4. The performance is 2.5%-5.8% worse, especially when the number of clien

Re: COPY performance on Windows

On Thu, Dec 12, 2024 at 1:18 AM Ryohei Takahashi (Fujitsu) wrote: > The performance of PG16.6 and PG17.0 are worse than PG16.4. > So, I think the commits between August and September affects the performance. > I will analyze these commits. If it reproduces reliably, maybe git bisect? Do you have

RE: COPY performance on Windows

Hi, I continuously investigate the performance problem of COPY on Windows. I noticed that not only PG17.0 but also PG16.6 have performance problem compared to PG16.4. The performance is 2.5%-5.8% worse, especially when the number of clients is 1 or 2. I modified the performance measurement sc

RE: COPY performance on Windows

Hi, Thank you for your reply. I don't want to "speed up" the COPY command. I just want to "prevent speed down" compared with PG16. But anyway, my current analysis is not convincing. So, I will do more analysis and get back to you. Regards, Ryohei Takahashi

Re: COPY performance on Windows

Hello Takahashi-san, I am reluctant to draw conclusions about the general performance of this patch from one example. It seems that the performance could depend on many things: table size, column definitions, row width, hardware, OS version, shared_buffers, max_wal_size. I don't think we can say f

Re: COPY performance on Windows

Hi Ryohei, Thanks for the patch. Here are my two cents. > I noticed that the COPY performance on PG 17.0 Windows is worse than PG 16.4. > > [...] > > By applying the attached patch to PG 17.0, the copy result is 401.5s. So we are trading a potential 3.8% speedup in certain environments for the