get the actual database size.

[Joanne Pham]

Hi All,
I have the database which has a lot of insertion and deletion.
Is there anyway that I can get the actual database size without running VACUUM.
Thanks,
JP




_______________________________________________
sqlite-users mailing list
sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[P Kishor]

Post by Joanne Pham
Hi All,
I have the database which has a lot of insertion and deletion.
Is there anyway that I can get the actual database size without running VACUUM.

Your question implies that VACUUM lets you "get the database size."
No, it doesn't. VACUUM recovers the space left behind by deleting data
from the db.

To find out the size of the database, just read the size of the file
in the operating system.

Or, maybe you are asking something completely different that I don't get.

Post by Joanne Pham
Thanks,
JP

[Darren Duncan]

Post by P Kishor

Post by Joanne Pham
Hi All,
I have the database which has a lot of insertion and deletion.
Is there anyway that I can get the actual database size without running VACUUM.

Your question implies that VACUUM lets you "get the database size."
No, it doesn't. VACUUM recovers the space left behind by deleting data
from the db.
To find out the size of the database, just read the size of the file
in the operating system.
Or, maybe you are asking something completely different that I don't get.

I think what Joanne's asking is if it were possible to query what size the
database would become if it were vacuumed without actually vacuuming it.
Maybe as part of a cost analysis for whether to vacuum, or stats for the
user as to how much of the database file is unused space. -- Darren Duncan

[Ronny Dierckx]

Hi,

I think a possible solution is to calculate the difference between
the database file size and the number of free pages multiplied by
the page size. This is of course an approximation, but it works for me.

Number of free pages: pragma freelist_count
Page size: pragma page_size

Ronny

http://www.syntegro.be


-----Original Message-----
From: sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org
[mailto:sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org] On Behalf Of P Kishor
Sent: vrijdag 30 mei 2008 2:40
To: General Discussion of SQLite Database
Subject: Re: [sqlite] get the actual database size.

Post by Joanne Pham
Hi All,
I have the database which has a lot of insertion and deletion.
Is there anyway that I can get the actual database size without running VACUUM.

Your question implies that VACUUM lets you "get the database size."
No, it doesn't. VACUUM recovers the space left behind by deleting data
from the db.

To find out the size of the database, just read the size of the file
in the operating system.

Or, maybe you are asking something completely different that I don't get.

Post by Joanne Pham
Thanks,
JP

[Mihai Limbasan]

Post by Joanne Pham
Hi All,
I have the database which has a lot of insertion and deletion.
Is there anyway that I can get the actual database size without running VACUUM.
Thanks,
JP

Hi, Joanne.

I assume that by "actual database size" you mean "the size of the
database file minus the size of the 'dead' space". If that assumption
holds, then no, there is no way to do that using the public API - you
would have to look at the actual VACUUM implementation and replicate the
size calculations done there. So the answer would be no, you must run a
VACUUM first.

[Joanne Pham]

Thanks for response.
I know the VACUUM is recovers the space left behind by deleting data from db but this
is very expensive operator and it holds the locks for this database which will be the big impact for other operations like inserting/updating  the database.
I can read tye size of file in the operating system but without VACUUM the database first the size is not accurate.
So it seems like it is the must to ran the VACUUM before read the size of the file.
Thanks,
JP



----- Original Message ----
From: P Kishor <punk.kish-***@public.gmane.org>
To: General Discussion of SQLite Database <sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org>
Sent: Thursday, May 29, 2008 5:40:23 PM
Subject: Re: [sqlite] get the actual database size.

Post by Joanne Pham
Hi All,
  I have the database which has a lot of insertion and deletion.
  Is there anyway that I can get the actual database size without running VACUUM.

Your question implies that VACUUM lets you "get the database size."
No, it doesn't. VACUUM recovers the space left behind by deleting data
from the db.

To find out the size of the database, just read the size of the file
in the operating system.

Or, maybe you are asking something completely different that I don't get.

Post by Joanne Pham
  Thanks,
  JP

[P Kishor]

Post by Joanne Pham
Thanks for response.
I know the VACUUM is recovers the space left behind by deleting data from db but this
is very expensive operator and it holds the locks for this database which
will be the big impact for other operations like inserting/updating the
database.
I can read tye size of file in the operating system but without VACUUM the
database first the size is not accurate.

Well, it depends on how one defines the "size of the database." That
is such a misleading concept. In my view, the file size *is* the size
of the database with or without VACUUM-ing. VACUUM simply "changes"
the database ("changes" is in quotes) by recovering the deleted space.

There are so many other ways one could envision the "size" of the database --

1. The number of rows in tables

2. #1 above + all the space held by INDEXes (which can be substantial,
if not more than the tables themselves)

3. Any additional space held by the database's internal administrative
overhead -- TRIGGERs, VIEWs, schema tables, etc. all which are mystery
to mortals.

Then, while exactly the same database will be exactly the same bytes,
on the disk it will occupy different number of bytes depending on the
operating system and disk size.

To be absolutely clear, you have to ask for not the size of the
database, but the size of the file in which the database is held. Its
correct size is read from the filesystem (easy to do in SQLite, more
complicated in other db that scatter crap around on all kinds of
locations). The size at any given time depends on whether or not you
have run VACUUM.

So, there you have it. If you don't want to VACUUM it, you still get
the file size, but with deleted space included. If you VACUUM it, you
get the file size with no air pockets.

Post by Joanne Pham
So it seems like it is the must to ran the VACUUM before read the size of the file.
Thanks,
JP
----- Original Message ----
Sent: Thursday, May 29, 2008 5:40:23 PM
Subject: Re: [sqlite] get the actual database size.

