Re: [sqlite] SQlite on flash filesystem

[Howard Chu]

Simon Slavin wrote:


On 16 Aug 2014, at 9:32pm, Baruch Burstein  wrote:


On Fri, Aug 15, 2014 at 10:45 AM, Levente  wrote:


I am thinking about putting an SQLite database on a flash drive (SD card).
I would like to know what kind of file system is the optimal. I want to
query a lot, and insert or update a few times per minute.


Not directly related to your question, but I am curious: Since sqlite's
atomicity  guarantee assumes that write to a disk sector are linear (e.g.
either beginning to end or end to beginning, but never the middle before
either end),


Mmm ... no.  SQLite's atomicity assumes in-order execution of writes.  In other 
words that the changes to the storage device will be done in the order they are 
requested.  Anything else breaks ACID.  However, SQLite does a lot of things to 
try and avoid a corrupted database even if ACID is broken, in an effort to let 
you rescue the maximum amount of data even if something went wrong with 
hardware.  Removing all the 'paranoid about hardware' and 'rescue' code from 
SQLite would make it significantly smaller.


I was wondering if this assumption was ever verified for flash
drives. I have no information to contrary, but while that assumption makes
a lot of sense for a spinning disk, I see no reason to assume this for
flash drives and other SSDs. I would think that they may parallelize writes
to different parts of the "sector" for efficiency.


Current Flash chips are optimized for sequential writes - while they allow 
writes to a sector in random order, doing so requires sending a new row 
address for each random access. Meanwhile, if you simply write them 
sequentially the write address auto-increments. Since operating systems always 
perform whole-sector writes (indeed, they perform whole-page writes) to 
current storage devices, and they use a DMA controller which is programmed 
with a start address and a block length, you can safely assume that they are 
writing each sector sequentially.


There is no efficiency gain in writing multiple sections of a single sector in 
parallel. (In fact it is an efficiency loss.) Multi-channel SSDs parallelize 
across multiple Flash chips, sending whole sectors to each separate channel/chip.



A good question.  By which I mean it's one people should worry about, not one 
which we're not sure about.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite on flash filesystem

[Simon Slavin]

On 16 Aug 2014, at 9:32pm, Baruch Burstein  wrote:

> On Fri, Aug 15, 2014 at 10:45 AM, Levente  wrote:
> 
>> I am thinking about putting an SQLite database on a flash drive (SD card).
>> I would like to know what kind of file system is the optimal. I want to
>> query a lot, and insert or update a few times per minute.
> 
> Not directly related to your question, but I am curious: Since sqlite's
> atomicity  guarantee assumes that write to a disk sector are linear (e.g.
> either beginning to end or end to beginning, but never the middle before
> either end),

Mmm ... no.  SQLite's atomicity assumes in-order execution of writes.  In other 
words that the changes to the storage device will be done in the order they are 
requested.  Anything else breaks ACID.  However, SQLite does a lot of things to 
try and avoid a corrupted database even if ACID is broken, in an effort to let 
you rescue the maximum amount of data even if something went wrong with 
hardware.  Removing all the 'paranoid about hardware' and 'rescue' code from 
SQLite would make it significantly smaller.

> I was wondering if this assumption was ever verified for flash
> drives. I have no information to contrary, but while that assumption makes
> a lot of sense for a spinning disk, I see no reason to assume this for
> flash drives and other SSDs. I would think that they may parallelize writes
> to different parts of the "sector" for efficiency.

A good question.  By which I mean it's one people should worry about, not one 
which we're not sure about.

No.  Physical writes to Flash drives are optimized for best use of hardware 
(minimum writes, promoting maximum lifetime for the device and fastest 
execution of programs).  Worse still, there is no guarantee that any writes are 
done to the device at all until you have issued 'unmount' to it (shown to the 
user as 'Eject' or 'Make safe for removal' on popular popular operating 
systems). Until then, as far as you're concerned, all the changes may be made 
only to a cache held by the device driver.

So if you pull your Flash drive without telling the OS to unmount it you can 
get all sorts of problems, including no files at all if they were created 
during that session.  It's rare to see this actually happen (few systems create 
a cache that reflects the whole drive then never bother to flush it) but the 
specs of the hardware involved do allow it.

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

Re: [sqlite] SQlite on flash filesystem

[Baruch Burstein]

On Fri, Aug 15, 2014 at 10:45 AM, Levente  wrote:

> I am thinking about putting an SQLite database on a flash drive (SD card).
> I would like to know what kind of file system is the optimal. I want to
> query a lot, and insert or update a few times per minute.
>

