Re: [sqlite] "INSERT" and "END TRANSACTION" time

[Stephan Beal]

On Tue, Mar 4, 2014 at 3:44 AM, Simon Slavin  wrote:

>
> On 4 Mar 2014, at 1:15am, romtek  wrote:
>
> > I have a question based on my observation. According to your numbers for
> a
> > 5400 RPM disk, one write op should take about 11 ms. However, it often
> > takes only about 1 ms on HostGator drives on its shared hosting servers.
> > Are there drives that are SO much faster than 5400 RPM ones?
>
> I'll bet that the hosting servers are virtual machines and the drives are
> virtual drives, not physical drives.  Everything is actually done in RAM
> and just flushed to physical disk every so often.
>

A related anecdote: i develop primarily on 64-bit hardware but very often
have a 32-bit VM open for portability testing, and in my experience my
sqlite-based apps _invariably_ run 20-30% faster in my 32-bit VM than on my
64-bit HW. While i cannot with 100% certainty say why that is, others have
suggested (quite possibly correctly) that this is probably due to relevant
parts of the VM's virtual drive container being cached within the host OS.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "INSERT" and "END TRANSACTION" time

[Simon Slavin]

On 4 Mar 2014, at 5:06am, romtek  wrote:

> If that were true, then I wouldn't be getting a very large speed-up when
> enveloping write ops in an explicit transaction, would I?

Sorry, I can't tell.  It depends on how the virtual storage mechanism works.  
But if your performance characteristics when using a third-party server 
resemble the performance of using a :memory: database, then they're using 
virtualised main storage.  If you're finding that when using your own computer, 
you may find that your main storage is actually a hybrid drive which uses a 
large solid state cache.

Virtual storage is what hosting companies do these days: it uses less power, 
the servers take up less room, and the lack of moving parts means less failure. 
 Unless your hosting company guarantees ACID behaviour and minimal loss on 
power failure, which I don't think I've seen in standard cheap hosting 
contracts.

The take-away messages from all of this, if I may be so bold, are

(A) Your use of transactions in SQL should reflect which database modifications 
go together: which ones should fail if they can't all be done.  Performance 
issues are secondary.

(B) Your program should run "fast enough", not "as fast as possible".

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "INSERT" and "END TRANSACTION" time

[romtek]

If that were true, then I wouldn't be getting a very large speed-up when
enveloping write ops in an explicit transaction, would I?


On Mon, Mar 3, 2014 at 8:44 PM, Simon Slavin  wrote:

>
> On 4 Mar 2014, at 1:15am, romtek  wrote:
>
> > I have a question based on my observation. According to your numbers for
> a
> > 5400 RPM disk, one write op should take about 11 ms. However, it often
> > takes only about 1 ms on HostGator drives on its shared hosting servers.
> > Are there drives that are SO much faster than 5400 RPM ones?
>
> I'll bet that the hosting servers are virtual machines and the drives are
> virtual drives, not physical drives.  Everything is actually done in RAM
> and just flushed to physical disk every so often.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "INSERT" and "END TRANSACTION" time

[Simon Slavin]

On 4 Mar 2014, at 1:15am, romtek  wrote:

> I have a question based on my observation. According to your numbers for a
> 5400 RPM disk, one write op should take about 11 ms. However, it often
> takes only about 1 ms on HostGator drives on its shared hosting servers.
> Are there drives that are SO much faster than 5400 RPM ones?

I'll bet that the hosting servers are virtual machines and the drives are 
virtual drives, not physical drives.  Everything is actually done in RAM and 
just flushed to physical disk every so often.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "INSERT" and "END TRANSACTION" time

[romtek]

Thank for the clarification, Simon!

I have a question based on my observation. According to your numbers for a
5400 RPM disk, one write op should take about 11 ms. However, it often
takes only about 1 ms on HostGator drives on its shared hosting servers.
Are there drives that are SO much faster than 5400 RPM ones?


On Mon, Mar 3, 2014 at 5:57 PM, Simon Slavin  wrote:

>
> On 3 Mar 2014, at 9:11pm, romtek  wrote:
>
> > Simon, does a real disk have to be a rotating hard disk? Is there
>  problem
> > with SSDs as far as SQLite is concerned?
>
> SSDs aren't a problem, and SQLite works fine with them, but they change
> the timings associated with SQLite a great deal.  Simplified explanation
> follows.
>
> With rotating hard disks each read or write operation has to wait for the
> disk to rotate to the right place.  And if you have your files
> defragmented, all of a SQLite database is clumped up together on disk right
> next to the journal file for that database.  An END TRANSACTION command can
> involve six (more ?  anyone ?) accesses of database or journal file, and if
> all the parts of disk you're writing to are near one-another that can
> involve six rotations of the hard disk.  So ...
>
> Hard disk rotates at 5400rpm = 90rotations/s .
> 6 reads or writes to disk, assuming no skipped rotations, takes 66ms =
> 1/15th of a second .
> A loop doing 100 INSERTs can take over six seconds !
>
> This means that in real life SQLite can be faster if you do /not/
> defragment your drive.  Because you don't have to wait for a full rotation
> every time.
>
> SSDs, of course, don't have to wait for anything physical.  The
> improvement in speed gained by using transactions does not work the same
> way: there are still fewer operations, so it should still be faster, but
> not by as much.  My original statement about so much of the time being
> spent on the END TRANSACTION no longer holds.  That's why I asked.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "INSERT" and "END TRANSACTION" time

[Simon Slavin]

On 3 Mar 2014, at 9:11pm, romtek  wrote:

> Simon, does a real disk have to be a rotating hard disk? Is there  problem
> with SSDs as far as SQLite is concerned?

SSDs aren't a problem, and SQLite works fine with them, but they change the 
timings associated with SQLite a great deal.  Simplified explanation follows.

With rotating hard disks each read or write operation has to wait for the disk 
to rotate to the right place.  And if you have your files defragmented, all of 
a SQLite database is clumped up together on disk right next to the journal file 
for that database.  An END TRANSACTION command can involve six (more ?  anyone 
?) accesses of database or journal file, and if all the parts of disk you're 
writing to are near one-another that can involve six rotations of the hard 
disk.  So ...

Hard disk rotates at 5400rpm = 90rotations/s .
6 reads or writes to disk, assuming no skipped rotations, takes 66ms = 1/15th 
of a second .
A loop doing 100 INSERTs can take over six seconds !

This means that in real life SQLite can be faster if you do /not/ defragment 
your drive.  Because you don't have to wait for a full rotation every time.

SSDs, of course, don't have to wait for anything physical.  The improvement in 
speed gained by using transactions does not work the same way: there are still 
fewer operations, so it should still be faster, but not by as much.  My 
original statement about so much of the time being spent on the END TRANSACTION 
no longer holds.  That's why I asked.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "INSERT" and "END TRANSACTION" time

[romtek]

>
> a normal DB where writing does not happen often I would suggest a SSD...
> but anything with a very active read/write cycle is best avoided


If you were to quantify this, would you say that "active" starts at once
per second, many times per second, once a min or...?


On Mon, Mar 3, 2014 at 3:47 PM, RSmith  wrote:

>
> On 2014/03/03 23:11, romtek wrote:
>
>> Simon, does a real disk have to be a rotating hard disk? Is there  problem
>> with SSDs as far as SQLite is concerned?
>>
>
> No, what Simon is trying to point out is simply that the write performance
> experienced by L. Wood might be because journal writes might be synced to
> hard disk (in rotating disk cases anyway) and as such cause delays all
> throughout the transaction even if they are not holding up the final
> commit.  Not because this is in error or wrong in any way, simply as trying
> to explain why he sees the performance spread he sees.
>
> Other reasons might be excessively large binds causing the cache to spill
> or simply have memory writes taking so long that it seems to cause
> inter-transaction delays, although if that were the case one would expect
> the final commit to take a lot longer even.
>
> My money is still on the specific pragmas used which might be forcing
> syncs or non-ACID operation. We would need to have the DB schema and the
> typical query to really test why it works the way it works in his case.
>
>
> One note on SSD's, they pose no physical problem to SQLite, and in fact
> works magically fast, but having a DB which read/write a LOT of data on
> them is not really great since  the repeated read-write cycle of specific
> data areas tire down the typical NAND flash that makes up the SSD, even
> though modern SSDs may use MLC NAND or have firmware that tries to exercise
> every bit in memory equally so as to spread the write operations to avoid
> one piece of disk-memory dying quickly. Eventually though, when all bits of
> memory experienced upwards of 500K write operations (which is quite a
> while), it will fail... but you will have lots of warning. A read-only DB
> on an SSD drive cannot be beaten... even a normal DB where writing does not
> happen often I would suggest a SSD... but anything with a very active
> read/write cycle is best avoided - or at a minimum backed up by a good old
> rotating magnetic platter drive.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "INSERT" and "END TRANSACTION" time

