Re: [sqlite] SQLITE_MAX_PAGE_COUNT

[Raheel Gupta]

I will make the changes as per my knowledge and send it to all  on this
list to see if it helps in anyway.



On Thu, Mar 6, 2014 at 5:25 AM, Roger Binns <rog...@rogerbinns.com> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 05/03/14 10:59, Raheel Gupta wrote:
> > If you point out to me the changes required I will do it and have it
> > reviewed.
>
> The changes required are to update the test suites (there are several) to
> hit/cross the current limit, to modify all relevant code including any
> code that calls that code, to audit for overflows, to provide a new api
> and tests for that.
>
> And to ensure that stays maintained for the future lifetime of SQLite 3.
>
> Roger
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.14 (GNU/Linux)
>
> iEYEARECAAYFAlMXuWsACgkQmOOfHg372QRWcgCgmLmuv+d7Mxu5sfn9XFxAwx8N
> xagAoIpbTOEPWtPqXRsQSBuIjgV4NYhl
> =RMyF
> -END PGP SIGNATURE-
> ___
> 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] SQLITE_MAX_PAGE_COUNT

[Raheel Gupta]

Sir, will it require much change ?
If you point out to me the changes required I will do it and have it
reviewed.

AT the moment I am checking code related to the following variables :
mxPgno
sqlite3PagerMaxPageCount (Function)
pageSize


On Thu, Mar 6, 2014 at 12:06 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Wed, Mar 5, 2014 at 1:24 PM, Raheel Gupta <raheel...@gmail.com> wrote:
>
> > SQLITE_MAX_PAGE_COUNT is 2147483646.
> > After looking at the code, Pgno is a u32.
> > So isnt SQLITE_MAX_PAGE_COUNT capable of actually being 4294967292 ?
> >
>
> We have your request.  But as it will require a lot of testing, and because
> it is unlikely to actually be useful to anybody, it is a low priority.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] SQLITE_MAX_PAGE_COUNT

[Raheel Gupta]

SQLITE_MAX_PAGE_COUNT is 2147483646.
After looking at the code, Pgno is a u32.
So isnt SQLITE_MAX_PAGE_COUNT capable of actually being 4294967292 ?

Please correct me if I am wrong
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Free Page Data usage

[Raheel Gupta]

Hi,

If I were to implement it for my use only, any heads up where I could start.
I do know that PgNo is a variable used everywhere. Will changing that help ?

>> 64-bit page numbers would not be backwards compatible.
It might be possible to implement it in backwards compatible mode. I guess
SQlite has some free flags in its superblock. Maybe we can use a single
byte to mark that this is a 64 bit page number ?

Ext File System does that as well :)


On Mon, Feb 10, 2014 at 4:42 PM, Clemens Ladisch <clem...@ladisch.de> wrote:

> Raheel Gupta wrote:
> >If only the number of pages could be increased somehow. Does anyone think
> >its practical to make the pageNo from a 32 bit int to a 64 bit Unsigned
> >Integer.
>
> The 32-bit page number is part of the file format.
> 64-bit page numbers would not be backwards compatible.
>
>
> Regards,
> Clemens
>
> ___
> 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] Free Page Data usage

[Raheel Gupta]

>> Note that choosing a page size smaller than the typical row size means
that the bottom level of the BTree degrades to 1 row per node.
What do you mean by this ? How is a smaller page bad for the database ?


On Mon, Feb 10, 2014 at 2:43 PM, Hick Gunter <h...@scigames.at> wrote:

> With a record size of a little over 4K (note that the on-disk space
> requirement of a integer+4k Blob row is not always 4k+8) and a page size of
> 2K you are storing 1 row in 3 pages (close to 50% overhead). Deleting a
> record will give you 3 pages of free space, which will be reused quickly;
> some of it for the higher levels of the B-Tree. Note that choosing a page
> size smaller than the typical row size means that the bottom level of the
> BTree degrades to 1 row per node.
>
> Changing to 4k or 8k will increase overhead to near 100% (as you now need
> 2 Pages of 4k or one page of 8k for each row).
>
> 16k pages ( 3 rows/page) reduce this to 25.00% while deleting 5 adjacent
> rows is guaranteed to free up 1 page.
> 32k pages ( 7 rows/page) reduce this to 12.50% but requires 13 adjacent
> deletes for 1 guaranteed free page.
> 64k pages (15 rows/page) reduce this to  6.25% but requires 29 adjacent
> deletes for 1 guaranteed free page.
>
> You can choose the source of fragmentation: loosing close to 1 row per
> page (better in bigger pages) or having ununsed space due to nonadjacent
> deletes (better in smaller pages)
>
> -Ursprüngliche Nachricht-
> Von: Raheel Gupta [mailto:raheel...@gmail.com]
> Gesendet: Montag, 10. Februar 2014 07:14
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Free Page Data usage
>
> Hi,
>
> I tried the same database I had and used a 2KB page size.
> It works much faster and also the pages are reused immediattly to the
> extent of 95%.
>
> If only the number of pages could be increased somehow. Does anyone think
> its practical to make the pageNo from a 32 bit int to a 64 bit Unsigned
> Integer.
>
> I do understand that VACUUM is not a good option for me.
>
>
>
> On Sun, Feb 9, 2014 at 4:48 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> >
> > On 9 Feb 2014, at 10:45am, RSmith <rsm...@rsweb.co.za> wrote:
> >
> > > On 2014/02/09 12:06, Raheel Gupta wrote:
> > >> Sir, I have only one auto increment primary key.
> > >> Since the new rows will always have a higher number will the pages
> > >> which have some free space by deleting rows with lower numbered
> > >> keys
> > never
> > >> be reused ? e.g. If row with ROWID "1" was deleted and freed, will
> > >> it
> > not
> > >> be used to store the NEW row which will be assigned ROWID 10001 ?
> > >
> > > Yes. That is the point of AutoIncrement, every new Key will always
> > > be
> > higher than any previous key ever used, and always exactly one higher
> > than the highest ever previously used key. As such, it cannot be
> > re-used within pages that are half filled from deletion (except maybe
> > the last page), and I believe pages that go completely empty may be
> > re-used without the need to vacuum etc. (need someone to confirm this).
> >
> > You are correct, depending on this PRAGMA:
> >
> > <http://www.sqlite.org/pragma.html#pragma_auto_vacuum>
> >
> > auto_vacuum = NONE
> >
> > A page which has all its data deleted is added to the 'free pages'
> > list and eventually reused.
> >
> > auto_vacuum = FULL
> >
> > A page which has all its data deleted is replaced by the last page of
> > the file.  The file is then truncated to release the space of the last
> > page for use in other files.
> >
> > auto_vacuum = INCREMENTAL
> >
> > A page which has all its data deleted is replaced by the last used
> > page of the file.  When you issue "PRAGMA incremental_vacuum(N)" the
> > file is truncated to release unused pages at the end for use in other
> files.
> >
> > As in previous discussion, all this is about reclaiming space at the
> > page
> > level: releasing entire pages of space.  It has nothing to do with
> > reclaiming space within a page.  And also as in previous discussion,
> > the fastest of these is "auto_vacuum = NONE".  Copying one page to
> > another, releasing filespace and claiming it back again are slow and
> > require much reading and writing.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
&

Re: [sqlite] Free Page Data usage

[Raheel Gupta]

Hi,

I tried the same database I had and used a 2KB page size.
It works much faster and also the pages are reused immediattly to the
extent of 95%.

If only the number of pages could be increased somehow. Does anyone think
its practical to make the pageNo from a 32 bit int to a 64 bit Unsigned
Integer.

I do understand that VACUUM is not a good option for me.



On Sun, Feb 9, 2014 at 4:48 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 9 Feb 2014, at 10:45am, RSmith <rsm...@rsweb.co.za> wrote:
>
> > On 2014/02/09 12:06, Raheel Gupta wrote:
> >> Sir, I have only one auto increment primary key.
> >> Since the new rows will always have a higher number will the pages
> >> which have some free space by deleting rows with lower numbered keys
> never
> >> be reused ? e.g. If row with ROWID "1" was deleted and freed, will it
> not
> >> be used to store the NEW row which will be assigned ROWID 10001 ?
> >
> > Yes. That is the point of AutoIncrement, every new Key will always be
> higher than any previous key ever used, and always exactly one higher than
> the highest ever previously used key. As such, it cannot be re-used within
> pages that are half filled from deletion (except maybe the last page), and
> I believe pages that go completely empty may be re-used without the need to
> vacuum etc. (need someone to confirm this).
>
> You are correct, depending on this PRAGMA:
>
> <http://www.sqlite.org/pragma.html#pragma_auto_vacuum>
>
> auto_vacuum = NONE
>
> A page which has all its data deleted is added to the 'free pages' list
> and eventually reused.
>
> auto_vacuum = FULL
>
> A page which has all its data deleted is replaced by the last page of the
> file.  The file is then truncated to release the space of the last page for
> use in other files.
>
> auto_vacuum = INCREMENTAL
>
> A page which has all its data deleted is replaced by the last used page of
> the file.  When you issue "PRAGMA incremental_vacuum(N)" the file is
> truncated to release unused pages at the end for use in other files.
>
> As in previous discussion, all this is about reclaiming space at the page
> level: releasing entire pages of space.  It has nothing to do with
> reclaiming space within a page.  And also as in previous discussion, the
> fastest of these is "auto_vacuum = NONE".  Copying one page to another,
> releasing filespace and claiming it back again are slow and require much
> reading and writing.
>
> 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] Free Page Data usage

