Firebird

Vlad,=20

> 1. Look-ahead disk space allocation
>=20
>     To avoid problems with page buffers allocated in=20
> in-memory cache but not on disk i propose to grab enough disk=20
> space before any attempt to use pages not allocated on disk.=20
>=20
>     Only one thread (process) can extend file to avoid conflicts.=20
>=20
>     On Windows use SetFileValidData API call on those=20
> platforms where it is present (>=3D WinXP) to avoid very slow=20
> synchronous filling of a new place with zeros.
>=20
>     To make process more efficient i offer to allocate space=20
> for several pages at a time. Low and high number of batch of=20
> allocated pages limited by hard-coded constants (i propose=20
> 128KB and 128MB but this is can be discussed of course).
> Engine take 1/16 part of already allocated space and adjust=20
> this number against low and high limits above (1/16 also can=20
> be discussed).

Rather than this elaborate approach, could we not simply introduce a new
setting "minimum DB growth" which would represent the number of pages
which a database will grow when required?

A value range between 1 (naturally) and 16384 would constrain the value,
with 1 as the default.

There are some installs where file growth/size is critical, a low end
value of 128KB would be problematic.

(Ideally, this growth setting should be defined/stored in the DB)


> If look-ahead allocation failed engine divided=20
> requested number of pages by factor of 2 and repeat process=20
> until success or requested number more than zero. If request=20
> to allocate one page is failed too error is raised (also we=20
> can log it into firebird.log)

I don't know if this is really required, but it sure sounds nice.


> 2. Allow engine to not use file system cache
>=20
>     Currently engine works with database files by using file=20
> system cache (at least on Windows). This is good (and must)=20
> for classic server with its small page cache but can hurt=20
> performance of super server configured to use really large page cache.
> If super server will not use file system cache this allow=20
> more efficient use of memory (if there are not enough memory=20
> to fit both kind of caches) and faster access to the disk.

I don't understand the case where this is required.

If there is not enough memory for the database cache, isn't it the DBA
responsibility to reduce the database page cache settings?

Isn't the larger issue that since the SS cache is per connection, as the
number of connection increases, that the allocation SS cache can
outstrip the free physical RAM (as different from Virtual Memory) and
therefore make the entire server grind (as the OS pages memory to the
disk)?


Sean

Hi,
>>     To make process more efficient i offer to allocate space 
>> for several pages at a time. Low and high number of batch of 
>> allocated pages limited by hard-coded constants (i propose 
>> 128KB and 128MB but this is can be discussed of course).
>> Engine take 1/16 part of already allocated space and adjust 
>> this number against low and high limits above (1/16 also can 
>> be discussed).
> 

MS SQL Server stores "FILEGROWTH" information per each database file.
FILEGROWTH may be specified in KB,MB,GB,TB or percent %
If FILEGROWTH is not specified, the default value is 10 percent and the 
minimum value is 64 KB.
-Laco.

> -----Original Message-----
> From: firebird-devel-bounces@...
> [mailto:firebird-devel-bounces@... Behalf Of Vlad
> Horsun
> Sent: Martes, 23 de Enero de 2007 20:19
> To: fb-devel
>
>     Only one thread (process) can extend file to avoid conflicts.
>
>     On Windows use SetFileValidData API call on those platforms
> where it is
> present (>= WinXP) to avoid very slow synchronous filling of a new place
> with zeros.

Hmm, but I see several restrictions on SetFileValidData. Copy/paste from
MSDN:
- The file cannot be on a network drive, or be compressed, sparse, or
transacted.
- A caller must have the SE_MANAGE_VOLUME_NAME privilege enabled when
opening a file initially. Applications should only call SetFileValidData on
files that restrict access to those entities that have SE_MANAGE_VOLUME_NAME
access. For more information, see Running with Special Privileges.

And I see conflicting information:
- Windows 2000:  The system can defragment only the valid data length of a
file.
- Client 	Requires Windows Vista or Windows XP.

What's the purpose of the W2k warning if the API can't be used on it?


>     I propose to introduce new configuration setting "UseFileSystemCache".

I propose another setting: keep record versions on RAM.
:-)

>     If database page cache is more than value of this setting
> then file system
> cache is not used. If database page cache is less than
> "UseFileSystemCache"
> value then file system cache is used (as before).

Then the name of the config param is misleading. It should be something like
MaxFileSystemCache, because it's a number, not a boolean setting.

C.

Sean, thank you for comments

> > 1. Look-ahead disk space allocation
> > 
> >     To avoid problems with page buffers allocated in 
> > in-memory cache but not on disk i propose to grab enough disk 
> > space before any attempt to use pages not allocated on disk. 
> > 
> >     Only one thread (process) can extend file to avoid conflicts. 
> > 
> >     On Windows use SetFileValidData API call on those 
> > platforms where it is present (>= WinXP) to avoid very slow 
> > synchronous filling of a new place with zeros.
> > 
> >     To make process more efficient i offer to allocate space 
> > for several pages at a time. Low and high number of batch of 
> > allocated pages limited by hard-coded constants (i propose 
> > 128KB and 128MB but this is can be discussed of course).
> > Engine take 1/16 part of already allocated space and adjust 
> > this number against low and high limits above (1/16 also can 
> > be discussed).
> 
> Rather than this elaborate approach, could we not simply introduce a new
> setting "minimum DB growth" which would represent the number of pages
> which a database will grow when required?

    Yes we can introduce new setting of course. But my goal was to avoid
new setting if possible ;) Another opinions ?
 
> A value range between 1 (naturally) and 16384 would constrain the value,
> with 1 as the default.
> 
> There are some installs where file growth/size is critical, a low end
> value of 128KB would be problematic.

    We can reduce this limit. But empty database have initial size of 
620KB so i don't think minimum grows of 128K is a problem. 
 
> (Ideally, this growth setting should be defined/stored in the DB)

    If we'll introduce such settings - agree it must be per database

> > If look-ahead allocation failed engine divided 
> > requested number of pages by factor of 2 and repeat process 
> > until success or requested number more than zero. If request 
> > to allocate one page is failed too error is raised (also we 
> > can log it into firebird.log)
> 
> I don't know if this is really required, but it sure sounds nice.

    This is to avoid complains such as 

'I have 1MB free disk space but Firebird failed to allocate it' 

;)

> > 2. Allow engine to not use file system cache
> > 
> >     Currently engine works with database files by using file 
> > system cache (at least on Windows). This is good (and must) 
> > for classic server with its small page cache but can hurt 
> > performance of super server configured to use really large page cache.
> > If super server will not use file system cache this allow 
> > more efficient use of memory (if there are not enough memory 
> > to fit both kind of caches) and faster access to the disk.
> 
> I don't understand the case where this is required.
> 
> If there is not enough memory for the database cache, isn't it the DBA
> responsibility to reduce the database page cache settings?

    Imagine PC with, say 1GB RAM. Imagine also database with standard 
page size 4KB and page buffers of 128 * 1024 (our current maximum). It will
take 512MB for page buffers. If database is large enough, OS (at least windows)
will keep big part of it in file system cache. Why waste memory for double
caching ? We can better use it for sorting and metadata cache,  for second 
database or for something else

> Isn't the larger issue that since the SS cache is per connection, as the
> number of connection increases, that the allocation SS cache can
> outstrip the free physical RAM (as different from Virtual Memory) and
> therefore make the entire server grind (as the OS pages memory to the
> disk)?

    Can i return to you your phrase above :

"If there is not enough memory for the database cache, isn't it the DBA
responsibility to reduce the database page cache settings? "

:)

    This is another problem i want to discuss later 

Regards,
Vlad

> >>     To make process more efficient i offer to allocate space 
> >> for several pages at a time. Low and high number of batch of 
> >> allocated pages limited by hard-coded constants (i propose 
> >> 128KB and 128MB but this is can be discussed of course).
> >> Engine take 1/16 part of already allocated space and adjust 
> >> this number against low and high limits above (1/16 also can 
> >> be discussed).
> > 
> 
> MS SQL Server stores "FILEGROWTH" information per each database file.
> FILEGROWTH may be specified in KB,MB,GB,TB or percent %


    I know, thanks ;)

> If FILEGROWTH is not specified, the default value is 10 percent and the 
> minimum value is 64 KB.

    And this is more or less the same values i propose 

Regards,
Vlad

> > If FILEGROWTH is not specified, the default value is 10 percent and the 
> > minimum value is 64 KB.
> 
>     And this is more or less the same values i propose 

  I also don't like new configuration options, but in this case it really worth, IMHO. Something like ChunckSize in pages (not kilobytes).

  SY, Dimitry Sibiryakov

Leyne, Sean wrote:
> 
>> Currently engine works with database files by using file 
>> system cache (at least on Windows). This is good (and must) 
>> for classic server with its small page cache but can hurt 
>> performance of super server configured to use really large page cache.
>> If super server will not use file system cache this allow 
>> more efficient use of memory (if there are not enough memory 
>> to fit both kind of caches) and faster access to the disk.
> 
> I don't understand the case where this is required.

The problem that you cannot control how much the file cache is involved 
for your database. If the page cache is noticeably less than the RAM 
size (< 70%), then Windows usually does its work very well. Otherwise (> 
80%) Windows tends to swap the page cache in order to keep the file 
cache in RAM. Of course, the speech is about big databases.

When doing some related tests, I've noticed 2x-3x performance 
degradation because of a permanent paging happening.

 > If there is not enough memory for the database cache, isn't it the DBA
 > responsibility to reduce the database page cache settings?

It's hard to find a balance between double caching and having some RAM 
unused. You may experimentally find a good threshold for some load 
pattern, but you cannot have any guarantee it will work well always.

> Isn't the larger issue that since the SS cache is per connection

It's per database.


Dmitry

> >     On Windows use SetFileValidData API call on those platforms
> > where it is
> > present (>= WinXP) to avoid very slow synchronous filling of a new place
> > with zeros.
> 
> Hmm, but I see several restrictions on SetFileValidData. Copy/paste from
> MSDN:
> - The file cannot be on a network drive, or be compressed, sparse, or
> transacted.

    Not a problem for us. Even if we allow to work with network drive or some 'smart'
admin put database on compressed volume we can safely ignore result of 
SetFileValidData call

> - A caller must have the SE_MANAGE_VOLUME_NAME privilege enabled when
> opening a file initially. Applications should only call SetFileValidData on
> files that restrict access to those entities that have SE_MANAGE_VOLUME_NAME
> access. For more information, see Running with Special Privileges.

    Yes, we must try to adjust process privileges at startup and note it in ReleaseNotes ;) 

> And I see conflicting information:
> - Windows 2000:  The system can defragment only the valid data length of a
> file.
> - Client Requires Windows Vista or Windows XP.
> 
> What's the purpose of the W2k warning if the API can't be used on it?

    In my local copy of MSDN (Feb 2003) a see no such note about W2K.

    But looking on-line (http://msdn2.microsoft.com/en-us/library/aa365544.aspx) 
i see it.

    AFAIU, "file valid data" concept present in all NT versions...

> >     I propose to introduce new configuration setting "UseFileSystemCache".
> 
> I propose another setting: keep record versions on RAM.
> :-)

    This is already copyrighted by another project :)))

> >     If database page cache is more than value of this setting
> > then file system
> > cache is not used. If database page cache is less than
> > "UseFileSystemCache"
> > value then file system cache is used (as before).
> 
> Then the name of the config param is misleading. It should be something like
> MaxFileSystemCache, because it's a number, not a boolean setting.

    Agreed. This was one of the reasons i wrote this RFC and asked for opinions :)

Regards,
Vlad

Hi,

At January 24, 2007, 4:25 AM, Dimitry Sibiryakov wrote:

>> > If FILEGROWTH is not specified, the default value is 10 percent and the 
>> > minimum value is 64 KB.
>> 
>>     And this is more or less the same values i propose 

>   I also don't like new configuration options, but in this case it
> really worth, IMHO. Something like ChunckSize in pages (not kilobytes).

If FB3 will use the Vulcan database configuration file, then I don't
think that there will be any problems with a per database
configuration setting. Because, you might have a database that might
only need to increase the database file size by 10 pages once per
week, but there might be another database where it would be beneficial
to increase the size by 200 pages at a time.

And, I like the term DatabaseGrowthPages. For me, it's more
descriptive.

-- 
Best regards,
 Daniel Rail
 Senior Software Developer
 ACCRA Solutions Inc. (www.accra.ca)
 ACCRA Med Software Inc. (www.filopto.com)

Daniel Rail:

> If FB3 will use the Vulcan database configuration file, then I don't
> think that there will be any problems with a per database
> configuration setting. Because, you might have a database that might
> only need to increase the database file size by 10 pages once per
> week, but there might be another database where it would be beneficial
> to increase the size by 200 pages at a time.
> 

Certainly, it will. But I do not see BIG use to make this parameter per 
database. In your case simply let it each DB grow 200 pages per time. 
Even with 16K page it will be 3Mb. What are we talking about in epoch of 
minimum 80Gb disks?

Hi,

At January 24, 2007, 9:08 AM, Alex Peshkov wrote:

> Certainly, it will. But I do not see BIG use to make this parameter per
> database. In your case simply let it each DB grow 200 pages per time. 
> Even with 16K page it will be 3Mb. What are we talking about in epoch of
> minimum 80Gb disks?

True, not much of a difference. And, I should've calculated what 200
pages meant(I got my coffee after posting earlier), because I was
thinking of a much bigger size increase(i.e. 20,000 pages of 16KB at a
time, for approx. 320MB). We do have an application were it's possible
to add over 200MB of data in one day, and it's mostly blobs.

Although I agree that with 80+GB disks, there shouldn't be much of a
need to worry, but it might also depend on the customer and what they
do with the server. As an example, we just had a customer that maxed
out their server's hard drive(120GB) where the database is located.
What happened was that the person that is assigned the task to
administer that server installed and copied a bunch of stuff on it,
which wasn't supposed to be there.

-- 
Best regards,
 Daniel Rail
 Senior Software Developer
 ACCRA Solutions Inc. (www.accra.ca)
 ACCRA Med Software Inc. (www.filopto.com)

Daniel Rail:
> Although I agree that with 80+GB disks, there shouldn't be much of a
> need to worry, but it might also depend on the customer and what they
> do with the server. As an example, we just had a customer that maxed
> out their server's hard drive(120GB) where the database is located.
> What happened was that the person that is assigned the task to
> administer that server installed and copied a bunch of stuff on it,
> which wasn't supposed to be there.
> 

In that case it makes almost no difference, what amount of disk memory 
will be added to the database. What will help in such a case is control, 
suggested by Vlad: avoid having pages that can't be flashed from RAM to 
the disk due to no more space on it.

> 1. Look-ahead disk space allocation
...
>     Constants (128KB, 128MB and 1/16 part of already allocated space) are chosen 
> so to not grow small database too fast from the one side and to allocate enough 
> number of pages to not extend file too often and reduce disk fragmentation from 
> the other side.

    May i ask those who want to move this limits into firebird.conf : 
what values you would suggest to assign to this new settings and why ?
Why this settings will be different for different databases ? 
Why do you want to set it in pages but not in KB's ?

    My arguments why this is not needed : 

we have two opposite requirements here

a) not waste disk space - to do it we must allocate minimum possible chunk 
    at a time (one page of course)

b) reduce disk fragmentation and avoid frequent file extension - to do it we must 
    allocate maximum possible chunk at a time 

mine proposition seems to me as good balance between this two requirements.

For databases less then 128KB * 16 = 2MB we will waste maximum 128KB - 1 page.
Is it too much ? We can lower this 128KB to 64KB

For databases between 2MB and 2GB we will waste less then 1/16 part of database

For databases more then 2GB we will waste less then 128MB of database

    I not insist on this numbers but approach seems to me as good replacement of 
several new settings and general enough

Regards,
Vlad

Vlad Horsun:
>> 1. Look-ahead disk space allocation
> ...
>>     Constants (128KB, 128MB and 1/16 part of already allocated space) are chosen 
>> so to not grow small database too fast from the one side and to allocate enough 
>> number of pages to not extend file too often and reduce disk fragmentation from 
>> the other side.
> 
>     May i ask those who want to move this limits into firebird.conf : 
> what values you would suggest to assign to this new settings and why ?
> Why this settings will be different for different databases ? 
> Why do you want to set it in pages but not in KB's ?
> 
>     My arguments why this is not needed : 
> 
> we have two opposite requirements here
> 
> a) not waste disk space - to do it we must allocate minimum possible chunk 
>     at a time (one page of course)
> 
> b) reduce disk fragmentation and avoid frequent file extension - to do it we must 
>     allocate maximum possible chunk at a time 
> 
> mine proposition seems to me as good balance between this two requirements.
> 
> For databases less then 128KB * 16 = 2MB we will waste maximum 128KB - 1 page.
> Is it too much ? We can lower this 128KB to 64KB
> 
> For databases between 2MB and 2GB we will waste less then 1/16 part of database
> 
> For databases more then 2GB we will waste less then 128MB of database
> 
>     I not insist on this numbers but approach seems to me as good replacement of 
> several new settings and general enough

Agreed.

