On Mon, 24 Sep 2007 21:36:14 -0700, Gururaja Nittur wrote:
>On 9/24/07, Kees Nuyt <[EMAIL PROTECTED]> wrote:
>>
>> On Mon, 24 Sep 2007 10:35:51 -0700, Gerry Snyder wrote:
[big snip]
>> >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?
First of all: I'm not an expert in SQLite internals, so I can
only speak in general terms.
This pPg=pPg->pNextAll loop might be more efficient than
maintaining another pointer chain for a list of dirty pages.
Compared to the time spent on I/O waits this loop will be quite
fast for a few thousand pages anyway: The code path for a single
I/O (one database page) typically is in the order of 5000 to
10000 CPU instructions, depending on the OS of course, and it
will also cause a few context switches. This loop is in the
order of 10 instructions per page.
By the way, this is one more reason to use a larger page size.
Transaction size is relevant here, the bigger the better, but
not bigger than the cache can hold.
> 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?
A too small cache will cause more I/O.
I would use a large cache size during the initial load, to avoid
I/O during the building of the btrees, typically some 20000
pages of 8192 bytes (164 MB) for large databases (800 MByte or
so).
During inserts and updates the cache has to be big enough to
hold all data- and index pages that will be touched in the
transaction, plus the schema, of course. How much that is, is
hard to estimate; the output of sqlite3_analyzer can give some
insight in this respect.
As always: benchmarks will tell the truth ;)
In my case the on-the-fly virusscan of I/O buffers on our
corporate PC's is the most important cause of slowdown :)
> Thanks,
> Guru
You're welcome.
Regards,
--
( Kees Nuyt
)
c[_]
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