Not directly related to your question, but I am curious: Since sqlite's
atomicity  guarantee assumes that write to a disk sector are linear (e.g.
either beginning to end or end to beginning, but never the middle before
either end), I was wondering if this assumption was ever verified for flash
drives. I have no information to contrary, but while that assumption makes
a lot of sense for a spinning disk, I see no reason to assume this for
flash drives and other SSDs. I would think that they may parallelize writes
to different parts of the "sector" for efficiency.
I don't have any knoladge to base this on, I just don't know if this was
ever verified with actual flash drive driver manufacturers.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] geteuid call

[Simon Slavin]

> On 16 Aug 2014, at 8:48pm, Tal Tabakman  wrote:
> 
>> he question becomes "Are you running as root ?  If so, do you have a
>> good reason to be running as root ?"
> 
> No, I am not running as root. I am running in one of those secure
> chamber at customer site.can it be related ?

You should be finding only one geteuid() call each time SQLite tries to open a 
file.  So either geteuid() is taking a long time to execute, or you are doing a 
lot of opening and closing of files.

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

Re: [sqlite] geteuid call

[Tal Tabakman]

Hi Simon

per your question

he question becomes "Are you running as root ?  If so, do you have a
good reason to be running as root ?"

No, I am not running as root. I am running in one of those secure
chamber at customer site.can it be related ?




On Sat, Aug 16, 2014 at 10:01 PM, Tal Tabakman 
wrote:

> Hi Guys,
> we have an application that generates  database in sqlite format,
> at customer, we saw while profiling that the geteuid() call done in
> sqlite3.c is showing up rather high in our profiling report, any idea why ?
> what is this used for ?
> thanks
> Tal
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] geteuid call

[Simon Slavin]

On 16 Aug 2014, at 8:08pm, Richard Hipp  wrote:

> It comes from here:
> http://www.sqlite.org/src/artifact/bd7df3094a609?ln=314-325
> 
> The posixFchown() function is used to ensure that the ownership of newly
> created journal files match the ownership of the database file if the
> process is running as root.

The question becomes "Are you running as root ?  If so, do you have a good 
reason to be running as root ?"

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

Re: [sqlite] geteuid call

[Richard Hipp]

On Sat, Aug 16, 2014 at 3:01 PM, Tal Tabakman 
wrote:

> Hi Guys,
> we have an application that generates  database in sqlite format,
> at customer, we saw while profiling that the geteuid() call done in
> sqlite3.c is showing up rather high in our profiling report, any idea why ?
> what is this used for ?
>

It comes from here:
http://www.sqlite.org/src/artifact/bd7df3094a609?ln=314-325

The posixFchown() function is used to ensure that the ownership of newly
created journal files match the ownership of the database file if the
process is running as root.




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



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

Re: [sqlite] Long time to drop tables.

[Richard Hipp]

On Sat, Aug 16, 2014 at 2:41 PM, Jonathan Moules <
jonathanmou...@warwickshire.gov.uk> wrote:

> Hi List,
> More of a curiosity.
> I'm doing some general data munging and set off a query that consists
> entirely of 37 DROP TABLEs in it. The database it's running against is
> a bit less than 1GB made of about 5 million rows, and the tables being
> dropped constitute about 99% of the content.
>
> My questions is - why does it take so long? The total time required
> to create this dataset (most of which was processing on the Python
> side) was about 11 minutes.
>
> The total time required to perform these drops is ... well I
> cancelled it at 20mins - it had deleted 20 of the 37. For that entire
> period SQLite has been reading at a rate of 170MB/s - by my maths it
> had read about 200GB!
>
> The tables don't have indexes, the settings are all whatever the
> defaults are.
>
> Any suggestions what's going on? Is this normal behavior?
>


I made a copy of a 2.3GB database that contained two tables that comprised
90% of the database space.  I did a DROP TABLE on each.  Each DROP TABLE
took about 100 milliseconds, real-time.

DROP TABLE bmdTileTable;
Run Time: real 0.109 user 0.052133 sys 0.043098
DROP TABLE bmdTileTable_with_out;
Run Time: real 0.102 user 0.052688 sys 0.029648

Maybe you have autovacuum turned on in your database?  Autovacuum makes
doing things like DROP TABLE much slower because it has to rearrange
content in order to move it all to the front of the file then truncate the
file.


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

[sqlite] geteuid call

[Tal Tabakman]

Hi Guys,
we have an application that generates  database in sqlite format,
at customer, we saw while profiling that the geteuid() call done in
sqlite3.c is showing up rather high in our profiling report, any idea why ?
what is this used for ?
thanks
Tal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Long time to drop tables.

[Keith Medcalf]