On 1/25/07, Vlad Horsun <hvlad@...> wrote:
>
> we have two opposite requirements here
>
> a) not waste disk space - to do it we must allocate minimum possible chunk
>     at a time (one page of course)
>
> b) reduce disk fragmentation and avoid frequent file extension - to do it
> we must
>     allocate maximum possible chunk at a time
>
>

I like the idea of a percentage growth. If your database is small, 10% will
not wast much disk space. If your database is big, 10% will be a nice big
chunk to allocate.

Just my 2c.


-- 
: Barry Kukkuk         http://www.kukkuk.org
.:.
: barry@...
: kukkuk@...
.:.
: Retail Analyza      http://www.analyza.biz
: Business Intelligence for Retailers
.:.
: ICON Retail    http://www.iconretail.co.za
: Point-of-sale software
.:.
: Proudly South African

> -----Original Message-----
> From: firebird-devel-bounces@...
> [mailto:firebird-devel-bounces@... Behalf Of Vlad
> Horsun
> Sent: Jueves, 25 de Enero de 2007 6:02
>
> For databases less then 128KB * 16 = 2MB we will waste maximum
> 128KB - 1 page.
> Is it too much ? We can lower this 128KB to 64KB

I think 128 KB is good.


> For databases between 2MB and 2GB we will waste less then 1/16
> part of database
>
> For databases more then 2GB we will waste less then 128MB of database
>
>     I not insist on this numbers but approach seems to me as good
> replacement of several new settings and general enough.

Yes, each setting complicates even more the product. If a sensible automatic
approach can be taken, use it!

C.

----- Original Message ----- 
From: Barry Kukkuk 
To: firebird-devel@... 
Sent: Thursday, January 25, 2007 11:17 AM
Subject: Re: [Firebird-devel] RFC: Look-ahead disk space allocation and nouseof file system cache


>>On 1/25/07, Vlad Horsun <hvlad@...> wrote:
>>we have two opposite requirements here
>>
>>a) not waste disk space - to do it we must allocate minimum possible chunk
>>    at a time (one page of course)
>>
>>b) reduce disk fragmentation and avoid frequent file extension - to do it we must 
>>    allocate maximum possible chunk at a time
>>
>
>I like the idea of a percentage growth. If your database is small, 10% will not wast much disk space. 

    There are no sence to grow file less then, say 64KB, at a time - it not avoids fragmentation at all.

>If your database is big, 10% will be a nice big chunk to allocate. 

    There are practically no difference between 128MB and 1GB file grows from "avoid fragmentation" 
point of view, IMO. 128MB is large enough chunk to lower fragmentation's bad effects. From the
other side, having absolute (not relative in percent) high grows limit do satisfy requirement (a) - 
to not waste disk space more than necessary

Regards,
Vlad

Vlad Horsun wrote:
>
> For databases between 2MB and 2GB we will waste less then 1/16 part of database
> For databases more then 2GB we will waste less then 128MB of database

For a big database running on NT4 or W2K it would mean that a 128MB 
chunk is synchronously filled with zeros. It would appear as unexpected 
performance slowdowns that cannot be predicted. This worries me a bit.


Dmitry

Dmitry Yemanov wrote:
> For a big database running on NT4 or W2K it would mean that a 128MB 
> chunk is synchronously filled with zeros. It would appear as unexpected 
> performance slowdowns that cannot be predicted. This worries me a bit.
>   
Documentation of SetFileValidData says that it don't fill the file with 
zeroes.
Ok, it don't work for NT4 and W2K.

But, what is the difference between SetFileValidData and SetEndOfFile?

The latter is documented with "the contents of the file between the old 
end of the file and the new end of the file are not defined" and works 
in all Windows versions.


Adriano

> Vlad Horsun wrote:
> >
> > For databases between 2MB and 2GB we will waste less then 1/16 part of database
> > For databases more then 2GB we will waste less then 128MB of database
> 
> For a big database running on NT4 or W2K it would mean that a 128MB 
> chunk is synchronously filled with zeros. It would appear as unexpected 
> performance slowdowns that cannot be predicted. This worries me a bit.

    Windows not filled file with zeros when it reserved space. It just "moves the end-of-file 
(EOF) position".

    I.e. if you have an empty file and call SetEndOfFile (128MB) - it will return immediately. 
If you will write consecutive pages (0, 1, 2, 3) - there will be no penalty also. But if you do 
seek to page 10000 and write this page - there Windows will fill previous (4..9999) pages 
with zeros. Unfortunately it is often happens in Firebird.

Regards,
Vlad

>But if you do seek to page 10000 and write this page - 
>there Windows will fill previous (4..9999) pages 
>with zeros. Unfortunately it is often happens in Firebird.

  Do you mean that FB use unused pages in random order?

  SY, Dimitry Sibiryakov

> >But if you do seek to page 10000 and write this page - 
> >there Windows will fill previous (4..9999) pages 
> >with zeros. Unfortunately it is often happens in Firebird.
> 
>   Do you mean that FB use unused pages in random order?

    Yes. Due to "careful writes" FB often writes pages in _very_ random order

Regards,
Vlad

> -----Original Message-----
> From: firebird-devel-bounces@...
> [mailto:firebird-devel-bounces@... Behalf Of Vlad
> Horsun
> Sent: Jueves, 25 de Enero de 2007 7:57
>
> > >But if you do seek to page 10000 and write this page -
> > >there Windows will fill previous (4..9999) pages
> > >with zeros. Unfortunately it is often happens in Firebird.
> >
> >   Do you mean that FB use unused pages in random order?
>
>     Yes. Due to "careful writes" FB often writes pages in _very_
> random order

If the pages aren't initialized ("zeroed") then will the server write random
garbage to disk?

C.

BTW, how this feature can cooperate with "use_all_space" gbak switch?

Claudio Valderrama C. wrote:
> If the pages aren't initialized ("zeroed") then will the server write random
> garbage to disk?
>   
Windows will not change the previous (already deleted file, for example) 
disk page content.


Adriano

> BTW, how this feature can cooperate with "use_all_space" gbak switch?

    Not related ;) 

    "use_all_space" switch tells engine to not reserve space on data pages
for future possible backversions. Useful for readonly databases

Regards,
Vlad

----- Original Message ----- 
From: "Adriano dos Santos Fernandes" <adrianosf@...>
To: <firebird-devel@...>
Sent: Thursday, January 25, 2007 1:27 PM
Subject: Re: [Firebird-devel] RFC: Look-ahead disk space allocation and no


> Dmitry Yemanov wrote:
> > For a big database running on NT4 or W2K it would mean that a 128MB 
> > chunk is synchronously filled with zeros. It would appear as unexpected 
> > performance slowdowns that cannot be predicted. This worries me a bit.
> >   
> Documentation of SetFileValidData says that it don't fill the file with 
> zeroes.
> Ok, it don't work for NT4 and W2K.
> 
> But, what is the difference between SetFileValidData and SetEndOfFile?

    SetFileValidData is not a replacement for SetEndOfFile. Files have two
markers - EndOfFile and ValidDataLength. SetEndOfFile moves EndOfFile
marker but not touched ValidDataLength marker while SetFileValidData
moved only ValidDataLength marker. 

    There are constraint : 0 <= ValidDataLength <= EndOfFile

    Attempt to write after current ValidDataLength leads that Windows
fills file space from current ValidDataLength to write position by zeros
and then moved ValidDataLength marker.

    Call to SetFileValidData just after SetEndOfFile allow to avoid this

Regards,
Vlad

> Useful for readonly databases

  Let me see... If I use this switch to prepare a DB for
writing on CD and save some space, I'll get up to 128Mb empty
space at the end of DB. Doesn't this feature defeat the
purpose of the switch?

  SY, Dimitry Sibiryakov

----- Original Message ----- 
From: "Dimitry Sibiryakov" <aafemt@...>
To: <firebird-devel@...>
Sent: Thursday, January 25, 2007 1:47 PM
Subject: Re: [Firebird-devel]RFC: Look-ahead disk space allocation and no


> > Useful for readonly databases
> 
>   Let me see... If I use this switch to prepare a DB for
> writing on CD and save some space, I'll get up to 128Mb empty
> space at the end of DB. Doesn't this feature defeat the
> purpose of the switch?

    Now i understand. Good question. We can grab space by one page at time
in this case

Regards,
Vlad

PS If we talk about CD then we'll waste less than 44MB (700MB \ 16)
    But it can be critical of course. Don't think it will be important for DVD :)

Vlad Horsun:
>>   Let me see... If I use this switch to prepare a DB for
>> writing on CD and save some space, I'll get up to 128Mb empty
>> space at the end of DB. Doesn't this feature defeat the
>> purpose of the switch?
> 
>     Now i understand. Good question. We can grab space by one page at time
> in this case
> 

Suppose this can be default behavior when use-all-space is set? Or 
special switch is needed in some cases?

>Suppose this can be default behavior when use-all-space is set?

Default seems ok for me.

"Alex Peshkov" ...
> Vlad Horsun:
> >>   Let me see... If I use this switch to prepare a DB for
> >> writing on CD and save some space, I'll get up to 128Mb empty
> >> space at the end of DB. Doesn't this feature defeat the
> >> purpose of the switch?
> > 
> >     Now i understand. Good question. We can grab space by one page at time
> > in this case
> > 
> 
> Suppose this can be default behavior when use-all-space is set? 

    Of course. We can use DBB_no_reserve flag for it

> Or special switch is needed in some cases?

    No, i just (it seems) has convinced peoples to not introduce new settings (switches) :)

Regards,
Vlad

Dmitry,

> > I don't understand the case where this is required.
>=20
> The problem that you cannot control how much the file cache is
involved
> for your database. If the page cache is noticeably less than the RAM
> size (< 70%), then Windows usually does its work very well. Otherwise
(>
> 80%) Windows tends to swap the page cache in order to keep the file
> cache in RAM. Of course, the speech is about big databases.
>=20
> When doing some related tests, I've noticed 2x-3x performance
> degradation because of a permanent paging happening.

OK.

Then what would be the purpose of this new "MaxFileSystemCache" setting?

A change to the caching characteristics of a file would require that the
file handle be re-opened...


Sean

Vlad,

> > 1. Look-ahead disk space allocation
> ...
> >     Constants (128KB, 128MB and 1/16 part of already allocated
space)
> are chosen
> > so to not grow small database too fast from the one side and to
allocate
> enough
> > number of pages to not extend file too often and reduce disk
> fragmentation from
> > the other side.
>=20
>     May i ask those who want to move this limits into firebird.conf :
> what values you would suggest to assign to this new settings and why ?

At the very least, this 'database growth' feature should be controlled
by a new setting -- the feature is not appropriate for all cases.


Sean

> Dmitry,
> 
> > > I don't understand the case where this is required.
> > 
> > The problem that you cannot control how much the file cache is
> involved
> > for your database. If the page cache is noticeably less than the RAM
> > size (< 70%), then Windows usually does its work very well. Otherwise
> (>
> > 80%) Windows tends to swap the page cache in order to keep the file
> > cache in RAM. Of course, the speech is about big databases.
> > 
> > When doing some related tests, I've noticed 2x-3x performance
> > degradation because of a permanent paging happening.
> 
> OK.
> 
> Then what would be the purpose of this new "MaxFileSystemCache" setting?

    a) tell operating system to not cache database file

    b) get some performance benefit bypassing system cache

> A change to the caching characteristics of a file would require that the
> file handle be re-opened...

    Yes, but this needs to be done only one time just after file opened and page_buffers
reads from header page, and only if necessary - if this stored value (or default value, 
if there are no stored value) greater than "MaxFileSystemCache".

Regards,
Vlad

PS To be precise - "not using file system cache" means

    on windows   - use FILE_FLAG_NO_BUFFERING flag when open file

    on posix        - i can't find exact equivalent and will use SYNC flag. so on posix
                            platform is will be the same as FW = ON

> Vlad,
> 
> > > 1. Look-ahead disk space allocation
> > ...
> > >     Constants (128KB, 128MB and 1/16 part of already allocated
> space)
> > are chosen
> > > so to not grow small database too fast from the one side and to
> allocate
> > enough
> > > number of pages to not extend file too often and reduce disk
> > fragmentation from
> > > the other side.
> > 
> >     May i ask those who want to move this limits into firebird.conf :
> > what values you would suggest to assign to this new settings and why ?
> 
> At the very least, this 'database growth' feature should be controlled
> by a new setting -- the feature is not appropriate for all cases.

    Can you show such case please ?

Regards,
Vlad

Hi Vlad,

>    Below i propose two change in a way engine worked with disk files
>
> 1. Look-ahead disk space allocation
>
>    To avoid problems with page buffers allocated in in-memory cache
> but not on disk i propose to grab enough disk space before any attempt
> to use pages not allocated on disk.

For all files involved for the database (including shadows)?
I assume they never go beyond it's max setting per file.

>    To make process more efficient i offer to allocate space for several pages at
> a time. Low and high number of batch of allocated pages limited by hard-coded
> constants (i propose 128KB and 128MB but this is can be discussed of course).
> Engine take 1/16 part of already allocated space and adjust this number against
> low and high limits above (1/16 also can be discussed). If look-ahead allocation
> failed engine divided requested number of pages by factor of 2 and repeat
> process until success or requested number more than zero. If request to allocate
> one page is failed too error is raised (also we can log it into firebird.log)

Are PIP pages updates also or let you only grow file (not initialize/allocate anything)?

>    Constants (128KB, 128MB and 1/16 part of already allocated space) are chosen
> so to not grow small database too fast from the one side and to allocate enough
> number of pages to not extend file too often and reduce disk fragmentation from
> the other side.

Sounds reasonable to me.

> 2. Allow engine to not use file system cache
>
>    Currently engine works with database files by using file system cache (at least on
> Windows). This is good (and must) for classic server with its small page cache but
> can hurt performance of super server configured to use really large page cache.
> If super server will not use file system cache this allow more efficient use of memory
> (if there are not enough memory to fit both kind of caches) and faster access to the
> disk.

So there are normal three caches involved?
-Disk-cache
-OS-cache
-FB-cache

What/How is the benefit from the OS-cache on small files then?
Are there differences between Forced Writes on/off.

>    I propose to introduce new configuration setting "UseFileSystemCache".
>
>    If database page cache is more than value of this setting then file system
> cache is not used. If database page cache is less than "UseFileSystemCache"
> value then file system cache is used (as before). Default value of "UseFileSystemCache"
> must be big enough to switch off FS cache only for databases with really big page
> cache configured. I offer to make it 65536 by default. With such default value we
> preserve performance of classic server and only super server with big cache will
> be affected by new rules

65536 pages/bytes/MB?

What does happen on switch-over point (from OS-cache to non OS-cache). Windows releases cache 
immediately without drawbacks?


Overall i think this could certainly give performance improvement (on restore, bulk inserts, ...).


btw, "UseFileSystemCache" sounds as On/Off setting. May be something like 
"IgnoreFileSystemCacheThreshold" or "DisableFileSystemCacheThreshold", ..

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database development support:
  http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features :
  http://www.firebirdsql.org
  http://www.firebirdsql.info
  http://www.fingerbird.de/
  http://www.comunidade-firebird.org/

Support list for Firebird and Interbase users :
firebird-support@...

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info

Vlad,

> > At the very least, this 'database growth' feature should be
controlled
> > by a new setting -- the feature is not appropriate for all cases.
>=20
>     Can you show such case please ?

#1  My database ships on a flash/thumb drive...  I want to allocate db
space on a very limited basis (1 or 2 pages at a time)...

#2  I need to ship my database on a CD along with other distributables,
I need to limit the amount of space "wasted" in the database allocation.


Vlad, your postings suggest that you think that because there are
multi-gigabyte HDs available that we should be allocating this disk
space freely...

Don't get me wrong, I think the idea of pre-allocating large blocks of
disk space is a very good feature.  I am simply saying that the feature
needs to be controllable, since not every database is installed on a
multi-GB drive.


Separately, have you measured the performance impact of allocating large
amounts of disk space, as compared to smaller block sizes (i.e. 64KB vs.
128KB vs. 1MB vs. 64MB vs. 128MB)?


Sean

Vlad,

> > A change to the caching characteristics of a file would require that
the
> > file handle be re-opened...
>=20
>     Yes, but this needs to be done only one time just after file
opened
> and page_buffers
> reads from header page, and only if necessary - if this stored value
(or
> default value,
> if there are no stored value) greater than "MaxFileSystemCache".

So, to be sure I understand...

The setting is per database server PC?


Sean

On 01/23/2007 03:18 PM, Vlad Horsun wrote:
>     Below i propose two change in a way engine worked with disk files

Hi Vlad,

I have a few questions and a couple of comments for your consideration.

Am I correct that neither one of these two changes is directly related
to or dependent on the other?

> 1. Look-ahead disk space allocation
> 
>     To avoid problems with page buffers allocated in in-memory cache
> but not on disk i propose to grab enough disk space before any attempt 
> to use pages not allocated on disk. 
>...

Given your recent postings, the primary goal of this proposed change
seems to be handling a "disk full" condition when "fake pages", as you
described them, still need to be written to disk. I like that you're
looking at this issue! Cost and space are big concerns when dealing with
small/cheap network infrastructure devices with embedded db's.
Gracefully handling unexpected "disk full" conditions is important in
such environments, where "disk" space is limited.

Questions:
  1.a) What is the minimum number of preallocated pages required to
