Re: File Fragmentation - Mailing list pgsql-general

Hi,

Atfer 30 minutes, on my Linux computer, with 2 files fill one after the other.

I got a fragmented files with many back step:

# /usr/sbin/filefrag -v 24586
Filesystem type is: ef53
File size of 24586 is 822231040 (200740 blocks, blocksize 4096)
 ext logical physical expected length flags
   0       0  2263040            2048
   1    2048  2271232  2265087   2048
   2    4096  2277376  2273279   2048
   3    6144  2289664  2279423   2048
   4    8192  2306048  2291711    658
   5    8850  2306707  2306705   1390
   6   10240  2316288  2308096   2048
   7   12288  2308097  2318335    102
   8   12390  2328576  2308198   1946
   9   14336  2336768  2330521   2048
  10   16384  2347008  2338815   4096
  11   20480  2357248  2351103   2048
  12   22528  2385920  2359295   4096
  13   26624  2416640  2390015   2048
  14   28672  2424832  2418687   4096
  15   32768  2439168  2428927   2048
  16   34816  2582528  2441215   2048
  17   36864  2940928  2584575   2048
  18   38912  3045376  2942975   2048
  19   40960  1845248  3047423   2048
  20   43008  1910784  1847295   2048
  21   45056  2017280  1912831   2048
  22   47104  2029568  2019327   2048
  23   49152  2146304  2031615   2048
  24   51200  2213888  2148351   2048
  25   53248  3096576  2215935   2048
  26   55296    40960  3098623   2048
  27   57344    90112    43007   2048
  28   59392   124928    92159   2048
  29   61440   102400   126975   2048
  30   63488   161792   104447   2048
  31   65536   164609   163839    680
  32   66216   243712   165288   1368
  33   67584   307200   245079   2048
  34   69632   372736   309247   2048
  35   71680   448512   374783   2048
  36   73728   495616   450559   2048
  37   75776   577536   497663   2048
  38   77824   649216   579583   2048
  39   79872   724992   651263   2048
  40   81920   757760   727039   2048
  41   83968   849920   759807   2048
  42   86016   909312   851967   2048
  43   88064   929792   911359   2048
  44   90112   972800   931839   2048
  45   92160   968704   974847    604
  46   92764  1040384   969307   1444
  47   94208  1081344  1041827   2048
  48   96256  1134592  1083391   2048
  49   98304  1171456  1136639   2048
  50  100352  1165312  1173503   2048
  51  102400  1202176  1167359   2048
  52  104448  1234944  1204223   2048
  53  106496  1267712  1236991   2048
  54  108544  1298432  1269759   2048
  55  110592  1325056  1300479   2048
  56  112640  1372160  1327103   2048
  57  114688  1384448  1374207   2048
  58  116736  1433600  1386495   2048
  59  118784  1452032  1435647   2048
  60  120832  1499136  1454079   2048
  61  122880  1529856  1501183   2048
  62  124928  1560576  1531903   2048
  63  126976  1687552  1562623   2048
  64  129024  2125824  1689599   2048
  65  131072   534560  2127871   2048
  66  133120   544800   536607   2048
  67  135168  1056800   546847   2048
  68  137216  2629789  1058847   6144
  69  143360  2867200  2635932   2048
  70  145408  2887680  2869247   2048
  71  147456    75776  2889727   2048
  72  149504  2990080    77823   2048
  73  151552  3014656  2992127   2048
  74  153600  3094528  3016703   2048
  75  155648  3117056  3096575   2048
  76  157696    63488  3119103   2048
  77  159744   190464    65535   2048
  78  161792   215040   192511   2048
  79  163840   284672   217087   2048
  80  165888   378880   286719   2048
  81  167936   419840   380927   2048
  82  169984   432128   421887   2048
  83  172032   501760   434175   2048
  84  174080   598016   503807   2048
  85  176128   659456   600063   2048
  86  178176   700416   661503   2048
  87  180224   772096   702463   2048
  88  182272   829440   774143   2048
  89  184320   864256   831487   2048
  90  186368   903168   866303   2048
  91  188416  1030144   905215   2048
  92  190464  1255424  1032191   2048
  93  192512  1431552  1257471   2048
  94  194560  1542144  1433599   2048
  95  196608  1732608  1544191   2048
  96  198656  1740800  1734655   2048
  97  200704  1787904  1742847     36 eof
24586: 98 extents found


You can see 3 back steps: position 26, 71, 76.
Just imagine, 50 files over few days, the files become heavely fragmented with many back steps.
So a sequential scan (logicaly from PostgreSQL point of view) is in fact random (filesystem point of view).

JG



Le Mercredi 20 Mars 2013 15:47 CET, jg@rilk.com a écrit:

> Hi,
>
> I create a test cas on Linux:
> postgres=# create table a (v int);
> postgres=# create table b (v int);
>
>
> Then a while(true) over the following script where 24577 and 24580 are the files of the tables a and b
> #!/bin/sh
> psql test -c 'insert into a select generate_series(1,100000,1);'
> psql test -c 'insert into b select generate_series(1,100000,1);'
> psql test -c 'checkpoint;'
> /usr/sbin/filefrag -v 24577 24580
> ls -lh 24577 24580
>
> After few minutes, I got 100 extend by files.
>
>
> The file fragmentation happens on Windows and Linux, too.
>
>
> I not sure that the Wait IO on Windows is related to file fragmentation.
> I try to find a way to analyse the situation.
>
>
> JG
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
Cordialement,
Jean-Gérard Pailloncy