Re: [Tracker] support for other database backends?

[Martyn Russell <martyn lanedo com>]

On 05/25/2012 01:20 PM, Jussi Laako wrote:
> Hi Martyn,

Hello Jussi,

> On 11.5.2012 17:18, Martyn Russell wrote:
> > they're faster. Last time I checked (a while back), MySQL was faster
> > than PostgreSQL for querying. Just wondering why you've thought of
> > PosgreSQL over MySQL really.
>
> PostgreSQL is the only one that has LISTEN/NOTIFY system (especially
> nice in 9.x series) with C function callbacks. Extremely useful for
> notifying about updates in multi-client environment.

Indeed, I remember it well. It's quite nice I agree.

> > I wasn't aware of the D-Bus ordering issue? Though I believe the store
> > does collapse duplicate events. It also has been designed to work on
> > embedded systems with less resources, so we have various optimisations
> > to avoid spamming clients with events which are useless.
>
> It still involves single-threaded central daemon that copies everything
> around. So everything goes through double-copy. This also messes up
> kernel scheduler because it hides inter-process scheduling dependencies.

Right.

> > Indeed. With the store, we really needed to be sure data was written in
> > cases where you had instant power outage (e.g. you drop your phone down
> > the toilet). So while caches are good, syncing is needed to some extent.
>
> 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).

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

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.

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...

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

--
Regards,
Martyn

Founder and CEO of Lanedo GmbH.