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