Re: [sqlite] Goodram C50 SSD slow in SQlite benchmark

[Max Vlasov]

On Mon, Mar 24, 2014 at 4:49 AM, piotr maliński  wrote:
> I know it's bad. I'm trying to determine the cause of the difference, and
> if it's a "feature" of that SSD or a bug of some sort.

There was a very intensive discussion for a post labeled
"UPDATE/INSERTing 1-2k rows slower than expected". You can read it at
https://www.mail-archive.com/sqlite-users%40sqlite.org/msg58872.html .
Also there were different tests I made during this discussion. As long
as I remember the general observation was that it's hardware that
usually says "ok, I did this guaranteed -to-be-on-disk operation
you've asked for", but actually caching it somewhere inside. And
probably multiply USB controllers from the bunch of manufacturers are
to blame. SATA controller on motherboards are usually less
diversified, so more likely to be more accurate.

Also there's a setting in Windows for hard drives, "enable writing
cache". If you find a similar setting in xubuntu, then probably
enabling it would make your sata connection on par with your usb
connection. But it's just a hypothesis, it's harder to make useful
tests with sata connection due physical and interface limitations of
the interface.

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

Re: [sqlite] How good is pragma integrity_check

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 21/03/14 15:24, Simon Slavin wrote:
> Checksums stored with the page index lists,

SQLite already has the ability to carve out data on each page for other
uses.  For example the encryption extension uses this.

> Nevertheless, the basic SQLite engine is so efficient that any
> slow-down would be noticed and might cause complaints.

It couldn't be on by default for backwards compatibility reasons.  (WAL is
another example of that.)

So you don't have to use it.  Those of us who would rather proactively
know about data corruption (to the best of SQLite's ability to detect it)
are happy to take whatever hit there would be.  After all, fast queries on
corrupted data are pointless.

And as we see in messages on this list, finally spotting corruption long
after it first happened is very hard to recover from and hard to nail down
the cause for.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.14 (GNU/Linux)

iEYEARECAAYFAlMvwWMACgkQmOOfHg372QSnOQCdEpBWBvcNsntkZ6WPvDs0yAju
fc0AoJzagj56DyoYrhmeE73rwHhe+D2f
=ZAfw
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Goodram C50 SSD slow in SQlite benchmark

[piotr maliński]

I know it's bad. I'm trying to determine the cause of the difference, and
if it's a "feature" of that SSD or a bug of some sort. I'll check if the
speed changes when I disable barrier on the mounted filesystem. If so then
it's slow on write barriers.


2014-03-24 1:35 GMT+01:00 Simon Slavin :

>
> On 23 Mar 2014, at 11:19pm, piotr maliński  wrote:
>
> > So maybe this particular SSD is slow with write barriers, while other
> > doesn't have a problem with it (as they don't change performance between
> > SATA and USB3).
>
> You have missed the point.  When Florian write "Many USB SATA adapters do
> not handle [write barriers] correctly" he is saying that your storage
> medium will corrupt your data if power fails or other hardware problems
> occur before the files are not closed correctly.  Write barriers are
> important.
>
> SQLite tries very hard to make sure your data is safe.  Doing this
> involves a number of 'write' commands which have to be done in a specific
> order.  Rotating disk drives are very slow to do this because they have to
> wait for the disk to rotate to the right position each time.  SSD storage
> is faster because any sector can be written at any time, but they still
> should be accepting each 'write' command, then doing it, then returning a
> result of 'write successful'.  In other words, they should not be able to
> say "command executed" immediately.  They should have a write barrier.
>
> If you find some storage system that is unexpectedly fast, it is probably
> not doing this correctly.  It is accepting the 'write' command, immediately
> saying that the command was successful, but actually doing the 'write'
> command in the background later.  Most desktop computers are deliberately
> set up to do this because it makes the computer run faster, and that's what
> users want.  And most storage systems that do this do not do the writes in
> the order the commands were given.  They blast all the writes to storage in
> sector order, because this gives, overall, a faster result.
>
> And that's why hard disks sold to be used in servers apparently run more
> slowly: they are set to do storage properly, because they are more likely
> to be used to store valuable data rather than just write a document or
> email which can be retyped if changes are lost.
>
> So yes, one drive system is running quickly.  But it's doing it at the
> cost of corrupting your data if you suffer a hardware problem.  You have
> lost the ACID property of your database.  If you want to know that your
> data is safe, you're probably going to want the system that runs slowly.
>
> On the other hand, that particular type of SSD may just have timings
> slightly different from what the storage system expects.
>
> 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] Goodram C50 SSD slow in SQlite benchmark

