Re: [Tracker] support for other database backends?

[Philip Van Hoof <philip codeminded be>]

On Mon, 2012-05-28 at 09:48 +0100, Martyn Russell wrote:
> On 05/25/2012 01:20 PM, Jussi Laako wrote:
> > Hi Martyn,

> > In most cases, like Nokia N9, you cannot have sudden power removal,
> > except if the device gets completely broken and in that case it doesn't
> > matter anymore anyway. However, excessive syncing causes kernel to
>
> These are hard conditions to cater for I think. It was a requirement to 
> make sure data was syncd frequently enough or when we knew there was 
> exposure to a situation where loss of power could occur (e.g. take the 
> back off the phone).

The reason for the N9 was that a long-hold press of the power button on
the N9 results in a (at least for Tracker's process) uninterruptable and
more importantly uninterceptable power shutdown of the device.

If this were not the case it indeed wouldn't have been needed to ever do
a fsync, as all shutdowns would do unmount prior to powerdown.

This is also why at some point, before the SQLite WAL stuff that we did,
we experimented with the SYNCHRONOUS=off SQLite PRAGMA setting.

Because we also needed backup+restore and because it turned out that the
device, by long-hold pressing the power button, could be turned off
unexpectedly, we at some point had a manually implemented journal that
was on top of giving us journal-style append writing with controllable
fsync behavior allowing us to keep synchronous=off and at the same time
implementing our backup-restore strategy (restore meant trashing meta.db
and simply letting the journal be applied on a new empty db, backup
meant making a copy of the journal).

Later we switched to SQLite's WAL and we restored our backup-restore
strategy to use the sqlite3_backup APIs (backup now means to make a copy
of meta.db at runtime, restore now means to replace meta.db and reinit
all the database handles).

> By the time of the launch of the N9 I don't recall hearing many (any?) 
> cases of data loss through power loss.

Hmm :)

> In fact, the extra sync pragma reduced the number of cases we heard 
> about related to DB corruption IIRC.
>
> > trigger filesystem barriers which causes severe filesystem stalls. It
> > also causes flash wear.
>
> The flash wear is indeed worth considering. Though from my brief 
> research, 100,000 program-erase cycles are guaranteed by most vendors 
> and some up to 1,000,000. What makes this hard to figure, is how many 
> Tracker is doing and what sort of use cases are causing this level of 
> use. There is also wear levelling which likely helps this situation.
>
> At least, testing with Tracker on the N9 rarely found any disk errors 
> and in the cases we did, it was a hardware fault Tracker couldn't help 
> with anyway.
>
> The rest of the guys can chime in here, but as I understand it, the sync 
> pragmas really just make sure we sync more often. A sync is needed at 
> some point regardless.

Sync is only needed in case power can be cut unexpectedly and you want
to somewhat survive that.

If the long-hold powerbutton press on the N9 were interceptable, we
wouldn't have needed sync.

Note for future device builders and for the people at the hardware
department:

        No you are not right when you say that your device needs a button that
isn't interceptable by the software guys. Your button must be
interceptable by the software guys and if you don't do that your
software guys do need to make decisions that will make the whole
experience of the entire platform a lot slower, thanks to your (and only
your) flawed decision. And no, battery-empty isn't a good counter-
argument: software can detect in time that the battery is about to be
empty, and software can take actions based on that information in time.

If your device needs a removable battery, provide short-term backup
power (small embedded battery) or signal the software that the battery
case got opened.

ps. yes, I fear that the HW world wont listen, oh well ;-)

> I would be interested to hear any research around disk use and flash 
> wear for databases which may have influenced your decisions here.
>
> There are a lot of variables which can affect flash wear, I don't have 
> enough information to know if extra syncing is really causing this or 
> not and the life expectancy difference too.
>
> Â http://en.wikipedia.org/wiki/Flash_memory#Memory_wear
> Â http://en.wikipedia.org/wiki/Wear_leveling
>
> > > PostgreSQL is nicer to work with than MySQL from what I recall. We would
> > > gain a lot from a bigger database, but likely loose a lot on embedded
> > > systems and the efficiencies we made there. That's partly why we wanted
> > > to do some testing on this some months ago, to figure if the switch was
> > > worth it.
> >
> > I was running postgre server for long time on my Nokia N900 proto and
> > nobody could notice any negative impact due to it. It can be tuned to be
> > fairly light. And it already consumes less CPU time than SQLite in many
> > real use cases.
>
> Interesting. We were really hoping to have time to try our PostgreSQL.
>
> > I think it should implemented such way that the database backend could
> > be build-time selected to be either sqlite or postgre. Not being
> > exclusively either one.
>
> The problem is, we have SQLite specifics which might be hard to adapt. I 
> think JÃrg and Philip might have more input here that's useful...

Sure :)

I'm awaiting a patch from the nice guys from Intel before spending time
and energy on "PostgreSQL porting", or a contract to do it for them ;-)

> At least it looks like PostgreSQL has FTS support which I would love to 
> look into.


Kind regards,

Philip

-- 


Philip Van Hoof
Software developer
Codeminded BVBA - http://codeminded.be