Compact Database Warning

Post Reply
rgifford
Posts: 9
Joined: 13 Jan 11 15:15

Compact Database Warning

Post by rgifford » 20 Jan 20 10:13

A recent change to Photo Supreme has introduced a warning that I haven't compacted my database for 15 days. The only way to get rid of it appears to be to click the Compact Now option. How do I stop this warning appearing?

I have 140000 files catalogued and my DB is 13Gb with my thumbs DB 361Gb. Compacting takes AGES!

I compact after I have done sugnificant imports or deletions. I see no need to compact regularly for minor changes in between.

Hert
Posts: 6142
Joined: 13 Sep 03 7:24

Re: Compact Database Warning

Post by Hert » 20 Jan 20 11:12

You can't prevent that check. In the single user edition you will be asked to compact the database if it wasn't compacted for more than 14 days.

You can try this;
1. Start PSU
2. Cancel the compact message
3. Select Catalog -> Compact Current Catalog from the hamburger menu
4. Only compact the Catalog database

Most of the time is needed to compact your thumbs database since it's 361GB. With these steps you'll only compact the catalog database. That will update the timestamp for another 14 days.
This is a User-to-User forum which means that users post questions here for other users.
Feature requests, change suggestions, or bugs can be logged in the ticketing system

rgifford
Posts: 9
Joined: 13 Jan 11 15:15

Re: Compact Database Warning

Post by rgifford » 20 Jan 20 11:32

Please either improve the trigger for this message, so that it is based on additions/deletions/need to compact rather than days, or provide the 'don't ask me again' type option that exists for many other warning type messages.

Compacting every 14 days increases the risk of database corruption so compacting where it isn't required is a bad thing rather than a good thing.

frank
Posts: 2
Joined: 25 Jan 20 19:27

Re: Compact Database Warning

Post by frank » 25 Jan 20 20:01

I agree with rgifford, it is really a pain to compact database every 15 days. Psu is already slow to start ( i have only 16 000 files) and i have to go throw this new message box to see my photos.

May be this is necessary for the server version but, IMO, it is not for the single user version.

The suggestions of rgifford are goods choices. Another option is to add a setting in the preferences indicating the number of days is suitable between compacting DB. This is more flexible and adapted to each other.

Please improve PSU. Thank you.

Frank

Hert
Posts: 6142
Joined: 13 Sep 03 7:24

Re: Compact Database Warning

Post by Hert » 26 Jan 20 13:21

frank wrote:
25 Jan 20 20:01
May be this is necessary for the server version but, IMO, it is not for the single user version.
The opposite is the case. In the Server edition compacting regularly isn’t that important.
In the Single User edition, the SQLite database can slow down significantly if not compacted regularly.

On startup psu reloads all open tabs which can impact startup time significantly. If psu starts slow then close all tabs before closing PSU. Starting PSU “clean” shouldn’t take more than a few seconds.
This is a User-to-User forum which means that users post questions here for other users.
Feature requests, change suggestions, or bugs can be logged in the ticketing system

rgifford
Posts: 9
Joined: 13 Jan 11 15:15

Re: Compact Database Warning

Post by rgifford » 26 Jan 20 20:50

An SQLite database only needs to be compacted regularly if it is updated regularly and those updates are significant.

Mine has major updates over a period of about a month a time 4 times a year. The rest of the time the database is mainly static apart from minor corrections and doesn't need to be compacted during those periods, i.e. most of the time. Most of the time I use Photo Supreme to find photos, not to add them to the database.

Every database compact runs the risk of data loss/damage and it make no sense to do them when they aren't needed.

If the only concern is speed, Photo Supreme could assess speed and recommend compacting based on that. There would still need to be a 'don't tell me about this' option where the speed isn't related to database issues but is something else.

User avatar
G8DHE
Posts: 201
Joined: 21 Aug 17 13:58

Re: Compact Database Warning

Post by G8DHE » 27 Jan 20 10:29

I've not studied it any depth but would the size of the .WAL file or the .SHM file not give a clue to the number of updates that have taken place and hence the need for the ,DB as a whole to be compacted ?
Geoff Mather (G8DHE)

rgifford
Posts: 9
Joined: 13 Jan 11 15:15

Re: Compact Database Warning

Post by rgifford » 03 Feb 20 23:16