[Raheel Gupta]

Hi,

Sir, I have only one auto increment primary key.
Since the new rows will always have a higher number will the pages
which have some free space by deleting rows with lower numbered keys never
be reused ? e.g. If row with ROWID "1" was deleted and freed, will it not
be used to store the NEW row which will be assigned ROWID 10001 ?


On Sat, Feb 8, 2014 at 11:38 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Fri, Feb 7, 2014 at 7:39 AM, Raheel Gupta <raheel...@gmail.com> wrote:
>
> > Hi,
> >
> > My Page size is 64KB and I store around 4KB of row data in one row.
> > I store around 1 rows in one table and the database size reaches
> 42MB.
> >
> > Now, I am facing a peculiar problem. When I delete just 2-3 rows, that
> page
> > is not reused for the new data which will be inserted in the future.
> >
>
> That space will be reused if your new data has the same (or similar) key as
> the rows that were deleted.
>
> In order to achieve fast lookup, content must be logically ordered by key.
> That means that all of the rows on a single page must have keys that are
> close to one another.  If you have space on a page, and you insert a new
> row with a nearby key, that space will be (re)used.  But if you insert a
> new row with a very different key, that new row must be placed on a page
> close to other rows with similar keys, and cannot appear on the same page
> with rows of very dissimilar keys.
>
>
>
> >
> > The pragma freelist_count shows 0 if I delete the 1st 10 rows (approx
> 40KB)
> > Only if I delete more than 20 rows does the freelist_count reflect 1 page
> > as free.
> >
> > How should I get SQLIte to use the free space within a partially used
> page
> > when rows from that page have been deleted.
> >
> > This causes a lot of space wastage when I store more rows.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Free Page Data usage

[Raheel Gupta]

@Simon, Sir I dont want to rearrange the data.

I will try to explain more.
All my rows have the exact same size. They will not differ in size.
My problem is due to the fact that I use 64kB page size.
My rows are exactly 8 Bytes + 4096 Bytes.

Now for the purpose of ease in calculation lets assume each row is exactly
4 KB.
So one page stores 16 rows.
Lets say 10 pages are in use and I have a total of 160 rows.

Now I delete Rows 1-4 (total 4 rows) and I insert another 4 rows.
What I wanted is that the space freed by the first 4 rows being deleted be
used for the 4 new rows.

This should be done without any re-arrangement of data (so no vacuum and no
internal data rearrangement !).

As far as I am aware if a page is marked as free, sqlite will first use the
page to store new data. But since my page size is 64 KB, this will not be
possible.

Hence I am evaluating all options on this to optimize my storage space
utilization.



On Sat, Feb 8, 2014 at 10:21 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 8 Feb 2014, at 11:24am, Raheel Gupta <raheel...@gmail.com> wrote:
>
> > I dont want to repack the DB sir.
> > When a page becomes free I want to make sure that page is used up first
> and
> > then new pages are created.
>
> Just to explain that this would be extremely inefficient because a new row
> that you write to a database will not take up the same space as a row you
> have deleted.
>
> While a database is in use it might use perhaps 100 pages for a particular
> table.  Almost every one of those pages will have a little space free:
> anything from 1 byte to most of the page, depending on how much space each
> row takes up.  When writing a new row to a table, SQLite intelligently
> figures out which existing page it can write the row to (or does it ?
>  someone who has read the source code can tell me I'm wrong and if it
> searches for the 'best' page).
>
> What it won't do is rearrange existing pages so that they are used as much
> as possible.  That could be done whenever a row is deleted (including when
> a row is replaced using UPDATE).  But it would require a lot of checking,
> processing, reading and writing, and this would slow SQLite down a great
> deal for every DELETE and UPDATE operation.  As an the top of my head
> guess, individual operations could take unpredictable amounts of time since
> most efficient packing could require any number of pages to be rewritten.
>  I don't know of any database system that works like this.
>
> So that's one thing that might make you want to use VACUUM.  Even VACUUM
> does not reclaim the maximum amount of space possible.  Instead it prefers
> to keep the data for a row together and rows in primary index order
> together, to increase speeds
>
> There are also the auto_vacuum and incremental-vacuum PRAGMAs.  However
> they operate only on the level of pages: they will reap entire unused
> pages, but not interfere with the packing of data within a page.
>
> 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] Free Page Data usage

[Raheel Gupta]

>> No matter what size you make the pages, a delete function is never going
to re-pack the db

I dont want to repack the DB sir.
When a page becomes free I want to make sure that page is used up first and
then new pages are created.
VACUUM is not what I want to do.

I think that free pages are used up for new data in SQLIte as well and I
have no doubt of that.
The issue is when the page is 64 KB and it has lets say 16KB free then
atleast the 16KB should be used before an entirely new page is created.

@Clemens told that the page is used again if it has 2/3 free space.
My question is that can this 2/3 ratio be changed by me for my purpose to
1/3 or any other ratio.



On Sat, Feb 8, 2014 at 3:47 PM, RSmith <rsm...@rsweb.co.za> wrote:

> Hi Raheel,
>
> It does make sense what you would like to do, but your concern does not
> make sense. You say you are "trying to optimize the utilization of the free
> space available" but give no indication why, it certainly does not seem
> that space is a problem.
>
> I do understand the urge to optimize very much, but inside a Database
> engine you can optimize either for speed or for size, not for both. SQLIte
> as it stands is quite good at not wasting space unnecessarily, BUT, it is
> first and foremost optimized for speed (Thank goodness for that), which
> means the space-saving you are looking for is not going to happen.  In my
> previous post I made a passing comment / suggestion re using your own data
> files in stead of sqlite, and if it is a case of not needing the sql
> ability - which I seriously doubt since you are basically saving blocks of
> information from a blocked device and doing so as byte streams (or BLOB
> fields in SQL terms) - then I seriously suggest creating your own files and
> custom index mechanism and saving the byte streams in there.  It will be a
> lot faster and with zero space wastage and the size limits can be whatever
> you like them to be.
>
> Trying to use SQLite (or any other DB engine) for this purpose is akin to
> using a full-function bakery with ovens, humidifiers, provers, rising
> agents and bake timers when you just want to warm up your pizza (not to
> mention being restricted by the limitations that come with it).
>
> No matter what size you make the pages, a delete function is never going
> to re-pack the db, though you might get better results at re-using the
> space - but this is a compromise and one that does not sit well with you
> (if I read you right).
>
> Best of luck!
> Ryan
>
>
>
> On 2014/02/08 07:57, Raheel Gupta wrote:
>
>> Hi,
>> Sir, the 32 TB size is not always going to be reached.
>> The Database is going to be used to store blocks of a Block Device like
>> /dev/sda1
>> The size can reach 3-4 TB easily and would start from atleast 20-100 GB.
>> 32 TB of data though impractical as of today will be possible in 2-3 years.
>> The issue happens when I delete the rows and new rows are inserted at the
>> end of the database the size of the database exceeds that of the actual
>> block device size even though many pages are having free space.
>> Hence I am simply trying to optimize the utilization of the free space
>> available.
>> I would have loved to use the page size of 2KB which would give me a
>> practical size of 4TB. But that would have this hard limit of 4TB.
>> So I have two possible options which I am trying to help me solve this
>> issue :
>> 1) Either make the page size to 2KB and increase the maximum page count
>> to 2^64 which will be more than sufficient.
>> 2) Improve the free space utilization of each page when the page size is
>> 64KB.
>> I hope this makes sense.
>>
>>
> ___
> 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] Free Page Data usage

[Raheel Gupta]

Hi,

Sir, the 32 TB size is not always going to be reached.
The Database is going to be used to store blocks of a Block Device like
/dev/sda1
The size can reach 3-4 TB easily and would start from atleast 20-100 GB. 32
TB of data though impractical as of today will be possible in 2-3 years.
The issue happens when I delete the rows and new rows are inserted at the
end of the database the size of the database exceeds that of the actual
block device size even though many pages are having free space.
Hence I am simply trying to optimize the utilization of the free space
available.

I would have loved to use the page size of 2KB which would give me a
practical size of 4TB. But that would have this hard limit of 4TB.
So I have two possible options which I am trying to help me solve this
issue :
1) Either make the page size to 2KB and increase the maximum page count to
2^64 which will be more than sufficient.
2) Improve the free space utilization of each page when the page size is
64KB.

I hope this makes sense.



On Sat, Feb 8, 2014 at 12:54 AM, RSmith <rsm...@rsweb.co.za> wrote:

