Re: [sqlite] Sqlite occasionally becomes CPU greedy
On Mon, 24 Sep 2007 07:13:51 -0700, Gururaja Nittur wrote: Sqlite experts, I am running Sqlite version 3.4.1. I ran some performance tests and observed that sqlite sometimes consumes more CPU than normal (I am using same dataset, all inserts and recreating new database for each test). Tried running oprofiile and got the following result. The function 'syncJournal' is taking around 60% of total Sqlite's CPU usage. 1. Is this normal behaviour? If not, is there a known fix for this? Do you use transactions? If not, every INSERT or UPDATE is a separate transaction, causing a lot of overhead because the journal is flushed for every transaction. 2. Are there any tunables that can be used to ease the issue? Several pragma's are available, the documentation is quite clear: http://www.sqlite.org/pragma.html Also read: http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations PS: My database is pretty small. I am setting max_page_count=896 and page_size = 512 (Max database size = 3.5MB). I don't understand that one. 512 Bytes * 896 Pages would be 458752 Bytes. I think you would need 7168 pages of 512 bytes to accomodate 3.5 MByte. Ideally, the page_size should equal the allocation unit (for Window: the cluster size of the formatted disk). Also, if your rows can be large, a too small page_size would cause overflow pages, and thus additional overhead. Thanks in advance. I hope this helps. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite occasionally becomes CPU greedy
On 9/24/07, Kees Nuyt [EMAIL PROTECTED] wrote: On Mon, 24 Sep 2007 07:13:51 -0700, Gururaja Nittur wrote: Sqlite experts, I am running Sqlite version 3.4.1. I ran some performance tests and observed that sqlite sometimes consumes more CPU than normal (I am using same dataset, all inserts and recreating new database for each test). Tried running oprofiile and got the following result. The function 'syncJournal' is taking around 60% of total Sqlite's CPU usage. 1. Is this normal behaviour? If not, is there a known fix for this? Do you use transactions? If not, every INSERT or UPDATE is a separate transaction, causing a lot of overhead because the journal is flushed for every transaction. Yes! I do use transaction. But, I am testing my application's worst case here (Inserting one record per transaction). 2. Are there any tunables that can be used to ease the issue? Several pragma's are available, the documentation is quite clear: http://www.sqlite.org/pragma.html Also read: http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations PS: My database is pretty small. I am setting max_page_count=896 and page_size = 512 (Max database size = 3.5MB). I don't understand that one. 512 Bytes * 896 Pages would be 458752 Bytes. I think you would need 7168 pages of 512 bytes to accomodate 3.5 MByte. Oops! my fault. You are right. I had changed the max_page_count=896 to test with smaller database. I do see similar behavior when max size is 3.5M. Ideally, the page_size should equal the allocation unit (for Window: the cluster size of the formatted disk). Also, if your rows can be large, a too small page_size would cause overflow pages, and thus additional overhead. I expect most of my rows to be less than 512 bytes and hence using smaller page_size. Thanks for the response. I just wanted to find out if there are any known issues with syncJournal. I will tweak more parameters and try to measure again. Best regards, Guru Thanks in advance. I hope this helps. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite occasionally becomes CPU greedy
Kees Nuyt wrote: On Mon, 24 Sep 2007 07:13:51 -0700, Gururaja Nittur wrote: Sqlite experts, I am running Sqlite version 3.4.1. I ran some performance tests and observed that sqlite sometimes consumes more CPU than normal (I am using same dataset, all inserts and recreating new database for each test). Tried running oprofiile and got the following result. The function 'syncJournal' is taking around 60% of total Sqlite's CPU usage. 1. Is this normal behaviour? If not, is there a known fix for this? Do you use transactions? If not, every INSERT or UPDATE is a separate transaction, causing a lot of overhead because the journal is flushed for every transaction. I'm confused. With a bunch of INSERTs per transaction, the CPU usage can be high. But with just one per transaction things will be I/O bound, and I would expect low CPU usage. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite occasionally becomes CPU greedy
On Mon, 24 Sep 2007 10:35:51 -0700, you wrote: Kees Nuyt wrote: On Mon, 24 Sep 2007 07:13:51 -0700, Gururaja Nittur wrote: Sqlite experts, I am running Sqlite version 3.4.1. I ran some performance tests and observed that sqlite sometimes consumes more CPU than normal (I am using same dataset, all inserts and recreating new database for each test). Tried running oprofiile and got the following result. The function 'syncJournal' is taking around 60% of total Sqlite's CPU usage. 1. Is this normal behaviour? If not, is there a known fix for this? Do you use transactions? If not, every INSERT or UPDATE is a separate transaction, causing a lot of overhead because the journal is flushed for every transaction. I'm confused. With a bunch of INSERTs per transaction, the CPU usage can be high. But with just one per transaction things will be I/O bound, and I would expect low CPU usage. That's true. The thing is, Guru isn't worried about the total CPU usage, but about the relative amount of CPU time that is spent in synching the journal (60%), relative to the total CPU time in SQLite (100%). Gerry -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Having issues Loading an external
I am running sqlite 3.4.2 and I am trying to get the extension_functions.c extenstion to work. I have followed the directions: How To Build a Loadable Extension Shared Library on Linux 0. untar latest sqlite3 source code in a new directory 1. cd to the newly untarred sqlite directory 2. Comment out the line in Makefile.in to enable loadable extensions: # TCC += -DSQLITE_OMIT_LOAD_EXTENSION=1 3. ./configure LIBS=-ldl make sqlite3 4. export LD_LIBRARY_PATH=`pwd`:$LD_LIBRARY_PATH 5. gcc -I`pwd` -fPIC -shared src/extension_functions.c -o libsqlitefunctions.so 6. ./sqlite3 and when I do .load libsqlitefunctions.so sqlite3RegisterExtraFunctions I get unable to open shared library. I tried putting libsqlitefunctions.sointo the current directory but to no avail. I even tried to do .load ./libsqlitefunctions.so sqlite3RegisterExtraFunctions Any help??? Or a quick work around to get STDEV and AVG functions that are relatively fast.
Re: [sqlite] sqlite3_open_v2 and SQLITE_OPEN_READONLY
On Thu, 20 Sep 2007 10:14:10 -0700 drh wrote: Sqlite3_open_v2() and SQLITE_OPEN_READONLY and a whole bunch of other stuff is all new to 3.5.0. Version 3.5.0 is stable. It has lots of cool stuff. Older versions are not supported (except for paying customers) - by which we mean that if any bugs are discovered they will be fixed in 3.5.0 only, not in branches. You (and a lot of other people) really need to upgrade. Stable? Since when was 3.5.0 even released? There has been no release announcement in this list (that I can find in the archives in any case), and the front page of http://sqlite.org announces that it is still in alpha. Surely until there is a formal release of 3.5.0, the existing version of 3.4.2 is still the current stable version? Regards, Brodie Sick of deleting your inbox? Yahoo!7 Mail has free unlimited storage. http://au.docs.yahoo.com/mail/unlimitedstorage.html - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite occasionally becomes CPU greedy
On 9/24/07, Kees Nuyt [EMAIL PROTECTED] wrote:
On Mon, 24 Sep 2007 10:35:51 -0700, you wrote:
Kees Nuyt wrote:
On Mon, 24 Sep 2007 07:13:51 -0700, Gururaja Nittur wrote:
Sqlite experts,
I am running Sqlite version 3.4.1. I ran some performance tests and
observed that sqlite sometimes consumes more CPU than normal (I am
using same dataset, all inserts and recreating new database for each
test). Tried running oprofiile and got the following result. The
function 'syncJournal' is taking around 60% of total Sqlite's CPU
usage.
1. Is this normal behaviour? If not, is there a known fix for this?
Do you use transactions? If not, every INSERT or UPDATE is a
separate transaction, causing a lot of overhead because the
journal is flushed for every transaction.
I'm confused. With a bunch of INSERTs per transaction, the CPU usage can
be high. But with just one per transaction things will be I/O bound, and
I would expect low CPU usage.
That's true. The thing is, Guru isn't worried about the total
CPU usage, but about the relative amount of CPU time that is
spent in synching the journal (60%), relative to the total CPU
time in SQLite (100%).
Yes! you are right. The overall CPU is not that high. I am seeing
spikes in sqlite CPU usage and not getting consistent results. So
started profiling. It may be due to transaction size as well.
I tried to understand syncJournal code a little bit. It is pretty
evident that the following loop in pager.cc is consuming lot of CPU
cycles (it iterates upto SQLITE_DEFAULT_CACHE_SIZE, i.e, 2000 times
per transaction).
2716 22.6768 :for(pPg=pPager-pAll; pPg; pPg=pPg-pNextAll){
79 0.6596 : pPg-needSync = 0;
:}
One thing I didn't understand is, why we need to traverse through all
pages and update needSync flag. Can't we keep a list of dirty pages
and clear needSync only in those pages?
Also, what would be the effect of reducing SQLITE_DEFAULT_CACHE_SIZE
from 2000 to say 500?. My application mostly writes or updates the
database and rarely reads from it. Reducing cache pages would help me?
Thanks,
Guru
Gerry
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Having issues Loading an external
This contrib extension library cannot be used directly as an sqlite loadable module because it lacks an entry point function with the prototype: int sqlite3_extension_init( sqlite3 *db, /* The database connection */ char **pzErrMsg, /* Write error messages here */ const sqlite3_api_routines *pApi /* API methods */ ); See http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions You could add such a function that calls sqlite3RegisterExtraFunctions(sqlite3*). --- eric higashino [EMAIL PROTECTED] wrote: I am running sqlite 3.4.2 and I am trying to get the extension_functions.c extenstion to work. I have followed the directions: How To Build a Loadable Extension Shared Library on Linux 0. untar latest sqlite3 source code in a new directory 1. cd to the newly untarred sqlite directory 2. Comment out the line in Makefile.in to enable loadable extensions: # TCC += -DSQLITE_OMIT_LOAD_EXTENSION=1 3. ./configure LIBS=-ldl make sqlite3 4. export LD_LIBRARY_PATH=`pwd`:$LD_LIBRARY_PATH 5. gcc -I`pwd` -fPIC -shared src/extension_functions.c -o libsqlitefunctions.so 6. ./sqlite3 and when I do .load libsqlitefunctions.so sqlite3RegisterExtraFunctions I get unable to open shared library. I tried putting libsqlitefunctions.sointo the current directory but to no avail. I even tried to do .load ./libsqlitefunctions.so sqlite3RegisterExtraFunctions Any help??? Or a quick work around to get STDEV and AVG functions that are relatively fast. Luggage? GPS? Comic books? Check out fitting gifts for grads at Yahoo! Search http://search.yahoo.com/search?fr=oni_on_mailp=graduation+giftscs=bz - To unsubscribe, send email to [EMAIL PROTECTED] -
