Re: [sqlite] excessive malloc() calls
Le mardi 11 janvier 2005 à 08:13 -0500, Steve Frierdich a écrit : > Thanks Richard. We are using Sqlite on some military projects, and its > on the WinCE I am most concern on using Sqlite. I would similarly be concerned about using WinCE in military projects :-) ooops D.Morel signature.asc Description: Ceci est une partie de message =?ISO-8859-1?Q?num=E9riquement?= =?ISO-8859-1?Q?_sign=E9e?=
Re: [sqlite] excessive malloc() calls
Thanks Richard. We are using Sqlite on some military projects, and its on the WinCE I am most concern on using Sqlite. Steve D. Richard Hipp wrote: Steve Frierdich wrote: I have been noticing all the email messages about excessive malloc calls. Is there a serious bug in Sqlite about malloc being called excessively causing memory leaks in sqlite version 3? And if there is, is there a way to fix it the source code? There are no memory leaks in SQLite version 3 that I am aware of. The automated test suite makes an extensive search for memory leaks using an instrumented version of malloc and reports that there are none. There are no serious bugs in SQLite version 3 that I am aware of. The ORDER BY clause might cause more malloc()s to be done than are strictly necessary. But on a system with a good malloc implementation, that will normally go unnoticed. Nevertheless, we will be fixing the problem.
Re: [sqlite] excessive malloc() calls
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
~From what I can tell people are just in shock and awe that checking 3000
tables each holding several years of data for a company (again: several
years of data for 3000 different companies) calls malloc() several
million times.
Interesting enough, somebody came up with a hackish solution that could
probably be written to be more clean.
Matthew Arrington gives the below code:
#define SQLITE_WORK_BUFF_SIZE (128*1024) // make power of 2
#define SQLITE_WORK_BUFF_MASK (SQLITE_WORK_BUFF_SIZE-1)
char sqlite_workBuff[SQLITE_WORK_BUFF_SIZE];
int sqlite_writeIdx=0;
void *SQLITE_ALLOC(int nBytes)
{
~ void *ret;
~ sqlite_writeIdx = (sqlite_writeIdx + nBytes) & SQLITE_WORK_BUFF_MASK;
~ ret = sqlite_workBuff + sqlite_writeIdx;
~ sqlite_writeIdx+=nBytes;
~ return ret;
}
this idea could take being expanded on; as is it does leave room for
many screw-ups and hardcore memory corruption, especially in threaded
environments.
Steve Frierdich wrote:
| I have been noticing all the email messages about excessive malloc
| calls. Is there a serious bug in Sqlite about malloc being called
| excessively causing memory leaks in sqlite version 3? And if there is,
| is there a way to fix it the source code?
|
| Thank
|
| Steve
|
| D. Richard Hipp wrote:
|
|> Andrew Shakinovsky wrote:
|>
|>> I have noticed with SQLite (at least this was true with 2.8x, not
|>> sure about
|>> 3x) that if you try to use an ORDER BY with a table that doesn't have an
|>> index on the field you are ORDERing by, it will do the entire sort
|>> (presumably just the keys) in memory. This will cause you to run out of
|>> memory if the table is too large.
|>>
|>
|> This is also true of version 3.0 and (the soon to be released) version
|> 3.1. I believe this constraint is documented somewhere, though I
|> cannot say where right off hand. Somebody please correct me (and
|> submit documentation patches) if I am wrong.
|>
|>
|
- --
All content of all messages exchanged herein are left in the
Public Domain, unless otherwise explicitly stated.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.0 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFB43M3hDd4aOud5P8RApMzAJ4+qkchPTbM4CF9DWrIblE4AJHLRACffZON
mc8txoELVoMtnqph6G2+jX4=
=KoXS
-END PGP SIGNATURE-
Re: [sqlite] excessive malloc() calls
Steve Frierdich wrote: I have been noticing all the email messages about excessive malloc calls. Is there a serious bug in Sqlite about malloc being called excessively causing memory leaks in sqlite version 3? And if there is, is there a way to fix it the source code? There are no memory leaks in SQLite version 3 that I am aware of. The automated test suite makes an extensive search for memory leaks using an instrumented version of malloc and reports that there are none. There are no serious bugs in SQLite version 3 that I am aware of. The ORDER BY clause might cause more malloc()s to be done than are strictly necessary. But on a system with a good malloc implementation, that will normally go unnoticed. Nevertheless, we will be fixing the problem. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
> SELECT x, y, z FROM table1 ORDER BY 2; > SELECT x, y, z FROM table1 ORDER BY y; > ... > This is a standard SQL thing, apparently. It surprised me too > when I first found out about it (and had to fix SQLite to do it.) Using an integer is generally deprecated. It was left in the std to avoid breaking old code. Aside from theoretical objections, consider potential practical problems, e.g., suppose you write a bunch of code using 'order by 5' and then someone decides to drop column 4. Regards, Kurt Welgehausen
Re: [sqlite] excessive malloc() calls
I have been noticing all the email messages about excessive malloc calls. Is there a serious bug in Sqlite about malloc being called excessively causing memory leaks in sqlite version 3? And if there is, is there a way to fix it the source code? Thank Steve D. Richard Hipp wrote: Andrew Shakinovsky wrote: I have noticed with SQLite (at least this was true with 2.8x, not sure about 3x) that if you try to use an ORDER BY with a table that doesn't have an index on the field you are ORDERing by, it will do the entire sort (presumably just the keys) in memory. This will cause you to run out of memory if the table is too large. This is also true of version 3.0 and (the soon to be released) version 3.1. I believe this constraint is documented somewhere, though I cannot say where right off hand. Somebody please correct me (and submit documentation patches) if I am wrong.
Re: [sqlite] excessive malloc() calls
On Mon, 10 Jan 2005 17:07:11 -0500, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > This is a standard SQL thing, apparently. It surprised me too > when I first found out about it (and had to fix SQLite to do it.) Very interesting. Thanks.
Re: [sqlite] excessive malloc() calls
Tom,
Yes I agree. It does make a lot of assumptions. I would not consider using
that code permanently without knowing exactly the way the system worked,
and then I'd add error checking code that would detect misuse and/or
overflow at run time. We use this type of allocation scheme for real time
rendering where I work without any problems.
I think the system would benefit greatly from using a simple memory
allocation scheme instead of the brute force malloc() method when it needs
temporary storage during a prepare(), step(), and finalize() sequence.
Matt
Tom
<[EMAIL PROTECTED]
> To
sqlite-users@sqlite.org
01/10/2005 01:39 cc
PM
Subject
Re: [sqlite] excessive malloc()
Please respond to calls
[EMAIL PROTECTED]
te.org
I am sorry, typing too fast I had made typos which made my message not
understandable. So here it is again:
I would say it is one of the most dangerous code snippets I have seen
in a while.
The code makes a lot of assumptions but they are NOT explicitly stated.
It may work under some strictly defined conditions but after several
months you or anyone else modifying the code may forget what those
conditions were and it would lead to a memory corrupting bug which
would be very hard to trace.
Tom
Abracode
On Jan 10, 2005, at 3:15 PM, Tom wrote:
> I would say it is one of the most dangerous code snippets I have seen
> in a while.
> The code makes a lot of assumption but they are explicitly stated. It
> may work under some strictly defined conditions but after several
> months you or anyone else modifying may forget what those conditions
> were and it would lead to a memory corrupting bug which would be very
> hard to trace.
>
> Tom
> Abracode
>
> On Jan 10, 2005, at 2:39 PM, [EMAIL PROTECTED] wrote:
>
>> Windows XP, visual studio .NET.
>> The malloc call ends up calling RtlAllocateHeap().
>>
>> I hacked the code to allocate the aType structure from a circular ring
>> buffer. This small change made my app go from spending 60% of its
>> time in
>> malloc to about 3%.
>>
>> Here's the allocator I hacked in. No freeing is needed, the buffer
>> just
>> wraps. This assumes that by the time a wrap occurs, memory perviously
>> allocated at the top is no longer needed. It seems to work ok from my
>> limited testing. :-)
>>
>> #define SQLITE_WORK_BUFF_SIZE (128*1024) // make power of 2
>> #define SQLITE_WORK_BUFF_MASK (SQLITE_WORK_BUFF_SIZE-1)
>> char sqlite_workBuff[SQLITE_WORK_BUFF_SIZE];
>> int sqlite_writeIdx=0;
>>
>> void *SQLITE_ALLOC(int nBytes)
>> {
>> void *ret;
>> sqlite_writeIdx = (sqlite_writeIdx + nBytes) &
>> SQLITE_WORK_BUFF_MASK;
>> ret = sqlite_workBuff + sqlite_writeIdx;
>> sqlite_writeIdx+=nBytes;
>> return ret;
>>
>> }
>>
>> Matthew Arrington
>> Sony Computer Entertainment America
>
>
>
Re: [sqlite] excessive malloc() calls
Will Leshner wrote: On Mon, 10 Jan 2005 13:49:21 -0800, [EMAIL PROTECTED] By the way, "ORDER BY 1" I believe to mean order by column id = 1. So it's not really a meaningless statement. Is that true? I don't get the from the documentation, but I'm probably just looking at it wrong. Yes. You can put integer constants in an ORDER BY or GROUP BY clause and they mean "use the N-th column of the result set". So SELECT x, y, z FROM table1 ORDER BY 2; SELECT x, y, z FROM table1 ORDER BY y; are equivalent. In fact, the first is transformed into the second as part of the compilation process. This is a standard SQL thing, apparently. It surprised me too when I first found out about it (and had to fix SQLite to do it.) -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
Derrell Thanks for that! I did not have the primary key set up right. (I'm an SQL newbie) which forced me to use "order by." By the way, "ORDER BY 1" I believe to mean order by column id = 1. So it's not really a meaningless statement. I think any "order by clause" is going to cause a per row memory allocation. Matt [EMAIL PROTECTED] wiredUniverse.com To 01/10/2005 11:24 sqlite-users@sqlite.org AM cc Subject Please respond to Re: [sqlite] excessive malloc() [EMAIL PROTECTED] calls te.org [EMAIL PROTECTED] writes: > I switched my application over to SQLite3 and did some performance > profiling and found that the majority of the processing time spent is > making calls to malloc(). > > sqlite3_step() is the function that is making all the excessive calls, one > call per row fetched. > > The program is a stock scanning / data mining program. It keeps about 6-7 > years worth of daily stock price data for a company in a table, and I have > about 3000 of these tables. One complete scan of all 3000 companies will > make roughly 5.5 million malloc() calls! > > I create each table using SQL command: > CREATE TABLE 'SPY' (key INTEGER PRIMARYKEY UNIQUE, open FLOAT, low FLOAT, > high FLOAT, close FLOAT, volume FLOAT) Firstly, if "key" is your primary key, you need not specify UNIQUE because primary keys are, by definition, unique. Secondly, I don't know if sqlite understands "PRIMARYKEY". The more standard way of specifying that is with a space in it: "PRIMARY KEY". You can confirm whether sqlite understood this to be your primary key field by doing the following from the sqlite shell: .mode line pragma table_info(SPY); If the "pk" field shows as '1', then it knows it's a primary key; otherwise it does not. > When I read a table I use the following SQL select statement. > SELECT * FROM 'SPY' ORDER BY 1 ASC "ORDER BY 1" is probably causing an additional calculation for each row, in order to sort by whatever that means (which is probably nothing). If you discard the ORDER BY clause, the results will be in primary key order. You could be explicit about it by saying "ORDER BY key" (with or without the ASC indication, since that's the default). > I debugged / stepped in to the step() code and noticed that it is the op > code COLUMN making the memory allocation. I think it may be my ORDER BY > clause that is causing it to take the path to the malloc() call. Without looking in more detail at the code, that would be a reasonable supposition. It probably allocates memory during each step() call, to contain the field which will be sorted by. I suspect that in your query, it's allocating space to put the constant value "1"... a not very useful piece of information to be allocating for and saving. Cheers, Derrell
Re: [sqlite] excessive malloc() calls
Andrew Shakinovsky wrote: I have noticed with SQLite (at least this was true with 2.8x, not sure about 3x) that if you try to use an ORDER BY with a table that doesn't have an index on the field you are ORDERing by, it will do the entire sort (presumably just the keys) in memory. This will cause you to run out of memory if the table is too large. This is also true of version 3.0 and (the soon to be released) version 3.1. I believe this constraint is documented somewhere, though I cannot say where right off hand. Somebody please correct me (and submit documentation patches) if I am wrong. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
[EMAIL PROTECTED] wrote: > > The "order by" clause I think is what causes the memory allocation of > "aType" for each row. I think you are right. The aType is cached when data is coming out of the database file so the allocation only occurs once. But when data is coming out of the sorter, the aType must be reallocated and recomputed each type a column of the result set is referenced. This needs to be worked on. > > This does point out however, that Win32 users who need to use ORDER > BY on a large table are paying a hefty price. > Win32 users pay a hefty price for lots of thing ;-) -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
On Mon, 10 Jan 2005 13:49:21 -0800, [EMAIL PROTECTED] > By the way, "ORDER BY 1" I believe to mean order by column id = 1. So it's > not really a meaningless statement. Is that true? I don't get the from the documentation, but I'm probably just looking at it wrong.
RE: [sqlite] excessive malloc() calls
I have noticed with SQLite (at least this was true with 2.8x, not sure about 3x) that if you try to use an ORDER BY with a table that doesn't have an index on the field you are ORDERing by, it will do the entire sort (presumably just the keys) in memory. This will cause you to run out of memory if the table is too large. >>-Original Message- >>From: Will Leshner [mailto:[EMAIL PROTECTED] >>Sent: Monday, January 10, 2005 4:49 PM >>To: sqlite-users@sqlite.org >>Subject: Re: [sqlite] excessive malloc() calls >> >> >>On Mon, 10 Jan 2005 13:43:20 -0800, >>[EMAIL PROTECTED] >>> This does point out however, that Win32 users who need to >>use ORDER BY >>> on a large table are paying a hefty price. >> >>Is that true even if they are using ORDER BY properly? >>
Re: [sqlite] excessive malloc() calls
On Mon, 10 Jan 2005 13:43:20 -0800, [EMAIL PROTECTED] > This does point out however, that Win32 users who need to use ORDER BY on a > large table are paying a hefty price. Is that true even if they are using ORDER BY properly?
Re: [sqlite] excessive malloc() calls
I'm just doing the select statement I mentioned in my first message. SELECT * FROM 'SPY' ORDER BY 1 ASC; "1" is the column id which in my case I thought was a primary integer key. The "order by" clause I think is what causes the memory allocation of "aType" for each row. I did not create the primary key properly which someone pointed out. With a proper primary key, I was able to get rid of the ORDER BY clause and the per row memory allocation went away. This does point out however, that Win32 users who need to use ORDER BY on a large table are paying a hefty price. Thanks, Matt "D. Richard Hipp" <[EMAIL PROTECTED]> To 01/10/2005 12:58 sqlite-users@sqlite.org PM cc Subject Please respond to Re: [sqlite] excessive malloc() [EMAIL PROTECTED] calls te.org [EMAIL PROTECTED] wrote: > > I hacked the code to allocate the aType structure from a circular ring > buffer. This small change made my app go from spending 60% of its time in > malloc to about 3%. > I'm very interested in knowing what SQL statements you are running to obtain this result. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
I am sorry, typing too fast I had made typos which made my message not
understandable. So here it is again:
I would say it is one of the most dangerous code snippets I have seen
in a while.
The code makes a lot of assumptions but they are NOT explicitly stated.
It may work under some strictly defined conditions but after several
months you or anyone else modifying the code may forget what those
conditions were and it would lead to a memory corrupting bug which
would be very hard to trace.
Tom
Abracode
On Jan 10, 2005, at 3:15 PM, Tom wrote:
I would say it is one of the most dangerous code snippets I have seen
in a while.
The code makes a lot of assumption but they are explicitly stated. It
may work under some strictly defined conditions but after several
months you or anyone else modifying may forget what those conditions
were and it would lead to a memory corrupting bug which would be very
hard to trace.
Tom
Abracode
On Jan 10, 2005, at 2:39 PM, [EMAIL PROTECTED] wrote:
Windows XP, visual studio .NET.
The malloc call ends up calling RtlAllocateHeap().
I hacked the code to allocate the aType structure from a circular ring
buffer. This small change made my app go from spending 60% of its
time in
malloc to about 3%.
Here's the allocator I hacked in. No freeing is needed, the buffer
just
wraps. This assumes that by the time a wrap occurs, memory perviously
allocated at the top is no longer needed. It seems to work ok from my
limited testing. :-)
#define SQLITE_WORK_BUFF_SIZE (128*1024) // make power of 2
#define SQLITE_WORK_BUFF_MASK (SQLITE_WORK_BUFF_SIZE-1)
char sqlite_workBuff[SQLITE_WORK_BUFF_SIZE];
int sqlite_writeIdx=0;
void *SQLITE_ALLOC(int nBytes)
{
void *ret;
sqlite_writeIdx = (sqlite_writeIdx + nBytes) &
SQLITE_WORK_BUFF_MASK;
ret = sqlite_workBuff + sqlite_writeIdx;
sqlite_writeIdx+=nBytes;
return ret;
}
Matthew Arrington
Sony Computer Entertainment America
Re: [sqlite] excessive malloc() calls
I would say it is one of the most dangerous code snippets I have seen
in a while.
The code makes a lot of assumption but they are explicitly stated. It
may work under some strictly defined conditions but after several
months you or anyone else modifying may forget what those conditions
were and it would lead to a memory corrupting bug which would be very
hard to trace.
Tom
Abracode
On Jan 10, 2005, at 2:39 PM, [EMAIL PROTECTED] wrote:
Windows XP, visual studio .NET.
The malloc call ends up calling RtlAllocateHeap().
I hacked the code to allocate the aType structure from a circular ring
buffer. This small change made my app go from spending 60% of its time
in
malloc to about 3%.
Here's the allocator I hacked in. No freeing is needed, the buffer just
wraps. This assumes that by the time a wrap occurs, memory perviously
allocated at the top is no longer needed. It seems to work ok from my
limited testing. :-)
#define SQLITE_WORK_BUFF_SIZE (128*1024) // make power of 2
#define SQLITE_WORK_BUFF_MASK (SQLITE_WORK_BUFF_SIZE-1)
char sqlite_workBuff[SQLITE_WORK_BUFF_SIZE];
int sqlite_writeIdx=0;
void *SQLITE_ALLOC(int nBytes)
{
void *ret;
sqlite_writeIdx = (sqlite_writeIdx + nBytes) &
SQLITE_WORK_BUFF_MASK;
ret = sqlite_workBuff + sqlite_writeIdx;
sqlite_writeIdx+=nBytes;
return ret;
}
Matthew Arrington
Sony Computer Entertainment America
Re: [sqlite] excessive malloc() calls
[EMAIL PROTECTED] wrote: I hacked the code to allocate the aType structure from a circular ring buffer. This small change made my app go from spending 60% of its time in malloc to about 3%. I'm very interested in knowing what SQL statements you are running to obtain this result. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
Windows XP, visual studio .NET.
The malloc call ends up calling RtlAllocateHeap().
I hacked the code to allocate the aType structure from a circular ring
buffer. This small change made my app go from spending 60% of its time in
malloc to about 3%.
Here's the allocator I hacked in. No freeing is needed, the buffer just
wraps. This assumes that by the time a wrap occurs, memory perviously
allocated at the top is no longer needed. It seems to work ok from my
limited testing. :-)
#define SQLITE_WORK_BUFF_SIZE (128*1024) // make power of 2
#define SQLITE_WORK_BUFF_MASK (SQLITE_WORK_BUFF_SIZE-1)
char sqlite_workBuff[SQLITE_WORK_BUFF_SIZE];
int sqlite_writeIdx=0;
void *SQLITE_ALLOC(int nBytes)
{
void *ret;
sqlite_writeIdx = (sqlite_writeIdx + nBytes) & SQLITE_WORK_BUFF_MASK;
ret = sqlite_workBuff + sqlite_writeIdx;
sqlite_writeIdx+=nBytes;
return ret;
}
Matthew Arrington
Sony Computer Entertainment America
"D. Richard Hipp"
<[EMAIL PROTECTED]>
To
01/10/2005 11:18 sqlite-users@sqlite.org
AM cc
Subject
Please respond to Re: [sqlite] excessive malloc()
[EMAIL PROTECTED] calls
te.org
[EMAIL PROTECTED] wrote:
> I switched my application over to SQLite3 and did some performance
> profiling and found that the majority of the processing time spent is
> making calls to malloc().
It sounds like you have a bad malloc() implementation. What
OS and compiler are you using. Malloc() on linux is normally
very good. Malloc() on win95/98/ME is often quite bad.
Malloc(0 on winNT/2K/XP is in between. Or at least that has
been my experience. If your system gas a slow malloc
implementation, you would do well to use one of the many
fine open-source implementations as a substitute.
>
> I create each table using SQL command:
> CREATE TABLE 'SPY' (key INTEGER PRIMARYKEY UNIQUE, open FLOAT, low FLOAT,
> high FLOAT, close FLOAT, volume FLOAT)
>
> I need each row sorted by date, which I setup as my primary key. Ideally
> each table would be stored sorted on disk since I do a very small amount
of
> updating of only one record per day per table. I could not figure out a
way
> to make do that.
If you put a space between PRIMARY and KEY, it will order the
entrys by the spy.key field. Omit the UNIQUE keyword as that
is redundant. PRIMARY KEYs are always UNIQUE.
>
> The actual code making the call is:
>
> /* Read and parse the table header. Store the results of the parse
> ** into the record header cache fields of the cursor.
> */
> if( pC && pC->cacheValid ){
> aType = pC->aType;
> aOffset = pC->aOffset;
> }else{
> int avail;/* Number of bytes of available data */
> if( pC && pC->aType ){
> aType = pC->aType;
> }else{
> aType = sqliteMallocRaw( 2*nField*sizeof(aType) );
> <<< HERE
> }
> aOffset = &aType[nField];
> if( aType==0 ){
> goto no_mem;
> }
>
The line of code marked should only execute once per
SELECT statement, unless you are running with triggers
in which case it will execute once per row. Either way,
it won't be executing 5.5 million times on a 3000 row
table.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
[EMAIL PROTECTED] writes: > I switched my application over to SQLite3 and did some performance > profiling and found that the majority of the processing time spent is > making calls to malloc(). > > sqlite3_step() is the function that is making all the excessive calls, one > call per row fetched. > > The program is a stock scanning / data mining program. It keeps about 6-7 > years worth of daily stock price data for a company in a table, and I have > about 3000 of these tables. One complete scan of all 3000 companies will > make roughly 5.5 million malloc() calls! > > I create each table using SQL command: > CREATE TABLE 'SPY' (key INTEGER PRIMARYKEY UNIQUE, open FLOAT, low FLOAT, > high FLOAT, close FLOAT, volume FLOAT) Firstly, if "key" is your primary key, you need not specify UNIQUE because primary keys are, by definition, unique. Secondly, I don't know if sqlite understands "PRIMARYKEY". The more standard way of specifying that is with a space in it: "PRIMARY KEY". You can confirm whether sqlite understood this to be your primary key field by doing the following from the sqlite shell: .mode line pragma table_info(SPY); If the "pk" field shows as '1', then it knows it's a primary key; otherwise it does not. > When I read a table I use the following SQL select statement. > SELECT * FROM 'SPY' ORDER BY 1 ASC "ORDER BY 1" is probably causing an additional calculation for each row, in order to sort by whatever that means (which is probably nothing). If you discard the ORDER BY clause, the results will be in primary key order. You could be explicit about it by saying "ORDER BY key" (with or without the ASC indication, since that's the default). > I debugged / stepped in to the step() code and noticed that it is the op > code COLUMN making the memory allocation. I think it may be my ORDER BY > clause that is causing it to take the path to the malloc() call. Without looking in more detail at the code, that would be a reasonable supposition. It probably allocates memory during each step() call, to contain the field which will be sorted by. I suspect that in your query, it's allocating space to put the constant value "1"... a not very useful piece of information to be allocating for and saving. Cheers, Derrell
Re: [sqlite] excessive malloc() calls
[EMAIL PROTECTED] wrote:
I switched my application over to SQLite3 and did some performance
profiling and found that the majority of the processing time spent is
making calls to malloc().
It sounds like you have a bad malloc() implementation. What
OS and compiler are you using. Malloc() on linux is normally
very good. Malloc() on win95/98/ME is often quite bad.
Malloc(0 on winNT/2K/XP is in between. Or at least that has
been my experience. If your system gas a slow malloc
implementation, you would do well to use one of the many
fine open-source implementations as a substitute.
I create each table using SQL command:
CREATE TABLE 'SPY' (key INTEGER PRIMARYKEY UNIQUE, open FLOAT, low FLOAT,
high FLOAT, close FLOAT, volume FLOAT)
I need each row sorted by date, which I setup as my primary key. Ideally
each table would be stored sorted on disk since I do a very small amount of
updating of only one record per day per table. I could not figure out a way
to make do that.
If you put a space between PRIMARY and KEY, it will order the
entrys by the spy.key field. Omit the UNIQUE keyword as that
is redundant. PRIMARY KEYs are always UNIQUE.
The actual code making the call is:
/* Read and parse the table header. Store the results of the parse
** into the record header cache fields of the cursor.
*/
if( pC && pC->cacheValid ){
aType = pC->aType;
aOffset = pC->aOffset;
}else{
int avail;/* Number of bytes of available data */
if( pC && pC->aType ){
aType = pC->aType;
}else{
aType = sqliteMallocRaw( 2*nField*sizeof(aType) );
<<< HERE
}
aOffset = &aType[nField];
if( aType==0 ){
goto no_mem;
}
The line of code marked should only execute once per
SELECT statement, unless you are running with triggers
in which case it will execute once per row. Either way,
it won't be executing 5.5 million times on a 3000 row
table.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] excessive malloc() calls
>It would seem really useful to be able to compare the performance of >two or more companies. Having each of them in a separate table means to >do any kind of comparison you need a join. It would seem lots better >to have a single table with a column for the company name. Eliminates >LOTS of work for that scenario... I don't compare companies against each other. I just need to get all the price data out of a table (around 1850 rows), sorted by primary key, as fast as possible. (ie. without making 1850 malloc calls.) Thanks, Matt
RE: [sqlite] excessive malloc() calls
Did some hashing code which used pooling and preallocate of block and it made the hash way (technical term) faster in insert and delete operations. As well as much less overhead for small hash object. Basically kept it's own pool of hash items. For SQLite might be harder to implement since things are not as fixed by nature (columns/datatypes), but would get rid of lots of malloc() which are slow as well as costly for internal overhead. Sandy -Original Message- From: Andrew Piskorski [mailto:[EMAIL PROTECTED] Sent: Monday, January 10, 2005 10:28 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] excessive malloc() calls On Mon, Jan 10, 2005 at 09:51:50AM -0800, [EMAIL PROTECTED] wrote: > sqlite3_step() is the function that is making all the excessive calls, one > call per row fetched. > about 3000 of these tables. One complete scan of all 3000 companies will > make roughly 5.5 million malloc() calls! That seems highly non-optimal. If there is no other way around that already present in SQLite, and you don't want to hack one in, then the obvious step might be to link in one of the several high-performance malloc replacement libraries out there, e.g. Hoard: http://www.cs.umass.edu/~emery/hoard/ http://developers.sun.com/solaris/articles/multiproc/multiproc.html Tcl 8.4.x includes a threaded allocator (which is used by AOLserver), which maintains its own thread-local pools of memory and calls the system malloc() underneath only infrequently. It is not intended as a drop in replacement for malloc(), but I THINK the various malloc replacement libraries operate in similar (though more complex) fashions. I'd expect (but have not tested) that these memory pooling techniques would offer you a speed benefit even when you're using only 1 thread, as malloc'ing millions of times is usually not a good idea. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] excessive malloc() calls
On Mon, Jan 10, 2005 at 09:51:50AM -0800, [EMAIL PROTECTED] wrote: > sqlite3_step() is the function that is making all the excessive calls, one > call per row fetched. > about 3000 of these tables. One complete scan of all 3000 companies will > make roughly 5.5 million malloc() calls! That seems highly non-optimal. If there is no other way around that already present in SQLite, and you don't want to hack one in, then the obvious step might be to link in one of the several high-performance malloc replacement libraries out there, e.g. Hoard: http://www.cs.umass.edu/~emery/hoard/ http://developers.sun.com/solaris/articles/multiproc/multiproc.html Tcl 8.4.x includes a threaded allocator (which is used by AOLserver), which maintains its own thread-local pools of memory and calls the system malloc() underneath only infrequently. It is not intended as a drop in replacement for malloc(), but I THINK the various malloc replacement libraries operate in similar (though more complex) fashions. I'd expect (but have not tested) that these memory pooling techniques would offer you a speed benefit even when you're using only 1 thread, as malloc'ing millions of times is usually not a good idea. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
[sqlite] excessive malloc() calls
I switched my application over to SQLite3 and did some performance
profiling and found that the majority of the processing time spent is
making calls to malloc().
sqlite3_step() is the function that is making all the excessive calls, one
call per row fetched.
The program is a stock scanning / data mining program. It keeps about 6-7
years worth of daily stock price data for a company in a table, and I have
about 3000 of these tables. One complete scan of all 3000 companies will
make roughly 5.5 million malloc() calls!
I create each table using SQL command:
CREATE TABLE 'SPY' (key INTEGER PRIMARYKEY UNIQUE, open FLOAT, low FLOAT,
high FLOAT, close FLOAT, volume FLOAT)
I need each row sorted by date, which I setup as my primary key. Ideally
each table would be stored sorted on disk since I do a very small amount of
updating of only one record per day per table. I could not figure out a way
to make do that.
When I read a table I use the following SQL select statement.
SELECT * FROM 'SPY' ORDER BY 1 ASC
This all seems like it should be pretty basic stuff, that I'd expect an SQL
engine to easily handle. I'm a novice when it comes to SQL, so maybe I'm
doing something dumb?
I debugged / stepped in to the step() code and noticed that it is the op
code COLUMN making the memory allocation. I think it may be my ORDER BY
clause that is causing it to take the path to the malloc() call.
The actual code making the call is:
/* Read and parse the table header. Store the results of the parse
** into the record header cache fields of the cursor.
*/
if( pC && pC->cacheValid ){
aType = pC->aType;
aOffset = pC->aOffset;
}else{
int avail;/* Number of bytes of available data */
if( pC && pC->aType ){
aType = pC->aType;
}else{
aType = sqliteMallocRaw( 2*nField*sizeof(aType) );
<<< HERE
}
aOffset = &aType[nField];
if( aType==0 ){
goto no_mem;
}
Thanks,
Matt Arrington