[Simon Slavin]

On 23 Mar 2014, at 11:19pm, piotr maliński  wrote:

> So maybe this particular SSD is slow with write barriers, while other
> doesn't have a problem with it (as they don't change performance between
> SATA and USB3).

You have missed the point.  When Florian write "Many USB SATA adapters do not 
handle [write barriers] correctly" he is saying that your storage medium will 
corrupt your data if power fails or other hardware problems occur before the 
files are not closed correctly.  Write barriers are important.

SQLite tries very hard to make sure your data is safe.  Doing this involves a 
number of 'write' commands which have to be done in a specific order.  Rotating 
disk drives are very slow to do this because they have to wait for the disk to 
rotate to the right position each time.  SSD storage is faster because any 
sector can be written at any time, but they still should be accepting each 
'write' command, then doing it, then returning a result of 'write successful'.  
In other words, they should not be able to say "command executed" immediately.  
They should have a write barrier.

If you find some storage system that is unexpectedly fast, it is probably not 
doing this correctly.  It is accepting the 'write' command, immediately saying 
that the command was successful, but actually doing the 'write' command in the 
background later.  Most desktop computers are deliberately set up to do this 
because it makes the computer run faster, and that's what users want.  And most 
storage systems that do this do not do the writes in the order the commands 
were given.  They blast all the writes to storage in sector order, because this 
gives, overall, a faster result.

And that's why hard disks sold to be used in servers apparently run more 
slowly: they are set to do storage properly, because they are more likely to be 
used to store valuable data rather than just write a document or email which 
can be retyped if changes are lost.

So yes, one drive system is running quickly.  But it's doing it at the cost of 
corrupting your data if you suffer a hardware problem.  You have lost the ACID 
property of your database.  If you want to know that your data is safe, you're 
probably going to want the system that runs slowly.

On the other hand, that particular type of SSD may just have timings slightly 
different from what the storage system expects.

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

Re: [sqlite] Goodram C50 SSD slow in SQlite benchmark

[piotr maliński]

So maybe this particular SSD is slow with write barriers, while other
doesn't have a problem with it (as they don't change performance between
SATA and USB3).


2014-03-23 22:18 GMT+01:00 Florian Weimer :

> * piotr maliński:
>
> > The usb3 case has asmedia chip and phoronix sqlite insert benchmark runs
> > around 6 times faster than when running via sata. Some benchmarks like
> > untaring, some iozone benchmarks do not differ so I'm suspecting
> controller
> > or OS issue. Does sqlite insert uses some specific I/O operations?
>
> Yes, write barriers.  Many USB SATA adapters do not handle them
> properly, leading to significant speed-ups and increased risk of data
> loss.
> ___
> 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] Goodram C50 SSD slow in SQlite benchmark

[Florian Weimer]

* piotr maliński:

> The usb3 case has asmedia chip and phoronix sqlite insert benchmark runs
> around 6 times faster than when running via sata. Some benchmarks like
> untaring, some iozone benchmarks do not differ so I'm suspecting controller
> or OS issue. Does sqlite insert uses some specific I/O operations?

Yes, write barriers.  Many USB SATA adapters do not handle them
properly, leading to significant speed-ups and increased risk of data
loss.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Goodram C50 SSD slow in SQlite benchmark

[piotr maliński]

I ha ve C50 Goodram SSD which behaves weirdly. Im using Xubuntu 14.04 and
phoronix benchmark suite. When the SSD is connected via SATA some
benchmarks like blogbench or even more sqlite inserts benchmark run much
slower than when the SSD is connected via usb3 case or vs other SSD
connected to that SATA 3 port.

