Hope to get some tips here, have a 3B+ running domoticz. At the moment the /var and /home is directed to a 1.8" harddisk to prevent the sd card from fast destruction. We talk about 1-3 events per second from my smart things that need to be written to the sqlite database. As harddisks in general are becoming rare, i plan to migrate that to an external SSD on the USB.
First question, are there any filesystem suggestions that match better to an sql database (only around 10meg)?
Are there further tips for formatted blocksize or how to get more lifespan from a SSD?
Solderbro
SQLite database on SSD?
RPI 3B+, Raspbian Buster
-
DougieLawson
- Posts: 39126
- Joined: Sun Jun 16, 2013 11:19 pm
- Location: A small cave in deepest darkest Basingstoke, UK
- Contact: Website Twitter
Re: SQLite database on SSD?
EXT4 or ReiserFS or anything else that's a native Linux FS (NOT FAT, FAT32, exFAT or NTFS). EXT2 is probably OK since SQLite3 does it's own journalling with a write-ahead log (WAL).
Bear in mind that SQLite3 has a simple restriction; ONE writer, many readers (use PRAGMA query_only; for readers).
Don't know squat about filesystem blocksizes, I'm in the "take the defaults" school.
Bear in mind that SQLite3 has a simple restriction; ONE writer, many readers (use PRAGMA query_only; for readers).
Don't know squat about filesystem blocksizes, I'm in the "take the defaults" school.
Note: Any requirement to use a crystal ball or mind reading will result in me ignoring your question.
Criticising any questions is banned on this forum.
Any DMs sent on Twitter will be answered next month.
All non-medical doctors are on my foes list.
Criticising any questions is banned on this forum.
Any DMs sent on Twitter will be answered next month.
All non-medical doctors are on my foes list.
-
GOTO GOSUB
- Posts: 12
- Joined: Tue Oct 01, 2019 11:15 am
Re: SQLite database on SSD?
Overprovisioning on an SSD is still probably a good idea too, this is where you deliberately leave a percentage of the drive unpartitioned so that the SSD controller always has a pool of spare cells available to it to improve overall write performance and wear levelling.
Most (if not all) modern drives and OS's can use existing cells for this so long as TRIM is enabled and modern controllers in particular are getting very good at garbage collection, but if you can afford to leave some of an SSD's capacity unallocated for use by the controller then it may well provide both performance gains and better life expectancy particularly as the drive ages.
Definitely a +1 for using a native file system as this should, in theory at least, have better performance that having to use a bridge to say NTFS. Speaking from personal experience with a Pi 4 and a Samsung QVO SSD in an external SATA - USB3 enclosure as a 24/7 torrent box I would say that this is working well for me - granted most of the time my torrent-box is seeding so there are very few writes going on but I use a SAMBA share to create a network drive visible to the Windows PC's on the network and while the SAMBA transfers are not spectacularly fast the Pi spends a lot of its time basically idling as the overall performance of the SSD is pretty high and the Pi 4 is unlikely to ever trouble the SSD in bandwidth.
If you are worried about write amplification or general write frequency then perhaps you can allocate some RAM to allow for some caching to reduce write-out. Personally I am using:
... and have turned off the last-read logging at the file system level as I don't care when a file was last accessed.
... just make sure you won't lose any vital data in the event of a power failure between writes if you start caching anything to RAM.
Most (if not all) modern drives and OS's can use existing cells for this so long as TRIM is enabled and modern controllers in particular are getting very good at garbage collection, but if you can afford to leave some of an SSD's capacity unallocated for use by the controller then it may well provide both performance gains and better life expectancy particularly as the drive ages.
Definitely a +1 for using a native file system as this should, in theory at least, have better performance that having to use a bridge to say NTFS. Speaking from personal experience with a Pi 4 and a Samsung QVO SSD in an external SATA - USB3 enclosure as a 24/7 torrent box I would say that this is working well for me - granted most of the time my torrent-box is seeding so there are very few writes going on but I use a SAMBA share to create a network drive visible to the Windows PC's on the network and while the SAMBA transfers are not spectacularly fast the Pi spends a lot of its time basically idling as the overall performance of the SSD is pretty high and the Pi 4 is unlikely to ever trouble the SSD in bandwidth.
If you are worried about write amplification or general write frequency then perhaps you can allocate some RAM to allow for some caching to reduce write-out. Personally I am using:
... and have turned off the last-read logging at the file system level as I don't care when a file was last accessed.
... just make sure you won't lose any vital data in the event of a power failure between writes if you start caching anything to RAM.