Re: [sqlite] get the actual database size.
On May 31, 2008, at 6:54 AM, Aladdin Lampé wrote: > >> From: [EMAIL PROTECTED] >> 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 [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] get the actual database size.
> From: [EMAIL PROTECTED] > 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] get the actual database size.
On Fri, 30 May 2008 09:18:39 -0700 (PDT), you wrote: >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 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 . -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] get the actual database size.
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 <[EMAIL PROTECTED]> To: [EMAIL PROTECTED]; General Discussion of SQLite Database 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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. On 5/29/08, Joanne Pham <[EMAIL PROTECTED]> wrote: > 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. > Thanks, > JP > > ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] get the actual database size.
On 5/30/08, Joanne Pham <[EMAIL PROTECTED]> wrote:
>
>
>
> 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.
>
> 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 <[EMAIL PROTECTED]>
> To: General Discussion of SQLite Database
> Sent: Thursday, May 29, 2008 5:40:23 PM
> Subject: Re: [sqlite] get the actual database size.
>
> On 5/29/08, Joanne Pham <[EMAIL PROTECTED]> wrote:
> > 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.
>
>
> > Thanks,
> > JP
> >
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] get the actual database size.
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 <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Thursday, May 29, 2008 5:40:23 PM Subject: Re: [sqlite] get the actual database size. On 5/29/08, Joanne Pham <[EMAIL PROTECTED]> wrote: > 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. > Thanks, > JP > > ___ 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] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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. On 5/29/08, Joanne Pham <[EMAIL PROTECTED]> wrote: > 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. > Thanks, > JP > > ___ 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] get the actual database size.
P Kishor wrote: > On 5/29/08, Joanne Pham <[EMAIL PROTECTED]> wrote: >> 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] get the actual database size.
Joanne Pham wrote: 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] get the actual database size.
On 5/29/08, Joanne Pham <[EMAIL PROTECTED]> wrote: > 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. > Thanks, > JP > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] get the actual database size.
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@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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
