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] 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] -
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 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
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] -