ensure that you'll have enough disk space to write all "fake" pages to
disk before a "disk full/low" error is raised and, potentially, the db
is restarted?

  1.b) Is it possible that your proposed 128KB minimum would not be
large enough and might still, potentially, result in a corrupted db due
to lack of space?

  1.c) If the answer to 1.b) is YES, then why is it good to allow a user
to specify a value that they think will protect them from a potential db
corruption, but it may not?


As an aside, it might be worth noting that while preallocating large
chunks of file space might help limit "file fragmentation" on some file
stores, it may not on others. Raw devices, for example, are conceptually
always "preallocated". Doing extra/larger writes to preallocate space
on them is a waste of cycles. For file systems, it will depend on the
allocation policy of file system used (e.g., block-based, extent-based,
cluster-based, write-anywhere, allocate-on-flush, etc.). For example,
writing 128KB to an empty file on a FAT file system will allocate file
space differently then would the same size write to an empty file on an
extent-based file system with 500MB extents. My point here is that the
proposed change may or may not help with "file fragmentation" depending
on factors outside the scope of FB. Therefore, you'll probably need to
let users enable/disable/tune preallocation on a per *file* basis. For
this reason, I'd address this goal and the "disk full" goal separately.

Also, just in case you're not aware of it, many unix-like file systems
have "sparse" file space allocation. That is, files can have unallocated
"holes" in them. For example, create an empty file, seek to offset
10,000,000, write one byte and then close the file. After this, the
file's "size" may appear to be 10,000,001 bytes. However, how much file
space was allocated depends upon the file system used. It might be one
fs block (say 8KB), one fs extent (say 150MB), ~10MB, or something else.
 So, on "sparse" file systems, you'll probably need to write at least
one byte to each fs block you want to preallocate.

An alternative implementation that I've seen is to keep an "emergency
free block list" of preallocated (or previously freed) blocks. This
space is reserved for writes during "disk full" conditions only. As long
as you ensure that the emergency list is full when you first start
writing the database file, you should be able to flush "fake" blocks to
disk in the event of a subsequent "disk full" condition. This has the
advantage of having "near zero runtime cost" because you only burn extra
cpu cycles for preallocation at db creation time and after a "disk full"
condition occurs. While this implementation doesn't address the
secondary goal of "file fragmentation" avoidance, I see that as an
advantage for the reasons stated above.

> 
> 2. Allow engine to not use file system cache
> 
>     Currently engine works with database files by using file system cache (at least on 
> Windows). This is good (and must) for classic server with its small page cache but
> can hurt performance of super server configured to use really large page cache.
> If super server will not use file system cache this allow more efficient use of memory 
> (if there are not enough memory to fit both kind of caches) and faster access to the 
> disk.
>...

This is a nice option to have!

As for implementation on "unix-like" systems, you might try searching
for something like "direct io", "direct I/O", etc. Some OS's only offer
this feature as a file system mount option. Linux, on the other hand,
supports it on a per file descriptor basis via the O_DIRECT open/fcntl
flag.  Here is an excerpt from "man 2 open" on openSUSE 10.2:

O_DIRECT
	Try  to  minimize  cache  effects of the I/O to and from this
	file.  In general this will degrade performance, but it is
	useful in special situations, such as when applications do their
	own caching.  File I/O is done directly to/from user
	space  buffers.   The  I/O  is synchronous, i.e., at the
	completion of a read(2) or write(2), data is guaranteed to have
	been transferred.  Under Linux 2.4 transfer sizes, and the
	alignment of user buffer and file offset must all  be  multiples
	of the logical block size of the file system. Under Linux 2.6
	alignment to 512-byte boundaries suffices.


Hope this helps,
Fred P.

On 1/26/07, Vlad Horsun <hvlad@...> wrote:
> PS To be precise - "not using file system cache" means
>
>     on windows   - use FILE_FLAG_NO_BUFFERING flag when open file
>
>     on posix        - i can't find exact equivalent and will use SYNC flag. so on posix
>                             platform is will be the same as FW = ON

Perhaps this recent LKML thread will be of help?
http://lkml.org/lkml/2007/1/10/233

-- 
Alexander S. Klenin
Insight Experts Ltd.

On 01/25/2007 05:14 PM, Alexander Klenin wrote:
> On 1/26/07, Vlad Horsun <hvlad@...> wrote:
>> PS To be precise - "not using file system cache" means
...
>>
>>     on posix        - i can't find exact equivalent and will use SYNC flag. so on posix
>>                             platform is will be the same as FW = ON
> 
> Perhaps this recent LKML thread will be of help?
> http://lkml.org/lkml/2007/1/10/233> 

Good pointer!!

How could I forget about posix_fadvise() and posix_fallocate()?!?!
Guess I need a vacation. :) :)

These calls should be more portable than the linux O_DIRECT flag; at
least for systems with POSIX ADVANCED REALTIME support.

> > > A change to the caching characteristics of a file would require that
> the
> > > file handle be re-opened...
> > 
> >     Yes, but this needs to be done only one time just after file
> opened
> > and page_buffers
> > reads from header page, and only if necessary - if this stored value
> (or
> > default value,
> > if there are no stored value) greater than "MaxFileSystemCache".
> 
> So, to be sure I understand...
> 
> The setting is per database server PC?

    This setting is per server (it is stored in firebird.conf) but applied to database
with combination of "page_cache" setting stored in database header page or
"DefaultDbCachePages" setting in firebird.conf.

    Few examples :

a) most common case :
    header page's "page_cache" 
        not set

    DefaultDbCachePages 
        not set (default is 2048 for SS and 75 for CS)

    "MaxFileSystemCache"
        not set (default is 65536)

    database will use file system cache (as always)

b) superserver or classic

    header page's "page_cache" 
        4096

    DefaultDbCachePages 
        not set (default is 2048 for SS and 75 for CS)

    "MaxFileSystemCache"
        not set (default is 65536)

    database will use file system cache (as always)

c) large database (> 2 x RAM), superserver

    header page's "page_cache" 
        100000

    DefaultDbCachePages 
        not set (default is 2048 for SS and 75 for CS)

    "MaxFileSystemCache"
        not set (default is 65536)

    database will _not_ use file system cache 

d) medium-size database which guaranteed fit into RAM. 
    Say RAM = 2GB, database = 1GB, pager_size = 8KB, 
    superserver

    header page's "page_cache" 
        131072

    DefaultDbCachePages 
        not set (default is 2048 for SS and 75 for CS)

    "MaxFileSystemCache"
        not set (default is 65536)

    database will _not_ use file system cache 

Regards,
Vlad

> > PS To be precise - "not using file system cache" means
> >
> >     on windows   - use FILE_FLAG_NO_BUFFERING flag when open file
> >
> >     on posix        - i can't find exact equivalent and will use SYNC flag. so on posix
> >                             platform is will be the same as FW = ON
> 
> Perhaps this recent LKML thread will be of help?
> http://lkml.org/lkml/2007/1/10/233


    Big thanks ! This is what i searched for.

    From this thread i understand following :

a) O_DIRECT is wrong and broken
b) use POSIX_FADV_NOREUSE instead of O_DIRECT 
c) O_DIRECT used by 'database peoples'
d) POSIX_FADV_NOREUSE is not implemented (it is no-op)

    So, i conclude we must use O_DIRECT for this purpose, right ?

    And one more question : is posix_falloc expensive call or not ?
Windows's SetEndOfFile is very cheap call as it not wrte file. 
Is it true for posix_falloc also ?

Thanks,
Vlad

> > > At the very least, this 'database growth' feature should be
> controlled
> > > by a new setting -- the feature is not appropriate for all cases.
> > 
> >     Can you show such case please ?
> 
> #1  My database ships on a flash/thumb drive...  I want to allocate db
> space on a very limited basis (1 or 2 pages at a time)...

    Hmm...
 
> #2  I need to ship my database on a CD along with other distributables,
> I need to limit the amount of space "wasted" in the database allocation.

    This is adressed - use "use_all_space" switch in gbak before put
database on CD 

> Vlad, your postings suggest that you think that because there are
> multi-gigabyte HDs available that we should be allocating this disk
> space freely...

    No. It is all about disk fragmentation
 
> Don't get me wrong, I think the idea of pre-allocating large blocks of
> disk space is a very good feature.  I am simply saying that the feature
> needs to be controllable, since not every database is installed on a
> multi-GB drive.

    Ok. What (minimum please) config setting you suggest for such control ?
On\off is enough or something more flexible ?
    
> Separately, have you measured the performance impact of allocating large
> amounts of disk space, as compared to smaller block sizes (i.e. 64KB vs.
> 128KB vs. 1MB vs. 64MB vs. 128MB)?

    It have zero performance impact on _allocating space_ (on windows) but i 
expect some benefit from later use of much less fragmented database

Regards,
Vlad

> Hi Vlad,
> 
> >    Below i propose two change in a way engine worked with disk files
> >
> > 1. Look-ahead disk space allocation
> >
> >    To avoid problems with page buffers allocated in in-memory cache
> > but not on disk i propose to grab enough disk space before any attempt
> > to use pages not allocated on disk.
> 
> For all files involved for the database (including shadows)?

    Yes. I'm not sure i implemented it for shadows too :) but it worth to do

> I assume they never go beyond it's max setting per file.

    If you about multy-file database then yes - i tested it
 
> >    To make process more efficient i offer to allocate space for several pages at
> > a time. Low and high number of batch of allocated pages limited by hard-coded
> > constants (i propose 128KB and 128MB but this is can be discussed of course).
> > Engine take 1/16 part of already allocated space and adjust this number against
> > low and high limits above (1/16 also can be discussed). If look-ahead allocation
> > failed engine divided requested number of pages by factor of 2 and repeat
> > process until success or requested number more than zero. If request to allocate
> > one page is failed too error is raised (also we can log it into firebird.log)
> 
> Are PIP pages updates also or let you only grow file (not initialize/allocate anything)?

    No. Grow file only. Nothing more. Here is algorithm in brief :

1. take a file extend lock
2. check amount of current allocated space
3. if still needed call SetFilePointer + SetEndOfFile + (if present) SetFileValidData
    SetEndOfFile will return error here if no space left on device
4. release a file extend lock
 

> > 2. Allow engine to not use file system cache
> >
> >    Currently engine works with database files by using file system cache (at least on
> > Windows). This is good (and must) for classic server with its small page cache but
> > can hurt performance of super server configured to use really large page cache.
> > If super server will not use file system cache this allow more efficient use of memory
> > (if there are not enough memory to fit both kind of caches) and faster access to the
> > disk.
> 
> So there are normal three caches involved?
> -Disk-cache
> -OS-cache
> -FB-cache

    Exactly
 
> What/How is the benefit from the OS-cache on small files then?

    Zero ;)

> Are there differences between Forced Writes on/off.

    This is somewhat related, at least on Windows. Using FILE_FLAG_NO_BUFFERING
can act like FW=ON (FILE_FLAG_WRITE_THROUGH) on writes with only difference
while FILE_FLAG_NO_BUFFERING ensure data is written on device but not force
device to write through its cache (as FILE_FLAG_WRITE_THROUGH does)
 
> >    I propose to introduce new configuration setting "UseFileSystemCache".
> >
> >    If database page cache is more than value of this setting then file system
> > cache is not used. If database page cache is less than "UseFileSystemCache"
> > value then file system cache is used (as before). Default value of "UseFileSystemCache"
> > must be big enough to switch off FS cache only for databases with really big page
> > cache configured. I offer to make it 65536 by default. With such default value we
> > preserve performance of classic server and only super server with big cache will
> > be affected by new rules
> 
> 65536 pages/bytes/MB?

    Pages. It is in pages to make easy comparing with page cache size measured in pages also
 
> What does happen on switch-over point (from OS-cache to non OS-cache). Windows releases cache 
> immediately without drawbacks?

    Decision about to use\not use FS cache made at file open time (first attach or create database). 
This file is not in FS cache at this time, only header page probably

> Overall i think this could certainly give performance improvement (on restore, bulk inserts, ...).

    If it is used properly - yes, i think so

> btw, "UseFileSystemCache" sounds as On/Off setting. May be something like 
> "IgnoreFileSystemCacheThreshold" or "DisableFileSystemCacheThreshold", ..

    "DisableFileSystemCacheThreshold" sounds better for me

Regards,
Vlad

> On 01/23/2007 03:18 PM, Vlad Horsun wrote:
> >     Below i propose two change in a way engine worked with disk files
> 
> Hi Vlad,
> 
> I have a few questions and a couple of comments for your consideration.
> 
> Am I correct that neither one of these two changes is directly related
> to or dependent on the other?

    Correct
 
> > 1. Look-ahead disk space allocation
> > 
> >     To avoid problems with page buffers allocated in in-memory cache
> > but not on disk i propose to grab enough disk space before any attempt 
> > to use pages not allocated on disk. 
> >...
> 
> Given your recent postings, the primary goal of this proposed change
> seems to be handling a "disk full" condition when "fake pages", as you
> described them, still need to be written to disk. I like that you're
> looking at this issue! Cost and space are big concerns when dealing with
> small/cheap network infrastructure devices with embedded db's.
> Gracefully handling unexpected "disk full" conditions is important in
> such environments, where "disk" space is limited.

    You right - first goal was to ensure we have no "floating" in memory
pages without allocated disk space for them
 
> Questions:
>   1.a) What is the minimum number of preallocated pages required to
> ensure that you'll have enough disk space to write all "fake" pages to
> disk before a "disk full/low" error is raised and, potentially, the db
> is restarted?

    One :) As we will allocate space first and fake page second having preallocated 
one page is enough. Preallocating more pages is good from performance point of 
view (as we must block other process from doing the same thing) and from disk
fragmentation point of view

>   1.b) Is it possible that your proposed 128KB minimum would not be
> large enough and might still, potentially, result in a corrupted db due
> to lack of space?

    No

>   1.c) If the answer to 1.b) is YES, then why is it good to allow a user
> to specify a value that they think will protect them from a potential db
> corruption, but it may not?

    Let me not answer here :)

> As an aside, it might be worth noting that while preallocating large
> chunks of file space might help limit "file fragmentation" on some file
> stores, it may not on others. Raw devices, for example, are conceptually
> always "preallocated". 

    Agree

> Doing extra/larger writes to preallocate space on them is a waste of cycles. 

    My point is that preallocating space does not write to file - only in system
catalog. On windows it is true

> For file systems, it will depend on the
> allocation policy of file system used (e.g., block-based, extent-based,
> cluster-based, write-anywhere, allocate-on-flush, etc.). For example,
> writing 128KB to an empty file on a FAT file system will allocate file
> space differently then would the same size write to an empty file on an
> extent-based file system with 500MB extents. My point here is that the
> proposed change may or may not help with "file fragmentation" depending
> on factors outside the scope of FB. Therefore, you'll probably need to
> let users enable/disable/tune preallocation on a per *file* basis. For
> this reason, I'd address this goal and the "disk full" goal separately.

    Preallocate at least one page is a must for "disk full" goal. So question
is will we get an user option to define how much pages can be preallocated,
right ?

> Also, just in case you're not aware of it, many unix-like file systems
> have "sparse" file space allocation. That is, files can have unallocated
> "holes" in them. For example, create an empty file, seek to offset
> 10,000,000, write one byte and then close the file. After this, the
> file's "size" may appear to be 10,000,001 bytes. However, how much file
> space was allocated depends upon the file system used. It might be one
> fs block (say 8KB), one fs extent (say 150MB), ~10MB, or something else.
>  So, on "sparse" file systems, you'll probably need to write at least
> one byte to each fs block you want to preallocate.

    From what i know about Windows spare files - it is not recommended for
database use
 
> An alternative implementation that I've seen is to keep an "emergency
> free block list" of preallocated (or previously freed) blocks. 

    What i propose can be named such also

> This space is reserved for writes during "disk full" conditions only. 

    When ?

> As long
> as you ensure that the emergency list is full when you first start
> writing the database file, you should be able to flush "fake" blocks to
> disk in the event of a subsequent "disk full" condition. 

    This is near the same as i offer, as i understand

> This has the
> advantage of having "near zero runtime cost" because you only burn extra
> cpu cycles for preallocation at db creation time and after a "disk full"
> condition occurs. 

    My way also have "near zero runtime cost" ;)
BTW, how can we extend file after a "disk full" condition occurs ?

> While this implementation doesn't address the
> secondary goal of "file fragmentation" avoidance, I see that as an
> advantage for the reasons stated above.
> 
> > 
> > 2. Allow engine to not use file system cache
> > 
> >     Currently engine works with database files by using file system cache (at least on 
> > Windows). This is good (and must) for classic server with its small page cache but
> > can hurt performance of super server configured to use really large page cache.
> > If super server will not use file system cache this allow more efficient use of memory 
> > (if there are not enough memory to fit both kind of caches) and faster access to the 
> > disk.
> >...
> 
> This is a nice option to have!

    Thanks ;)