Post by Joanne Pham
Hi All,
I have the database which has a lot of insertion and deletion.
Is there anyway that I can get the actual database size without running

VACUUM.
Your question implies that VACUUM lets you "get the database size."
No, it doesn't. VACUUM recovers the space left behind by deleting data
from the db.
To find out the size of the database, just read the size of the file
in the operating system.
Or, maybe you are asking something completely different that I don't get.

Post by Joanne Pham
Thanks,
JP

_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

...

[Joanne Pham]

Hi ,
I have two questions:
    1) Calucate the actual size of the database(NOT RUN VACUUM) - "Actual database size" which won't include the space of deleted rows.
        To be more specific you calculate the database size by the following below(WITHOUT VACUUM)
        Database file size - (pragma freelist_count *  pragma page_size )
        this calculation will return the actuall database size(the size exclude the space of deleted rows)
    2) No VACUUM the database which has a lot of deletion.
        Is that true that the space of the database won't reclaim if we don't run the "VACUUM" on the database.
  Thanks,
JP



----- Original Message ----
From: Ronny Dierckx <ronny.dierckx-***@public.gmane.org>
To: punkish-***@public.gmane.org; General Discussion of SQLite Database <sqlite-***@sqlite.org>
Sent: Friday, May 30, 2008 12:12:14 AM
Subject: Re: [sqlite] get the actual database size.

Hi,

I think a possible solution is to calculate the difference between
the database file size and the number of free pages multiplied by
the page size. This is of course an approximation, but it works for me.

Number of free pages: pragma freelist_count
Page size: pragma page_size

Ronny

http://www.syntegro.be


-----Original Message-----
From: sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org
[mailto:sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org] On Behalf Of P Kishor
Sent: vrijdag 30 mei 2008 2:40
To: General Discussion of SQLite Database
Subject: Re: [sqlite] get the actual database size.

Post by Joanne Pham
Hi All,
  I have the database which has a lot of insertion and deletion.
  Is there anyway that I can get the actual database size without running

VACUUM.

Your question implies that VACUUM lets you "get the database size."
No, it doesn't. VACUUM recovers the space left behind by deleting data
from the db.

To find out the size of the database, just read the size of the file
in the operating system.

Or, maybe you are asking something completely different that I don't get.

Post by Joanne Pham
  Thanks,
  JP

_______________________________________________
sqlite-users mailing list
sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Kees Nuyt]

Post by Joanne Pham
Hi ,
    1) Calucate the actual size of the database(NOT RUN VACUUM) - "Actual database size" which won't include the space of deleted rows.
        To be more specific you calculate the database size by the following below(WITHOUT VACUUM)
        Database file size - (pragma freelist_count *  pragma page_size )
        this calculation will return the actuall database size(the size exclude the space of deleted rows)
    2) No VACUUM the database which has a lot of deletion.
        Is that true that the space of the database won't reclaim if we don't run the "VACUUM" on the database.
  Thanks,
JP

...

Would sqlite3_analyzer work for you?
It produces both a human readable report as well as a table
definition and insert statements to feed to sqlite3 command
line tool.

CREATE TABLE space_used(
name clob, -- Name of a table or index in the
database file
tblname clob, -- Name of associated table
is_index boolean, -- TRUE if it is an index, false for a
table
nentry int, -- Number of entries in the BTree
leaf_entries int, -- Number of leaf entries
payload int, -- Total amount of data stored in this
table or index
ovfl_payload int, -- Total amount of data stored on
overflow pages
ovfl_cnt int, -- Number of entries that use overflow
mx_payload int, -- Maximum payload size
int_pages int, -- Number of interior pages used
leaf_pages int, -- Number of leaf pages used
ovfl_pages int, -- Number of overflow pages used
int_unused int, -- Number of unused bytes on interior
pages
leaf_unused int, -- Number of unused bytes on primary
pages
ovfl_unused int, -- Number of unused bytes on overflow
pages
gap_cnt int -- Number of gaps in the page layout
);

A wealth of information, really.
sqlite3_analyzer is available on the download page
http://www.sqlite.org/download.html .

[Aladdin Lampé]

Date: Fri, 30 May 2008 20:26:14 +0200
Would sqlite3_analyzer work for you?
It produces both a human readable report as well as a table
definition and insert statements to feed to sqlite3 command
line tool.

Where can we download the source of this tool "sqlite3_analyser"? (The precompiled binary is on the sqlite3 web site, download section).
It seems to be an interesting reading to understand the sqlite3 file format.
Thanks,
Aladdin

_________________________________________________________________
Caroline vient de mettre à jour son profil Messenger ! Connectez-vous !
http://login.live.com/login.srf?wa=wsignin1.0&rpsnv=10&ct=1198837564&rver=4.0.1534.0&wp=MBI&wreply=http:%2F%2Fhome.services.spaces.live.com%2F&lc=1036&id=73625

[D. Richard Hipp]

Post by Aladdin Lampé

Date: Fri, 30 May 2008 20:26:14 +0200
Would sqlite3_analyzer work for you?
It produces both a human readable report as well as a table
definition and insert statements to feed to sqlite3 command
line tool.

Where can we download the source of this tool "sqlite3_analyser"?
(The precompiled binary is on the sqlite3 web site, download section).
It seems to be an interesting reading to understand the sqlite3 file format.
Thanks,
Aladdin

The source code to sqlite3_analyzer is included in the source code
tarball and in the CVS tree. Sqlite3_analyzer is written in TCL. It
seems unlikely that it will be much help in understanding the file
format.


D. Richard Hipp
drh-***@public.gmane.org