Major memory leak

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Major memory leak

CedricCicada
Greetings!

I must be doing something wrong.  I've got a simple table with three
columns, a key column, a value column and a timestamp column.  There
are 357,000 rows.  The timestamps are stored as floating-point numbers
(Julian dates), and the other two fields contain integers.  I open the
table, read one record, and close it.  If I do not sort the data,
there is no memory loss.  Here's the query:

select datetime(value_timestamp) AS latest_time from trend_data

If I sort the data and ask for only the first record, I leak over 2
megabytes of data.  Here's the query:

select datetime(value_timestamp) AS latest_time from trend_data  order
by value_timestamp desc limit 1

I got the same result when the query used the max() function instead
of ordering the recordset.

My program use sqlite3_prepare16_v2, followed by sqlite3_step,
followed by sqlite3_finalize.

Is there something I've left out?  The application in which these
calls are made is designed to be started and left alone.  At one
point, I checked TaskManager on the customer's computer and found that
one instance of this program was using over 950 megabytes of memory!
At that point, I advised the customer to restart the program once a
day, but I'd really like to be able to tell them they don't have to do
that any more.

Thanks very much!

RobR
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Major memory leak

ken-33
It might be helpful to include the version of sqlite.

Have you run your code through a memory analysis routine such as valgrind, to validate that the leak is not occuring in your application code?


HTH,
Ken


Rob Richardson <[hidden email]> wrote: Greetings!

I must be doing something wrong.  I've got a simple table with three
columns, a key column, a value column and a timestamp column.  There
are 357,000 rows.  The timestamps are stored as floating-point numbers
(Julian dates), and the other two fields contain integers.  I open the
table, read one record, and close it.  If I do not sort the data,
there is no memory loss.  Here's the query:

select datetime(value_timestamp) AS latest_time from trend_data

If I sort the data and ask for only the first record, I leak over 2
megabytes of data.  Here's the query:

select datetime(value_timestamp) AS latest_time from trend_data  order
by value_timestamp desc limit 1

I got the same result when the query used the max() function instead
of ordering the recordset.

My program use sqlite3_prepare16_v2, followed by sqlite3_step,
followed by sqlite3_finalize.

Is there something I've left out?  The application in which these
calls are made is designed to be started and left alone.  At one
point, I checked TaskManager on the customer's computer and found that
one instance of this program was using over 950 megabytes of memory!
At that point, I advised the customer to restart the program once a
day, but I'd really like to be able to tell them they don't have to do
that any more.

Thanks very much!

RobR
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Major memory leak

CedricCicada
My SQLite library is built from the single translation unit
sqlite.c/sqlite.h.  That file contains the version number 3.3.17.

I do not have valgrind, but circumstantial evidence that this is a
SQLite problem is strong.  When stepping through my code, I see that
my application's memory jumps by over 2.5 megabytes when the
sqlite3_step() method is called when using either the sorted query or
the query using max().  The unsorted query doesn't show any memory
jump.  Also, the difference in memory consumption before this part of
the code is executed and after it is left is the same size as the jump
in memory when sqlite3_step() is called.

RobR


On 3/20/08, Ken <[hidden email]> wrote:
> It might be helpful to include the version of sqlite.
>
> Have you run your code through a memory analysis routine such as valgrind, to validate that the leak is not occuring in your application code?
>
>
> HTH,
> Ken
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Major memory leak

Christian Smith-4
On Fri, Mar 21, 2008 at 10:41:10AM -0400, Rob Richardson wrote:

> My SQLite library is built from the single translation unit
> sqlite.c/sqlite.h.  That file contains the version number 3.3.17.
>
> I do not have valgrind, but circumstantial evidence that this is a
> SQLite problem is strong.  When stepping through my code, I see that
> my application's memory jumps by over 2.5 megabytes when the
> sqlite3_step() method is called when using either the sorted query or
> the query using max().  The unsorted query doesn't show any memory
> jump.  Also, the difference in memory consumption before this part of
> the code is executed and after it is left is the same size as the jump
> in memory when sqlite3_step() is called.


When doing a sorted query, the result set is formed in a temporary database
somewhere defined by the environment. In your case, it sounds like the
temporary database is memory based. Once the result set is done with, SQLite
may return the memory to the OS using free, but that will show under the
process's virtual memory footprint.

You can tell SQLite to use a disk based temporary database using:
http://sqlite.org/pragma.html#pragma_temp_store

Using this, your memory usage will probably be more stable.

However, this certainly isn't a memory leak.


>
> RobR
>

Christian
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Major memory leak

CedricCicada
I'm thinking whether this is a memory leak or not sort of depends on
your definition.  If a process is designed to remain open for long
periods of time with little activity, and it ends up taking up 1
gigabyte of memory, that looks an awful lot like a leak to me.  There
are likely to be at least three instances of this application running,
and after they all run for a month, they're likely to be consuming 5
gigabytes of memory.  This is not acceptable.  If SQLite's sorted
query is taking up 2.5 megabytes of memory every time this piece of
the application is invoked, I need to know how to ensure that that
memory is released.

