Catalog unresponsive, CPU 100%

[Ethan Welty]

Hi Kirk, It didn't feel very different, unfortunately. Maybe a bit faster? Logs attached.

[Bill Kelly]

Hi Ethan,

It didn't feel very different, unfortunately. Maybe a bit faster?

On what type of drive does your Catalog reside? E.g. SSD or spinning HDD?

While deleting items is taking longer than expected on your system, the more logging we've added, the more we find time being spent in anticipated locations.

That is: the phases of a delete operation that do the most work, are taking the most time, as one would expect. However, your system is only managing to delete about 3 items per second from the catalog. Whereas we're seeing a rate closer to 30 items per second here on our development systems.

Regards,

Bill

[Ethan Welty]

Catalog is on a SSD. Details below:

File System:   APFS
Device Name:   SanDisk SSD PLUS 1000GB
Medium Type:   SSD
Protocol:   SATA
Internal:   Yes
Partition Map Type:   Unknown
S.M.A.R.T. Status:   Verified

Honestly, even 30 items per second seems very slow to me for dropping rows from a SQL database by item index. Are all the foreign key references indexed? At least for PostgreSQL, that can make a huge difference. Good explanation here: https://conorliv.com/postgresql-optimize-delete.html).

[Bill Kelly]

Catalog is on a SSD. Details below:

File System:   APFS
Device Name:   SanDisk SSD PLUS 1000GB
Medium Type:   SSD

Most intriguing. Will be interested to learn what could account for that order-of-magnitude performance difference.

Honestly, even 30 items per second seems very slow to me for dropping rows from a SQL database by item index.

Much of the work during a delete involves adjusting stats that are being maintained for the Browse and Filter interfaces. (E.g. every keyword, every path component – every field that is tracked in Browse and in the root-level Filter needs its count adjusted. Even if there's no metadata for a given field, that's still tracked as <none> for that document and so must also be adjusted on delete. So there's a fair amount of work occurring.)

Are all the foreign key references indexed? At least for PostgreSQL, that can make a huge difference. Good explanation here: https://conorliv.com/postgresql-optimize-delete.html).

Thanks. Certainly something to keep in mind - though it does not immediately appear to affect tables from which rows are being dropped during a Catalog delete.

We may need to prepare a custom build for you that enables full SQL logging during delete. It'd be wonderful if it were something as simple as a missing index.


Regards,

Bill

[Ethan Welty]

Hi Bill,

I see, there's a cascade of cache updates triggered on delete. Hmm, is this update performed once for each image? If so, it might be faster to compute counts from the images being deleted, then subtracting those counts from the database counts in a single pass. Anyway, happy to try out any special builds you send my way.

thanks,
ethan

[Bill Kelly]

I see, there's a cascade of cache updates triggered on delete. Hmm, is this update performed once for each image? If so, it might be faster to compute counts from the images being deleted, then subtracting those counts from the database counts in a single pass.

Indeed, once per image.

Agreed – the proposed optimization should be significant when adding/removing batches of images.

Means the transaction granularity would need to be per-batch rather than per-image, but that doesn't seem necessarily a problem.

(Will need to contemplate whether there's a way to finesse that sort of buffering into the existing code without a significant rewrite.)

-Bill

[Kirk Baker]

Ethan,

Please try this updated build, we expect that it should improve removing batches of images from a catalog: http://forums.camerabits.com/index.php?topic=13808.0

Thanks,

-Kirk