> A database that is geared for 32TB size and you are concerned about rather
> insignificant space wasted by the page size that is needed to reach the
> 32TB max size... does not make any sense unless you are simply paranoid
> about space.  Removing the gaps in the table space when deleting a row (or
> rows) will render a delete query several magnitudes slower.
>
> If it IS that big of a concern, then maybe use standard files rather than
> SQLite to save data in?  If the SQL functionality is a must, you can use
> vacuum as often as is needed to clear unused space - but beware, 1 - Vacuum
> takes some processing to re-pack a DB, especially a near 32TB one... in the
> order of minutes on a computer I would guess, and much much more on
> anything else.  2 - a 32TB DB will need up to 64TB total free disk space to
> be sure to vacuum correctly - so having issues with it taking up maybe 40TB
> for 32TB of data is in itself an irrelevant concern. Even large queries,
> temporary tables etc will all need additional interim space for the sorts
> of queries that might be requested of a 32TB data-set.
>
> The real point being: if you do not have at least 64TB free on whatever
> that 32TB DB will sit, you are doing it wrong, and if you do have that much
> free, you can ignore the 25% wasted deletion space problem.
>
> If the problem is simply your own pedanticism (at least I can sympathise
> with that!) then it's simply a case of "Welcome to efficient databasing",
> but if it is a real space deficit, then I'm afraid you will have to re-plan
> or reconsider either the max allowable DB, or the physical layer's space
> availability - sorry.
>
>
>
> On 2014/02/07 20:35, Raheel Gupta wrote:
>
>> Hi,
>>
>> I use a page size of 64 KB. But my row consists of 2 columns that is :
>> i - Auto Increment Integer,
>> b - 4096 Bytes of BLOB data
>>
>> Now for the sake of calculation, lets say 16 rows fit in a page and my
>> table has 1 rows when I start.
>>
>> Now, lets say I delete some data which is not in sequence i.e. it can be
>> deleted as per data which is not in use. To create such a hypothetical
>> situation for explaining this to you, here is a simple query :
>> DELETE from TABLE where i%4 = 0;
>>
>> As you may see that there is now 25% data deleted in each page.
>>
>> Now even if I do insert another 2500 rows (25% of original size) my
>> database size reaches 125% of the original size when I inserted the 1
>> rows initially.
>>
>> Hence there is significant space wastage. Anyway i can improve that ?
>> It would be nice if the database size would be close to the original size
>> after deleting 25% and adding some new 25% data.
>>
>> I know you would recommend to use smaller page sizes. Ideally 2KP page
>> size
>> is good but then, the number of pages is restricted to a max of 2^32 which
>> will restrict the total database size to 4TB only. I need the max size to
>> be capable of atleast 32TB.
>>
>>
>>
>> On Fri, Feb 7, 2014 at 11:14 PM, Donald Griggs <dfgri...@gmail.com>
>> wrote:
>>
>>  Can you write more about how this is causing you a problem? Most users
>>> don't experience this as a problem
>>> On Feb 7, 2014 10:30 AM, "Raheel Gupta" <raheel...@gmail.com> wrote:
>>>
>>>  SQLite's tables are B-trees, sorted by the rowid.  Your new data will
>>>>> probably get an autoincremented rowid, which will be appended at the
>>>>>
>>>> end
>>>
>>>> of the table.
>>>>>
>>>>> 

Re: [sqlite] Free Page Data usage

[Raheel Gupta]

Hi,

I use a page size of 64 KB. But my row consists of 2 columns that is :
i - Auto Increment Integer,
b - 4096 Bytes of BLOB data

Now for the sake of calculation, lets say 16 rows fit in a page and my
table has 1 rows when I start.

Now, lets say I delete some data which is not in sequence i.e. it can be
deleted as per data which is not in use. To create such a hypothetical
situation for explaining this to you, here is a simple query :
DELETE from TABLE where i%4 = 0;

As you may see that there is now 25% data deleted in each page.

Now even if I do insert another 2500 rows (25% of original size) my
database size reaches 125% of the original size when I inserted the 1
rows initially.

Hence there is significant space wastage. Anyway i can improve that ?
It would be nice if the database size would be close to the original size
after deleting 25% and adding some new 25% data.

I know you would recommend to use smaller page sizes. Ideally 2KP page size
is good but then, the number of pages is restricted to a max of 2^32 which
will restrict the total database size to 4TB only. I need the max size to
be capable of atleast 32TB.



On Fri, Feb 7, 2014 at 11:14 PM, Donald Griggs <dfgri...@gmail.com> wrote:

> Can you write more about how this is causing you a problem? Most users
> don't experience this as a problem
> On Feb 7, 2014 10:30 AM, "Raheel Gupta" <raheel...@gmail.com> wrote:
>
> > >
> > > SQLite's tables are B-trees, sorted by the rowid.  Your new data will
> > > probably get an autoincremented rowid, which will be appended at the
> end
> > > of the table.
> > >
> > > A page gets reorganized only when about 2/3 is free space.
> > >
> >
> > Anyway to make this ratio to lets say 1/3 ?
> > ___
> > 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] Free Page Data usage

[Raheel Gupta]

>
> SQLite's tables are B-trees, sorted by the rowid.  Your new data will
> probably get an autoincremented rowid, which will be appended at the end
> of the table.
>
> A page gets reorganized only when about 2/3 is free space.
>

Anyway to make this ratio to lets say 1/3 ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Free Page Data usage

[Raheel Gupta]

Hi,

My Page size is 64KB and I store around 4KB of row data in one row.
I store around 1 rows in one table and the database size reaches 42MB.

Now, I am facing a peculiar problem. When I delete just 2-3 rows, that page
is not reused for the new data which will be inserted in the future.

The pragma freelist_count shows 0 if I delete the 1st 10 rows (approx 40KB)
Only if I delete more than 20 rows does the freelist_count reflect 1 page
as free.

How should I get SQLIte to use the free space within a partially used page
when rows from that page have been deleted.

This causes a lot of space wastage when I store more rows.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hook / Trigger for Primary Key / Unique Error

[Raheel Gupta]

SCHEMA :
CREATE TABLE checksums
(i INTEGER,c VARCHAR(20) PRIMARY KEY) ;

CODE :