Here's a brief description of the application.  My company, Rad-Con,
Inc., is a major supplier of annealing furnaces and related equipment
and software to metal processors worldwide.  The application monitors
the annealing process on a customer's site.  There could be well over
a hundred annealing bases.  The applicaton's first screen displays an
overview of all of the bases, whether they have furnaces, if the
furnaces are turned on, and so on.  A user can double-click on base to
see details.  A button on the detail screen calls up a trend display.
Trend data is stored in SQLite database files, one per base.  The
application executes the query I described to find when the last row
was written to the table, and uses that to calculate the times that
will be displayed on the graph.  Then, the application reads the
entire table and plots the data.  When the user is finished, he closes
the trend screen.  My requirement is to ensure that the amount of
memory allocated to my application before the trend screen is
displayed and after the trend screen is closed is the same.  If more
memory is allocated after it is closed, that is a leak, by my
definition.


RobR


On 3/23/08, Christian Smith <[hidden email]> wrote:

> On Fri, Mar 21, 2008 at 10:41:10AM -0400, Rob Richardson wrote:
> > My SQLite library is built from the single translation unit
> > sqlite.c/sqlite.h.  That file contains the version number 3.3.17.
> >
> > I do not have valgrind, but circumstantial evidence that this is a
> > SQLite problem is strong.  When stepping through my code, I see that
> > my application's memory jumps by over 2.5 megabytes when the
> > sqlite3_step() method is called when using either the sorted query or
> > the query using max().  The unsorted query doesn't show any memory
> > jump.  Also, the difference in memory consumption before this part of
> > the code is executed and after it is left is the same size as the jump
> > in memory when sqlite3_step() is called.
>
>
> When doing a sorted query, the result set is formed in a temporary database
> somewhere defined by the environment. In your case, it sounds like the
> temporary database is memory based. Once the result set is done with, SQLite
> may return the memory to the OS using free, but that will show under the
> process's virtual memory footprint.
>
> You can tell SQLite to use a disk based temporary database using:
> http://sqlite.org/pragma.html#pragma_temp_store
>
> Using this, your memory usage will probably be more stable.
>
> However, this certainly isn't a memory leak.
>
>
> >
> > RobR
> >
>
> Christian
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


--
Please do not copy or forward this message or any attachments without
my permission.  Remember, asking permission is a great way to get me
to visit your site!
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Lit

D. Richard Hipp
"Rob Richardson" <[hidden email]> wrote:

> I'm thinking whether this is a memory leak or not sort of depends on
> your definition.  If a process is designed to remain open for long
> periods of time with little activity, and it ends up taking up 1
> gigabyte of memory, that looks an awful lot like a leak to me.  There
> are likely to be at least three instances of this application running,
> and after they all run for a month, they're likely to be consuming 5
> gigabytes of memory.  This is not acceptable.  If SQLite's sorted
> query is taking up 2.5 megabytes of memory every time this piece of
> the application is invoked, I need to know how to ensure that that
> memory is released.

The test harness for SQLite counts the number of mallocs and frees
and prints an error if the two do not match.  This happens every
time we run a test.  We also run the test suite through valgrind
prior to each release. The test scripts provide close to 100% test
coverage.  About 70% of the SQLite source code is devoted to testing.

Because of the extensive testing outlined above, memory leaks in
SQLite have never been a problem.  Historically, whenever somebody
comes forward with claims of memory leaks in SQLite, it generally
works out to be memory leaks in their application.

Based on prior experience and our extensive tests, claims that
SQLite leaks 2.5MB of RAM every time it sorts are generally met
with great skepticism.  I won't say that it is impossible that
SQLite is leaking memory, though it does seem unlikely.  Certainly
the claim that SQLite leaks 2.5MB on every sort is an extraordinary
one, and extradinary claims require extraordinary proof.

If you would like to submit a script or a short program that
appears to cause SQLite to leak memory, we will be happy to
look into the problem.  But until I see an actual demonstration
of the problem, I'm going to assume that the problem is really
outside of SQLite and pay the matter no further attention.

--
D. Richard Hipp <[hidden email]>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Major memory leak

Scott Hess
In reply to this post by CedricCicada
If the maximum memory footprint is too large, then you should arrange
to have a smaller memory footprint.  For instance, you can use PRAGMA
cache_size to reduce the footprint there, use PRAGMA temp_store to
make sure you aren't storing temporary tables in memory, call
sqlite3_release_memory() to release memory if it's using too much, use
sqlite3_soft_heap_limit() to provide a soft limit on the footprint,
use sqlite3_enable_shared_cache() to let your different threads share
memory.

"Memory leak" is a pretty specific thing, it means that the program no
longer references memory in a way that will allow it to release the
memory.  You mention that the memory footprint is too large - it would
be really helpful if you put up a database and query which
demonstrated what you're describing, and the constraints you _wish_
things to operate under.  Then people can make specific
recommendations.

