Re: [sqlite] How much disk space is required to checkpoint?

[David Barrett]

On Fri, Sep 28, 2012 at 10:49 AM, Jay A. Kreibich  wrote:

>   The standard C I/O library includes the tmpfile() call, which performs
>   the whole process of generating a random temporary file name, opening
>   the file, and then unlinking the file.


Wow, very interesting, thank you.  I stand corrected!

-david
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How much disk space is required to checkpoint?

[Jay A. Kreibich]

On Wed, Sep 26, 2012 at 02:59:12PM +0100, Simon Slavin scratched on the wall:

> Allow me to present an alternative point of view.
> 
> If filling up your filespace is causing you to crash, it's easier to
> understand what's going on if you can see the files which are filling
> it up.  Running out of disk space when you can't see huge files makes
> it difficult to debug.  With an unlinked file I would find it hard to 
> figure out what was filling my hard disk causing me to crash.

  That's a valid point, and is one of the reasons why /tmp is
  traditionally its own file system... if you accidentally fill it, you
  might lock an application or two, but you shouldn't cause issues
  with the whole OS.

  Of course if the application dies because it ran out of space, that's
  its own fault for making assumptions about storage.  Having the file
  unlinked (or not) doesn't change those issues, other than leaving
  behind evidence of the cause.  An application properly checking 
  and reporting error codes doesn't need evidence.

  Regardless, you're saying it is easier and more logical to hunt
  through a whole file tree looking for a large file, rather than just
  checking to see how full the disks are.  While I get where you're
  coming from, I think it is a pretty weak point next to the general
  advantages open-and-unlink offers.


  There also seems to be a running assumption that disk space should,
  in some direct way, relate to the sum total of all the file sizes on
  a volume.  That's not a very safe assumption with Unix file systems.
  While the use non-symbolic links is somewhat rare these days, they
  do exist.  You can have the same file show up multiple places in the
  file system as a full-on native file (not a link or alias), but it
  only shows up on the disk once (that's why the call is unlink() and
  not delete()).
  
  Similarly, the "size" of a file and how much disk space it takes up
  are not always the same.   Most Unix file systems support sparse
  files, so you can have a files that report a "size" significantly
  larger than their actual disk usage.

  In fact, we used to test backup and archive software that way.  Open
  a file, write a byte, advanced the file counter several hundred
  gigabytes, write another byte.  The file shows up as huge, but it is
  only taking up a few kilobytes on disk.  The smart backup systems
  understand this, the dumb ones burn through a lot of tape recording
  zeros.  Same with tar and similar utilities, and don't get me started
  with crappy home-grown quota systems.  You can cause a lot of headaches
  for your sysadmin that way.  It's also fun to freak out the newbies 
  by putting a 2 TB file onto their 500 GB disk.

> Avoiding filename clashes can be done by creating files with random
> or time-related elements to their names.  It's less of a problem.

  As I already pointed out, there are many, many reasons for the
  "open-and-unlink" pattern that go beyond file name collision.  In
  fact, that's a very minor reason, since an application should be
  using one of the C standard calls like tmpnam() to generate unique
  file names in a known, accepted way.

> > How would a file that clogs up /tmp be preferrable to some unnamed data
> > that will be automatically removed from the file system by the fsck
> > after the crash?
> 
> Unix deletes the contents of /tmp at boot time.

  Some, but not all.  I've run across systems that don't bother to
  delete /tmp.  Not that it matters... many Unix systems have a high
  enough uptime that anything done on reboot happens pretty rare, and
  really shouldn't be considered standard maintenance.

> That's why it's special. 

  That, and it has specific permissions that allow anyone to create
  files.  Traditionally, it is also its own file system, although
  that's somewhat rare these days.

> In contrast, using unlink() can cause some
> chaos including filespace hogs turning up in lost+found -- the sort
> of thing that might cause problems that a mundane user might never
> understand.

  No, it can't.  In fact, if "problems for the mundane user" is your
  primary concern, open-and-unlink is a much better pattern in almost
  every way.

  Understand that if a process does an open-and-unlink and the
  process terminates FOR ANY REASON, including a crash, then the OS
  will close all the file descriptors, which will trigger an automatic
  deletion of the file.  The process cannot exit without cleaning up.

  Compare this to a traditional file in temp, which is just going to get
  left there, taking up space until the next reboot.  Most users-- even
  savvy users-- don't manually clean up their /tmp directory between
  reboots.  If this is a desktop with a multi-month uptime, and more
  and more cruft is getting left behind, it is conceivable that /tmp
  may fill up.

  If an application manages to fill up /tmp, the correct thing to do is
  notice that the write operations aren't working, report the error and
  exit. 

