Advantage(s) of switching from SQLite to Postgres/MySQL backend?

Grafana Configuration
1

In a single-host environment, are there potential performance or reliability advantages to using an external PostgreSQL/MySQL store for Grafana’s database, instead of the on-disk SQLite database?

An external database engine would obviously be required in order to run a multi-host HA environment - and while we may shift to that in the future, it’s not something we’re doing just yet. Should we consider switching backend nonetheless?

With the SQLite backend, I’m somewhat spooked by a a large number of messages like

Apr 18 13:32:30 xxx grafana-server[1062]: t=2019-04-18T13:32:30+0000 lvl=info msg="Database table locked, sleeping then retrying" logger=sqlstore retry=1
Apr 18 13:32:30 xxx grafana-server[1062]: t=2019-04-18T13:32:30+0000 lvl=info msg="Database table locked, sleeping then retrying" logger=sqlstore retry=0
Apr 18 13:32:30 xxx grafana-server[1062]: t=2019-04-18T13:32:30+0000 lvl=info msg="Database table locked, sleeping then retrying" logger=sqlstore retry=2

which also bring to mind the possibility of on-disk data corruption.

We’re considering moving the datastore to a Postgres RDS instance in AWS. This may (notionally?) improve reliability, though performance-wise I guess it’s maybe somewhat of a toss-up? I’m in particular conscious that having all data be pulled from the DB over a network (rather than from local disk) may introduce some extra latency. Though not sure whether this would be noticeable to the user.

Any pointers on best practices here?

1 Like
2

For huge metrics, postgres has Timescale, which essentially cuts yours metrics table into pieces and references it into a hypertable making querys way faster under heavy workloads

3

@kainzje this was a question regarding Grafana backend database and not timeseries database.

@svetb sqlite usually works up until a certain load (number of users, alert rules). But with a lot of users or alert rules then we recommend MySQL/Postgres, Lately I’ve also seen more and more people running sqlite on docker with NFS backed volumes which is a bad idea and leads to a lot of locks. If you think you will get more and more users/alert rules over time and/or think you’ll need HA in the future I would recommend switching now.

4

Thanks @mefraimsson, that’s the sort of guidance I was after. My assumption has generally been that SQLite isn’t suitable for production loads that may at times get heavy, but I wasn’t sure if we should just roll with it for now and see how far we can push it.

But also given that we’ll definitely move to HA at some point in the coming months, it sounds like it makes sense to switch over to Postgres sooner rather than later.

5

Do you have some numbers in mind that can be considered “a lot of users”? I’m using SQLite now and trying to see if I need to switch to MySQL.

6

I don’t have any numbers, highly depends on setup and activity. But if you experience database locked errors in log it’s definitely time to migrate. If your serious about your Grafana setup you should use MySQL.

7

@svetb - I’d definitely recommend going from the bundled SQLite to a “proper” database. We’ve been running Grafana talking to a MySQL backend in production for about three years.

Initially we went for for NDB cluster (one for each of dev, test and production), but we recently migrated to MySQL Group Replication and collapsed the dev and test MySQL clusters into one “preproduction” one. Grafana’s use of the database is a pretty comfortable fit for doing that.

Abstracting the database has allowed us some major advantages, like being able to seamlessly setup multiple Grafana instances running from the same database and we load balance between them. We can also easily trial Grafana upgrades by duplicating the “production” database - actually use routing rules to run “production” and “beta” in parallel.

1 Like