// Generates a Random string
QByteArray Randstr_B(int len) {
char chars[36] = {'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j',
'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y',
'z', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9'};
QByteArray str;
for (int i = 0; i < len; i++) {
str.append(chars[qrand() % 35]);
}
return str;
}

// Converts  quint64 to Little Endian bytes
QByteArray toLE(quint64 num){
unsigned char bytes[8];
bytes[0] = (unsigned char)num;
bytes[1] = (unsigned char)(num >> 8);
bytes[2] = (unsigned char)(num >> 16);
bytes[3] = (unsigned char)(num >> 24);
bytes[4] = (unsigned char)(num >> 32);
bytes[5] = (unsigned char)(num >> 40);
bytes[6] = (unsigned char)(num >> 48);
bytes[7] = (unsigned char)(num >> 56);

return QByteArray((const char *)bytes, 8);
}

insQ->prepare("INSERT INTO checksums (i, c) VALUES (?, ?)");

I then do inserts in the loop (I am using QT) :
for(quint64 i = 1; i <= 1000; i++){
// Create the data RANDOMLY
QByteArray block = Randstr_B(4088).append(toLE(i)); // "toLE" is a
function which converts quint64 to Little Endian Bytes i.e. 8 bytes. I used
this to make each block unique
QByteArray csum = QCryptographicHash::hash(block,
QCryptographicHash::Sha1);
unsigned char C = (unsigned char)csum.at(0);

// Insert into the MAP
insQ->bindValue(0, i); // insQ is a QSqlQuery *
insQ->bindValue(1, csum);
if(!insQ->exec()){
qDebug() << "Error Inserting :" << i << csum.toHex().toUpper()
<< insQ->lastError().text();
//exit(0);
}
if((i % 1) == 0){
mapconn.commit(); // Database Connection "mapconn"
Q.exec("PRAGMA wal_checkpoint;"); // Checkpointing the WAL and
"Q" is also another QSqlQuery.
mapconn.transaction(); // Restarting Transaction
qDebug() "Committed :" << i << csum.toHex() << time();
}
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hook / Trigger for Primary Key / Unique Error

[Raheel Gupta]

Sir, I tested the method of the following :
sqlite3_exec("INSERT OR IGNORE INTO ;");
if( sqlite3_changes()==0 ){
sqlite3_exec("UPDATE ...");
}
I have seen my performance degrade from 1 records / second to 1
records / 10 seconds after 3 million UNIQUE Checksums inserted. Am I doing
anything wrong ? BTW, there were only 10 CONFLICTS in 3 Million rows. So
they are all unique 20 Byte Sha1 Checksums. I am committing at 1
inserts.


On Mon, Dec 30, 2013 at 6:38 PM, Richard Hipp  wrote:

> On Mon, Dec 30, 2013 at 7:48 AM, Richard Hipp  wrote:
>
> >
> > The wordcount.c test program does the above using prepared statements and
> > exceeds 348,000 inserts+updates per second on my desktop
> >
>
> Further information:  My desktop is a relatively new and fast SSD machine.
> A 3-year-old ubuntu machine with spinning disks did over 250,000
> inserts+updates per second and a beagleboard black (
> http://beagleboard.org/products/beaglebone%20black) did just shy of 20,000
> inserts+updates per second on the same test case of 791319 insert+updates.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Hook / Trigger for Primary Key / Unique Error

[Raheel Gupta]

Hi,

I have the following tables :
CREATE TABLE checksums
(i INTEGER,c VARCHAR(16) PRIMARY KEY) ;

CREATE TABLE data
(i INTEGER, data BLOB, isUnique INTEGER)

My application needs to insert 1000s of rows/second into the data table
hence I use transactions and prepare statements.

Now is it possible that while inserting (using transactions) in the
Checksums table when there is a CONFLICT as per the column "c" I update
table "data" column isUnique with the corresponding ID "i" from table
checksums.

Something like :
INSERT INTO checksums (1000, "abcdefghijklmnop") ON CONFLICT IGNORE BUT
UPDATE data SET isUnique = OLD.checksums.i

I know the above ON CONFLICT statement is wrong but I need a similar
functionality.

Is this possible with HOOKS / triggers or anything at all ?
This will be a great feature if its there.

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

[sqlite] Functions affecting table structure ?

[Raheel Gupta]

Hi,

I am trying to add some additional data with the table structure stored in
SQLite. Everything seems to be working fine but when I do an alter query it
seems to drop the table.

I have made changes in the following functions to handle the addition of
the data:
sqlite3AlterFinishAddColumn() ,
sqlite3EndTable(),

I have made changes in the following functions to remove the changes during
runtime only (sqlite feels its a normal table)
sqlite3InitCallback(),
execExecSql()

I am trying to understand the ALTER queries and which function handles
them. Any hint would be immensely appreciated. I have wasted a day or two
trying to figure out why is the database dropping the structure when I add
a column to an existing table (modified by my code)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Raheel Gupta]

Yes, I did test SQLCipher and it slows down a bit.
Now, I would like to go with SEE if its available for the latest version.
SQLCipher is available for 3.8.0.2 while 3.8.1 is out.


On Thu, Dec 5, 2013 at 9:34 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 5 Dec 2013, at 3:02pm, Raheel Gupta <raheel...@gmail.com> wrote:
>
> > The only problem is that I dont have access to SEE by Dr. Richard. So how
> > should I test it ?
>
> Why should you test it ?  Have you tested the solutions you do have access
> to and found that they make your app too slow to use ?
>
> 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] Encryption

[Raheel Gupta]

Hi,

The only problem is that I dont have access to SEE by Dr. Richard. So how
should I test it ? Does he give test licenses ? Also I am assuming it will
always be supported by the latest version of SQLite right ?


On Thu, Dec 5, 2013 at 7:09 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 5 Dec 2013, at 9:15am, Raheel Gupta <raheel...@gmail.com> wrote:
>
> > Yes, I agree. But it should not make the inserts and read too slow as
> well.
>
> The key word here is 'too'.  If there's only 5% difference in speed
> between the two systems then it doesn't matter which one you use.
>
> So you have to write your application using one system, try it out on the
> kind of hardware you expect it to be used on, then figure out if one or
> both systems are 'too slow' for your specific use.
>
> 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] Encryption

[Raheel Gupta]

Yes, I agree. But it should not make the inserts and read too slow as well.
I just want to support 256 Bit AES.


On Thu, Dec 5, 2013 at 1:50 PM, Klaas V <klaasva...@yahoo.com> wrote:

> When you'use the word 'perfornance' you might be interested not jonly in
> speed, but in strength of protection, privacy of the employees, your
> company as a whole and above all your clients.
> One of the goals of encryption is to avoid e.g. the government(s and the
> spies they hired to peek into your own business and with this behavior
> those of - again - your clients, their clients, etcetera.
>
> There is more in the world than speed and size.
>
> On Wed, Dec 4, 2013 at 6:18 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
>
> On 4 Dec 2013, at 10:45am, Raheel Gupta <raheel...@gmail.com> wrote:
>
>
> I wanted to know which is the best in performance ?
>
>
> Season's greetings and cheers.
> Klaas `Z4us` V
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Raheel Gupta]

>> The answer may depend on what storage system you were using

7200 RPM Sata Drives

>> whether you have small or big databases,

Big which may even reach TBs of data.

>> and whether you have just one user at a time or many users at once.
Multi user. I do use WAL mode.



On Wed, Dec 4, 2013 at 6:18 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 4 Dec 2013, at 10:45am, Raheel Gupta <raheel...@gmail.com> wrote:
>
> > I wanted to know which is the best in performance ?
>
> There would probably not be a simple definite answer that applied to all
> setups.  The answer may depend on what storage system you were using,
> whether you have small or big databases, and whether you have just one user
> at a time or many users at once.
>
> 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

[sqlite] Encryption

[Raheel Gupta]

Hi,

I wanted to implement Encryption on my sqlite DB with least load on
performance.
I have come across Sqlcipher.net and SQLite Encryption Extension (By Dr.
Hipp)

I wanted to know which is the best in performance ? Has anyone tested both
of these and can provide some feedback ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

Ok. Thank you for your explanation everyone and for being patient with me.
I look forward to Sqlite4 in whatever features you implement :)


On Mon, Nov 11, 2013 at 8:17 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, Nov 11, 2013 at 1:40 AM, Raheel Gupta <raheel...@gmail.com> wrote:
>
> >
> > I guess a Row level locking could be difficult but a Page Level locking
> > could be not that difficult.
> >
>
> In an anomaly-free system, page level locking is not difficult.  The
> difficulty comes when you have to recover from an application crash (due to
> a bug or a "kill -9" or a power loss or some other cause) that occurs in
> the middle of updating the page.
>
> In a client/server database engine, you have a central server that can keep
> track of page (or row) locks efficiently.  Doing the same in a serverless
> system like SQLite is rather more difficult and (as far as I can determine)
> requires debilitating run-time overhead.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Row Level Locking as in InnoDB

[Raheel Gupta]

@simon

I guess a Row level locking could be difficult but a Page Level locking
could be not that difficult.

ATM "db level locking" :
If DB locked throw busy error
In not locked lock db, let the writer do its thing

For Page level locking (I think you could allow something like) :
Let writer write / modify pages IF not locked
ONLY If writer comes across a locked page wait for it to be released

In this way, multiple threads could do writes. Again I am not an expert but
from my little understanding this might not remove the leaness. You are the
expert.

And even I agree that Sqlite must be "lite"



On Sun, Nov 10, 2013 at 8:39 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 10 Nov 2013, at 12:05pm, Raheel Gupta <raheel...@gmail.com> wrote:
>
> >>> I can't think of any other single feature that would remove the "lite"
> >
> > I am not a database expert. If you say so, it must be the case.
> > But if there is a way to implement concurrent writers in SQLite
> maintaining
> > the "lite" in SQLite, I would be the most happiest person here :)
>
> The main reason you seem to prefer SQLite to other databases is that it's
> faster.  Adding row-level locking to SQLite would slow it down a lot.  As a
> very simplified explanation, for one SELECT instead of
>
> try to lock the database
> check to see that the lock on the database is yours
> FOR EACH ROW:
> figure out where the row's data is
> read the data
> unlock the database
>
> you have to do
>
> FOR EACH ROW:
> figure out where the row's data is
> try to lock the row
> check to see that the lock on the row is yours
> read the data
> release the row
>
> If your SELECT returns 10 rows you end up doing 50 operations instead of
> 23.  Which would mean that SQLite was half the speed, and no longer had any
> advantages for you, so you would use something else.
>
> Locking is the single hardest thing to get right when writing a DBMS.
>  SQLite gets a lot of its tininess and speed by implementing the simplest
> fastest method of locking possible.
>
> 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] Row Level Locking as in InnoDB

[Raheel Gupta]

>> Look at the performance difference between BDB and SQLite3 here
http://symas.com/mdb/microbench/#sec1

I did, and I really cant comment on that. The results are of 2012 and its
almost 2013. You should update the page with a newer result set.

>> I can't think of any other single feature that would remove the "lite"

I am not a database expert. If you say so, it must be the case.
But if there is a way to implement concurrent writers in SQLite maintaining
the "lite" in SQLite, I would be the most happiest person here :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

>
> This is the BDB SQL doc I found.
>
>
> http://docs.oracle.com/cd/E17076_02/html/bdb-sql/dbfeatures.html#bulkloading
>
> If you insert record in bulk, you can use PRAGMA TXN_BULK for optimization.
>
I tested TXN_BULK, still pretty slow. Nearly 2.4 times.
Also the space wastage is pretty high. I set a page size of 64K in this and
the space wastage is very high as the DBNAME.db-journal folder has too many
files.
DBNAME.db is 448MB
DBNAME.db-journal is 161MB.

Which is after inserting 10 rows of 4K = 400MB.

In SQLITE 3.7.17 I get only 6.69% wastage of space.
I wish BDB worked faster to see my alternatives.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