> As for implementation on "unix-like" systems, you might try searching
> for something like "direct io", "direct I/O", etc. Some OS's only offer
> this feature as a file system mount option. Linux, on the other hand,
> supports it on a per file descriptor basis via the O_DIRECT open/fcntl
> flag.  Here is an excerpt from "man 2 open" on openSUSE 10.2:
> 
> O_DIRECT
> Try  to  minimize  cache  effects of the I/O to and from this
> file.  In general this will degrade performance, but it is
> useful in special situations, such as when applications do their
> own caching.  File I/O is done directly to/from user
> space  buffers.   The  I/O  is synchronous, i.e., at the
> completion of a read(2) or write(2), data is guaranteed to have
> been transferred.  Under Linux 2.4 transfer sizes, and the
> alignment of user buffer and file offset must all  be  multiples
> of the logical block size of the file system. Under Linux 2.6
> alignment to 512-byte boundaries suffices.

    Thanks for the info, now i know what i need to look for 

    We already satisfy this conditions as Windows have the same 
requirements for unbuffered IO

Regards,
Vlad

> -----Original Message-----
> From: firebird-devel-bounces@...
> [mailto:firebird-devel-bounces@... Behalf Of Vlad
> Horsun
> Sent: Viernes, 26 de Enero de 2007 6:13
> >
> > Are PIP pages updates also or let you only grow file (not
> initialize/allocate anything)?
>
>     No. Grow file only. Nothing more. Here is algorithm in brief :
>
> 1. take a file extend lock
> 2. check amount of current allocated space
> 3. if still needed call SetFilePointer + SetEndOfFile + (if
> present) SetFileValidData

Call SetEndOfFile PLUS SetFileValidData? I didn't understand previously that
you will call both if they are available.


>     SetEndOfFile will return error here if no space left on device
> 4. release a file extend lock


> > So there are normal three caches involved?
> > -Disk-cache
> > -OS-cache
> > -FB-cache
>
>     Exactly
>
> > What/How is the benefit from the OS-cache on small files then?
>
>     Zero ;)

Can all the small db file reside in the OS cache? Is this zero advantage?

C.

> > > Are PIP pages updates also or let you only grow file (not
> > initialize/allocate anything)?
> >
> >     No. Grow file only. Nothing more. Here is algorithm in brief :
> >
> > 1. take a file extend lock
> > 2. check amount of current allocated space
> > 3. if still needed call SetFilePointer + SetEndOfFile + (if
> > present) SetFileValidData
> 
> Call SetEndOfFile PLUS SetFileValidData? I didn't understand previously that
> you will call both if they are available.

    Sorry if i was unclear

> >     SetEndOfFile will return error here if no space left on device
> > 4. release a file extend lock
> 
> 
> > > So there are normal three caches involved?
> > > -Disk-cache
> > > -OS-cache
> > > -FB-cache
> >
> >     Exactly
> >
> > > What/How is the benefit from the OS-cache on small files then?
> >
> >     Zero ;)
> 
> Can all the small db file reside in the OS cache? Is this zero advantage?

    I meant the case when small database fits in FB cache too

Regards,
Vlad

> -----Original Message-----
> From: firebird-devel-bounces@...
> [mailto:firebird-devel-bounces@... Behalf Of Fred
> Polizo
> Sent: Jueves, 25 de Enero de 2007 22:01
> To: firebird-devel@...
>
> O_DIRECT
> 	Try  to  minimize  cache  effects of the I/O to and from this
> 	file.  In general this will degrade performance, but it is
> 	useful in special situations, such as when applications do their
> 	own caching.  File I/O is done directly to/from user
> 	space  buffers.   The  I/O  is synchronous, i.e., at the
> 	completion of a read(2) or write(2), data is guaranteed to have
> 	been transferred.  Under Linux 2.4 transfer sizes, and the
> 	alignment of user buffer and file offset must all  be  multiples
> 	of the logical block size of the file system. Under Linux 2.6
> 	alignment to 512-byte boundaries suffices.

Warning: drifting OT.

Thinking again, unbuffered I/O seems to go against Charles Caro idea of
SuperServer v2. Unbuffered implies always sync and Charles idea was to take
advantage of async I/O at least on Windows with Overlapped operations.
Although I have doubts on the merits of overlapped programming in the db
server.

Speaking of that disabled code, I found conflicting code:

pio.h:
#ifdef SUPERSERVER_V2
	void* fil_io_events[MAX_FILE_IO];	/* Overlapped I/O events */
#endif

In winnt.cpp it's assumed that sizeof(HANDLE) <= sizeof(void*) but the
initialization is not coherent:
#ifdef SUPERSERVER_V2
	memset(file->fil_io_events, 0, MAX_FILE_IO * sizeof(SLONG));
#endif

SLONG size won't match void* size in 64-bits, but this code is disabled.
:-)

C.

>     And one more question : is posix_falloc expensive call or not ?
> Windows's SetEndOfFile is very cheap call as it not wrte file. 
> Is it true for posix_falloc also ?
> 

Unfortunately it's extremely NOT effective. Omitting details (this is 
from glibc 2.4):

/* Reserve storage for the data of the file associated with FD.  */

int
posix_fallocate (int fd, __off_t offset, __off_t len)
{
/// skipped a lot of checks
   /* Write something to every block.  */
   for (offset += (len - 1) % f.f_bsize; len > 0; offset += f.f_bsize) {
       len -= f.f_bsize;

/// some more skipps

       if (__pwrite (fd, "", 1, offset) != 1)
         return errno;
     }

     return 0;
}

You see - write something to every block :-(

And as I've read in the NET, it will not come soon.
http://linux.derkeiler.com/Mailing-Lists/Kernel/2003-08/5680.html

Therefore you see - M$ is great here. Additional information - this may 
be done under linux, but only for XFS, using this filesystem specific 
API. Not sure it's good solution for us.

> >     And one more question : is posix_falloc expensive call or not ?
> > Windows's SetEndOfFile is very cheap call as it not wrte file. 
> > Is it true for posix_falloc also ?
> > 
> 
> Unfortunately it's extremely NOT effective. Omitting details (this is 
> from glibc 2.4):
> 
> /* Reserve storage for the data of the file associated with FD.  */
> 
> int
> posix_fallocate (int fd, __off_t offset, __off_t len)
> {
> /// skipped a lot of checks
>    /* Write something to every block.  */
>    for (offset += (len - 1) % f.f_bsize; len > 0; offset += f.f_bsize) {
>        len -= f.f_bsize;
> 
> /// some more skipps
> 
>        if (__pwrite (fd, "", 1, offset) != 1)
>          return errno;
>      }
> 
>      return 0;
> }
> 
> You see - write something to every block :-(

    Very sad

> And as I've read in the NET, it will not come soon.
> http://linux.derkeiler.com/Mailing-Lists/Kernel/2003-08/5680.html
> 
> Therefore you see - M$ is great here. Additional information - this may 
> be done under linux, but only for XFS, using this filesystem specific 
> API. Not sure it's good solution for us.

    Then we can or disable preallocation of more than needed pages for 
non Win platforms in code or implement it (using posix_falloc) but make 
it disabled by default in config with approprate warning (if we'll introduce 
config settings)

Regards,
Vlad

On 01/26/2007 01:37 AM, Vlad Horsun wrote:
...
> 
>> Doing extra/larger writes to preallocate space on them is a waste of cycles. 
> 
>     My point is that preallocating space does not write to file - only in system
> catalog. On windows it is true

But for the non-windows file systems this is not necessarily true.

>> For file systems, it will depend on the
>> allocation policy of file system used ...
> 
>     Preallocate at least one page is a must for "disk full" goal. So question
> is will we get an user option to define how much pages can be preallocated,
> right ?

Not exactly. Users may want to disable the "file fragmentation
avoidance" feature in cases where it does not benefit them because of
their choice of file system. However, they may still want protection
from "disk full" errors. That's why I'm not sure coupling the two goals
is a good idea.

I guess users could specify a value of one page to protect them from the
"disk full" condition only, but that is a not obvious.

>> Also, just in case you're not aware of it, many unix-like file systems
>> have "sparse" file space allocation. That is, files can have unallocated
>> "holes" in them. For example, create an empty file, seek to offset
>> 10,000,000, write one byte and then close the file. After this, the
>> file's "size" may appear to be 10,000,001 bytes. However, how much file
>> space was allocated depends upon the file system used. It might be one
>> fs block (say 8KB), one fs extent (say 150MB), ~10MB, or something else.
>>  So, on "sparse" file systems, you'll probably need to write at least
>> one byte to each fs block you want to preallocate.
> 
>     From what i know about Windows spare files - it is not recommended for
> database use

Sorry, many of the unix-like file systems in use today use sparse space
allocation (e.g., ext3, reiserfs, UFS, etc.). I think you'll need to
deal with this issue on these file systems.

You could try posix_fallocate() to preallocate space. If that isn't
supported on your target platform, there is another trick I've seen used
to allocate space at the end of a file efficiently. You call the
sendfile() system call. This call transfers the data from one file
descriptor to another and does the entire transfer in the kernel! If you
want to try this, I can explain how this might be done.

>> An alternative implementation that I've seen is to keep an "emergency
>> free block list" of preallocated (or previously freed) blocks. 
> 
>     What i propose can be named such also

They are not the same.

>> This space is reserved for writes during "disk full" conditions only. 
> 
>     When ?

In my proposed implementation, you only preallocate the "emergency page
list" space at database creation time OR after you restart after a "disk
full" error, when we hope there is free space available. You only use
these emergency page(s) in the event of a ENOSPC (no space left on
device) error on a file write. The page(s) are reserved exclusively for
this purpose.

Thus, periodic preallocation is not required. There is virtually no
additional runtime cost for this implementation.  And since you say you
only need one emergency page to handle the "disk full" error, then that
is all need to allocate for the "emergency page list" -- one page.

...
>> This has the
>> advantage of having "near zero runtime cost" because you only burn extra
>> cpu cycles for preallocation at db creation time and after a "disk full"
>> condition occurs. 
> 
>     My way also have "near zero runtime cost" ;)

:)  Actually, you're proposal does incur additional runtime costs
because it periodically preallocates space and then uses that space. So,
you must periodically replenish your supply of preallocated pages. This
isn't required in the alternate implementation that I described.

> BTW, how can we extend file after a "disk full" condition occurs ?

There are various ways you might do this. One way, assuming the db would
be restarted after the disk full error, is to check at db start time (or
the first db file write) to see if the emergency page list is full. If
it is not, fill it and then proceed.

If you don't want to require a restart, I suppose you could do something
like run in "read only" mode until some free space becomes available,
then refill the emergency page list, and then again allow db writes.

Vlad Horsun :
>> You see - write something to every block :-(
> 
>     Very sad
> 
>> And as I've read in the NET, it will not come soon.
>> http://linux.derkeiler.com/Mailing-Lists/Kernel/2003-08/5680.html
>>
>> Therefore you see - M$ is great here. Additional information - this may 
>> be done under linux, but only for XFS, using this filesystem specific 
>> API. Not sure it's good solution for us.
> 
>     Then we can or disable preallocation of more than needed pages for 
> non Win platforms in code or implement it (using posix_falloc) but make 
> it disabled by default in config with approprate warning (if we'll introduce 
> config settings)

I prefer not to add config settings, that will definitely make life 
worst (pay attention, glibc does not use better ways to do things even 
when file is on XFS). Appears for linux (may be some other 
posix-compatible like FreeBSD does it, BSD does not use glibc at all) we 
should not preallocate more pages then needed at all.

On 01/26/2007 12:46 AM, Vlad Horsun wrote:
...
>> Perhaps this recent LKML thread will be of help?
>> http://lkml.org/lkml/2007/1/10/233
> 
> 
>     Big thanks ! This is what i searched for.
> 
>     From this thread i understand following :
> 
> a) O_DIRECT is wrong and broken
> b) use POSIX_FADV_NOREUSE instead of O_DIRECT 
> c) O_DIRECT used by 'database peoples'
> d) POSIX_FADV_NOREUSE is not implemented (it is no-op)
> 
>     So, i conclude we must use O_DIRECT for this purpose, right ?

On second thought, I'm not sure O_DIRECT is a good choice.  If Linus
doesn't like the implementation, that isn't a good sign. For example,
I'm not sure what will happen if you have some processes using this flag
and some that don't.

I haven't used it, but POSIX_FADV_DONTNEED may be of some use. This
posix_fadvise() hint asks the kernel to invalidate the blocks in the fs
cache associated with the specified file region. So, for example, you
could call this to periodically free cached file pages. Unwritten pages
are not affected by this operation, so they would need to be flushed
first. But, I'm not sure if that is necessary if you open the file with
O_SYNC. Some investigation/experimentation might be required here.

Vlad,

> > #2  I need to ship my database on a CD along with other
distributables,
> > I need to limit the amount of space "wasted" in the database
allocation.
>=20
>     This is adressed - use "use_all_space" switch in gbak before put
> database on CD

I'm sorry but I don't see how "use_all_space" has anything to do with
your proposal.

You are suggesting that if the disk space is available, that the
database will grow in increments of 1/16 the database size. =20

Accordingly, if my database is 480MB and only a single new page is
required (even after "use_all_space" is considered) that the database
file will grow by 30MB!

This will leave the database with ~29.9MB of wasted space.


> > Don't get me wrong, I think the idea of pre-allocating large blocks
of
> > disk space is a very good feature.  I am simply saying that the
feature
> > needs to be controllable, since not every database is installed on a
> > multi-GB drive.
>=20
>     Ok. What (minimum please) config setting you suggest for such
control
> ?
> On\off is enough or something more flexible ?

As has been asked by others, a "growth factor" setting -- in db pages.


Why DB pages and not KB/MB?

Because it is the most appropriate way to describe the db growth, after
all we describe the db cache in page terms?


> > Separately, have you measured the performance impact of allocating
large
> > amounts of disk space, as compared to smaller block sizes (i.e. 64KB
vs.
> > 128KB vs. 1MB vs. 64MB vs. 128MB)?
>=20
>     It have zero performance impact on _allocating space_ (on windows)
but
> I expect some benefit from later use of much less fragmented database

But, is not necessary to initialize those pages (write the page headers)
as well?


Sean

> Vlad,
> 
> > > #2  I need to ship my database on a CD along with other
> distributables,
> > > I need to limit the amount of space "wasted" in the database
> allocation.
> > 
> >     This is adressed - use "use_all_space" switch in gbak before put
> > database on CD
> 
> I'm sorry but I don't see how "use_all_space" has anything to do with
> your proposal.

    This was pointed by Dimitry Sibiryakov (in this thread) and was agreed
that if "use_all_space" switch is set then database will grow by one page
at time
 
> You are suggesting that if the disk space is available, that the
> database will grow in increments of 1/16 the database size.  
> 
> Accordingly, if my database is 480MB and only a single new page is
> required (even after "use_all_space" is considered) that the database
> file will grow by 30MB!
> 
> This will leave the database with ~29.9MB of wasted space.

    Please, re-read what Dimitry Sibiryakov wrote and my answers to him


> >     Ok. What (minimum please) config setting you suggest for such
> control
> > ?
> > On\off is enough or something more flexible ?
> 
> As has been asked by others, a "growth factor" setting -- in db pages.

    In my proposition "growth factor" depends on current database size
and have lower and upper limits. Do you want to eliminate it ?

> Why DB pages and not KB/MB?
> 
> Because it is the most appropriate way to describe the db growth, after
> all we describe the db cache in page terms?

    But when you look at your disk you see how many free KB\MB\GB it have.
Personally, i think describe page buffers cache size in pages also not good 
idea. You must every time recalculate pages in KB\MB\GB to compare with
amount of memory available. 

> > > Separately, have you measured the performance impact of allocating
> large
> > > amounts of disk space, as compared to smaller block sizes (i.e. 64KB
> vs.
> > > 128KB vs. 1MB vs. 64MB vs. 128MB)?
> > 
> >     It have zero performance impact on _allocating space_ (on windows)
> but
> > I expect some benefit from later use of much less fragmented database
> 
> But, is not necessary to initialize those pages (write the page headers)
> as well?

    Not necesary. Content of unused (or orphan if case of crash) pages 
must not have any value for engine as there are no path to read it

Regards,
Vlad

> Warning: drifting OT.
> 
> Thinking again, unbuffered I/O seems to go against Charles Caro idea of
> SuperServer v2. Unbuffered implies always sync and Charles idea was to take
> advantage of async I/O at least on Windows with Overlapped operations.
> Although I have doubts on the merits of overlapped programming in the db
> server.

    It seems you mixed terms 'unbuffered IO' and 'non-blocking IO'. The faster 
way to deal with disk is unbuffered IO (at least on Windows but i'm pretty sure 
it is platform independent statement). Of course if you implement own caching. 

    'sync' not equal to 'blocking'. AsyncIO in terms of file system API means 
non-blocked IO. It can work with files opened with or without SYNC flag

Regards,
Vlad

PS Looking at behavior of IB 7.x i can say that this is very likely 
    SUPERSERVER_V2

Vlad,

> > > > #2  I need to ship my database on a CD along with other
> > distributables,
> > > > I need to limit the amount of space "wasted" in the database
> > allocation.
> > >
> > >     This is adressed - use "use_all_space" switch in gbak before
put
> > > database on CD
> >
> > I'm sorry but I don't see how "use_all_space" has anything to do
with
> > your proposal.
>=20
>     This was pointed by Dimitry Sibiryakov (in this thread) and was
agreed
> that if "use_all_space" switch is set then database will grow by one
page
> at time

