Database is Locked pmm 2.38.1

for What it is worth… I did a search for grafana.db inside the docker and found two of them… Not quite sure why or if somehow I did it…

But Once I did pragma journal_mode=wal on both files I have not seen it (yet) I did NOT think to look at the other potential settings prior to me setting this

Additionally I did find that when I work with the Alerting aspect quickly I tend to see it happen more often (modifying alerts after using a Template base that comes with pmm)

@meyerder

Can you please share the locations?
or
How can I search, Can you please guide me?

Inside the docker

find / -name grafana.db
cd to the directory. also research a few other tweaks you can do on the file I am running 80 nodes now with no issues

@meyerder

I found the below files.

bash-5.1# find / -name grafana.db
/etc/grafana/grafana.db
/opt/grafana.db
/srv/grafana/grafana.db
/usr/share/grafana/grafana.db
bash-5.1#

@Naresh9999 @Lauri

While I do NOT know if it is suggested I have not seen the issue since I did the following as well as the above (Again on each grafana.db file as I had two but was not sure WHY I had two)
Do this at your own risk…**** I only did the first three .

When you issue the commands you MUST be in the directory that the grafana.db file is in… Else it does nothing

sqlite> pragma journal_mode=wal;
sqlite> PRAGMA cache_size = 10000; -- Set cache size to 10000 pages
sqlite> PRAGMA synchronous = OFF; -- Disable synchronous write (less durable, more performance)
sqlite> PRAGMA locking_mode = EXCLUSIVE; -- Use exclusive locking mode


sqlite> PRAGMA busy_timeout = 1000; -- Set a timeout of 1 second for waiting on locks
sqlite> PRAGMA temp_store = MEMORY; -- Use memory for temporary storage (if feasible)



Also if you are like me and new to Docker Make sure you commit the changes as well (and then I normally restart the docker)

@meyerder

Many thanks for your assistance. I’ll test out the aforementioned suggestions and keep you informed of my results.

@Naresh9999 and the result was?

@meyerder New servers are being added to PMM, and so far, so good. I will update you complete status by tomorrow or day after tomorrow.

Only the parameters below have been altered by me:

sqlite> pragma journal_mode=wal;

@meyerder @steve.hoffman @nurlan @Roma_Novikov

The issue was fixed by switching the journal_mode from del to wal. As of now, PMM is functioning well.
Thanks for all your support.

SQLlite DB path: /srv/grafana/grafana.db

1 Like

Thanks for the update, @Naresh9999!
Everyone suffering from the issue - can use this workaround.
We are in the final stages of testing PG inside PMM, so this problem should disappear.

1 Like

@Roma_Novikov

It seems that two SQLite databases are running inside a container, so we are migrating both SQLite databases to PostgreSQL, right, Roma?

we’ll migrate SQLite used by Grafana. not sure is this one or several Db :thinking:

@Roma_Novikov Can you please check whether the below path or not?

SQLlite DB path: /srv/grafana/grafana.db

@Roma_Novikov Is it the same DB that Grafana uses?

@Naresh9999 yes, that’s a correct path used by grafana.

Thanks @nurlan for the confirmation.

Therefore, none of the services will use the other SQLite databases mentioned below. In that case, do we have to move the items below, or is it not necessary?

  1. /etc/grafana/grafana.db
  2. /opt/grafana.db
  3. /usr/share/grafana/grafana.db

Yeah, as far as I know we don’t use them in PMM.

So in this case, we can permanently remove or uninstall SQLite DB from the PMM container or image, so that we can save some resource utilization. Unnecessarily, we do need to run SQLite DB inside the container.

I encounter the same issues after upgrading to 2.39.0, e.g. database locked and high cpu and load, you can refer to my post at After upgrade to 2.39.0, the grafana takes too much CPU

Update: We are working on RC testing for pmm 2.40 with a fix for this problem.