On 28 Oct 2018, at 11:47pm, Gerlando Falauto <gerlando.fala...@gmail.com> wrote:
> 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? You do indeed have no clue. And so do I unless I use a utility to look at the low-level formatting of the SSD. Which involves you doing things at the wrong level which, as I point out later, is not going to matter in the long run. Please also remember that an SSD can write a new block even if you change just one byte in the middle of an existing file. That's what wear-levelling can mean. >> 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. With an SSD, if you're minimising wear you want to do the minimum number of operations. SQLite is good at doing the minimum number of write operations to take care of lots of changes at once. Deleting a hundred rows individually may require five hundred write operations. Deleting a hundred contiguous rows in one command may require just fifty. Or even twenty. Of course, the requirement to reduce write operations may be in conflict with other requirements: lack of a long lock-out time, or minimised size of WAL file. >> 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? The WAL file is not the only file which gets written in that pattern. So does the database file itself. The additional non-standard weirdness of putting the WAL file somewhere special isn't going to make a huge difference. >> 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'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. Writing a log entry to a text file is incredibly efficient. It's the number of octets in the log entry plus, for the sake of argument, one extra octet for a return character. At most, you're writing to two blocks, plus assorted file meta-information. Writing a field with the same number of octets to a SQLite database, in its own transaction, involves (not in this order) writing it to the journal file, writing it to the database, adding an entry to all indexes, writing row-length data, changing table-length and index-length data, changing the database header twice, and probably a bunch of things I've forgotten. If your log database gets long you might write four times the amount of octets, to six different locations on disk. For each log entry. I don't really know enough about SQLite's ACID writing pattern to give accurate numbers but that's a reasonable horseback guess. Maybe someone who does will improve it. My take-away from thinking about this is again what I wrote before: write each log to a text file in a single atomic operation. That way you know exactly what you did, and you perform one operation per log entry. And deleting one log file per day/week/month is a single DELETE & TRIM to get rid of numerous log entries. By all means, when/if it comes time to analyse the log entries, turn them into the so-useful database on whatever computer you use to do your analysis, meaning you aren't worried about performing lots of operations on your production SSD. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