But this switch only applies to GBAK restore operations.  The switch
does not apply to general database operations afterwards.


> > You are suggesting that if the disk space is available, that the
> > database will grow in increments of 1/16 the database size.
> >
> > Accordingly, if my database is 480MB and only a single new page is
> > required (even after "use_all_space" is considered) that the
database
> > file will grow by 30MB!
> >
> > This will leave the database with ~29.9MB of wasted space.
>=20
>     Please, re-read what Dimitry Sibiryakov wrote and my answers to
him

OK, I will.


> > >     It have zero performance impact on _allocating space_ (on
windows)
> > but
> > > I expect some benefit from later use of much less fragmented
database
> >
> > But, is not necessary to initialize those pages (write the page
headers)
> > as well?
>=20
>     Not necesary. Content of unused (or orphan if case of crash) pages
> must not have any value for engine as there are no path to read it

Since the page must not have any value, does this not require the pages
to be initialized/cleared of any data which is present?

After all, the OS will re-allocate old disk space, which will have
garbage data present.

Further, have you checked the operation of NBackup against your
proposal/private branch?  NBackup expects a proper DB page header for
each database page.


Sean

> > > > > #2  I need to ship my database on a CD along with other
> > > distributables,
> > > > > I need to limit the amount of space "wasted" in the database
> > > allocation.
> > > >
> > > >     This is adressed - use "use_all_space" switch in gbak before
> put
> > > > database on CD
> > >
> > > I'm sorry but I don't see how "use_all_space" has anything to do
> with
> > > your proposal.
> > 
> >     This was pointed by Dimitry Sibiryakov (in this thread) and was
> agreed
> > that if "use_all_space" switch is set then database will grow by one
> page
> > at time
> 
> But this switch only applies to GBAK restore operations.  The switch
> does not apply to general database operations afterwards.

    You said above about database which will be placed on CD and must 
no waste limited CD space. Therefore GBAK restore operation with
"use_all_space" switch is very logical way to do it in _all_ FB versions.

...

> > > >     It have zero performance impact on _allocating space_ (on
> windows)
> > > but
> > > > I expect some benefit from later use of much less fragmented
> database
> > >
> > > But, is not necessary to initialize those pages (write the page
> headers)
> > > as well?
> > 
> >     Not necesary. Content of unused (or orphan if case of crash) pages
> > must not have any value for engine as there are no path to read it
> 
> Since the page must not have any value, does this not require the pages
> to be initialized/cleared of any data which is present?

    Why  ? Engine will never read content of this pages
 
> After all, the OS will re-allocate old disk space, which will have
> garbage data present.
> 
> Further, have you checked the operation of NBackup against your
> proposal/private branch?  NBackup expects a proper DB page header for
> each database page.

    Good point. NBackup must not read not allocated pages (at least at the tail) 
- it is waste of time and disk space. Currently NBackup just reads whole database 
file. I'll look what i can do with it. Simplest way is to track inventory pages while
reading db file and take care about bits on a last PIP. To detect last used PIP we 
can introduce new page flag.

Regards,
Vlad

Vlad,

> > Further, have you checked the operation of NBackup against your
> > proposal/private branch?  NBackup expects a proper DB page header
for
> > each database page.
>=20
>     Good point. NBackup must not read not allocated pages (at least at
the
> tail) - it is waste of time and disk space. Currently NBackup just
reads
> whole database file. I'll look what i can do with it. Simplest way is
to
> track inventory pages while reading db file and take care about bits
on a
> last PIP. To detect last used PIP we can introduce new page flag.

How about a new value in the database header, to note the last 'real' db
page?

That would seem better than having to look for a new page flag.


Sean

Leyne, Sean wrote:
> How about a new value in the database header, to note the last 'real' db
> page?
>   
This is what we want, IMO.

And how about shrink the database, based on this value, when user change 
it to read-only mode?


Adriano

> > How about a new value in the database header, to note the last 'real' db
> > page?
> >   
> This is what we want, IMO.

    This will lead to big amount of additional writes to header page and will slow
whole process as we must move HDD heads from header page to the last page
every time we have allocate new page

    Much better to save this value in PIP page - maximum number of page from 
this PIP written to database. We can use Ods::pag::reserved for this purpose
to not introduce new major ODS version
 
> And how about shrink the database, based on this value, when user change 
> it to read-only mode?

    It will be possible doing nbackup level 0 if we'll teach nbackup to ignore 
allocated but not used space

Regards,
Vlad

Hi, All.

Reading through this thread, it seems to me that the basic design idea of 
having a database / system wide configuration parameter solves, or can solve 
the problem of OS disk fragmentation.

However, it looks to me like this is the wrong problem to solve. As far as I 
can tell, the whole point of doing things like allocating large blocks is to 
help performance of the database system - this is a system as a whole, NOT 
just the way the O/S allocates pages off a disk / disks.

In order to do this in a more useful manner, what I propose is as follows:

1) A parameter PER TABLE, which specifies the growth factor (KB/MB/No of 
pages/Whatever, I don't really care how). You can access this with an ALTER 
TABLE <whatever syntax you like>, to change the setting. Default would be 
current behaviour of single page at a time, if not otherwise specified.

2) A database specific configuration parameter, TO SUPPRESS use of the 
growth factors.

3) On a database restore, you DO NOT USE these values. (this solves the 'I 
have a readonly database I want to distribute, and I want it to be small' 
problem). All they have to do is backup the system, and restore, then 
distribute the restored version as the 'readonly' system, with no extra 
junk. After a restore, the tables will already be grouped together, with a 
single table's data in consecutive pages anyway (ok, excluding a page here 
and there, for housekeeping).

The reasons that I think that this is the way to go are:

a) I feel that it is rather pointless dealing with the O/S disk 
fragmentation issues, and ignore the table fragmentation WITHIN the 
allocated O/S file/files. An extremely common operation is database backup, 
for example. What does it do? Reads each TABLE, sequentially. It is very 
inexpensive to buy RAID systems that are capable of reading / writing 
500-800MB/S, sustained, for sequential reads/writes, but you only get this 
performance level if BOTH the O/S file fragmentation AND the table 
fragmentation within the O/S file/files is minimised. For large tables, this 
is critical.

b) It allows you to base your decision on real expected growth factors of 
each table.

c) It should increase backup performance SIGNIFICANTLY.

d) If you really want to muck about with the data, restore for a test 
system, whatever, and you do not want to incur the space overhead, all you 
have to do for the test system / whatever is to switch on the option to 
suppress the growth factors.

Other things to bear in mind:

Obviously, your performance will suffer if you have lots of back 
versions/garbage all over the place etc, but this is true, even if we don't 
use an intelligent growth scheme. (Perhaps this should be looked at as well, 
so that there is initially an attempt to store old backversions on the page 
directly after the one with the primary record on it, or something like 
that?)

To keep the scheme clean, ultimately it makes sense to take pages which have 
become empty by deleting records from a table, and NOT make them generally 
accessible. Reserve them for the exclusive use of the table they originally 
came from, so that we don't end up refragmenting stuff that was already 
defragmented by initial placement, on mass deletion. (Yes, I am quite happy 
to waste 1GB of table space after deleting 5 million records, because:
   a) On backup, to read 1GB of empty table space will only take 2 seconds, 
with decent hardware (because it is sequentially allocated, of course).
   b) I also know that pretty soon, due to the nature of the app, that 
within 6 hours, the 5 million records will be back, and the space will no 
longer be 'wasted'.
)

Any thoughts?

Kind regards
Larry

Hi Vlad,

>> > How about a new value in the database header, to note the last 'real' db
>> > page?
>> >   
>> This is what we want, IMO.
> 
>    This will lead to big amount of additional writes to header page and will slow
> whole process as we must move HDD heads from header page to the last page
> every time we have allocate new page

What about the transaction information then?

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
  http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
  http://www.firebirdsql.org
  http://www.firebirdsql.info

Support list for Interbase and Firebird users:
  firebird-support@...

Nederlandse firebird nieuwsgroep:
  news://newsgroups.firebirdsql.info

> Hi Vlad,
> 
> >> > How about a new value in the database header, to note the last 'real' db
> >> > page?
> >> >   
> >> This is what we want, IMO.
> > 
> >    This will lead to big amount of additional writes to header page and will slow
> > whole process as we must move HDD heads from header page to the last page
> > every time we have allocate new page
> 
> What about the transaction information then?

    Not understand, sorry

Regards,
Vlad

> >> Doing extra/larger writes to preallocate space on them is a waste of cycles. 
> > 
> >     My point is that preallocating space does not write to file - only in system
> > catalog. On windows it is true
> 
> But for the non-windows file systems this is not necessarily true.

    Now i see it, thanks for the explanations
 
> >> For file systems, it will depend on the
> >> allocation policy of file system used ...
> > 
> >     Preallocate at least one page is a must for "disk full" goal. So question
> > is will we get an user option to define how much pages can be preallocated,
> > right ?
> 
> Not exactly. Users may want to disable the "file fragmentation
> avoidance" feature in cases where it does not benefit them because of
> their choice of file system. However, they may still want protection
> from "disk full" errors. That's why I'm not sure coupling the two goals
> is a good idea.

    Without preallocating at least one page i doubt we can avoid "disk full" errors
 
> I guess users could specify a value of one page to protect them from the
> "disk full" condition only, but that is a not obvious.

    Therefore i offer always allocate new page physically before fake it.
It worked fine on Windows - i even get a little performance increase at
database restore
 
> >> Also, just in case you're not aware of it, many unix-like file systems
> >> have "sparse" file space allocation. That is, files can have unallocated
> >> "holes" in them. For example, create an empty file, seek to offset
> >> 10,000,000, write one byte and then close the file. After this, the
> >> file's "size" may appear to be 10,000,001 bytes. However, how much file
> >> space was allocated depends upon the file system used. It might be one
> >> fs block (say 8KB), one fs extent (say 150MB), ~10MB, or something else.
> >>  So, on "sparse" file systems, you'll probably need to write at least
> >> one byte to each fs block you want to preallocate.
> > 
> >     From what i know about Windows spare files - it is not recommended for
> > database use
> 
> Sorry, many of the unix-like file systems in use today use sparse space
> allocation (e.g., ext3, reiserfs, UFS, etc.). I think you'll need to
> deal with this issue on these file systems.

    We already agreed that posix have no standard and cheap way to physically
extend file. Sad but we must live with it

> You could try posix_fallocate() to preallocate space. If that isn't
> supported on your target platform, there is another trick I've seen used
> to allocate space at the end of a file efficiently. You call the
> sendfile() system call. This call transfers the data from one file
> descriptor to another and does the entire transfer in the kernel! If you
> want to try this, I can explain how this might be done.

    Thank you, but most time is spend not in (relatively cheap) memory transfers 
but filling disk with data. I doubt it will work much faster then plain write() :)
 
> >> An alternative implementation that I've seen is to keep an "emergency
> >> free block list" of preallocated (or previously freed) blocks. 
> > 
> >     What i propose can be named such also
> 
> They are not the same.

    Now i see it ;)
 
> >> This space is reserved for writes during "disk full" conditions only. 
> > 
> >     When ?
> 
> In my proposed implementation, you only preallocate the "emergency page
> list" space at database creation time OR after you restart after a "disk
> full" error, when we hope there is free space available. You only use
> these emergency page(s) in the event of a ENOSPC (no space left on
> device) error on a file write. The page(s) are reserved exclusively for
> this purpose.

    Where this pages will be stored ? In database file ? Then this is very close
to my initial offer except of that that pages will never used in normal cases and we 
need implement new page type and way to manage this list. 

    In separate file ? Then we have no guarantee that we will use this space 
if there are concurrent process actively eating disk space. 

    How much pages we will need to preallocate ? At least page cache size. 
What it we change page cache size after initial preallocation ? 

    As you see - this is not as easy as it seems 
 
> Thus, periodic preallocation is not required. There is virtually no
> additional runtime cost for this implementation.  And since you say you
> only need one emergency page to handle the "disk full" error, then that
> is all need to allocate for the "emergency page list" -- one page.

    No. One page is enough if we allocate it before faking the new page. If we 
leave page allocation mechanism as is then we'll need page_cache_size
pages in reserve in worst case.

> >> This has the
> >> advantage of having "near zero runtime cost" because you only burn extra
> >> cpu cycles for preallocation at db creation time and after a "disk full"
> >> condition occurs. 
> 
> >     My way also have "near zero runtime cost" ;)
> 
> :)  Actually, you're proposal does incur additional runtime costs
> because it periodically preallocates space and then uses that space. So,
> you must periodically replenish your supply of preallocated pages. This
> isn't required in the alternate implementation that I described.

    In my offer cost of preallocation is cheap and rare because it is done by 
big chunks. On windows only, of course ;(

> > BTW, how can we extend file after a "disk full" condition occurs ?
> 
> There are various ways you might do this. One way, assuming the db would
> be restarted after the disk full error, is to check at db start time (or
> the first db file write) to see if the emergency page list is full. If
> it is not, fill it and then proceed.
> 
> If you don't want to require a restart, I suppose you could do something
> like run in "read only" mode until some free space becomes available,
> then refill the emergency page list, and then again allow db writes.

    It looks as one more problem in your offer ;)

Regards,
Vlad

> Reading through this thread, it seems to me that the basic design idea of 
> having a database / system wide configuration parameter solves, or can solve 
> the problem of OS disk fragmentation.

    Perhaps

> However, it looks to me like this is the wrong problem to solve. 

    Why ? Anyway - if we can solve it by the cheap way - why not ?

> As far as I 
> can tell, the whole point of doing things like allocating large blocks is to 
> help performance of the database system - this is a system as a whole, NOT 
> just the way the O/S allocates pages off a disk / disks.
> 
> In order to do this in a more useful manner, what I propose is as follows:
> 
> 1) A parameter PER TABLE, which specifies the growth factor (KB/MB/No of 
> pages/Whatever, I don't really care how). You can access this with an ALTER 
> TABLE <whatever syntax you like>, to change the setting. Default would be 
> current behaviour of single page at a time, if not otherwise specified.

    We want to avoid additional parameters as much as possible (we are Firebird, 
not ORACLE and want to stay 'self-tune' ;).
 
> 2) A database specific configuration parameter, TO SUPPRESS use of the 
> growth factors.

    This is more appropriate (as for me)
 
> 3) On a database restore, you DO NOT USE these values. (this solves the 'I 
> have a readonly database I want to distribute, and I want it to be small' 
> problem). 

    This is wrong way. If parameters present it must work. 
My personal opinion of course

> All they have to do is backup the system, and restore, then 
> distribute the restored version as the 'readonly' system, with no extra 
> junk. After a restore, the tables will already be grouped together, with a 
> single table's data in consecutive pages anyway (ok, excluding a page here 
> and there, for housekeeping).
> 
> The reasons that I think that this is the way to go are:
> 
> a) I feel that it is rather pointless dealing with the O/S disk 
> fragmentation issues, and ignore the table fragmentation WITHIN the 
> allocated O/S file/files. 

    Why do you think we'll ignore table fragmentation ? Because i still not 
post all RFC's i have to post ? :)

> An extremely common operation is database backup, 
> for example. What does it do? Reads each TABLE, sequentially. It is very 
> inexpensive to buy RAID systems that are capable of reading / writing 
> 500-800MB/S, sustained, for sequential reads/writes, but you only get this 
> performance level if BOTH the O/S file fragmentation AND the table 
> fragmentation within the O/S file/files is minimised. For large tables, this 
> is critical.

    Agree
 
> b) It allows you to base your decision on real expected growth factors of 
> each table.

    We are not ORACLE do you remember it ? :)
 
> c) It should increase backup performance SIGNIFICANTLY.

    It is overstated i'd said
 
> d) If you really want to muck about with the data, restore for a test 
> system, whatever, and you do not want to incur the space overhead, all you 
> have to do for the test system / whatever is to switch on the option to 
> suppress the growth factors.
> 
> Other things to bear in mind:
> 
> Obviously, your performance will suffer if you have lots of back 
> versions/garbage all over the place etc, but this is true, even if we don't 
> use an intelligent growth scheme. 

    Agree

> (Perhaps this should be looked at as well, 
> so that there is initially an attempt to store old backversions on the page 
> directly after the one with the primary record on it, or something like 
> that?)

    I don't know such attempts. 
 
> To keep the scheme clean, ultimately it makes sense to take pages which have 
> become empty by deleting records from a table, and NOT make them generally 
> accessible. Reserve them for the exclusive use of the table they originally 
> came from, so that we don't end up refragmenting stuff that was already 
> defragmented by initial placement, on mass deletion. (Yes, I am quite happy 
> to waste 1GB of table space after deleting 5 million records, because:
>    a) On backup, to read 1GB of empty table space will only take 2 seconds, 
> with decent hardware (because it is sequentially allocated, of course).
>    b) I also know that pretty soon, due to the nature of the app, that 
> within 6 hours, the 5 million records will be back, and the space will no 
> longer be 'wasted'.
> )

    Without way to manage it we must not do it. One user know it will reuse space, 
second know it will not reuse space and third know nothing about it.

Regards,
Vlad

Vlad,

> > > How about a new value in the database header, to note the last
'real'
> db
> > > page?
> > >
> > This is what we want, IMO.
>=20
>     This will lead to big amount of additional writes to header page
and
> will slow
> whole process as we must move HDD heads from header page to the last
page
> every time we have allocate new page