-scott


On Mon, Mar 24, 2008 at 2:34 PM, Rob Richardson <[hidden email]> wrote:

>  I'm thinking whether this is a memory leak or not sort of depends on
>  your definition.  If a process is designed to remain open for long
>  periods of time with little activity, and it ends up taking up 1
>  gigabyte of memory, that looks an awful lot like a leak to me.  There
>  are likely to be at least three instances of this application running,
>  and after they all run for a month, they're likely to be consuming 5
>  gigabytes of memory.  This is not acceptable.  If SQLite's sorted
>  query is taking up 2.5 megabytes of memory every time this piece of
>  the application is invoked, I need to know how to ensure that that
>  memory is released.
>
>  Here's a brief description of the application.  My company, Rad-Con,
>  Inc., is a major supplier of annealing furnaces and related equipment
>  and software to metal processors worldwide.  The application monitors
>  the annealing process on a customer's site.  There could be well over
>  a hundred annealing bases.  The applicaton's first screen displays an
>  overview of all of the bases, whether they have furnaces, if the
>  furnaces are turned on, and so on.  A user can double-click on base to
>  see details.  A button on the detail screen calls up a trend display.
>  Trend data is stored in SQLite database files, one per base.  The
>  application executes the query I described to find when the last row
>  was written to the table, and uses that to calculate the times that
>  will be displayed on the graph.  Then, the application reads the
>  entire table and plots the data.  When the user is finished, he closes
>  the trend screen.  My requirement is to ensure that the amount of
>  memory allocated to my application before the trend screen is
>  displayed and after the trend screen is closed is the same.  If more
>  memory is allocated after it is closed, that is a leak, by my
>  definition.
>
>
>  RobR
>
>
>
>
>  On 3/23/08, Christian Smith <[hidden email]> wrote:
>  > On Fri, Mar 21, 2008 at 10:41:10AM -0400, Rob Richardson wrote:
>  > > My SQLite library is built from the single translation unit
>  > > sqlite.c/sqlite.h.  That file contains the version number 3.3.17.
>  > >
>  > > I do not have valgrind, but circumstantial evidence that this is a
>  > > SQLite problem is strong.  When stepping through my code, I see that
>  > > my application's memory jumps by over 2.5 megabytes when the
>  > > sqlite3_step() method is called when using either the sorted query or
>  > > the query using max().  The unsorted query doesn't show any memory
>  > > jump.  Also, the difference in memory consumption before this part of
>  > > the code is executed and after it is left is the same size as the jump
>  > > in memory when sqlite3_step() is called.
>  >
>  >
>  > When doing a sorted query, the result set is formed in a temporary database
>  > somewhere defined by the environment. In your case, it sounds like the
>  > temporary database is memory based. Once the result set is done with, SQLite
>  > may return the memory to the OS using free, but that will show under the
>  > process's virtual memory footprint.
>  >
>  > You can tell SQLite to use a disk based temporary database using:
>  > http://sqlite.org/pragma.html#pragma_temp_store
>  >
>  > Using this, your memory usage will probably be more stable.
>  >
>  > However, this certainly isn't a memory leak.
>  >
>  >
>  > >
>  > > RobR
>  > >
>  >
>  > Christian
>  > _______________________________________________
>  > sqlite-users mailing list
>  > [hidden email]
>  > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>  >
>
>
>  --
>  Please do not copy or forward this message or any attachments without
>  my permission.  Remember, asking permission is a great way to get me
>  to visit your site!
>
>
> _______________________________________________
>  sqlite-users mailing list
>  [hidden email]
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Major memory leak

Dimitri-9
In reply to this post by CedricCicada
Hi,

> I'm thinking whether this is a memory leak or not sort of depends on
> your definition.  If a process is designed to remain open for long
> periods of time with little activity, and it ends up taking up 1
> gigabyte of memory, that looks an awful lot like a leak to me.  There
> are likely to be at least three instances of this application running,
> and after they all run for a month, they're likely to be consuming 5
> gigabytes of memory.  This is not acceptable.  If SQLite's sorted
> query is taking up 2.5 megabytes of memory every time this piece of
> the application is invoked, I need to know how to ensure that that
> memory is released.

Most probably SQLite does release malloc'ed memory using free(). Note however
that free() merely notifies the C runtime the free'd piece of memory is not
used anymore. The C runtime does not necessarily release this piece of memory
to the system. That would be inefficient. As a result, the process appears to
be still using the memory. That's why tools such as 'top' on Unix are not
necessarily appropriate to detect memory leaks, they show memory still being
used by a process, although the program has called free(). The C runtime might
give memory back to the system when the system is short on memory, or unused
memory may be swapped to disk. This is a C runtime issue, not an SQLite issue.

As already explained, it could indeed be that the memory footprint is a
problem for you, but a memory footprint problem is not a memory leak:
        http://en.wikipedia.org/wiki/Memory_leak

--
Dimitri
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users