Re: [sqlite] SQlite on flash filesystem
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
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
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
> 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
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
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
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.
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
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.
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.
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.
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?
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