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

2013-10-26 Thread 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

2013-10-26 Thread Christopher Vance 
Whether and how you can do so will depend on what operating system you are
using.


On 27 October 2013 16:10, Raheel Gupta  wrote:

> 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  wrote:
>
> > On Sun, Oct 27, 2013 at 12:02 AM, Raheel Gupta 
> > 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
>



-- 
Christopher Vance
___
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

2013-10-26 Thread 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  wrote:

> On Sun, Oct 27, 2013 at 12:02 AM, Raheel Gupta 
> 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

[sqlite] System.Data.SQLite version 1.0.89.0 released

2013-10-26 Thread Joe Mistachkin 

System.Data.SQLite version 1.0.89.0 (with SQLite 3.8.1) is now available on
the System.Data.SQLite website:

 http://system.data.sqlite.org/

Further information about this release can be seen at

 http://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki

Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you
encounter any problems with this release.

--
Joe Mistachkin

___
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

2013-10-26 Thread Richard Hipp 
On Sun, Oct 27, 2013 at 12:02 AM, Raheel Gupta  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

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

2013-10-26 Thread 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  wrote:

> On Sat, Oct 26, 2013 at 3:03 PM, Raheel Gupta  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

2013-10-26 Thread 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

Re: [sqlite] incredibly minor documentation bug

2013-10-26 Thread Richard Hipp 
On Sat, Oct 26, 2013 at 12:30 PM, George Collins  wrote:

>
> "If X is the integer -9223372036854775807 then abs(X) throws an integer
> overflow error since there is no equivalent positive 64-bit two complement
> value."
>
> It's off by one: X is actually -9223372036854775808. -9223372036854775807
> is the lowest number *with* a positive 64-bit two complement.
>

Fixed at http://www.sqlite.org/docsrc/info/9e1d78e903 - the change will be
in the next release.  Tnx.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tool for extracting deleted data from unvacuumed SQLite files

2013-10-26 Thread Paul L Daniels 
Hello everyone,

I've released v0.4 of Undark last night.  Improves on the
extraction of data out of the freeblocks as well as now
allowing for blob dumps that extend beyond a single page.

Also added an option to let you specify the DB page size for
when trying to recover data from corrupted SQLite databases.

At the moment you still have to run Undark in two passes if you
want to extract both the normal payload data and the freespace
data, hoping to merge this in 0.5.

http://pldaniels.com/undark

Regards, Paul.

-- 
Computer Repairs for Charters towers - http://ctpc.biz
A.B.N. 19 500 721 806
___
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

2013-10-26 Thread Richard Hipp 
On Sat, Oct 26, 2013 at 3:03 PM, Raheel Gupta  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

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

2013-10-26 Thread Simon Slavin 

On 26 Oct 2013, at 8:03pm, Raheel Gupta  wrote:

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

You stop using such big pages.

Try letting SQLite use its default for your platform.  Do an export and 
reimport without using any PRAGMAs at all.  Then test your SELECT (or all the 
SELECTs which give you problems).  Are they much slower than the version where 
you set pagesizes yourself ?

Simon.
___
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

2013-10-26 Thread 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

2013-10-26 Thread 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] possible join bug/regression with sqlite-3.8.1

2013-10-26 Thread Ferdinand Hübner 
On Sat, Oct 26, 2013 at 6:17 PM, Richard Hipp  wrote:
>
> We are looking into the problem.
>
> Meanwhile, I noticed some rather egregious inefficiencies in the XBMC
> schema and would like to contract the XBMC developers about this.  I spent
> 5 minutes clicking around on various XBMC websites trying to find an email
> address or other means of contact, with no success.  Are you able put us
> (the SQLite developers) in touch with the XBMC developers?  You can have
> the XBMC developers send me direct email to the address shown on my
> signature line below?  Tnx.

Thank you. I asked the developer on the bug tracker to get in touch with you.
I think the most efficient way to get in touch with an xbmc developer
would be opening an issue on their bug tracker.

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

[sqlite] incredibly minor documentation bug

2013-10-26 Thread George Collins 
Don't ask me what use case made me notice this, but the 
http://www.sqlite.org/lang_corefunc.html documentation for abs(), as of 12:29PM 
EDT on 10/26/2013, is only about 99.89157978275145% accurate.

"If X is the integer -9223372036854775807 then abs(X) throws an integer 
overflow error since there is no equivalent positive 64-bit two complement 
value."

It's off by one: X is actually -9223372036854775808. -9223372036854775807 is 
the lowest number *with* a positive 64-bit two complement.

On Win7 64-bit:

sqlite> .version
SQLite 3.8.1 2013-10-17 12:57:35 c78be6d786c19073b3a6730dfe3fb1be54f5657a
sqlite> SELECT ABS(-9223372036854775807); --docs claim will throw error; doesn't
9223372036854775807
sqlite> SELECT ABS(-9223372036854775808); --does throw error
Error: integer overflow
sqlite> SELECT ABS(-9223372036854775809); --now we're in floating point
9.22337203685478e+18

Happy to put my 0.0010842021724855 cents in,
GC
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible join bug/regression with sqlite-3.8.1

2013-10-26 Thread Richard Hipp 
On Sat, Oct 26, 2013 at 11:17 AM, Ferdinand Hübner <
ferdinand.hueb...@gmail.com> wrote:

>
> Please note that I'm just an xbmc user, not a developer.
>

We are looking into the problem.

Meanwhile, I noticed some rather egregious inefficiencies in the XBMC
schema and would like to contract the XBMC developers about this.  I spent
5 minutes clicking around on various XBMC websites trying to find an email
address or other means of contact, with no success.  Are you able put us
(the SQLite developers) in touch with the XBMC developers?  You can have
the XBMC developers send me direct email to the address shown on my
signature line below?  Tnx.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] possible join bug/regression with sqlite-3.8.1

2013-10-26 Thread Simon Slavin 

On 26 Oct 2013, at 4:17pm, Ferdinand Hübner  wrote:

> If you need any more information, don't hesitate to ask.

Ignoring the fact that two different versions of SQLite give different results, 
for a minute, can you answer these questions:

Is one of these results clearly right and the other clearly wrong, or could you 
make arguments for either one ?

If you remove the redundant JOIN (and anything else you feel is redundant) is 
the result you get in the current version of SQLite correct ?

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

[sqlite] possible join bug/regression with sqlite-3.8.1

2013-10-26 Thread Ferdinand Hübner 
Hello,

A query against the xbmc database doesn't return results anymore when
sqlite-3.8.1 is used. With sqlite-3.8.0.2, the query returns results
against the identical database just fine.
Please note that I'm just an xbmc user, not a developer. It's been quite a
while since I last posted to a mailing list, I hope the gmail web client
doesn't violate netiquette.

You can find a dump of a minimal database to reproduce the problem here:
https://gist.github.com/ferdinandhuebner/d0f3aad4b74e38940c3e
The query that doesn't return any rows:
https://gist.github.com/ferdinandhuebner/1bb14de6a58699afbebe

You can find a bug report on xbmc's trac: http://trac.xbmc.org/ticket/14646

To summarize what we figured out so far:
- The problem with the query seems to be the join on the "files" table
- The join on the "files" table is redundant because it is already joined
in the view "episodeview"
- If you replace the join with a left outer join the query returns results
and the aggregate on files.playCount is not zero
- If you join with a cast to integer the query returns results even though
the datatypes are already of type integer

If you need any more information, don't hesitate to ask.

Thank you for your help,
Ferdinand
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users