[RSmith]

On 2014/03/03 23:11, romtek wrote:

Simon, does a real disk have to be a rotating hard disk? Is there  problem
with SSDs as far as SQLite is concerned?


No, what Simon is trying to point out is simply that the write performance experienced by L. Wood might be because journal writes 
might be synced to hard disk (in rotating disk cases anyway) and as such cause delays all throughout the transaction even if they 
are not holding up the final commit.  Not because this is in error or wrong in any way, simply as trying to explain why he sees the 
performance spread he sees.


Other reasons might be excessively large binds causing the cache to spill or simply have memory writes taking so long that it seems 
to cause inter-transaction delays, although if that were the case one would expect the final commit to take a lot longer even.


My money is still on the specific pragmas used which might be forcing syncs or non-ACID operation. We would need to have the DB 
schema and the typical query to really test why it works the way it works in his case.



One note on SSD's, they pose no physical problem to SQLite, and in fact works magically fast, but having a DB which read/write a LOT 
of data on them is not really great since  the repeated read-write cycle of specific data areas tire down the typical NAND flash 
that makes up the SSD, even though modern SSDs may use MLC NAND or have firmware that tries to exercise every bit in memory equally 
so as to spread the write operations to avoid one piece of disk-memory dying quickly. Eventually though, when all bits of memory 
experienced upwards of 500K write operations (which is quite a while), it will fail... but you will have lots of warning. A 
read-only DB on an SSD drive cannot be beaten... even a normal DB where writing does not happen often I would suggest a SSD... but 
anything with a very active read/write cycle is best avoided - or at a minimum backed up by a good old rotating magnetic platter drive.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "INSERT" and "END TRANSACTION" time

[romtek]

Simon, does a real disk have to be a rotating hard disk? Is there  problem
with SSDs as far as SQLite is concerned?


On Mon, Mar 3, 2014 at 2:21 PM, Simon Slavin  wrote:

>
> On 3 Mar 2014, at 6:03pm, L. Wood  wrote:
>
> > _bind() on a prepared statement and execution of "INSERT" are taking 70%
> of the time, but the "END TRANSACTION" only 30% of the time.
> >
> > The time between _bind() and execution of "INSERT" is roughly 50/50, so
> it's a total of:
> >
> > _bind(): 35%
> > INSERT: 35%
> > END TRANSACTION: 30%
> >
> > I would have expected the execution of INSERT to taken almost 0% within
> a transaction. Why is this not the case?
>
> Have you used any PRAGMAs which defeat ACID ?  In other words, anything
> that speeds SQLite up ?
>
> Are you writing to a real file on a real rotating hard disk ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "INSERT" and "END TRANSACTION" time

[Simon Slavin]

On 3 Mar 2014, at 6:03pm, L. Wood  wrote:

> _bind() on a prepared statement and execution of "INSERT" are taking 70% of 
> the time, but the "END TRANSACTION" only 30% of the time.
> 
> The time between _bind() and execution of "INSERT" is roughly 50/50, so it's 
> a total of:
> 
> _bind(): 35%
> INSERT: 35%
> END TRANSACTION: 30%
> 
> I would have expected the execution of INSERT to taken almost 0% within a 
> transaction. Why is this not the case? 

Have you used any PRAGMAs which defeat ACID ?  In other words, anything that 
speeds SQLite up ?

Are you writing to a real file on a real rotating hard disk ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users