Re: [sqlite] How much disk space is required to checkpoint?

[Christopher Vance]

On 28 September 2012 03:08, David Barrett  wrote:
> Also, I disagree its a "standard, time tested" solution.  I've never even
> heard of this trick before, and despite its passionate defense, I'd
> encourage everybody (including the SQLite maintainers) to stop using it.

I heard of it decades ago, at which point it became obvious, so you
must be a young'un. A common justification is that it means the file
disappears when the program terminates, so you don't have to catch all
exit and signal paths to delete the file. And the file is gone even if
your program receives an uncatchable signal.

-- 
Christopher Vance
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How much disk space is required to checkpoint?

[David Barrett]

Incidentally, I agree with Simon.  It exchanges the extremely easy problem
of temp namespace collision for the very hard problem of invisible files
filling up your hard drive.  Indeed, it was diagnosing this hard problem --
at great cost in time and confusion -- that caused us to discover it in the
first place.  (Though to clarify, it seems to be in /var/tmp for extra
confusion.)

Also, I disagree its a "standard, time tested" solution.  I've never even
heard of this trick before, and despite its passionate defense, I'd
encourage everybody (including the SQLite maintainers) to stop using it.

(No need to defend it further Jay, I'm done.)

-david
On Sep 26, 2012 7:21 AM, "Simon Slavin"  wrote:

>
> On 26 Sep 2012, at 3:07pm, Jay A. Kreibich  wrote:
>
> >  Because it is the standard, time-tested way of doing this kind of
> >  thing on Unix-- for a lot of very good reasons.
>
> Okay.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How much disk space is required to checkpoint?

[Simon Slavin]

On 26 Sep 2012, at 3:07pm, Jay A. Kreibich  wrote:

>  Because it is the standard, time-tested way of doing this kind of
>  thing on Unix-- for a lot of very good reasons.

Okay.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How much disk space is required to checkpoint?

[Jay A. Kreibich]

On Wed, Sep 26, 2012 at 01:55:33PM +0100, Simon Slavin scratched on the wall:


> There are problems with doing this under some versions of Unix/Linux,
> and especially variants like Android. 

  This technique is almost as old as Unix itself.  If some OS versions
  can't deal with it, they're buggy.  I would not consider it a
  "trick", but rather a standard best-practice for dealing with temp
  files.

> For instance, another user/app can make a file with the same name.

  And how, exactly, would keeping the file around fix this?

  If the file is properly unlinked, it doesn't exist in the directory.
  It has no name, so it is impossible to create another file with the
  "same" name. 

  Besides, if the you kept the file around, you'd have the exact same
  problem.  There are also APIs to get unique temp file names.  If an
  application isn't using them, its buggy.  SQLite also uses the string
  "etilqs" in temp files to avoid collisions.

> Or the system may crash while the file is still open.

  That's why file systems fsck when they remount.  There are all kinds
  of things that can go wrong with a file system when a machine crashes.

  An application crash is no big deal, however.

> I would believe that any Unix user who knows to look in /tmp could
> deal with what they found there.

  No offense to the professional system administrators out there, but 
  I think you're vastly over estimating the average sys-admin, especially
  when they're a teenager with a new MacBook and just enough knowledge
  of Unix to be dangerous.

  Richard made a post about it some months ago.  SQLite is embedded in a
  lot of applications.  Some of them are buggy, crashy applications.  The
  reason SQLite uses "etilqs" rather than "sqlite" in the temp file names
  (as it used to) is because of such clueless people using bad software
  find the temp files and then go off ranting about how SQLite sucks and
  needs to be fixed.  Flipping the name around weeds out enough Google
  searches to avoid such people.  Of course, emails like this, that use
  the string, don't help the situation.  
  
  PLEASE NOTE: if you found this message via a Google search at some
  future date, please re-read the previous paragraph until you
  understand the full ramifications of what it is saying.  Don't be
  clueless.

> I would say that the file should be deleted normally when SQLite is
> finished with it rather than this trick being used.
> 
> Of course, there may be a specific reason why the programmers of
> SQLite decided to do this.

  Because it is the standard, time-tested way of doing this kind of
  thing on Unix-- for a lot of very good reasons.

  The file cannot be open by another process, period.  Even a root
  process.  So it provides security and isolation from stupid programs
  doing dumb things.  It avoids file name collisions, as the file
  doesn't exist in the file tree.  Deleting the file means that as soon
  as that SQLite process exits-- no matter how or why-- the file will
  be cleaned up and removed.  That's important for files put outside of
  /tmp.  It can be important for files inside /tmp... most systems only
  clean /tmp on reboot, and that can be months, if not years, on many
  Unix systems.  Some don't clean /tmp at all.

  The "create and unlink" pattern is so common, many UNIX systems have
  a tmpfile() or similar library call to do the whole thing... create a
  unique file in /tmp, open it, unlink it, and return the file pointer.

  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How much disk space is required to checkpoint?

[Simon Slavin]

On 26 Sep 2012, at 2:36pm, Clemens Ladisch  wrote:

> Simon Slavin wrote:
>> On 26 Sep 2012, at 5:42am, Dan Kennedy  wrote:
>>> So when SQLite requires a temporary
>>> file, it normally creates and opens a file with a randomized name
>>> in /tmp then calls unlink() on it immediately - before it starts
>>> using the file.
>> 
>> [...] There are problems with doing this under some versions of Unix/
>> Linux, and especially variants like Android.  For instance, another
>> user/app can make a file with the same name.
> 
> Andorid doesn't have a shared /tmp.  In any case, temporary files are
> opened with O_CREAT|O_EXCL, so two apps will never accidentally open
> each other's file.  Furthermore, by deleting the file immediately after
> creating it, the time window in which a conflict is possible is made as
> short as possible.

Allow me to present an alternative point of view.

If filling up your filespace is causing you to crash, it's easier to understand 
what's going on if you can see the files which are filling it up.  Running out 
of disk space when you can't see huge files makes it difficult to debug.  With 
an unlinked file I would find it hard to figure out what was filling my hard 
disk causing me to crash.

Avoiding filename clashes can be done by creating files with random or 
time-related elements to their names.  It's less of a problem.

>> Or the system may crash while the file is still open.
> 
> How would a file that clogs up /tmp be preferrable to some unnamed data
> that will be automatically removed from the file system by the fsck
> after the crash?

Unix deletes the contents of /tmp at boot time.  That's why it's special.  In 
contrast, using unlink() can cause some chaos including filespace hogs turning 
up in lost+found -- the sort of thing that might cause problems that a mundane 
user might never understand.

>> Is there any chance that the use of this trick can be discontinued ?
> 
> This is not a trick, it's a widely used Unix idiom.

It's widely used outside /tmp.  It's an alternative to using /tmp, especially 
on platforms which don't support a /tmp.  I don't think the two techniques are 
often used with the same file.  If you're using /tmp then your 
sysadmin/programmer needs to know what's suddenly killing freespace on their 
working volume and looking in /tmp is a great way to find everything that might 
be a problem.  But if the file is unlinked they'll never see it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How much disk space is required to checkpoint?

[Pavel Ivanov]

On Wed, Sep 26, 2012 at 5:55 AM, Simon Slavin  wrote:
>
> On 26 Sep 2012, at 5:42am, Dan Kennedy  wrote:
>
>> So when SQLite requires a temporary
>> file, it normally creates and opens a file with a randomized name
>> in /tmp then calls unlink() on it immediately - before it starts
>> using the file.
>
> Goodness me.  Okay, I wasn't aware that SQLite used this trick.  Thanks for 
> the correction.  That certainly does tie in with what the OP reported.
>
> There are problems with doing this under some versions of Unix/Linux, and 
> especially variants like Android.

I wonder what problems do you have in mind?

> For instance, another user/app can make a file with the same name.

If it doesn't happen in between file creation and unlinking then
there's absolutely no problem in this -- this app will see a different
file, not the one that SQLite sees. And with name randomization and
extremely short time between creation and unlinking there virtually no
chances to have any problems of this type.

> Or the system may crash while the file is still open.

What's the problem with this? File will be deleted by the file system
after reboot. To the contrary this scenario will give you problems if
you don't do unlinking.

> Is there any chance that the use of this trick can be discontinued ?

Hopefully you have some compelling reason for suggesting that.


Pavel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How much disk space is required to checkpoint?

[Clemens Ladisch]

Simon Slavin wrote:
> On 26 Sep 2012, at 5:42am, Dan Kennedy  wrote:
>> So when SQLite requires a temporary
>> file, it normally creates and opens a file with a randomized name
>> in /tmp then calls unlink() on it immediately - before it starts
>> using the file.
>
> [...] There are problems with doing this under some versions of Unix/
> Linux, and especially variants like Android.  For instance, another
> user/app can make a file with the same name.

Andorid doesn't have a shared /tmp.  In any case, temporary files are
opened with O_CREAT|O_EXCL, so two apps will never accidentally open
each other's file.  Furthermore, by deleting the file immediately after
creating it, the time window in which a conflict is possible is made as
short as possible.

> Or the system may crash while the file is still open.

How would a file that clogs up /tmp be preferrable to some unnamed data
that will be automatically removed from the file system by the fsck
after the crash?

> Is there any chance that the use of this trick can be discontinued ?

This is not a trick, it's a widely used Unix idiom.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How much disk space is required to checkpoint?

[Simon Slavin]

On 26 Sep 2012, at 5:42am, Dan Kennedy  wrote:

> So when SQLite requires a temporary
> file, it normally creates and opens a file with a randomized name
> in /tmp then calls unlink() on it immediately - before it starts
> using the file.

Goodness me.  Okay, I wasn't aware that SQLite used this trick.  Thanks for the 
correction.  That certainly does tie in with what the OP reported.

There are problems with doing this under some versions of Unix/Linux, and 
especially variants like Android.  For instance, another user/app can make a 
file with the same name.  Or the system may crash while the file is still open. 
 Is there any chance that the use of this trick can be discontinued ?  I would 
believe that any Unix user who knows to look in /tmp could deal with what they 
found there.  I would say that the file should be deleted normally when SQLite 
is finished with it rather than this trick being used.

Of course, there may be a specific reason why the programmers of SQLite decided 
to do this.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How much disk space is required to checkpoint?

[Dan Kennedy]

On 09/26/2012 05:48 AM, Simon Slavin wrote:


On 25 Sep 2012, at 10:54pm, David Barrett
wrote:


If my database is X GB, and the WAL file is Y GB, how much total
disk space is required to:

1) Checkpoint the database