Hi,

>> You use BDB SQL or BDB KV ?
I built BDB 6.0.20 with --enable-sql_compat
It made a libsqlite3.so in the .libs folder which I linked with my QT C++
Application.

>> You must try it with SQLightning too, https://gitorious.org/mdb/
sqlightning

I tried to build it, but it says lmdb.h missing. Will check it as well.



On Fri, Nov 8, 2013 at 4:12 PM, Aris Setyawan <aris.s...@gmail.com> wrote:

> This is the BDB SQL doc I found.
>
>
> http://docs.oracle.com/cd/E17076_02/html/bdb-sql/dbfeatures.html#bulkloading
>
> If you insert record in bulk, you can use PRAGMA TXN_BULK for optimization.
>
>
>
> On 11/8/13, Aris Setyawan <aris.s...@gmail.com> wrote:
> > You use BDB SQL or BDB KV ?
> >
> > You must try it with SQLightning too,
> https://gitorious.org/mdb/sqlightning
> >
> >
> > On 11/8/13, Aris Setyawan <aris.s...@gmail.com> wrote:
> >>> For a single threaded application BDB is very bad after I tested.
> >>> It takes nearly 2.5 times the amount of time and CPU to do a
> transaction
> >>> of
> >>> 40MB Data. E.g. If SQLIte did the 40MB data transaction (1 rows of
> 4
> >>> K)
> >>> in 1 second, BDB was taking 2.5 seconds and more CPU as well. I did
> this
> >>> in
> >>> QT C++. Overall BDB SQL interface is slower than Sqlite for inserts.
> >>> That
> >>> is what I found.
> >>
> >> Have you consult this to the BDB forum?
> >> BDB doesn't have SQL parsing overhead, so it will be faster in general.
> >>
> >> On 11/8/13, Raheel Gupta <raheel...@gmail.com> wrote:
> >>>>> If you have many core of processors [and big RAM], then I recommend
> >>> BDB Sql over Sqlite. Because you can have many processes or threads to
> >>> write to a database concurrently.
> >>>
> >>> For a single threaded application BDB is very bad after I tested.
> >>> It takes nearly 2.5 times the amount of time and CPU to do a
> transaction
> >>> of
> >>> 40MB Data. E.g. If SQLIte did the 40MB data transaction (1 rows of
> 4
> >>> K)
> >>> in 1 second, BDB was taking 2.5 seconds and more CPU as well. I did
> this
> >>> in
> >>> QT C++. Overall BDB SQL interface is slower than Sqlite for inserts.
> >>> That
> >>> is what I found.
> >>>
> >>>
> >>>
> >>> On Fri, Nov 8, 2013 at 12:03 AM, Simon Slavin <slav...@bigfraud.org>
> >>> wrote:
> >>>
> >>>>
> >>>> On 7 Nov 2013, at 6:31pm, Raheel Gupta <raheel...@gmail.com> wrote:
> >>>>
> >>>> > Any idea when will SQLite4 be released as stable ?
> >>>>
> >>>> No.  It's not even feature-frozen yet, as far as we know.  And
> whenever
> >>>> it
> >>>> is, it's incredibly unlikely to have row level locking.
> >>>>
> >>>> 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
> >>>
> >>
> >
> ___
> 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] Row Level Locking as in InnoDB

[Raheel Gupta]

>> No. It's not even feature-frozen yet, as far as we know. And whenever it
is, it's incredibly unlikely to have row level locking.

Please add row-level locking if possible.


On Fri, Nov 8, 2013 at 12:03 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 7 Nov 2013, at 6:31pm, Raheel Gupta <raheel...@gmail.com> wrote:
>
> > Any idea when will SQLite4 be released as stable ?
>
> No.  It's not even feature-frozen yet, as far as we know.  And whenever it
> is, it's incredibly unlikely to have row level locking.
>
> 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] Row Level Locking as in InnoDB

[Raheel Gupta]

>> If you have many core of processors [and big RAM], then I recommend
BDB Sql over Sqlite. Because you can have many processes or threads to
write to a database concurrently.

For a single threaded application BDB is very bad after I tested.
It takes nearly 2.5 times the amount of time and CPU to do a transaction of
40MB Data. E.g. If SQLIte did the 40MB data transaction (1 rows of 4 K)
in 1 second, BDB was taking 2.5 seconds and more CPU as well. I did this in
QT C++. Overall BDB SQL interface is slower than Sqlite for inserts. That
is what I found.



On Fri, Nov 8, 2013 at 12:03 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 7 Nov 2013, at 6:31pm, Raheel Gupta <raheel...@gmail.com> wrote:
>
> > Any idea when will SQLite4 be released as stable ?
>
> No.  It's not even feature-frozen yet, as far as we know.  And whenever it
> is, it's incredibly unlikely to have row level locking.
>
> 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] Row Level Locking as in InnoDB

[Raheel Gupta]

>> SQLite4 still in development phase. It is not production ready.

Any idea when will SQLite4 be released as stable ?


On Tue, Nov 5, 2013 at 8:20 AM, Howard Chu  wrote:

> Aris Setyawan wrote:
>
>> Hi Howard,
>>
>>  I just looked, sophia is nothing special. See these microbench results.
>>> http://pastebin.com/cFK1JsCN
>>>
>>> LMDB's codebase is still smaller and faster. Nothing else touches LMDB's
>>> read
>>> speed.
>>>
>>
>> This is micro benchmark from sophia author compare with lmdb.
>> http://sphia.org/benchmarks.html
>>
>
> Quite off-topic for this list, but those results are garbage.
> https://github.com/pmwkaa/sophia_benchmark/issues/2#issuecomment-27740082
>
>
> --
>   -- Howard Chu
>   CTO, Symas Corp.   http://www.symas.com
>   Director, Highland Sun http://highlandsun.com/hyc/
>   Chief Architect, OpenLDAP  http://www.openldap.org/project/
> ___
> 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] Row Level Locking as in InnoDB

[Raheel Gupta]

>> SQLite4 still in development phase. It is not production ready.

But isnt that the same thing as BDB or Kyoto i.e. a Key Value store ?

>> If you have many core of processors [and big RAM], then I recommend
BDB Sql over Sqlite.

I have large space and around 4GB of ram with Dual Cores to Quad Cores
processors meant only for storage.

>> If you can choose DBMS, other than SQLite, try to use DB that have
storage engine optimized for write, for example LSM (hypertable),
Fractal Tree (tokudb engine for mysql).

I would be interested in a embedded DB which can give good performance i.e.
write fast with indexing and read fast as well.


On Mon, Nov 4, 2013 at 6:42 PM, Aris Setyawan <aris.s...@gmail.com> wrote:

> > Will SQLite4 be a better solution for me then ?
>
> SQLite4 still in development phase. It is not production ready.
>
> > Also @aris do you recommend BDB over Sqlite for 1-10 Billion records ?
>
> If you have many core of processors [and big RAM], then I recommend
> BDB Sql over Sqlite. Because you can have many processes or threads to
> write to a database concurrently.  Because it use row or page level
> locking.
>
> If you can choose DBMS, other than SQLite, try to use DB that have
> storage engine optimized for write, for example LSM (hypertable),
> Fractal Tree (tokudb engine for mysql).
>
> On 11/4/13, Raheel Gupta <raheel...@gmail.com> wrote:
> > Will SQLite4 be a better solution for me then ?
> >
> > Also @aris do you recommend BDB over Sqlite for 1-10 Billion records ?
> >
> >
> > On Mon, Nov 4, 2013 at 8:03 AM, Aris Setyawan <aris.s...@gmail.com>
> wrote:
> >
> >> > I just looked, sophia is nothing special. See these microbench
> results.
> >> > http://pastebin.com/cFK1JsCN
> >> >
> >> > LMDB's codebase is still smaller and faster. Nothing else touches
> >> > LMDB's
> >> > read
> >> > speed.
> >>
> >> Focus to the write number.
> >>
> >> You are using SSD or HDD?
> >>
> >> On 11/4/13, Howard Chu <h...@symas.com> wrote:
> >> > Aris Setyawan wrote:
> >> >>> SQLightning replaces the SQLite backend with Symas' LMDB, which also
> >> >>> uses
> >> >>> MVCC
> >> >>> and thus supports high concurrency. It is also many times faster
> than
> >> >>> BerkeleyDB and vanilla SQLite.
> >> >>
> >> >> Your MVCC is different compared to InnoDB or BDB locking. Every one
> >> >> should carefully read each DB's doc, then test it before decide to
> use
> >> >> it.
> >> >
> >> > Yes, it's different. In LMDB writers never block readers and readers
> >> never
> >> > block writers. The original poster was complaining about SELECT taking
> >> > a
> >> > long
> >> > time and preventing other threads from making progress. That problem
> >> doesn't
> >> >
> >> > exist in LMDB. BDB locking *might* be able to avoid this in many
> cases,
> >> if
> >> > there are no hotspots, but is prone to deadlocks which require the
> >> calling
> >> > application to retry failed requests.
> >> >
> >> >> LMDB is storage engine optimized for read. Why you don't optimize
> it's
> >> >> write using cache oblivious data structure, for example LSM tree or
> >> >> create new, like in sophia (sphia.org) key value DB?
> >> >
> >> > I just looked, sophia is nothing special. See these microbench
> results.
> >> > http://pastebin.com/cFK1JsCN
> >> >
> >> > LMDB's codebase is still smaller and faster. Nothing else touches
> >> > LMDB's
> >> > read
> >> > speed.
> >> >
> >> > --
> >> >-- Howard Chu
> >> >CTO, Symas Corp.   http://www.symas.com
> >> >Director, Highland Sun http://highlandsun.com/hyc/
> >> >Chief Architect, OpenLDAP  http://www.openldap.org/project/
> >> > ___
> >> > 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
> >
> ___
> 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] Row Level Locking as in InnoDB