Do you have foreign key constraints on the tables?  Are they enabled?

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Jonathan Moules
>Sent: Saturday, 16 August, 2014 12:41
>To: sqlite-users@sqlite.org
>Subject: [sqlite] Long time to drop tables.
>
>Hi List,
>   More of a curiosity.
>I'm doing some general data munging and set off a query that consists
>entirely of 37 DROP TABLEs in it. The database it's running against is
>a bit less than 1GB made of about 5 million rows, and the tables being
>dropped constitute about 99% of the content.
>
>   My questions is - why does it take so long? The total time required
>to create this dataset (most of which was processing on the Python
>side) was about 11 minutes.
>
>   The total time required to perform these drops is ... well I
>cancelled it at 20mins - it had deleted 20 of the 37. For that entire
>period SQLite has been reading at a rate of 170MB/s - by my maths it
>had read about 200GB!
>
>   The tables don't have indexes, the settings are all whatever the
>defaults are.
>
>   Any suggestions what's going on? Is this normal behavior?
>   Thanks,
>   Jonathan
>
>--
>This transmission is intended for the named addressee(s) only and may
>contain confidential, sensitive or personal information and should be
>handled accordingly. Unless you are the named addressee (or authorised to
>receive it for the addressee) you may not copy or use it, or disclose it
>to
>anyone else. If you have received this transmission in error please
>notify
>the sender immediately. All email traffic sent to or from us, including
>without limitation all GCSX traffic, may be subject to recording and/or
>monitoring in accordance with relevant legislation.
>___
>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] Long time to drop tables.

[Simon Slavin]

On 16 Aug 2014, at 7:41pm, Jonathan Moules  
wrote:

>   My questions is - why does it take so long? The total time required
> to create this dataset (most of which was processing on the Python
> side) was about 11 minutes.
>   
>   The total time required to perform these drops is ... well I
> cancelled it at 20mins

What hardware are you running ?  Is the database on storage belonging to that 
computer running the SQLite command, or are you accessing it across a network 
or as an external storage device ?  What version of SQLite are you running ?

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

[sqlite] Long time to drop tables.

[Jonathan Moules]

Hi List,
More of a curiosity.
I'm doing some general data munging and set off a query that consists
entirely of 37 DROP TABLEs in it. The database it's running against is
a bit less than 1GB made of about 5 million rows, and the tables being
dropped constitute about 99% of the content.

My questions is - why does it take so long? The total time required
to create this dataset (most of which was processing on the Python
side) was about 11 minutes.

The total time required to perform these drops is ... well I
cancelled it at 20mins - it had deleted 20 of the 37. For that entire
period SQLite has been reading at a rate of 170MB/s - by my maths it
had read about 200GB!

The tables don't have indexes, the settings are all whatever the 
defaults are.

Any suggestions what's going on? Is this normal behavior?
Thanks,
Jonathan

-- 
This transmission is intended for the named addressee(s) only and may 
contain confidential, sensitive or personal information and should be 
handled accordingly. Unless you are the named addressee (or authorised to 
receive it for the addressee) you may not copy or use it, or disclose it to 
anyone else. If you have received this transmission in error please notify 
the sender immediately. All email traffic sent to or from us, including 
without limitation all GCSX traffic, may be subject to recording and/or 
monitoring in accordance with relevant legislation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does SQLite lock the entire DB for writes, or lock by table?

[Tom]

Hi guys,

I've proceeded to test out whether SQLite could serve my needs and so far I
am pretty optimistic that I can continue to use it at least until my game
becomes massively popular. Wanted to share my results. 

My game does not in fact require a lot of write concurrency. It is just the
daily processing of results that needs to happen concurrently. So I wanted
to see if using 2 DB's would work. 

I have 2 threads and 2 DB's. 

Threads:
* main thread
* daily processing thread ("DP")

Databases:
* main DB
* results DB

Usage:
* Main DB is read and written to by the main thread and read by the DP
thread
* Results DB is read and written to by the DP thread and read by the main
thread

So there is no write concurrency to any DB. The daily processing happens
once a day and takes about 10-15 minutes for 1M records in the main DB (very
intensive computations, particularly for tallying votes). In effect, the
results DB is the output of the DP acting on the main DB.

This seems to be working really well. I am testing a simulated load of
15 clients, and the server doing all the DB work is running at my target
framerate of 30 fps. Typically with games we expect around 10% of users to
be online at peak, so let's say this solution supports 1.5M active players
or more. Good enough to go live with!

A few more details:
* Each thread has its own connection, opening the main DB and attaching the
results DB.
* I'm using WAL on both connections
* No operations return BUSY or LOCKED, and I don't use any mutexes.

Anyway I wanted to share these results and see if you had any comments. 

Cheers

Tom








--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Does-SQLite-lock-the-entire-DB-for-writes-or-lock-by-table-tp76921p77358.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users