I think it's a maximum of X+Y+Y (including the existing X+Y) plus or
minus a page or two.


2) Vacuum the database


I think it's a maximum of 3*(X+Y) (including the existing X+Y) plus
or minus a page or two.  It might be 2*(X+Y).  I'm not certain how of
how checkingpointing is done.  Both of those answers are dependent on
the database not being corrupt, and SQLite being in a proper working
state.


Additionally, if there are any temporary files created, where do
they exist?  We've done some poking around and are having some
crazy theory about a temporary file being created inside /tmp, but
in a *deleted* state.


As far as I know, SQLite does not intentionally play tricks like
that.  Your OS may report the length of a temporary file as zero
because it's still open, but SQLite is just using the normal file
writing calls.


Actually it might. On unix, if you unlink a file while one or more
clients have it open, the clients can keep reading and writing the
file descriptor. No other can open the file though (as it is not
linked into the file-system). So when SQLite requires a temporary
file, it normally creates and opens a file with a randomized name
in /tmp then calls unlink() on it immediately - before it starts
using the file.

Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How much disk space is required to checkpoint?

[Simon Slavin]

On 25 Sep 2012, at 10:54pm, David Barrett  wrote:

> If my database is X GB, and the WAL file is Y GB, how much total disk space
> is required to:
> 
> 1) Checkpoint the database

