Bookmarks App problem

My NC install has been showing excessive CPU utilization and mysql utilization. Cron jobs run for hours at a time. I just received the following from my hosting provider: XYZ is my sanitized db name. For now I have disabled the bookmarks app to see if that heps. The broswer bookmarks extension Floccus seemed to have problems with lots of bookmarks with lots of subfolders and kept creating duplicates and generally not working so may be a combination of these things? I’m not very technical so apologize in advance for things that don’t make sense to you all who know what you’re talking about…

Our server monitoring systems indicate excessive MySQL server load persistently maintained by your hosting account for XYZ.

Most of the MySQL load is caused by the following query:

# Exec time 44 14789s 202ms 221s 65s 159s 52s 57s

# Lock time 5 12s 179us 2s 53ms 356ms 227ms 657us

# Rows sent 0 226 1 1 1 1 0 1

# Rows examine 37 70.93M 11.82k 645.05k 321.37k 509.78k 175.00k 399.43k

# Rows affected 0 0 0 0 0 0 0 0

# Bytes sent 0 457.92k 1.95k 2.38k 2.03k 2.06k 59.30 1.96k

# Query size 0 293.67k 1.25k 1.67k 1.30k 1.33k 47.02 1.26k

# String:

# Databases XYZ

# Hosts localhost

# Last errno 1260

# Users XYZ

# Query_time distribution

# 1us

# 10us

# 100us

# 1ms

# 10ms

# 100ms

# 1s

# 10s+

# Tables

# SHOW TABLE STATUS FROM `XYZ` LIKE ‘oc_bookmarks’\G

# SHOW CREATE TABLE `XYZ`.`oc_bookmarks`\G

# SHOW TABLE STATUS FROM `XYZ` LIKE ‘oc_bookmarks_tree’\G

# SHOW CREATE TABLE ‘XYZ’ `.`oc_bookmarks_tree`\G

# SHOW TABLE STATUS FROM `XYZ` LIKE ‘oc_bookmarks_tags’\G

# SHOW CREATE TABLE `XYZ`.`oc_bookmarks_tags`\G

# SHOW TABLE STATUS FROM `XYZ` LIKE ‘oc_bookmarks_shared_folders’\G

# SHOW CREATE TABLE `XYZ`.`oc_bookmarks_shared_folders`\G

# EXPLAIN /*!50100 PARTITIONS*/

SELECT `b`.`id`, `b`.`url`, `b`.`title`, `b`.`description`, `b`.`lastmodified`, `b`.`added`, `b`.`clickcount`, `b`.`last_preview`, `b`.`available`, `b`.`archived_file`, `b`.`user_id`, `b`.`text_content`, `b`.`html_content`, GROUP_CONCAT(`tree`.`parent_folder`) AS `folders`, GROUP_CONCAT(`t`.`tag`) AS `tags` FROM `oc_bookmarks` `b` LEFT JOIN `oc_bookmarks_tree` `tree` ON b.id =tree.id AND tree.type = ‘bookmark’ LEFT JOIN `oc_bookmarks_tags` `t` ON `t`.`bookmark_id` = `b`.`id` LEFT JOIN `oc_bookmarks_tree` `tr` ON tr.id = b.id AND tr.type = ‘bookmark’ LEFT JOIN `oc_bookmarks_shared_folders` `sf` ON `tr`.`parent_folder` = `sf`.`folder_id` LEFT JOIN `oc_bookmarks_tree` `tr2` ON tr2.id = tr.parent_folder AND tr2.type = ‘folder’ LEFT JOIN `oc_bookmarks_shared_folders` `sf2` ON `tr2`.`parent_folder` = `sf`.`folder_id` WHERE (((`b`.`user_id` = ‘me’) OR (`sf`.`user_id` = ‘me’) OR (`sf2`.`user_id` = ‘me’)) AND (`b`.`user_id` IN (‘me’))) AND (`b`.`url` = ‘https:deleted url’) GROUP BY `b`.`id`, `b`.`url`, `b`.`title`, `b`.`description`, `b`.`lastmodified`, `b`.`added`, `b`.`clickcount`, `b`.`last_preview`, `b`.`available`, `b`.`archived_file`, `b`.`user_id`, `b`.`text_content`, `b`.`html_content`, `b`.`id` ORDER BY `b`.`lastmodified` DESC, `b`.`id` ASC LIMIT 10\G

It appears that the Bookmarks App is making these queries. A possible solution would be to delete all bookmarks, as advised here:

If this action doesn’t reduce the MySQL usage, you can try completely disabling this app and also optimize the authentication procedure, as advised here:

It could be that the database contains corrupt bookmarks folder remnants. Can you try:

  1. Making db backup and bookmarks backup for good measure
  2. Running occ maintenance:repair

I followed these instructions, occ maintenance:repair didn’t seem to do anything to bookmarks. With all of my bookmarks deleted via the Bookmarks app and Bookmarks app disables Nextcloud is running fine with 100th of the CPU/db utilization. I’m frightened to reenable Bookmarks until somethjing has been done to solve (or at least explain) the problem

I’m frightened to reenable Bookmarks until somethjing has been done to solve (or at least explain) the problem

If you want a solution or an explanation, I will need your cooperation to investigate the issue, as I am unable to replicate it on my own installation. This would definitely involve reenabling the app.

Specifically, as suggested in the github issue that you gracefully also copy-pasted the above post into,

  1. Export your bookmarks
  2. Make a db dump
  3. Uninstall the app
  4. Remove all bookmark traces from the db
  5. Reinstall the app
  6. Import your bookmarks
  7. Check whether the spike comes back
  8. If it does not come back, I’d love to see that db dump so I can write an appropriate repair step

I’d be happy to try and help diagnose. However, I can’t re-enable because Nextcloud (and the shared server it’s running on) becomes completely unusable and my hosting provider will kick me off their service and/or demand I upgrade to a dedicated server so this problem doesn’t effect others.

Anything I can look for (e.g. corrupt entries) in my saved bookmarks file?

Anything I can look for (e.g. corrupt entries) in my saved bookmarks file?

The answer more likely lurks in a db dump.

It could be that your troubles were started by changes due to reactivated Firefox Sync / built-in Bookmark sync, I’ve had one user report this recently. if built-in browser sync is enabled things are likely to go haywire.

I am seeing this problem on 27 branch and would be able to assist in debugging. Firefox ESR 115.8, latest Floccus plugin, intermediate heavy system load on php-fpm pool www.

@inetuka Do you have APCU and or Redis caching enabled? I really recommend it

Sure! I have redis enabled for memcache.local and memcache.locking and can see it working with redis-cli monitor.