Isn't this effectively happening anyways, since the header needs to be
updated with the latest transaction number???


> > And how about shrink the database, based on this value, when user
change
> > it to read-only mode?
>=20
>     It will be possible doing nbackup level 0 if we'll teach nbackup
to
> ignore allocated but not used space

It will be necessary to teach nbackup to handle/igonre the unused db
space for all backup levels, not just level 0, since level > 0 reads all
the db pages and checks the page header to see if the page was updated
since the last backup.


Sean

Hi, Vlad.

>> Reading through this thread, it seems to me that the basic design idea of
>> having a database / system wide configuration parameter solves, or can 
>> solve
>> the problem of OS disk fragmentation.
>
>    Perhaps
>
>> However, it looks to me like this is the wrong problem to solve.
>
>    Why ? Anyway - if we can solve it by the cheap way - why not ?
Read the performance numbers below, and I'm sure it will become a lot 
clearer.

>> As far as I
>> can tell, the whole point of doing things like allocating large blocks is 
>> to
>> help performance of the database system - this is a system as a whole, 
>> NOT
>> just the way the O/S allocates pages off a disk / disks.
>>
>> In order to do this in a more useful manner, what I propose is as 
>> follows:
>>
>> 1) A parameter PER TABLE, which specifies the growth factor (KB/MB/No of
>> pages/Whatever, I don't really care how). You can access this with an 
>> ALTER
>> TABLE <whatever syntax you like>, to change the setting. Default would be
>> current behaviour of single page at a time, if not otherwise specified.
>
>    We want to avoid additional parameters as much as possible (we are 
> Firebird,
> not ORACLE and want to stay 'self-tune' ;).
I absolutely agree. If you read my post a bit more carefully, you will 
notice that the default behaviour (ie: You, as a SYSDBA, have NOT used the 
"ALTER TABLE MY_BIG_TABLE SET AUTOEXPANDPAGECOUNT 1234" <or whatever other 
syntax you like>), is exactly the way it is today. If you want to call that 
'self-tuning', then that's fine.

On the topic of ORACLE, while I agree 100% that there is a HUGE amount of 
stuff in ORACLE that has no value to us, either because it is there for 
historical purposes, or simply does not apply due to architectural 
differences, it does not mean that there is absolutely nothing to learn from 
ORACLE, or any other system. The requirement is to take things on their own 
merit, and use what is useful, and learn from other ideas, regardless.

Yes, self-tuning is a neat goal, and if you really want to do this, then 
that's great. However, I have no doubt that if I had submitted an initial 
proposal that stated that the requirement was to have an automatic tuning 
system for this, the post would have been regarded as too much work. Let's 
get something reasonably simple working, and later on you can do the 
'correct' self-tuning, if you like, by expanding the statement to allow 
something like "ALTER TABLE MY_BIG_TABLE SET AUTOEXPANDPAGECOUNT 
AUTOCALCULATE", or whatever. By all means. Heck, change this to default 
behaviour, if you like, later on. :)

>> 2) A database specific configuration parameter, TO SUPPRESS use of the
>> growth factors.
>
>    This is more appropriate (as for me)
>
>> 3) On a database restore, you DO NOT USE these values. (this solves the 
>> 'I
>> have a readonly database I want to distribute, and I want it to be small'
>> problem).
>
>    This is wrong way. If parameters present it must work.
> My personal opinion of course
Remember, I just proposed this as a way to solve the already debated issue 
of 'I have a readonly database, and I want it small' problem. I certainly 
won't be using it, and I don't think that many people who actually do have 
tables with more than 50 million rows will either. People with small tables 
won't even know about the feature in the first place, and therefore their 
potential use of the parameter is irrelevant anyway.

>
>> All they have to do is backup the system, and restore, then
>> distribute the restored version as the 'readonly' system, with no extra
>> junk. After a restore, the tables will already be grouped together, with 
>> a
>> single table's data in consecutive pages anyway (ok, excluding a page 
>> here
>> and there, for housekeeping).
>>
>> The reasons that I think that this is the way to go are:
>>
>> a) I feel that it is rather pointless dealing with the O/S disk
>> fragmentation issues, and ignore the table fragmentation WITHIN the
>> allocated O/S file/files.
>
>    Why do you think we'll ignore table fragmentation ? Because i still not
> post all RFC's i have to post ? :)
Nope... I have no idea what you think about - all I can use is what is 
public info :)
A quick search of ib-architect, firebird-devel, and firebird-doc for the 
phrase 'table fragmentation' produced 2 hits in total - my original post, 
and your response. :)

>> An extremely common operation is database backup,
>> for example. What does it do? Reads each TABLE, sequentially. It is very
>> inexpensive to buy RAID systems that are capable of reading / writing
>> 500-800MB/S, sustained, for sequential reads/writes, but you only get 
>> this
>> performance level if BOTH the O/S file fragmentation AND the table
>> fragmentation within the O/S file/files is minimised. For large tables, 
>> this
>> is critical.
>
>    Agree
Great.

>> b) It allows you to base your decision on real expected growth factors of
>> each table.
>
>    We are not ORACLE do you remember it ? :)
Yes, I do. But please remember that there is a tremendous amount of focus on 
multithreading, tls efficiencies, and about 100 other performance related 
things in firebird, which are not really required if you want to write an 
application to handle 'Shopping Lists'. :)
Firebird is growing up, bit by bit, and the more it grows up, the more 
'Enterprise' type features that will be required to sustain largeish 
systems. By all means, make the defaults do what they do today, but give 
people who actually have non-trivial sized systems the ability to use 
Firebird too.

>> c) It should increase backup performance SIGNIFICANTLY.
>    It is overstated i'd said
OK. I have a REAL system. I pulled all the clients off the system. Ran a 
garbage collection. Twice. After the first time, gstat told me that the 
transactions were right next to each other. Oldest to next. So I ran it 
again, just to make 100% sure there was no cruft lying about. NO GARBAGE.
Did a backup. 39 Minutes, 13 seconds.
Did a restore.
Did a backup of the just restored database. 11 minutes, 2 seconds.
Also note that there is just about ZERO O/S file fragmentation for these 
tests. At the time, the tests were done on A RAID10 volume, consisting of 22 
Physical disks, with ONLY 2 files on the entire RAID volume - The original 
database, and the restored one. (Also note that the restored database of 
course only existed after the original was 'as full as it was going to get', 
for this test).

Significant is an understatement, not an overstatement.

>> d) If you really want to muck about with the data, restore for a test
>> system, whatever, and you do not want to incur the space overhead, all 
>> you
>> have to do for the test system / whatever is to switch on the option to
>> suppress the growth factors.
>>
>> Other things to bear in mind:
>>
>> Obviously, your performance will suffer if you have lots of back
>> versions/garbage all over the place etc, but this is true, even if we 
>> don't
>> use an intelligent growth scheme.
>
>    Agree
>
>> (Perhaps this should be looked at as well,
>> so that there is initially an attempt to store old backversions on the 
>> page
>> directly after the one with the primary record on it, or something like
>> that?)
>
>    I don't know such attempts.
Nor do I. That's why I brought it up. So people can chew it over, and see 
what they think. This fits squarely into the 'auto-tune' concept, if you ask 
me. :)  It's a hell of a lot more efficient to read the next consecutive 
file block to get your back version than it is to read something 100 miles 
away - most O/S do a certain amount of read-ahead anyway, and the block is 
probably already knocking about in the O/S cache, due to this.

>
>> To keep the scheme clean, ultimately it makes sense to take pages which 
>> have
>> become empty by deleting records from a table, and NOT make them 
>> generally
>> accessible. Reserve them for the exclusive use of the table they 
>> originally
>> came from, so that we don't end up refragmenting stuff that was already
>> defragmented by initial placement, on mass deletion. (Yes, I am quite 
>> happy
>> to waste 1GB of table space after deleting 5 million records, because:
>>    a) On backup, to read 1GB of empty table space will only take 2 
>> seconds,
>> with decent hardware (because it is sequentially allocated, of course).
>>    b) I also know that pretty soon, due to the nature of the app, that
>> within 6 hours, the 5 million records will be back, and the space will no
>> longer be 'wasted'.
>> )
>
>    Without way to manage it we must not do it. One user know it will reuse 
> space,
> second know it will not reuse space and third know nothing about it.
Ummm... not sure quite what you mean here. If you are suggesting that we 
need a way to see whether or not a table has a magic automatic growth factor 
attached to it, I agree with you.
The way to manage it is above. If you have already issued a
"ALTER TABLE MY_BIG_TABLE SET AUTOEXPANDPAGECOUNT 1234" <or whatever other 
syntax you like> command and change your mind, then just issue a
"ALTER TABLE MY_BIG_TABLE SET AUTOEXPANDPAGECOUNT 1", and you're back to 
where you were before.
I can't see the problem.
It's clean, simple to understand, simple to document and if you don't know 
about the feature, It won't hurt you. Default is to do what we've been doing 
all along.

Kind regards
Larry

> > > > How about a new value in the database header, to note the last
> 'real'
> > db
> > > > page?
> > > >
> > > This is what we want, IMO.
> > 
> >     This will lead to big amount of additional writes to header page
> and
> > will slow
> > whole process as we must move HDD heads from header page to the last
> page
> > every time we have allocate new page
> 
> Isn't this effectively happening anyways, since the header needs to be
> updated with the latest transaction number???

    What if transaction insert 1000 records on 100 pages ? ;) Think
about restore - it insert all user data in the same transaction.

> > > And how about shrink the database, based on this value, when user
> change
> > > it to read-only mode?
> > 
> >     It will be possible doing nbackup level 0 if we'll teach nbackup
> to
> > ignore allocated but not used space
> 
> It will be necessary to teach nbackup to handle/igonre the unused db
> space for all backup levels, not just level 0, since level > 0 reads all
> the db pages and checks the page header to see if the page was updated
> since the last backup.

    Of course

    nbackup level 0 creates a copy of database therefore i said about
this level when Adriano asked about shrink.

Regards,
Vlad

Hi, all

Do you remember this RFC ? :)

>     Below i propose two change in a way engine worked with disk files
> 
> 1. Look-ahead disk space allocation
> 
>     To avoid problems with page buffers allocated in in-memory cache
> but not on disk i propose to grab enough disk space before any attempt 
> to use pages not allocated on disk. 
> 
>     Only one thread (process) can extend file to avoid conflicts. 
> 
>     On Windows use SetFileValidData API call on those platforms where it is 
> present (>= WinXP) to avoid very slow synchronous filling of a new place 
> with zeros.
> 
>     To make process more efficient i offer to allocate space for several pages at 
> a time. Low and high number of batch of allocated pages limited by hard-coded
> constants (i propose 128KB and 128MB but this is can be discussed of course).
> Engine take 1/16 part of already allocated space and adjust this number against
> low and high limits above (1/16 also can be discussed). If look-ahead allocation 
> failed engine divided requested number of pages by factor of 2 and repeat 
> process until success or requested number more than zero. If request to allocate 
> one page is failed too error is raised (also we can log it into firebird.log)
> 
>     Constants (128KB, 128MB and 1/16 part of already allocated space) are chosen 
> so to not grow small database too fast from the one side and to allocate enough 
> number of pages to not extend file too often and reduce disk fragmentation from 
> the other side.

    This part is reviewed by project leader and ready to be committed. After our first
conversation i made following changes :

a) Engine tracked really used pages (i.e. allocated by means of Firebird). To do it
    efficiently i use one reserved long on pointer page (this is also allow to not introduce
    new major ODS number). Nbackup also know about it

b) When allocating new page engine write it to disk immediately (if this page was never
    allocated before). This allows to detect out of disk space condition early and handle
    this error gracefully. Also this make file growth sequentially and improve performance
    on bulk inserts when database grow actively

c) Due to (b) i don't need SetFileValidData anymore

d) As was strongly proposed i introduce new configuration setting to control preallocation 
    of database files. This setting allow to set upper bound value for number of bytes
    preallocated and have default value of 128MB (was hardcoded in initial proposition).
    To disable disk space preallocation user can set it to zero

e) If "no reserve" flag is set in database header then no preallocation is performed

    Only thing i want to ask for is the name of new setting (as most hard part of whole work ;). 
I propose "MaxDatabaseFileGrowth"

    Opinions ?

Regards,
Vlad

Vlad Horsun wrote:
>
> I propose "MaxDatabaseFileGrowth"

MaxDatabaseExtentSize


Dmitry

Dmitry,

> Vlad Horsun wrote:
> >
> > I propose "MaxDatabaseFileGrowth"
>=20
> MaxDatabaseExtentSize

This is term is too "technical" (i.e. obscure).

I would propose "DatabaseGrowthIncrement":

- "File" is redundant
- "Max...Growth" can be interpreted to mean max file size (by
non-english users)
- "Increment" specifies that the value applies only when growth is
required.


Sean

> I propose "MaxDatabaseFileGrowth"
> 

MaxFileGrowth

(like MS SQLs FILEGROWTH)

-Laco.

On Wednesday 25 April 2007 20:41, Leyne, Sean wrote:
> Dmitry,
>
> > Vlad Horsun wrote:
> > > I propose "MaxDatabaseFileGrowth"
> >
> > MaxDatabaseExtentSize
>
> This is term is too "technical" (i.e. obscure).
>
> I would propose "DatabaseGrowthIncrement":
>
> - "File" is redundant
> - "Max...Growth" can be interpreted to mean max file size (by
> non-english users)
> - "Increment" specifies that the value applies only when growth is
> required.

Additional desire - do not specify it in bytes, suggesting users to refer to 
table in the end of firebird.conf. Let it be set in Mb. 
And what about setting it in %% of existing DB size? I.e.

DatabaseGrowthIncrement = 20%

and you treat this parameter as string, not numeric, and interpret '%' in the 
end?

At 05:11 PM 26/04/2007, Alex wrote:

>Additional desire - do not specify it in bytes, suggesting users to refer to
>table in the end of firebird.conf. Let it be set in Mb.
>And what about setting it in %% of existing DB size? I.e.
>
>DatabaseGrowthIncrement = 20%
>
>and you treat this parameter as string, not numeric, and interpret '%' in the
>end?

Since the allocation will always be in full pages, it would make the 
most sense to specify the MaxGrowthChunk in pages and make it an integer.

Helen

Alex Peshkov wrote:
> 
> Additional desire - do not specify it in bytes, suggesting users to refer to 
> table in the end of firebird.conf. Let it be set in Mb.

All our configuration numbers are in bytes. It would be good to 
implement suffixes {K,M,G}, but it's for another day, I suppose.

> And what about setting it in %% of existing DB size? I.e.
> 
> DatabaseGrowthIncrement = 20%

I doubt anyone wants to always track the current database size and 
recalculate the extent size to be sure he has enough disk space for the 
next increment.


Dmitry

On Thursday 26 April 2007 11:20, Helen Borrie wrote:
> At 05:11 PM 26/04/2007, Alex wrote:
> >Additional desire - do not specify it in bytes, suggesting users to refer
> > to table in the end of firebird.conf. Let it be set in Mb.
> >And what about setting it in %% of existing DB size? I.e.
> >
> >DatabaseGrowthIncrement = 20%
> >
> >and you treat this parameter as string, not numeric, and interpret '%' in
> > the end?
>
> Since the allocation will always be in full pages, it would make the
> most sense to specify the MaxGrowthChunk in pages and make it an integer.

This is parameter of all server, not per database. Different databases may 
have different page size, and do we want them to grow with different visible 
increment? With size in Mb, it will always contain integer number of pages 
inside. With % - certainly, all calculations will be done in number of pages, 
not bytes.

> > Additional desire - do not specify it in bytes, suggesting users to
refer to
> > table in the end of firebird.conf. Let it be set in Mb.
>
> All our configuration numbers are in bytes. It would be good to
> implement suffixes {K,M,G}, but it's for another day, I suppose.
>
> > And what about setting it in %% of existing DB size? I.e.
> >
> > DatabaseGrowthIncrement = 20%
>
> I doubt anyone wants to always track the current database size and
> recalculate the extent size to be sure he has enough disk space for the
> next increment.

However, it is a rule-of-thumb that you always increase by a percentage
of currently allocated :-)

Ask any Oracle DBA and they would say increase by 50% of current
last increment.

Isn't that how memory allocators work as well?

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> On Wednesday 25 April 2007 20:41, Leyne, Sean wrote:

> Additional desire - do not specify it in bytes, suggesting users to refer to 
> table in the end of firebird.conf. Let it be set in Mb. 

    All other settings is in bytes. If we start support suffixes for numbers (K, M, G)
in config it would be great ;) But this is another task, is not is ?

> And what about setting it in %% of existing DB size? I.e.
> DatabaseGrowthIncrement = 20%

    Engine get base number as 1\16 of currently allocated pages and adjust it 
to be >= 128KB and <= DatabaseGrowthIncrement
 
> and you treat this parameter as string, not numeric, and interpret '%' in the 
> end?

    It is already numeric. In bytes, not in percents

Regards,
Vlad

