On Sun, Oct 28, 2018 at 6:18 PM Simon Slavin <slav...@bigfraud.org> wrote:
> On 28 Oct 2018, at 2:06pm, Gerlando Falauto <gerlando.fala...@gmail.com> > wrote: > > > There are two (apparently) opposing requirements: > > - in case of a power outage, all data collected up to at most N seconds > > prior to the power failure should be readable. Ideally N=0, but what's > > important is that the database never gets corrupted. > > Unless you really know what you're doing, leave SQLite settings and > PRAGMAs at their defaults. This gives you the safest, least corruptable, > settings. WAL mode is fine. > > That was also my guess. > > - the SSD's wear due to continuous writes should be reduced to a minimum > > I assume your App generates items for the log one by one. Yes, items are indeed generated one by one. However, since they're coming from several (about a dozen) more-or-less-synchronized sources, they would generally come in bursts. > There's not much you can do to reduce wear if you want each one logged as > soon as possible. > I guess I'm looking for the best trade-off, but I don't really know how things really work under the hood so I'd like to avoid making totally wrong assumptions or stupid mistakes. So for instance, on a bare NOR flash, I believe I might assume I can safely write at any granularity (even a single bit) as long as I change 1's to 0's. On an SSD, I have no clue what's going on -- will writing a 2 MB block one page at a time be much worse than writing an entire block at once? > In terms of deleting rows, if you want the least wear on your SSD, do the > deletes seldomly. Definitely no more than once a day. > Hmmm, I don't understand, could you please elaborate on that? I first tried this kind of approach (DELETE once a day) for simplicity, but deleting big amounts of rows at once seemed to potentially take a relatively long time and generate a very big WAL file. So my uneducated guess was then that I'd be better served by making room (DELETE) just before adding new data (INSERT) so to reuse just-deleted pages. > There's just too many layers which will affect the end result: > > Correct. And remember that pretty much every hardware driver lies to the > OS to make the hardware seem faster. > Agreed. > For instance, one crazy idea might be to put the WAL file on a ramdisk > > I understand why you're raise that but, as you suspect, it's not going to > help. > Reason for that being? The WAL file won't get sync()ed? > I found the "Total_Erase_Count" reported by smartmontools to be an > > interesting end-to-end metric in the long run > > If you care about that level of detail, using SQLite is the wrong > approach. > You should be logging by appending each entry to a text file. Or maybe to > a new textfile every day/week/month. Then, to prune the log just delete > one old textfile each time you start a new one. I was afraid I would get that kind of answer at some point. It's not that I do (or don't) care about that level of detail. It's just that I'd like to get a rough idea of how much worse it is to use SQLite compared to this approach, in terms of SSD wearout. If it's a 2x factor, I'm probably quite OK with that. If it's a 100x factor, then I have a problem. If there was a simple way to split the database into say 10 different files (which could be queried against as a single database though), and just rotate among those, I'd be very happy about it. Is there? Using ATTACHed databases and carefully-crafted UNION statements perhaps? > Convert a text file to a database only if you want to query the log, which > is presumably a rare operation because you're such a good programmer/admin > that stuff rarely goes wrong. > Good one. :-) Kidding aside, collected data are measurements from external devices whose reading back might be required at any point in time for e.g. analytics purposes. I still don't know when or with what pattern -- it just seemed quite convenient and efficient to have a SQL database (with its indexes) to query it. Thanks a lot for your help! Gerlando _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
