On 4/26/07, John Elrick <[EMAIL PROTECTED]> wrote:
Using two machines as an example, XP Home and XP Media Center.
XP Home and Media Center with the pragma synchronous=OFF executes the
test operation in under 2.0 seconds.
Set to FULL the times are, respectively 118 seconds and 8 seconds.
The test involves writing 1,000 rows to a table with no start/end
transaction. The resulting file is 59kb in size.
As each row has a "hard flush", we can see that each one takes about
118 mili-secs (118/1000s). That is a value well within the range of
current normal hard disks (it depends alot on the disk rotation
speed).
8 mili-seconds is a too low value, It is a value within the average
read access time of current hard-disks (well, maybe not so current,
but in the same order of magnitude). Too fast for a single write, so
it's impossible that the flush was done.
Write caching is enabled on both machines, therefore it is likely that
we can eliminate Windows caching as a factor.
At this point we have several distinct possibilities:
1. Hard drive caching. Both machines are relatively new, the Home
machine is a two year old HP Pavilion desktop replacement. The Media
Center machine is a year old HP Pavilion desktop replacement. I would
tend to think that the caching capabilities of the two machines are
close enough that 59kb of data shouldn't cause an order of magnitude
difference. I could be wrong.
I believe you are right.
2. Settings which cause Media Center to return control more
optimistically than Pro or Home. In this case, there would be a
hypothetically higher risk of data loss on the Media Center machine.
However, the point of a synchronous=FULL, as I understand it, it to
thoroughly verify that the data is "safe". It would be good to know if
there is a way to defeat this safeguard and how to determine if it has
been defeated.
The thing is cheap IDE disks already lie to the OS saying they had
flushed the buffers when they don't. Maybe Windows knows it can't
depend on the drive and falls back to the lazy write scheme, (the
default on Windows 2000).
Maybe Windows XP is more conscious about this and tries to make sure
it does it's best to flush the buffers (or maybe it's disk drivers are
better). The only way for you to make sure is to install 2000/Media
Center and XP on the same machine and test.
SQLite always does the right thing. If the OS lies and says it
completed the operation (probably because the hardware also lied to
it), there's nothing SQLite can do.
There is an interesting parallel around Win95 and Win2000 FLUSH performance:
http://support.microsoft.com/kb/281281
And you seem to not have noticed this link:
http://www.microsoft.com/technet/prodtechnol/windows2000serv/maintain/optimize/wperfch7.mspx
Regards,
~Nuno Lucas
3. Settings which cause Media Center to be more aggressive about
flushing its cache than Pro or Home. If this hypothesis is correct, Pro
or Home would be putting a higher priority on services other than disk I/O.
One final factor in my weighing is that performance reports I have read
on the Wiki and other postings indicate that inserts outside of a
transaction should still be closer to the single digit mark than triple
digits.
All of this having been said, all I am certain about at this time is
that XP/Vista/Pro/Home appear to be an order of magnitude slower in
returning control than Media Center and W2K. I don't know why. And
that bothers me.
John
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