On Thursday 26 April 2007 11:29, Dmitry Yemanov wrote:
> Alex Peshkov wrote:
> > Additional desire - do not specify it in bytes, suggesting users to refer
> > to table in the end of firebird.conf. Let it be set in Mb.
>
> All our configuration numbers are in bytes. It would be good to
> implement suffixes {K,M,G}, but it's for another day, I suppose.
>

What do we plan to do if users sets
10000000 //got tired myself counting zeros to get around 10M
which is obviously not a multiple of page size?

> > And what about setting it in %% of existing DB size? I.e.
> >
> > DatabaseGrowthIncrement = 20%
>
> I doubt anyone wants to always track the current database size and
> recalculate the extent size to be sure he has enough disk space for the
> next increment.

I hope that's not needed. In case of not enough space for increment we should 
increase size as much as possible, log error - if there is disk space for 
it:) - and continue operation.

> Since the allocation will always be in full pages, it would make the 
> most sense to specify the MaxGrowthChunk in pages and make it an integer.

    I like "MaxGrowthChunk" name. 

    I already explained why it must be in bytes but pages : when we look at available 
disk space and\or on file size we always see bytes, not pages. The same about number 
of cache buffers - it is a headache to calculate how many pages fits in, say, 50% of RAM
and not forget to change it when page size changed.

    I'd say that "natural" measure units is more friendly to end user than pages. This is just a 
my opinion of course

Regards,
Vlad

PS if we introduce suffixes for numbers we can have both ways to set values : 
    KB (for kilobytes), MB (for megabytes), GB (for gigabytes), PG (for pages)

On Thursday 26 April 2007 11:33, Vlad Horsun wrote:
>     Engine get base number as 1\16 of currently allocated pages and adjust
> it to be >= 128KB and <= DatabaseGrowthIncrement
>
In that case I see no problems. But parameter must not be called 
DatabaseGrowthIncrement with such behavior.

>     It is already numeric. In bytes, not in percents

If everyone besides me want to continue teaching users to count zeros in long 
numbers (or multiply 1024 * 1024 many times), I give up.

> On Thursday 26 April 2007 11:29, Dmitry Yemanov wrote:
> > Alex Peshkov wrote:
> > > Additional desire - do not specify it in bytes, suggesting users to refer
> > > to table in the end of firebird.conf. Let it be set in Mb.
> >
> > All our configuration numbers are in bytes. It would be good to
> > implement suffixes {K,M,G}, but it's for another day, I suppose.
> >
> 
> What do we plan to do if users sets
> 10000000 //got tired myself counting zeros to get around 10M
> which is obviously not a multiple of page size?

    Of course we extend database in whole page chunks ;)
 
> > > And what about setting it in %% of existing DB size? I.e.
> > >
> > > DatabaseGrowthIncrement = 20%
> >
> > I doubt anyone wants to always track the current database size and
> > recalculate the extent size to be sure he has enough disk space for the
> > next increment.
> 
> I hope that's not needed. In case of not enough space for increment we should 
> increase size as much as possible, log error - if there is disk space for 
> it:) - and continue operation.

    And this is how it works now. 

    About percents growth. Imagine 10GB database. 10% is 1GB. Why do you need
such large extend chunk ? What problem it can solve which can't be solved by
lower chunk ? If database have big size it not means it always will grow very fast.
Therefore i limited maximum growth chunk value.

Regards,
Vlad

> On Thursday 26 April 2007 11:33, Vlad Horsun wrote:
> >     Engine get base number as 1\16 of currently allocated pages and adjust
> > it to be >= 128KB and <= DatabaseGrowthIncrement
> >
> In that case I see no problems. But parameter must not be called 
> DatabaseGrowthIncrement with such behavior.

    Your proposition ? ;)

> >     It is already numeric. In bytes, not in percents
> 
> If everyone besides me want to continue teaching users to count zeros in long 
> numbers (or multiply 1024 * 1024 many times), I give up.

    They just accept default value i think ;)

Regards,
Vlad

On Thursday 26 April 2007 11:59, Vlad Horsun wrote:
> > On Thursday 26 April 2007 11:33, Vlad Horsun wrote:
> > >     Engine get base number as 1\16 of currently allocated pages and
> > > adjust it to be >= 128KB and <= DatabaseGrowthIncrement
> >
> > In that case I see no problems. But parameter must not be called
> > DatabaseGrowthIncrement with such behavior.
>
>     Your proposition ? ;)
>

Sorry, do not have better idea except
DatabaseIncrementLimit

And certainly, text about 1/16 and so on in firebird.conf :)

>     I like "MaxGrowthChunk" name.
>
>     I already explained why it must be in bytes but pages : when we  
> look at available
> disk space and\or on file size we always see bytes, not pages. The  
> same about number
> of cache buffers - it is a headache to calculate how many pages  
> fits in, say, 50% of RAM
> and not forget to change it when page size changed.


Do you really need a parameter?
Can't this be made "deploy & relax"?

Let the engine decide for itself by how much and when it should re- 
extent based on past extensions size and frequency?  An increase by  
10% of the file needing a new allocation looks like a good default /  
base pattern which could be dynamically reduced or increased  
depending on the ratio of the file-size / over free-space on the  
volume and the frequency and size of, let's say, last 3 extensions?

If for very specific cases, someone wants to pre-allocate a file to 1  
GB right now from the beginning, gfix could probably be extended for  
this?  On Windows NT 4 and up there are low-level APIs which could  
even be used in these ports to let gfix not only preallocate a  
database file to a large size but also get that space as a non- 
fragmented space.

-- 
Olivier Mascia

> >     I like "MaxGrowthChunk" name.
> >
> >     I already explained why it must be in bytes but pages : when we  
> > look at available
> > disk space and\or on file size we always see bytes, not pages. The  
> > same about number
> > of cache buffers - it is a headache to calculate how many pages  
> > fits in, say, 50% of RAM
> > and not forget to change it when page size changed.
> 
> 
> Do you really need a parameter?
> Can't this be made "deploy & relax"?

    This is how i made it initially - there was no parameters in my first version

    The reason to have parameter was requirement to have ability to disable 
pre-allocation if it is not needed. Hmm, may be we must implement exactly
this parameter ? I.e. enable\disable pre-allocation. And let the engine choose 
other details ? It seems for me as good compromise. Sean, what do you think ?

> Let the engine decide for itself by how much and when it should re- 
> extent based on past extensions size and frequency?  An increase by  
> 10% of the file needing a new allocation looks like a good default /  
> base pattern which could be dynamically reduced or increased  
> depending on the ratio of the file-size / over free-space on the  
> volume and the frequency and size of, let's say, last 3 extensions?

    This is some more complex than i ready to discuss ;) What problem
this dynamic algoritm going to solve ?
 
> If for very specific cases, someone wants to pre-allocate a file to 1  
> GB right now from the beginning, gfix could probably be extended for  
> this?  On Windows NT 4 and up there are low-level APIs which could  
> even be used in these ports to let gfix not only preallocate a  
> database file to a large size but also get that space as a non- 
> fragmented space.

    ALTER DATABASE ALLOCATE xxx SPACE ? I like it...

    I don't know about such API and not sure we really need it. We can 
discuss if it needed of course.

Regards,
Vlad

Hi,

>> Can't this be made "deploy & relax"?
> 
>    This is how i made it initially - there was no parameters in my first version
> 
>    The reason to have parameter was requirement to have ability to disable 
> pre-allocation if it is not needed. Hmm, may be we must implement exactly
> this parameter ? I.e. enable\disable pre-allocation. And let the engine choose 
> other details ? It seems for me as good compromise. Sean, what do you think ?

If still a config-parameter is needed then this fits probably better:

MaximumFilePreAllocationSize
UseFilePreAllocation

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
  http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
  http://www.firebirdsql.org
  http://www.firebirdsql.info

Support list for Interbase and Firebird users:
  firebird-support@...

Nederlandse firebird nieuwsgroep:
  news://newsgroups.firebirdsql.info

Le 26-avr.-07 =E0 12:40, Vlad Horsun a =E9crit :

>    This is some more complex than i ready to discuss ;) What problem
> this dynamic algoritm going to solve ?

Hmm, err, no specific problem actually.  Preallocating by some large =20
chunk may help keeping fragmentation level low and disk i/o higher =20
(on some kind of systems at least).  Really if my DB is 1,2 GB large =20
today and I have 20 GB free on my volume I don't care if the engine =20
allocates by chunks of 512 MB or by page of 8 KB: I just like it to =20
allocate as it sees fit my case.  But that is certainly not required =20
too.

An option to simply turn on/off your feature (however you tune it) =20
looks more than adequate to me.


>     I don't know about such API and not sure we really need it. We can
> discuss if it needed of course.

You have to dig in the DDK (not the SDK) to get access to a whole =20
world of really interesting APIs of the NT-based Windows systems.  =20
Only a slim portion of it is remapped/renamed and exposed at the =20
Win32 level.  But Win32 is only one facet of the beast, =20
actually. ;-)  Now I must admit that the ability for Firebird to self =20=

defragment its own files on NT-based systems is NOT a top-requested =20
feature.  So it is _not needed_.  I'm not sure many even knew it =20
could be feasible with the help of some low-level APIs of the =20
system.  So this will live happily in the refrigerator for now. ;-)

--=20
Olivier Mascia

Hi,

I'm really sorry that final steps of this RFC escaped my attention, so I 
stepped on it indirectly working on QA. We really need a wiki to handle 
RFC's and related discussions, as important stuff may get buried in this 
list very easily. As it already makes its way into the code and Beta 1 
is around the corner, I hope it's not too late to make you reconsider 
this RFC for 2.1 release.

While I fully agree with the first part of the RFC dealing with graceful 
handling of "out of disk space" condition, I have strong objections 
about bulk database extensions/allocation. To put it mildly, I think 
it's worst implementation of bad idea that ever made it into Firebird 
code base. Sorry that I went all Jim here, but it really turned me 
inside out when I saw the final result that IMO solves nothing but adds 
a stockpile of problems.

First, what it's supposed to solve:

1. Database file fragmentation. This is not an issue as most users don't 
care anyway (small and personal users), and those who care can avoid it 
with dedicated disk partitions and proper maintenance discipline without 
this feature. In fact, as it's designed now, it will not solve the 
problem at all, it just *may* improve the situation (it depends on too 
much factors outside our control), hence it would give a false sense of 
real solution to end users ("hurray! I don't need to care about this 
anymore"). So in relation to fragmentation issue, it's either a solution 
that looks for a problem or badly designed solution.

2. It's questionable whether there is any *significant* difference in 
efficiency in one page at a time vs. larger chunk allocations that would 
justify the trouble to implement it in such general way. Certainly there 
could be some gain for systems with frequent mass inserts, but a) number 
of pages preallocated should match the immediate need as closely as 
possible, otherwise the *average* gain would be minimal b) the absolute 
value of this gain also depends on used HW and OS/file system, and in 
fact this issue could be at least partially solved this way c) this use 
pattern is typical for only a tiny fraction of all systems used out 
there. It could be nice to allow bulk page preallocation for those who 
would really need it (or think that they need it), but it still not 
justifies its implementation as generally used feature turned on by default.

Now about the current implementation:

1. While it handles nbackup, it doesn't handle shadows at all. Well, 
almost nobody uses shadows today, but it's still a sign of half-baked 
solution that makes one to wonder what other unhandled traps may lurk 
out there ready to bite you. For example, does anyone considered/checked 
how this feature plays and fits with multi-file databases?

2. Automatic bulk preallocation with variable chunks makes maintenance 
harder. Database size jumps up in increasing leaps that make "out of 
space" condition monitoring problematic. It hides the grow pattern and 
keeps it out of sights from DBA and automated monitoring tools. Current 
situation when one has not any good tools to tell how much free space 
really is in database gives enough trouble for busy systems to predict 
the pace of database growth, but bulk allocation makes it even worse. 
Introduction of this feature should be accompanied with tools 
(gfix/gstat/API extension, whatever) to see the real space usage in 
database as a countermeasure.

3. Configuration is simply awkward. It's per server only, but it should 
be also per database (with per server default). It uses bytes, which is 
unnatural for any common user in this case (we really need to implement 
suffixes). It's on by default, while it IMHO should be off by default.


If you really think that Firebird needs this feature, then I think that 
best implementation would allow only manual extension via DDL SQL (ALTER 
DATABASE ADD PAGES or whatever). That would give users more control over 
it and hence empower them to really *solve* their problem, while it 
would still allow automatic preallocations performed by external 
monitoring tools (even provided by us), user applications before large 
inserts etc. It could be easily extended in future with 
table/data/whatever placement along the same lines. It would also solve 
the problem of extent size configuration (user dicedes on case by case 
basis or tool dicedes on simpler, more complex or just different 
configuration and calculation, you name it) and unwanted "out of disk 
space" situations (people know how much space they have and want to give 
out for this). And as a bonus, it would keep the logic embedded into 
engine clean and simple.

best regards
Pavel Cisar
IBPhoenix

Hi, Pavel!
It's really a pity that you did not take part in this RFC earlier, cause all 
solutions you do not like were discussed publically. And everybody was in 
agreement that they are OK.

On Saturday 16 June 2007 22:53, Pavel Cisar wrote:
> Hi,
>
> I'm really sorry that final steps of this RFC escaped my attention, so I
> stepped on it indirectly working on QA. We really need a wiki to handle
> RFC's and related discussions, as important stuff may get buried in this
> list very easily. As it already makes its way into the code and Beta 1
> is around the corner, I hope it's not too late to make you reconsider
> this RFC for 2.1 release.
>
> While I fully agree with the first part of the RFC dealing with graceful
> handling of "out of disk space" condition, I have strong objections
> about bulk database extensions/allocation. To put it mildly, I think
> it's worst implementation of bad idea that ever made it into Firebird
> code base. Sorry that I went all Jim here, but it really turned me
> inside out when I saw the final result that IMO solves nothing but adds
> a stockpile of problems.
>
> First, what it's supposed to solve:
>
> 1. Database file fragmentation. This is not an issue as most users don't
> care anyway (small and personal users), and those who care can avoid it
> with dedicated disk partitions and proper maintenance discipline without
> this feature. In fact, as it's designed now, it will not solve the
> problem at all, it just *may* improve the situation (it depends on too
> much factors outside our control), hence it would give a false sense of
> real solution to end users ("hurray! I don't need to care about this
> anymore"). So in relation to fragmentation issue, it's either a solution
> that looks for a problem or badly designed solution.
>

Certainly, in case when database is located on the partition on which a lot of 
writes happen simultaneously, it will not improve anything - will work as bad 
as it was. On dedicated partition it will not improve something too - will 
work as good as it was. But on partition where a number of files 
grow "sometimes" (i.e. 10 database are located on it or even database and 
office data storage together), it will improve situation. Therefore if some 
feature makes life better in some cases and does nothing bad in another - why 
not use it? 
I did not analyze problems of disk fragmentation deep enough, but as for RAM 
fragmentation I know very well that allocating space in smaller number of big 
chunks reduces fragmentation. And as far as I've read, I never new that on 
disk behavior is not such. What about false sense of real solution - this 
sooner depends upon how is it described in documentation. Certainly, we 
should not write in it - hey, we solved your fragmentation issues :)

> 2. It's questionable whether there is any *significant* difference in
> efficiency in one page at a time vs. larger chunk allocations that would
> justify the trouble to implement it in such general way. Certainly there
> could be some gain for systems with frequent mass inserts, 

For systems which do not do mass inserts (BTW, mass update will give almost 
same effect - many new versions need disk space), speed of disk allocation is 
certainly not a problem. But I suppose we should think about others, that do 
such inserts/updates.

> but a) number 
> of pages preallocated should match the immediate need as closely as
> possible, otherwise the *average* gain would be minimal 

??????

> b) the absolute 
> value of this gain also depends on used HW and OS/file system, and in
> fact this issue could be at least partially solved this way 

I've made tests for linux. Take into account, that linux is very bad OS for 
implementation of this feature - it does not support on kernel level 
preallocation of file space w/o writing to that file. No matter of it I've 
got 10% faster results when doing mass insert compared with this feature 
turned off. Suppose on windows (or any other OS, supporting file 
preallocation) difference should be bigger.

> c) this use 
> pattern is typical for only a tiny fraction of all systems used out
> there. It could be nice to allow bulk page preallocation for those who
> would really need it (or think that they need it), but it still not
> justifies its implementation as generally used feature turned on by
> default.
>

Please specify why this feature can't be used as general. We discussed it here 
and accepted solution was that it's OK in almost all cases. May be we missed 
something?

> Now about the current implementation:
>
> 1. While it handles nbackup, it doesn't handle shadows at all. Well,
> almost nobody uses shadows today, but it's still a sign of half-baked
> solution that makes one to wonder what other unhandled traps may lurk
> out there ready to bite you. For example, does anyone considered/checked
> how this feature plays and fits with multi-file databases?

Agreed, should be fixed and tested. It will be very bad to have wrong shadow 
in case of main storage falure.

> 2. Automatic bulk preallocation with variable chunks makes maintenance
> harder. Database size jumps up in increasing leaps that make "out of
> space" condition monitoring problematic. 

Do you really want to say that currently enabled (by default) maximum jump of 
128Mb is something extraordinary with today 160Gb disks?