Hert wrote:
20 Jan 20 11:12
You can't prevent that check. In the single user edition you will be asked to compact the database if it wasn't compacted for more than 14 days.

You can try this;
1. Start PSU
2. Cancel the compact message
3. Select Catalog -> Compact Current Catalog from the hamburger menu
4. Only compact the Catalog database
I tried that.

20 minutes for the compact plus the backup beforehand in case it goes wrong plus comparing key States numbers before and after as a quick check that it hasn't corrupted anything, for no benefit whatsoever apart from turning the nag off for another 2 weeks. Size went from 13185328 to 13171236.

Please allow this "feature" to be turned off.

Hert
Posts: 6142
Joined: 13 Sep 03 7:24

Re: Compact Database Warning

Post by Hert » 04 Feb 20 9:43

The chances that Compacting the catalog will corrupt your database are very slim. During a Vacuum, SQLite will first create a new file before replacing the old one. During compact PSU not only Vacuums, but also checks the database integrity state, rebuilds the indexes, and performs some internal cleaning.
This feature wasn't implemented without reason. There won't be an option to switch this off because people will switch it off and then forget about it. This is too important.

FWIW, the Server Edition allows you to skip compacting. That uses either PostgreSQL or MS-SQLServer where vacuuming is less important than in SQLite.
This is a User-to-User forum which means that users post questions here for other users.
Feature requests, change suggestions, or bugs can be logged in the ticketing system

rgifford
Posts: 9
Joined: 13 Jan 11 15:15

Re: Compact Database Warning

Post by rgifford » 04 Feb 20 9:58

What is the important reason for introducing this which is too important to ignore? Insisting that a database which is more or less static is compacted every two weeks is stupid.

There is a risk of corruption. That risk is not zero so backing up beforehand and doing some sort of quick check afterwards is essential. That is also too important to ignore.

Your suggested work-round is to not compact the thumbs database every two weeks. It is either essential to do this every two weeks or it isn't. I don't see why it is essential for one and not the other.

Hert
Posts: 6142
Joined: 13 Sep 03 7:24

Re: Compact Database Warning

Post by Hert » 04 Feb 20 10:15

rgifford wrote:
04 Feb 20 9:58
What is the important reason for introducing this which is too important to ignore
A 96% reduction of reported issues that are related to users not compacting.
This is a User-to-User forum which means that users post questions here for other users.
Feature requests, change suggestions, or bugs can be logged in the ticketing system

rgifford
Posts: 9
Joined: 13 Jan 11 15:15

Re: Compact Database Warning

Post by rgifford » 04 Feb 20 10:48

Hert wrote:
04 Feb 20 10:15
rgifford wrote:
04 Feb 20 9:58
What is the important reason for introducing this which is too important to ignore
A 96% reduction of reported issues that are related to users not compacting.
Yes, I can see that is important :D

It is mega irritating for me as I do know the importance of compacting and I do it when necessary. I do it more often than every two weeks when I'm making major changes and not at all when I'm not.

If it was changed to every three months rather than every two weeks I wouldn't have noticed that it was there.

gcorbin
Posts: 49
Joined: 21 Aug 06 12:31
Location: Brisbane

Re: Compact Database Warning

Post by gcorbin » 07 Feb 20 3:15

I think we all agree that compacting is important but it is very inconvenient to compact when you start PS to do something with your photos.

Is there an easy way to schedule the compact unattended at a convenient time, for example overnight? This would probably solve the issue for the majority of users if it was configurable.

rgifford
Posts: 9
Joined: 13 Jan 11 15:15

Re: Compact Database Warning

Post by rgifford » 07 Feb 20 9:55

I tried to compact overnight last night and get an error that the database is locked. This morning the same again.

This isn't a compact prompted by the 14 day message, it is a compact that I need to do because I have finished cataloguing my last big batch.

frank
Posts: 2
Joined: 25 Jan 20 19:27

Re: Compact Database Warning

Post by frank » 09 Feb 20 15:19

I change my database 5 or 6 time a year. However, I view my photos very often, 80% of the I open PSU is to view my photos. It is ridiculous to compact my databases each time I want to view my photos. I am an adult, major and vaccinated, so I can decide by myself when I should or should not compact my databases. I don't need anybody to tell me what I should do with my life.

So, please Hert, add an option to stop this warning, for those people like me, who can assume their own decisions, even when they made the wrong choice.

Thank you

Post Reply