[Raheel Gupta]

Will SQLite4 be a better solution for me then ?

Also @aris do you recommend BDB over Sqlite for 1-10 Billion records ?


On Mon, Nov 4, 2013 at 8:03 AM, Aris Setyawan  wrote:

> > I just looked, sophia is nothing special. See these microbench results.
> > http://pastebin.com/cFK1JsCN
> >
> > LMDB's codebase is still smaller and faster. Nothing else touches LMDB's
> > read
> > speed.
>
> Focus to the write number.
>
> You are using SSD or HDD?
>
> On 11/4/13, Howard Chu  wrote:
> > Aris Setyawan wrote:
> >>> SQLightning replaces the SQLite backend with Symas' LMDB, which also
> >>> uses
> >>> MVCC
> >>> and thus supports high concurrency. It is also many times faster than
> >>> BerkeleyDB and vanilla SQLite.
> >>
> >> Your MVCC is different compared to InnoDB or BDB locking. Every one
> >> should carefully read each DB's doc, then test it before decide to use
> >> it.
> >
> > Yes, it's different. In LMDB writers never block readers and readers
> never
> > block writers. The original poster was complaining about SELECT taking a
> > long
> > time and preventing other threads from making progress. That problem
> doesn't
> >
> > exist in LMDB. BDB locking *might* be able to avoid this in many cases,
> if
> > there are no hotspots, but is prone to deadlocks which require the
> calling
> > application to retry failed requests.
> >
> >> LMDB is storage engine optimized for read. Why you don't optimize it's
> >> write using cache oblivious data structure, for example LSM tree or
> >> create new, like in sophia (sphia.org) key value DB?
> >
> > I just looked, sophia is nothing special. See these microbench results.
> > http://pastebin.com/cFK1JsCN
> >
> > LMDB's codebase is still smaller and faster. Nothing else touches LMDB's
> > read
> > speed.
> >
> > --
> >-- Howard Chu
> >CTO, Symas Corp.   http://www.symas.com
> >Director, Highland Sun http://highlandsun.com/hyc/
> >Chief Architect, OpenLDAP  http://www.openldap.org/project/
> > ___
> > 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] Row Level Locking as in InnoDB

[Raheel Gupta]

@Howard I had tested your code earlier but it didnt seem to be stable and
getting it to run was a task. Also I learnt that it is a "in-memory"
database.

@Aris are you saying BDB is better and faster than SQLite ?


On Sun, Nov 3, 2013 at 8:28 PM, Howard Chu <h...@symas.com> wrote:

> Aris Setyawan wrote:
>
>> SQLightning replaces the SQLite backend with Symas' LMDB, which also uses
>>> MVCC
>>> and thus supports high concurrency. It is also many times faster than
>>> BerkeleyDB and vanilla SQLite.
>>>
>>
>> Your MVCC is different compared to InnoDB or BDB locking. Every one
>> should carefully read each DB's doc, then test it before decide to use
>> it.
>>
>> LMDB is storage engine optimized for read. Why you don't optimize it's
>> write using cache oblivious data structure, for example LSM tree or
>> create new, like in sophia (sphia.org) key value DB?
>>
>
> Because read optimization is what was important to us when I created LMDB.
> That's like asking why a hammer isn't a screwdriver.
>
>
>  On 11/3/13, Howard Chu <h...@symas.com> wrote:
>>
>>> Aris Setyawan wrote:
>>>
>>>> SQLite do not use row level locking, but db level locking, so it was
>>>> the right behavior the second thread was blocked.
>>>>
>>>> For innodb like in SQLite, Oracle have SQLite compatible API, but use
>>>> BDB backend.
>>>> Since BDB use MVCC (row/page level locking), your threads only blocked
>>>> if they will write in the same row/page.
>>>>
>>>> www.oracle.com/technetwork/database/berkeleydb/bdb-
>>>> sqlite-comparison-wp-176431.pdf
>>>>
>>>> * You must aware that BDB now have AGPL license.
>>>>
>>>
>>> SQLightning replaces the SQLite backend with Symas' LMDB, which also uses
>>> MVCC
>>> and thus supports high concurrency. It is also many times faster than
>>> BerkeleyDB and vanilla SQLite.
>>>
>>> https://gitorious.org/mdb/sqlightning/
>>>
>>>
>>>> On 11/3/13, Raheel Gupta <raheel...@gmail.com> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> I have been using SQLite for one project of mine and I will be storing
>>>>> TBs
>>>>> of Data.
>>>>> Now there will be a lot of selections in this database and I have come
>>>>> across one problem with SQLite.
>>>>> In journal_mode=delete the selection is database locked.
>>>>> When one thread does a "TRANSACTION" on the database and soon after
>>>>> another
>>>>> thread does "SELECT" on the database (using the same connection) or
>>>>> vice
>>>>> versa, the second thread has to wait till the first thread finishes.
>>>>>
>>>>> In order to avoid this, I had to use journal_mode=wal so that two
>>>>> threads
>>>>> dont have to wait when they both are doing SELECTs which might be
>>>>> taking
>>>>> 3-5 seconds to process.
>>>>>
>>>>> I was wondering if Row Level Locking would be introduced in
>>>>> journal_mode=delete as its there in InnoDB for MySQL. Atleast for
>>>>> selects
>>>>> or inserts Row Level rocking should be possible as neither modify the
>>>>> existing rows.
>>>>>
>>>>> journal_mode=wal is a little slower and has its own limitations over
>>>>> NFS.
>>>>>
>>>>> OR if there is a mode equivalent to innodb in SQLITE please do let me
>>>>> know.
>>>>> I need to do a lot of selects and inserts in my application and hence a
>>>>> row
>>>>> level locking is suitable vs table or database level locking.
>>>>>
>>>>
>
> --
>   -- Howard Chu
>   CTO, Symas Corp.   http://www.symas.com
>   Director, Highland Sun http://highlandsun.com/hyc/
>   Chief Architect, OpenLDAP  http://www.openldap.org/project/
> ___
> 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] Row Level Locking as in InnoDB

[Raheel Gupta]

Hi,

I have been using SQLite for one project of mine and I will be storing TBs
of Data.
Now there will be a lot of selections in this database and I have come
across one problem with SQLite.
In journal_mode=delete the selection is database locked.
When one thread does a "TRANSACTION" on the database and soon after another
thread does "SELECT" on the database (using the same connection) or vice
versa, the second thread has to wait till the first thread finishes.

In order to avoid this, I had to use journal_mode=wal so that two threads
dont have to wait when they both are doing SELECTs which might be taking
3-5 seconds to process.

I was wondering if Row Level Locking would be introduced in
journal_mode=delete as its there in InnoDB for MySQL. Atleast for selects
or inserts Row Level rocking should be possible as neither modify the
existing rows.

journal_mode=wal is a little slower and has its own limitations over NFS.

OR if there is a mode equivalent to innodb in SQLITE please do let me know.
I need to do a lot of selects and inserts in my application and hence a row
level locking is suitable vs table or database level locking.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Extra Space Required for Index ?

[Raheel Gupta]

>> B) By insisting on your email values being unique you have already
required SQLite to make up an index for the column. SQLite needs that index
so that when you do an INSERT it can quickly check to see that the value
you use doesn't already exist.
Agreed. I was just asking the general space requirements for indexing a
column.
So as mentioned in the earlier email, it is equivalent to the space of the
cell + a few additional bytes. Lets say I have a column with all rows
having a md5sum.
I wanted to create an INDEX (not unique) of the md5sum column. Would the
index also eat up 20 Bytes or more than that ?



On Wed, Oct 30, 2013 at 3:15 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 30 Oct 2013, at 8:37am, Raheel Gupta <raheel...@gmail.com> wrote:
>
> > email VARCHAR(255) UNIQUE NOT NULL
> > }
> >
> > I wanted to know if I create an INDEX for the column "email" what isg
> going
> > to be the extra space the index will occupy ?
>
> Two things:
>
> A) SQLite interprets "VARCHAR(255)" as "TEXT".  Each value does not take
> up 255 bytes of space.  The amount of space taken up varies with the length
> of the value.
>
> B) By insisting on your email values being unique you have already
> required SQLite to make up an index for the column.  SQLite needs that
> index so that when you do an INSERT it can quickly check to see that the
> value you use doesn't already exist.
>
> So creating another index on the same thing would just be a waste of space.
>
> 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