> It hides the grow pattern and 
> keeps it out of sights from DBA and automated monitoring tools. Current
> situation when one has not any good tools to tell how much free space
> really is in database gives enough trouble for busy systems to predict
> the pace of database growth, but bulk allocation makes it even worse.
> Introduction of this feature should be accompanied with tools
> (gfix/gstat/API extension, whatever) to see the real space usage in
> database as a countermeasure.

Agreed here - moreover, it will be useful to know both number of busy pages 
and last busy page. In case of mass delete there may be free pages in the 
middle of database.

>
> 3. Configuration is simply awkward. It's per server only, but it should
> be also per database (with per server default). 

Once more - is 128Mb growth of 2Gb DB (not sure for exact digits, but order of 
magnitude is OK) is something such awful to make it really needed to have 
per-database configuration? Smaller databases grow slower than 128Mb, 
bigger - not faster (i.e. it's an absolute limit).

> It uses bytes, which is 
> unnatural for any common user in this case (we really need to implement
> suffixes). 

Yes, and for all other parameters (like TEMP dirs) too. But let's keep them in 
sync, OK?

> It's on by default, while it IMHO should be off by default. 

What goes wrong if it's on?

> If you really think that Firebird needs this feature, then I think that
> best implementation would allow only manual extension via DDL SQL (ALTER
> DATABASE ADD PAGES or whatever). That would give users more control over
> it and hence empower them to really *solve* their problem, while it
> would still allow automatic preallocations performed by external
> monitoring tools (even provided by us), user applications before large
> inserts etc. It could be easily extended in future with
> table/data/whatever placement along the same lines. It would also solve
> the problem of extent size configuration (user dicedes on case by case
> basis or tool dicedes on simpler, more complex or just different
> configuration and calculation, you name it) and unwanted "out of disk
> space" situations (people know how much space they have and want to give
> out for this). And as a bonus, it would keep the logic embedded into
> engine clean and simple.

This your suggestion will complicate database maintenance. Next, having a few 
database on same partition, it's good to have common space allocation rules 
for them, not different - different rules really increase fragmentation. And 
as a last argument not to have such DDL - it opens to any DB owner very 
simple way to perform DoS attack on a server - just type:
ALTER DATABASE ADD 1000000000000 PAGES
Hops - disk space is over.

Alex Peshkov wrote:
>
>> 2. It's questionable whether there is any *significant* difference in
>> efficiency in one page at a time vs. larger chunk allocations that would
>> justify the trouble to implement it in such general way. Certainly there
>> could be some gain for systems with frequent mass inserts, 
> 
> For systems which do not do mass inserts (BTW, mass update will give almost 
> same effect - many new versions need disk space), speed of disk allocation is 
> certainly not a problem. But I suppose we should think about others, that do 
> such inserts/updates.

A database restore is a good example of mass inserts happening for every 
user and database :-)


Dmitry

Alex Peshkov wrote:
> 
> Certainly, in case when database is located on the partition on which
> a lot of writes happen simultaneously, it will not improve anything -
> will work as bad as it was. On dedicated partition it will not
> improve something too - will work as good as it was. But on partition
> where a number of files grow "sometimes" (i.e. 10 database are
> located on it or even database and office data storage together), it
> will improve situation. Therefore if some feature makes life better
> in some cases and does nothing bad in another - why not use it?

As was pointed out earlier by someone else, trying to solve
fragmentation at file level without taking into account the
fragmentation inside the database is pointless. Ok, so you can improve
the file fragmentation in some cases, but that wouldn't buy you anything
when data inside the file are not continuous anyway, it may in fact make
the situation worse (depends on actual data location on disk).

>> 2. It's questionable whether there is any *significant* difference
>> in efficiency in one page at a time vs. larger chunk allocations
>> that would justify the trouble to implement it in such general way.
>> Certainly there could be some gain for systems with frequent mass
>> inserts,
> 
> For systems which do not do mass inserts (BTW, mass update will give
> almost same effect - many new versions need disk space), speed of
> disk allocation is certainly not a problem. But I suppose we should
> think about others, that do such inserts/updates.

1. There is a space reserved on each page to handle this, and thanks to 
GC, any prospective additional allocated extensions needed to cover this 
are later freed and reused (and increasing the level of internal 
fragmentation).

3. I'm not against providing preallocation to improve mass insert 
performance. I just don't think that this implementation is a good solution.

>> but a) number of pages preallocated should match the immediate need
>> as closely as possible, otherwise the *average* gain would be
>> minimal
> 
> ??????

1. The maximal gain is when page to be written is already preallocated. 
On the other hand, there is a penalty for single write, when you're 
about to write single page but have to allocate X additional pages. It's 
similar to cooperative GC problem, when someone is "penalized" for doing 
the cleanup for others, and individual insert performance may vary 
greatly. You have to go down to *average* gain that will equal to 
performance difference between X * single page allocation vs. X page 
allocation at once.

2. Automatic preallocation doesn't match real pattern how database is 
extended, so you can't improve the efficiency of time critical operation 
beyond average gain explained above. For example, you have a mass update 
every night when you'll update the central repository with data 
collected from external sources. You have only 30 minutes to do that, so 
your time per row insert performance is critical for you. You also know 
that daily batch is about 10MB of data. The best thing would be to 
preallocate this 10MB of space (or 70-100MB for a week) in advance. This 
implementation doesn't allow this. What's worse, it doesn't allow us to 
adapt it along the lines it's designed to such scenarios in future.

>> b) the absolute value of this gain also depends on used HW and
>> OS/file system, and in fact this issue could be at least partially
>> solved this way
> 
> I've made tests for linux. Take into account, that linux is very bad
> OS for implementation of this feature - it does not support on kernel
> level preallocation of file space w/o writing to that file. No matter
> of it I've got 10% faster results when doing mass insert compared
> with this feature turned off. Suppose on windows (or any other OS,
> supporting file preallocation) difference should be bigger.

I never said that there is no gain from preallocation for mass inserts, 
the opposite is true. I've said that it depends on too much factors 
outside our control, so the real gain in each case may vary greatly for 
such generic solution. Because the gist of it is outside our control, we 
should give control over preallocation to those who'll know these 
factors - users (or specialized tools).

This specific implementation is just an afterthought, a quick shot at 
the problem. It's not well designed solution that's trying to solve the 
maximum range of real world situations with minimal effort that will add 
a solid architectural foundation for future extensions and improvements. 
It's just a quick and dirty improvement to page allocation mechanics 
that will very likely complicate the future work, as it's defines hard 
to extend behavior and accompanied configuration to support in future.

>> c) this use pattern is typical for only a tiny fraction of all
>> systems used out there. It could be nice to allow bulk page
>> preallocation for those who would really need it (or think that
>> they need it), but it still not justifies its implementation as
>> generally used feature turned on by default.
> 
> Please specify why this feature can't be used as general. We
> discussed it here and accepted solution was that it's OK in almost
> all cases. May be we missed something?

As was pointed out earlier, this feature is important only for some 
users, and irrelevant for rest (Yes, it will not hurt them, but it 
wouldn't help much either, many don't even notice if their invoice is 
not inserted 0.1s faster.). Such features shouldn't be ON by default 
because it's the right line of thinking. Line of thinking you defend 
here leads to feature creep and code bloat at the end. More to that, at 
the high end deployments where this feature really matters it should 
need conscious action from DBA to make the most out of it. That's it, 
this is very different case than optimizer improvement.

>> 2. Automatic bulk preallocation with variable chunks makes
>> maintenance harder. Database size jumps up in increasing leaps that
>> make "out of space" condition monitoring problematic.
> 
> Do you really want to say that currently enabled (by default) maximum
> jump of 128Mb is something extraordinary with today 160Gb disks?

Fact that maximal jump size is small in comparison to maximal available 
space doesn't make it good, it just means it's not *critically* bad, but 
just bad.

>> 3. Configuration is simply awkward. It's per server only, but it
>> should be also per database (with per server default).
> 
> Once more - is 128Mb growth of 2Gb DB (not sure for exact digits, but
> order of magnitude is OK) is something such awful to make it really
> needed to have per-database configuration? Smaller databases grow
> slower than 128Mb, bigger - not faster (i.e. it's an absolute limit).

Because each database handled by the same server could have different 
grow patterns (size is not important, the pace of growth is) and be on 
different media with completely different space constraints?

>> It uses bytes, which is unnatural for any common user in this case
>> (we really need to implement suffixes).
> 
> Yes, and for all other parameters (like TEMP dirs) too. But let's
> keep them in sync, OK?

Yeah, I know and I hate it for ages. Now you're going to add another 
one. As I wrote, we need measure suffixes if you want to continue adding 
such kind of parameters.

>> It's on by default, while it IMHO should be off by default.
> 
> What goes wrong if it's on?

As I wrote earlier, basically nothing critical (it's not ASYNC WRITES), 
but it's simply wrong way of thinking to enable minority features by 
default.

>> If you really think that Firebird needs this feature, then I think
>> that best implementation would allow only manual extension via DDL
>> SQL (ALTER DATABASE ADD PAGES or whatever). That would give users
>> more control over it and hence empower them to really *solve* their
>> problem, while it would still allow automatic preallocations
>> performed by external monitoring tools (even provided by us), user
>> applications before large inserts etc. It could be easily extended
>> in future with table/data/whatever placement along the same lines.
>> It would also solve the problem of extent size configuration (user
>> dicedes on case by case basis or tool dicedes on simpler, more
>> complex or just different configuration and calculation, you name
>> it) and unwanted "out of disk space" situations (people know how
>> much space they have and want to give out for this). And as a
>> bonus, it would keep the logic embedded into engine clean and
>> simple.
> 
> This your suggestion will complicate database maintenance. Next,
> having a few database on same partition, it's good to have common
> space allocation rules for them, not different - different rules
> really increase fragmentation. 

Please explain to me why this implementation doesn't allow you to 
implement common space allocation rules? These rules are not enforced by 
db server, but that doesn't mean that they couldn't be defined. In fact, 
it does allow you to implement common space allocation rules for 
whatever combination of factors you like (which current implementation 
doesn't).

 > And as a last argument not to have
> such DDL - it opens to any DB owner very simple way to perform DoS
> attack on a server - just type: ALTER DATABASE ADD 1000000000000
> PAGES Hops - disk space is over.

Pure BS.

1. When you're able to pass through all defenses to execute this 
command, you can do the same by pure INSERT statements. Sure, this way 
is quicker, but that's all.

2. This will just allocate the space up to the limit. Of course, other 
applications that may want to use the space would have trouble, but it 
will not kill the database (in fact, database would work more happily 
than ever).

best regards
Pavel Cisar
IBPhoenix

Dmitry Yemanov wrote:
> 
> A database restore is a good example of mass inserts happening for every 
> user and database :-)

This is also one from common cases when amount of data to be inserted is 
well known in advance. By preallocate all pages needed at once would 
give the best possible performance out of it. Even rough guess on data 
would be better than generic bulk preallocation. For example gbak may 
store the number of pages used by db into backup file, so it can 
preallocate for example 1/2 or 2/3 of them at once on restore.


best regards
Pavel Cisar
IBPhoenix

Hi, i'm using Interbase/FB and following this list for some years and
i would like to write some words about this kind of discussion,
because this is a problem that is happening quite frequently:

When someone proposes a new feature and is motivated to implement it,
it's very usual to have 2 situations:

1- Everybody complains about the technical aspects of the feature, the
majority of people don't propose a alternative, and few people start
an endless discussion about how to make the feature 'perfect' in terms
of architecture, etc... At this time, what should be a simple feature
becomes a monster that would take some years of discussion to design
and implement and a complete reengeneering of the firebird codebase.
At this point, the proposer is not motivated anymore, because the
'monster' feature doesn't worth the trouble he will have or he is
simply not skilled enough or don't have time to do it, and the feature
is archived.
2 - The feature is acccepted without too much discussion, and after
the developer spend his spare time implementing it, and happily
announces or commit the feature, hi is attacked from all sides because
what he implemented is crap, was not discussed enought, violates the
architecture, etc..., etc..., etc...

I tend to agree that all implemented features need to be discussed at
exhaustion, and it's exactly what i do in my business, but i am paid
for that, and we need to remember that this is an open source
projetct, developed mainly by volunteers, and it's crucial to keep the
few serious developers motivated and  productive, and i see the exact
opposite is happening. I would really love that firebird was a perfect
database in terms of architecture and implementation, but if it means
that we will have to cut out so many good features because they didn't
raised the perfection, i'm inclined to think that's not a good
tradeoff for this project.

Finally, what i think this project is really missing is leadership. A
leader endorsed by the firebird community empowered enought to make or
to speed up a decision when there are no agreement between the
developers, for good and for bad. Off course i think our current
leader, Dmitry, is able to acomplish this task. We just need to agree
and entitle him with this power and respect his decisions. I know it
will sound quite bold, but we need, in other words, a kind of 'boss',
like many other open source projects needs also.


PS: By the way, if i remember right, the look-ahead disk space
allocation was implemented to avoid the case when the database grows
in memory more than the available disk space, and don't have space to
save this information, causing coruption. This problem happened once
to me, and i am pretty sure that it's much better to have the current
implementation, even if it's not perfect, than to have the troubles i
and many others had to restore a corrupted database.

Regards,

Fabiano.

Fabiano,

Fabiano Bonin wrote:
> 
> PS: By the way, if i remember right, the look-ahead disk space
> allocation was implemented to avoid the case when the database grows
> in memory more than the available disk space, and don't have space to
> save this information, causing coruption. This problem happened once
> to me, and i am pretty sure that it's much better to have the current
> implementation, even if it's not perfect, than to have the troubles i
> and many others had to restore a corrupted database.

Not precisely. Original proposal was to allocate pages on disk not at 
commit time, but immediately when they're allocated for cache manager. 
It's a right thing to do without question. But allocation of more pages 
than needed is an *extension* that goes beyond it.

The problem with this particular RFC was targeting. It should be rather 
sent to Architect list instead Devel list, where RFC's actually belong 
and where it would get proper attention.

Anyway, no big deal here, it just means that this thing is not over yet 
and has to be discussed further. This feature as is implemented now 
doesn't do any damage (hopefully, the jury is still out), but has to be 
taken a little bit further to get really usable. Hence I would like 
propose to get us some room for changes at this point by turning it off 
by default in configuration. People who would like use it would still 
can, but as it would need a conscious action (change it in config file 
and read the release notes), they would also know all details and that 
it may change in future, so they would be careful on next upgrade. 
Without this step, we may set a support trap when we'll do any 
significant or even incompatible changes, as majority of users wouldn't 
even notice that this feature exists nor all its consequences. Let 
incubate this feature a little bit more before it gets automatically 
used on all servers out there.

best regards
Pavel Cisar
IBPhoenix

On Monday 18 June 2007 19:41, Pavel Cisar wrote:
> Fabiano,
>
> Fabiano Bonin wrote:
> > PS: By the way, if i remember right, the look-ahead disk space
> > allocation was implemented to avoid the case when the database grows
> > in memory more than the available disk space, and don't have space to
> > save this information, causing coruption. This problem happened once
> > to me, and i am pretty sure that it's much better to have the current
> > implementation, even if it's not perfect, than to have the troubles i
> > and many others had to restore a corrupted database.
>
> Not precisely. Original proposal was to allocate pages on disk not at
> commit time, but immediately when they're allocated for cache manager.
> It's a right thing to do without question. But allocation of more pages
> than needed is an *extension* that goes beyond it.

As far as I remember (it was Vlad, not me, who suggested and implemented a 
feature), at commit time when doing mass inserts pages are allocated more or 
less in big amount - imagine gbak's transaction, restoring big table 
(actually one can see in any file's listing tool how DB grows step by step). 
But if we allocate space exactly page by page when page is needed, we will 
always allocate by single page. Therefore turning this mode 'off' by default 
means regression in speed, or we must keep 'legacy' behavior, which was 
leading sometimes to broken databases when out-of-memory.

Pavel, as far as I see from your long big answer, you say that there are 
better and more precise ways to allocate pages then one implemented currently 
in HEAD. In general I'm agree here - there are such, and lets move discussion 
about it to architect. And it will be good to wait for Vlad to be able to 
take part in it. OK?

Alex Peshkov wrote:
> 
> As far as I remember (it was Vlad, not me, who suggested and implemented a 
> feature), at commit time when doing mass inserts pages are allocated more or 
> less in big amount - imagine gbak's transaction, restoring big table 
> (actually one can see in any file's listing tool how DB grows step by step). 
> But if we allocate space exactly page by page when page is needed, we will 
> always allocate by single page. Therefore turning this mode 'off' by default 
> means regression in speed, or we must keep 'legacy' behavior, which was 
> leading sometimes to broken databases when out-of-memory.

I see, the old behavior deffer physical allocation and I/O to commit 
time, hence it has better performance than with fix that allocates disk 
space immediately when allocation is kept on page by page level.

Pity situation. Retaining old behavior is not an option, and introducing 
general slowdown for bulk inserts isn't either. Ok, I understand the 
rationale behind this much better now, so I changed my mind a bit :)

So, let it there as is now, it works for what it was intended, and 
describe it accordingly in Release Notes in Fix section, not as new 
feature, as it's really just optimization, a countermeasure, and we'll 
deal with better user interface to it that would turn it into real 
feature sometime later (in Architect list).


best regards
Pavel Cisar
IBPhoenix