The usb3 case has asmedia chip and phoronix sqlite insert benchmark runs
around 6 times faster than when running via sata. Some benchmarks like
untaring, some iozone benchmarks do not differ so I'm suspecting controller
or OS issue. Does sqlite insert uses some specific I/O operations? Such
that wouldn't be common or present in test like untaring or more raw speed
testing.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why is SQLite refusing to use available indexes when adding a JOIN?

[Ofer Sadgat]

Why is SQLite refusing to use available indexes when adding a JOIN?

The queries to create the database is:

CREATE TABLE foo(id TEXT);
CREATE INDEX `foo.index` ON foo(id);
CREATE TABLE bar(id TEXT);
CREATE INDEX `bar.index` ON bar(id);
CREATE VIEW baz AS SELECT id FROM foo UNION ALL SELECT id FROM bar;
CREATE TABLE bam(id TEXT, value TEXT);

INSERT INTO foo VALUES('123');
INSERT INTO foo VALUES('1123');
INSERT INTO foo VALUES('2123');
INSERT INTO foo VALUES('3123');

INSERT INTO bar VALUES('44123');
INSERT INTO bar VALUES('441123');
INSERT INTO bar VALUES('442123');
INSERT INTO bar VALUES('443123');

The result of EXPLAIN QUERY PLAN SELECT * FROM baz LEFT JOIN bam ON baz.id=
bam.id WHERE baz.id IN ('123', '234'); is:

SCAN TABLE foo (~100 rows)
SCAN TABLE bar (~100 rows)
COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
SCAN SUBQUERY 1 (~200 rows)
EXECUTE LIST SUBQUERY 4
SEARCH TABLE bam USING AUTOMATIC COVERING INDEX (id=?) (~7 rows)

Interestingly enough if I do EXPLAIN QUERY PLAN SELECT * FROM (SELECT *
FROM baz WHERE baz.id IN ('123', '234')) AS t LEFT JOIN bam ON t.id=bam.id ; it
still doesnt use the index but if i do EXPLAIN QUERY PLAN SELECT * FROM baz
WHERE baz.id IN ('123', '234'); it does. What is going on?

Why isnt it using the indexes on foo and bar?? It does use the indexes
without the JOIN section as apparent in the linked question.

SQL Fiddle: http://sqlfiddle.com/#!7/32af2/14 (use WebSQL)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why is SQLite refusing to use available indexes when adding a JOIN?

[e dooby]

The queries to create the database is:

CREATE TABLE foo(id TEXT);
CREATE INDEX `foo.index` ON foo(id);
CREATE TABLE bar(id TEXT);
CREATE INDEX `bar.index` ON bar(id);
CREATE VIEW baz AS SELECT id FROM foo UNION ALL SELECT id FROM bar;
CREATE TABLE bam(id TEXT, value TEXT);

INSERT INTO foo VALUES('123');
INSERT INTO foo VALUES('1123');
INSERT INTO foo VALUES('2123');
INSERT INTO foo VALUES('3123');

INSERT INTO bar VALUES('44123');
INSERT INTO bar VALUES('441123');
INSERT INTO bar VALUES('442123');
INSERT INTO bar VALUES('443123');

The result of EXPLAIN QUERY PLAN SELECT * FROM baz LEFT JOIN bam ON 
baz.id=bam.id WHERE baz.id IN ('123', '234'); is:

SCAN TABLE foo (~100 rows)
SCAN TABLE bar (~100 rows)
COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
SCAN SUBQUERY 1 (~200 rows)
EXECUTE LIST SUBQUERY 4
SEARCH TABLE bam USING AUTOMATIC COVERING INDEX (id=?) (~7 rows)

Interestingly enough if I do EXPLAIN QUERY PLAN SELECT * FROM (SELECT * 
FROM baz WHERE baz.id IN ('123', '234')) AS t LEFT JOIN bam ON t.id=bam.id ; it 
still doesnt use the index but if i do EXPLAIN QUERY PLAN SELECT * FROM baz 
WHERE baz.id IN ('123', '234'); it does. What is going on?

Why isnt it using the indexes on foo and bar?? It does use the indexes 
without the JOIN section as apparent in the linked question.

SQL Fiddle: http://sqlfiddle.com/#!7/32af2/14 (use WebSQL)

This issue has been plaguing me for quite a while. Any help is greatly 
appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users