0

I'm COPY-ing 300 tables / 400G to PG10@win2016server using a few threads to speed things up, but because the table files are incremented in 64-kb units (or something there-about) they get fragmented on disk which hurt read performance.

Ideally I would like to make sure that files are expanded in larger increments (eg. 8 mb), alternative would be to make initial size = max segment size (1gb). Is there any way to accomplish this?

And I know, single threaded copy would solve the issue at hand, but I would still have the same problem after the copying, as tables grow continually and still only in small fragmented chunks.

EDIT: As a "proof" of sorts, i created a simple table

CREATE TABLE testtable 
(
  id bigint primary key,
  somestring varchar(4000)
);

BEGIN
  FOR i IN 1..2000 LOOP
   insert into testtable values(i, repeat('Jeg kan en sang der kan drive dig til vanvid.', 40));
 END LOOP;
END

which produces a table of 4096kb, which contig.exe reports to be of 25 fragments (in this instance), or 160kb on average. For 4 mb this is not an issue, for 400gb it is.

0

NTFS is, in my experience, typically pretty good about cleaning up fragmentation as files grow. I would wait until you actually have the large files and have run defragmentation on the volume before worrying about it.

Just because small files tend to be easily fragmented does not mean that large files will be a problem.

| improve this answer | |
0

In my experience NTFS is not good at avoiding file fragments in the above scenario.

If you only increment a file by 64 kB ntfs will find some "random" area with 64kB free space (unless there is free space right after the current end of the file).

It would be nice with some storage parameter on the table/index indicating how it should expand the file.

Oracle has the parameters INITIAL and NEXT for this purpose.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.