[sqlite] Extra Space Required for Index ?

[Raheel Gupta]

Hi,

I have the following Table :
CREATE TABLE users (
uid INTEGER PRIMARY KEY AUTOINCREMENT,
username VARCHAR(100) UNIQUE NOT NULL DEFAULT '',
email VARCHAR(255) UNIQUE NOT NULL
}

I wanted to know if I create an INDEX for the column "email" what isg going
to be the extra space the index will occupy ? By extra space I mean the
extra amount of disk space required by the index ?

I tried to search for any docs on this but couldnt.
This table is going to have 1 Million records and I need to save space here.
Adding an index makes the lookup very fast.

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

Re: [sqlite] SQlite Memory Leakage with 65536 Page Size

[Raheel Gupta]

Sir, is there any way to not allow malloc to hold memory ? I mean shouldnt
free(), be freeing the memory ?


On Tue, Oct 29, 2013 at 1:19 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, Oct 28, 2013 at 3:47 PM, Raheel Gupta <raheel...@gmail.com> wrote:
>
> >
> > Then I ran "Pragma shrink_memory"
> >
> > Memory Used: 152584 (max 131658680) bytes
> > Number of Outstanding Allocations:   79 (max 2081)
> >
>
> So, after running "PRAGMA shrink_memory", SQLite has reduced its memory
> holdings to 152KB in 79 separate memory allocations.If your OS says the
> process is using more memory than that, then the extra memory must be used
> by the application or else be reusable freespace still held by your
> malloc() implementation.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] SQlite Memory Leakage with 65536 Page Size

[Raheel Gupta]

Hi,

After running my query :
Memory Used: 131655360 (max 131656000) bytes
Number of Outstanding Allocations:   2079 (max 2081)
Number of Pcache Overflow Bytes: 131567752 (max 131567752) bytes
Number of Scratch Overflow Bytes:0 (max 0) bytes
Largest Allocation:  65784 bytes
Largest Pcache Allocation:   65784 bytes
Largest Scratch Allocation:  0 bytes
Lookaside Slots Used:18 (max 36)
Successful lookaside attempts:   76
Lookaside failures due to size:  19
Lookaside failures due to OOM:   0
Pager Heap Usage:131536576 bytes
Page cache hits: 2
Page cache misses:   5298
Page cache writes:   0
Schema Heap Usage:   2536 bytes
Statement Heap/Lookaside Usage:  4016 bytes
Fullscan Steps:  0
Sort Operations: 0
Autoindex Inserts:   0

Then I ran "Pragma shrink_memory"

Memory Used: 152584 (max 131658680) bytes
Number of Outstanding Allocations:   79 (max 2081)
Number of Pcache Overflow Bytes: 65536 (max 131567752) bytes
Number of Scratch Overflow Bytes:0 (max 0) bytes
Largest Allocation:  65784 bytes
Largest Pcache Allocation:   65784 bytes
Largest Scratch Allocation:  0 bytes
Lookaside Slots Used:3 (max 36)
Successful lookaside attempts:   80
Lookaside failures due to size:  22
Lookaside failures due to OOM:   0
Pager Heap Usage:66344 bytes
Page cache hits: 0
Page cache misses:   0
Page cache writes:   0
Schema Heap Usage:   2536 bytes
Statement Heap/Lookaside Usage:  1408 bytes
Fullscan Steps:  0
Sort Operations: 0
Autoindex Inserts:   0



On Mon, Oct 28, 2013 at 6:51 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, Oct 28, 2013 at 9:16 AM, Raheel Gupta <raheel...@gmail.com> wrote:
>
> > >> Whether or not free() returns that space to the operating system or
> > keeps
> > it around to satisfy future malloc() calls is a detail of the
> > implementation of free().
> >
> > Sir, anyway to be sure of that ?
> >
> > In the command-line shell, do ".stats on".  Then run your commands.
> Monitor the "Memory Used:" and "Number of Outstanding Allocations:"
> answers.  What do they tell you?
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] SQlite Memory Leakage with 65536 Page Size

[Raheel Gupta]

>> Whether or not free() returns that space to the operating system or keeps
it around to satisfy future malloc() calls is a detail of the
implementation of free().

Sir, anyway to be sure of that ?



On Mon, Oct 28, 2013 at 5:45 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Mon, Oct 28, 2013 at 8:13 AM, Raheel Gupta <raheel...@gmail.com> wrote:
>
> > >> Then you have answered your question. The amount of memory still being
> > used is the size of the cache.
> >
> > Sir, that is the PEAK usage it goes upto.
> > After "PRAGMA shrink_memory" it goes down only to 65MB, which is 1000
> Pages
> > as per 64K page sizes.
> > Shouldnt it go down to the original usage of around 2-3 MB ?
> > No matter what you do the 65MB usage is always there.
> >
>
>
> SQLite is invoking the free() library routine on the excess memory.
> Whether or not free() returns that space to the operating system or keeps
> it around to satisfy future malloc() calls is a detail of the
> implementation of free().  SQLite has no control over that.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] SQlite Memory Leakage with 65536 Page Size

[Raheel Gupta]

>> Then you have answered your question. The amount of memory still being
used is the size of the cache.

Sir, that is the PEAK usage it goes upto.
After "PRAGMA shrink_memory" it goes down only to 65MB, which is 1000 Pages
as per 64K page sizes.
Shouldnt it go down to the original usage of around 2-3 MB ?
No matter what you do the 65MB usage is always there.


On Mon, Oct 28, 2013 at 5:20 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 28 Oct 2013, at 10:45am, Raheel Gupta <raheel...@gmail.com> wrote:
>
> >>> PRAGMA cache_size
> >
> > I have set that 2000 in both cases.
>
> Then you have answered your question.  The amount of memory still being
> used is the size of the cache.
>
> 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] SQlite Memory Leakage with 65536 Page Size

[Raheel Gupta]

>>  If you multiply that by your page size do you get the amount of memory
you see being used

During peak usage the memory reaches 126 MB which is similar to 2000 x 64K.
After "PRAGMA shrink_memory" it goes down only to 65MB, which is 1000 Pages
as per 64K page sizes.



On Mon, Oct 28, 2013 at 4:15 PM, Raheel Gupta <raheel...@gmail.com> wrote:

> >> PRAGMA cache_size
>
> I have set that 2000 in both cases.
>
>
> On Sun, Oct 27, 2013 at 7:49 PM, Simon Slavin <slav...@bigfraud.org>wrote:
>
>>
>> On 27 Oct 2013, at 5:10am, Raheel Gupta <raheel...@gmail.com> wrote:
>>
>> > But why would this happen with 64K pages ? In 1024 Sqlite is able to
>> > release all the memory.
>>
>> I doubt it's releasing all the memory.  It's probably holding on to a few
>> pages.  But the pages are so small that the memory usage doesn't register.
>>
>> What's your setting for
>>
>> PRAGMA cache_size
>>
>> ?  If you multiply that by your page size do you get the amount of memory
>> you see being used ?
>>
>> 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] SQlite Memory Leakage with 65536 Page Size

[Raheel Gupta]

>> PRAGMA cache_size

I have set that 2000 in both cases.


On Sun, Oct 27, 2013 at 7:49 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 27 Oct 2013, at 5:10am, Raheel Gupta <raheel...@gmail.com> wrote:
>
> > But why would this happen with 64K pages ? In 1024 Sqlite is able to
> > release all the memory.
>
> I doubt it's releasing all the memory.  It's probably holding on to a few
> pages.  But the pages are so small that the memory usage doesn't register.
>
> What's your setting for
>
> PRAGMA cache_size
>
> ?  If you multiply that by your page size do you get the amount of memory
> you see being used ?
>
> 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] SQlite Memory Leakage with 65536 Page Size

[Raheel Gupta]

>> Whether and how you can do so will depend on what operating system you
are
using.

I am using a CentOS 6.4
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite Memory Leakage with 65536 Page Size

[Raheel Gupta]

Hi,

Yes, I tried Valgrind and it shows no leaks.
But why would this happen with 64K pages ? In 1024 Sqlite is able to
release all the memory.

>> It might also be that your memory allocator is holding onto freed memory
rather than releasing it back to the OS.

How should I free it ?



On Sun, Oct 27, 2013 at 9:46 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Sun, Oct 27, 2013 at 12:02 AM, Raheel Gupta <raheel...@gmail.com>
> wrote:
>
> > Hi,
> >
> > Sir, if you see my first email, I have already tried that. When the 15
> > Million records are being outputted, the ram usage shoots to a MAX of
> > 126MB. After the "PRAGMA shrink_memory" it goes down to 65Mb but doesnt
> go
> > below that.
> >
> >
> It might also be that your memory allocator is holding onto freed memory
> rather than releasing it back to the OS.  Have you tried running with
> valgrind to see it shows any leaks?
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] SQlite Memory Leakage with 65536 Page Size