I think it's a maximum of X+Y+Y (including the existing X+Y) plus or minus a 
page or two.

> 2) Vacuum the database

I think it's a maximum of 3*(X+Y) (including the existing X+Y) plus or minus a 
page or two.  It might be 2*(X+Y).  I'm not certain how of how checkingpointing 
is done.  Both of those answers are dependent on the database not being 
corrupt, and SQLite being in a proper working state.

> Additionally, if there are any temporary files created, where do they
> exist?  We've done some poking around and are having some crazy theory
> about a temporary file being created inside /tmp, but in a *deleted* state.

As far as I know, SQLite does not intentionally play tricks like that.  Your OS 
may report the length of a temporary file as zero because it's still open, but 
SQLite is just using the normal file writing calls.

> I'm guessing we're wrong, so I figured it would be easiest just to ask.

Good strategy.

You might find something interesting from this PRAGMA:



though you should read the notes first.  But as a rule SQLite will create 
temporary files in just two places: the folder the database is in, and wherever 
your OS thinks temporary files should be (e.g. /tmp, C:\windows\temp).  There 
are some problems with OSen like Android which do not have a built-in concept 
of a temporary folder.  For those, I believe the temporary folder is set at 
compilation time.

I did once find SQLite creating temporary files on the root of my boot disk.  
This problem was traced to the fact that the disk format (not the database 
format) was corrupt !

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How much disk space is required to checkpoint?

[David Barrett]

If my database is X GB, and the WAL file is Y GB, how much total disk space
is required to:

1) Checkpoint the database

2) Vacuum the database

Additionally, if there are any temporary files created, where do they
exist?  We've done some poking around and are having some crazy theory
about a temporary file being created inside /tmp, but in a *deleted* state.
 I'm guessing we're wrong, so I figured it would be easiest just to ask.
 Thanks!

-david
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users