[Raheel Gupta]

Hi,

Sir, if you see my first email, I have already tried that. When the 15
Million records are being outputted, the ram usage shoots to a MAX of
126MB. After the "PRAGMA shrink_memory" it goes down to 65Mb but doesnt go
below that.


On Sun, Oct 27, 2013 at 4:55 AM, Richard Hipp <d...@sqlite.org> wrote:

> On Sat, Oct 26, 2013 at 3:03 PM, Raheel Gupta <raheel...@gmail.com> wrote:
>
> >
> > This leads me to conclude that there is some kind of Memory Leakage when
> > the page size is 64K.
> >
> > How can I bring down the memory usage atleast when I shrink_memory after
> > the query executes.
> >
>
>
> Doubtful.  Probably the excess memory is just be used for the page cache.
>
> Did you try running "PRAGMA shrink_memory"?
> http://www.sqlite.org/pragma.html#pragma_shrink_memory - that will force
> the cache to flush.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] SQlite Memory Leakage with 65536 Page Size

[Raheel Gupta]

>> Try letting SQLite use its default for your platform.

The default when the database is created is 1024.
It works well in that page size as I have mentioned in my first email.
The issue is with 65536.
Why should there be a memory leak when the page size is 65536 ?

I have to use 65536 to enable the storing of huge amount of data.
With a page size of 65536 I can store upto 140 TB (theorotically - but I
need 8 TB for sure). When the page size is 1024 I can store a max of 2 TB.

I have found 65536 3-5% slower than the usual 1024 page size.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQlite Memory Leakage with 65536 Page Size

[Raheel Gupta]

Hi,

I am using a Page Size of 65536 and I have found the performance good
enough for me until now.
I have the database having the following table:

CREATE TABLE map (
n BIGINT NOT NULL DEFAULT 0,
s INT(5) NOT NULL DEFAULT 0,
d INT(5) NOT NULL DEFAULT 0,
c INT(1) NOT NULL DEFAULT 0,
b UNSIGNED BIGINT NOT NULL DEFAULT 0
);
CREATE INDEX map_index ON map (d, n, s, c, b);

This table has around 600 Million records.

I do the following :
root> sqlite3 my.db
sqlite> SELECT * FROM map where d = 15;

There are around 15 Million records for 'd' column with value 15.

As the rows are outputted the memory usage of sqlite shoots to 126 MB
(which I assume is 2000 pages x 64KB which is ok).

After the query is finished I run the following :
sqlite> pragma shrink_memory;

The memory drops to 65M.

I then dumped the database and re-imported it into a newer database with
page size as 1024. When running the select query the memory usage doesnt
cross 5-6 MB and shrink_memory reduces it back to near 2 MB.

I am checking the memory usage with the following :
root> top -d 1 -p `pidof sqlite3`

This leads me to conclude that there is some kind of Memory Leakage when
the page size is 64K.

How can I bring down the memory usage atleast when I shrink_memory after
the query executes.

I have tried this on SQLITE 3.8.1 and SQLITE 3.7.17

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

Re: [sqlite] SQLite keeps on searching endlessly

[Raheel Gupta]

Hi,

The index of (d,n,s) has improved the performance and is WAY better than
(n,s,d)

Thanks to everyone for helping me out.

>>  So which is better ? An Index or a Primary Key ?
My index is not unique and hence I guess going to Primary Keys would slow
down inserts quite a lot. Please correct me if I am wrong.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite keeps on searching endlessly

[Raheel Gupta]

>
> Yes, but they allow the searches to be faster.  You are making it longer
> to do INSERT but shorter to do SELECT.  Which is best for you depends on
> your purposes.
>

I need the inserts to be faster.
So which is better ? An Index or a Primary Key ?

The new INDEX that I created on your suggestion with d,n,s solves the
problem.
Now the question is over Primary key whether that will slow it down or not.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite keeps on searching endlessly

[Raheel Gupta]

Hi,

>> First, consider if some combination of those columns constitute a
primary key. That would be stronger than a simple index.
Does SQLite support multi column primary keys ?
Also wouldnt primary keys actually slow down further inserts. I have
queries to insert nearly 1 rows in one second. With larger database
multi column primary keys might slow down right ?

>> create /* covering */ index ByDS on map(d, s, n);

Creating this index in my DB. It takes time. Its too big you know.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite keeps on searching endlessly

[Raheel Gupta]

> I cannot definitely solve your problem but I can think of some things to
> try.  First, do these:
>
> ANALYZE;
> CREATE INDEX map_dsn ON map (d, s, n);
> CREATE INDEX map_dns ON map (d, n, s);
>
> then execute the same SELECT.  Does it have the same problem ?  Does the
> EXPLAIN QUERY PLAN tell you which of the (now) three indexes SQLite has
> chosen.
>
> Just in case you didn't know, you can download the SQLite command-line
> tool and execute SQL commands in it.  This means you don't have to change
> your own software.
>

My current index is actually in the correct order of my query.
I use 'n' and 's' and they are the first in the query.
I am trying your suggestion to make the d first in the index as that is
what is the limiting factor in the WHERE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite keeps on searching endlessly

[Raheel Gupta]

>> See if the situation changes if you drop all those single quotes around
your constants. Why are you comparing integer values to string literals?

Tried that and it doesnt change.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite keeps on searching endlessly

[Raheel Gupta]

Hi,

Here is the output :
0|0|0|SEARCH TABLE map USING COVERING INDEX map_index (n>?) (~4166 rows)
0|0|0|EXECUTE LIST SUBQUERY 1

I am not sure how can I optimize this ?
(Also I checked again and there are 4166 rows in this last result and not
1568 as per my last email.)

It seems to be using the the covering index which I guess is the fastest
way as Sqlite doesnt need to check the actual table. So why would it slow
down and use so much CPU in my last query ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite keeps on searching endlessly

[Raheel Gupta]

Hi,

I am facing a peculiar issue with SQLITE.
The following is my table structure :
CREATE TABLE map (
n BIGINT NOT NULL DEFAULT 0,
s INT(5) NOT NULL DEFAULT 0,
d INT(5) NOT NULL DEFAULT 0,
c INT(1) NOT NULL DEFAULT 0,
b UNSIGNED BIGINT NOT NULL DEFAULT 0
);

CREATE INDEX map_index ON map (n, s, d, c, b);

The above table is having nearly 600 Million Records and is of size 26 GB.
The column 'n' is representing Numbers of Blocks on the file system.
's' stands for Snapshot ID.
'd' is device id
'c' is not used and contains 0 all the time.
'b' is Block ID which is in another table altogether.

Now I need to retrieve the block numbers in order for lets say d = 15 and s
<= 326.
The device 15 has nearly 10 entries in the table while the remaining of
the 600 Million records belong to another device.

I retrieve the blocks in max limits of 32768 and my last query in the loop
is :

SELECT n, c, b, s FROM map WHERE s <= '326' AND s NOT IN (0) AND d = '15'
AND n >= '15591116' ORDER BY n ASC LIMIT 0, 32768

The query is executed immediately and it then starts to show the result and
the last rows are :
15731619|0|13359834|2
15731620|0|13359835|2
15731621|0|13359836|2
15731622|0|13359837|2
15731623|0|13359838|2
15731624|0|13359839|2
15731625|0|13359840|2
15731626|0|13359841|2
15731627|0|13359842|2
15731628|0|13359843|2
15731629|0|13359844|2
15731630|0|13359845|2
15731631|0|13359846|2
15731632|0|13359847|2
15731633|0|13359848|2
15731634|0|13359849|2
15731635|0|13359850|2
15731636|0|13359851|2
15731637|0|13359852|2
15731638|0|13359853|2
15731639|0|13359854|2

 After this SQLITE goes into an endless search for some reasons unknown. It
is doing something in the background (my guess its trying to search - I did
an strace) and keeps on doing it for nearly 10-15 minutes before it stops
and doesnt even give any new row after the above results.

The CPU shoots up during this and the following some of the strace (its
actually very large and I am just showing some results which show block
reads) :
lseek(3, 15185012736, SEEK_SET) = 15185012736
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
lseek(3, 15185013760, SEEK_SET) = 15185013760
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
lseek(3, 15185014784, SEEK_SET) = 15185014784
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
lseek(3, 15185015808, SEEK_SET) = 15185015808
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
lseek(3, 15185016832, SEEK_SET) = 15185016832
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024
lseek(3, 15185017856, SEEK_SET) = 15185017856
read(3,
"\n\0\0\0+\0c\0\0c\0y\0\216\0\244\0\271\0\317\0\344\0\372\1\17\1%\1:\1P"...,
1024) = 1024

Now, I dont understand why is this happening because I do have a full
fledged index for the entire table.
Also no new results are given after the whole search fiasco and the number
of results for this above query is 1568 rows.

Any ideas as to why this would be occuring.
I tried this similar query for the device which has nearly 500 Million
results and the last results of that query is pretty fast as usual.

Its for the smaller devices (e.g. with ID 15 above) the issue arises.

Any input will be appreciated.

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