bookmarks
bookmarks copied to clipboard
nextcloud
MySQL Backend Overhead / Extensive CPU Usage
Is your feature request related to a problem? Please describe.
When I have the Bookmarks app enabled, 90% of my CPU usage for a 7 day span is related to this doing the following query:
`SELECT `b`.`id`, `b`.`url`, `b`.`title`, `b`.`description`, `b`.`lastmodified`, `b`.`added`, `b`.`cl`
Describe the solution you'd like
I am not using any client to do a sync so this appears to be a regular maintenance CRON job? Is there a way to change the frequency of how often this query happens to once every 24 hours instead of hourly (maybe it's twice hourly)?
Describe alternatives you've considered
I disable the app until I add bookmarks via Floccus and then logon to the UI and enable the app.
Additional context
Not that I can think of
Hi @Xtropy74
Which version are you using? Is this a new problem since v11?
I am on 11.0.1.
I am not 100% sure if this is something new which is why I didn't report it as a bug. I just regularly see my server CPU at 90%+ on my server and it's related to MySQL when I check what SQL is doing, it's running this query. It stops and only runs a few minutes but seems to do it a LOT which is why I am trying to find a way to get to not process so frequently. When I disable Bookmarks in NextCloud, I don't see these spikes. I also checked/confirmed it's not related to a Floccus sync as I disabled them on my clients during testing. I have about 2K worth of bookmarks so probably related to how many I have?
I am not 100% sure if this is something new
There is definitely potential for improving efficiency in this app. Could you try finding out the complete query? You only posted the first half, I think.
Just ran and saw this one for about 2-3 minutes:
| 214629 | nextclouduser | localhost | nextcloud | Query | 0 | executing | WITH RECURSIVE folder_tree(item_id, parent_folder, type, idx) AS ( SELECT cast(1 as UNSIGNED) AS `it |
2-3 minutes
woh. that's a lot. The original purpose of this query was to be fast :(
I have about 2K worth of bookmarks so probably related to how many I have?
2k is not that much that it would warrant that long an execution time imho...
Can you...?
- set up slow query logging or some other means to get to the complete query
- Run EXPLAIN on the query so we can ideally see what is going on
Not sure if it's the same query as OP, but I've been having very similar problems with CPU usage. Running EXPLAIN on one of the queries outputs the following:
EXPLAIN WITH RECURSIVE folder_tree(item_id, parent_folder, type, idx) AS ( SELECT cast(1 as UNSIGNED) AS `item_id`, cast(0 as UNSIGNED) AS `parent_folder`, cast('folder' as CHAR(20)) AS `type`, cast(0 as UNSIGNED) AS `idx` UNION ALL SELECT `tr`.`id` AS `item_id`, `tr`.`parent_folder` AS `parent_folder`, `tr`.`type` AS `type`, `tr`.`index` AS `idx` FROM `oc_bookmarks_tree` `tr` INNER JOIN `folder_tree` `e` ON e.item_id = tr.parent_folder AND e.type = 'folder' UNION ALL SELECT `s`.`folder_id` AS `item_id`, `e`.`parent_folder`, 'folder' AS `type`, `e`.`idx` AS `idx` FROM `folder_tree` `e` INNER JOIN `oc_bookmarks_shared_folders` `s` ON s.id = e.item_id AND e.type = 'share') 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(`tr2`.`parent_folder`) AS `folders`, GROUP_CONCAT(`t`.`tag`) AS `tags` FROM `oc_bookmarks` `b` LEFT JOIN `oc_bookmarks_tree` `tr2` ON b.id = tr2.id AND tr2.type = 'bookmark' LEFT JOIN `oc_bookmarks_tags` `t` ON `t`.`bookmark_id` = `b`.`id` INNER JOIN `folder_tree` `tree` ON tree.item_id = b.id AND tree.type = 'bookmark' 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 300 OFFSET 3300;
+------+-----------------+--------------+--------+-----------------------------------------------+-------------------------+---------+----------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-----------------+--------------+--------+-----------------------------------------------+-------------------------+---------+----------------------+------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 8 | tree.item_id | 1 | Using where |
| 1 | PRIMARY | tr2 | ref | PRIMARY | PRIMARY | 90 | nextcloud.b.id,const | 1 | Using where; Using index |
| 1 | PRIMARY | t | ref | PRIMARY,bookmark_tag | PRIMARY | 8 | nextcloud.b.id | 1 | Using index |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 3 | RECURSIVE UNION | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 3 | RECURSIVE UNION | tr | ref | bookmarks_tree_parent,bookmarks_tree_parent_i | bookmarks_tree_parent_i | 8 | e.item_id | 85 | Using where; Using index |
| 4 | RECURSIVE UNION | s | index | PRIMARY,bookmarks_shared | bookmarks_shared_folder | 8 | NULL | 1 | Using index |
| 4 | RECURSIVE UNION | <derived2> | ref | key0 | key0 | 5 | nextcloud.s.id | 2 | Using where |
| NULL | UNION RESULT | <union2,3,4> | ALL | NULL | NULL | NULL | NULL | NULL | |
+------+-----------------+--------------+--------+-----------------------------------------------+-------------------------+---------+----------------------+------+----------------------------------------------+
10 rows in set (0.013 sec)
Looks like a particularly heavy query with lots of joins, but it makes sense because it needs to fetch data from several keys.
A similar query, this time while performing an operation on a bookmark entry:
EXPLAIN WITH RECURSIVE folder_tree(item_id, parent_folder, type, idx) AS ( SELECT cast(1 as UNSIGNED) AS `item_id`, cast(0 as UNSIGNED) AS `parent_folder`, cast('folder' as CHAR(20)) AS `type`, cast(0 as UNSIGNED) AS `idx` UNION ALL SELECT `tr`.`id` AS `item_id`, `tr`.`parent_folder` AS `parent_folder`, `tr`.`type` AS `type`, `tr`.`index` AS `idx` FROM `oc_bookmarks_tree` `tr` INNER JOIN `folder_tree` `e` ON e.item_id = tr.parent_folder AND e.type = 'folder' UNION ALL SELECT `s`.`folder_id` AS `item_id`, `e`.`parent_folder`, 'folder' AS `type`, `e`.`idx` AS `idx` FROM `folder_tree` `e` INNER JOIN `oc_bookmarks_shared_folders` `s` ON s.id = e.item_id AND e.type = 'share') 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(`tr2`.`parent_folder`) AS `folders`, GROUP_CONCAT(`t`.`tag`) AS `tags` FROM `oc_bookmarks` `b` LEFT JOIN `oc_bookmarks_tree` `tr2` ON b.id = tr2.id AND tr2.type = 'bookmark' LEFT JOIN `oc_bookmarks_tags` `t` ON `t`.`bookmark_id` = `b`.`id` INNER JOIN `folder_tree` `tree` ON tree.item_id = b.id AND tree.type = 'bookmark' WHERE `b`.`url` = 'https://www.librarything.com/topic/63238#1372429' 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;
+------+-----------------+--------------+--------+-----------------------------------------------+-------------------------+---------+----------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-----------------+--------------+--------+-----------------------------------------------+-------------------------+---------+----------------------+------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 8 | tree.item_id | 1 | Using where |
| 1 | PRIMARY | tr2 | ref | PRIMARY | PRIMARY | 90 | nextcloud.b.id,const | 1 | Using where; Using index |
| 1 | PRIMARY | t | ref | PRIMARY,bookmark_tag | PRIMARY | 8 | nextcloud.b.id | 1 | Using index |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 3 | RECURSIVE UNION | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 3 | RECURSIVE UNION | tr | ref | bookmarks_tree_parent,bookmarks_tree_parent_i | bookmarks_tree_parent_i | 8 | e.item_id | 85 | Using where; Using index |
| 4 | RECURSIVE UNION | s | index | PRIMARY,bookmarks_shared | bookmarks_shared_folder | 8 | NULL | 1 | Using index |
| 4 | RECURSIVE UNION | <derived2> | ref | key0 | key0 | 5 | nextcloud.s.id | 2 | Using where |
| NULL | UNION RESULT | <union2,3,4> | ALL | NULL | NULL | NULL | NULL | NULL | |
+------+-----------------+--------------+--------+-----------------------------------------------+-------------------------+---------+----------------------+------+----------------------------------------------+
10 rows in set (0.104 sec)
Checking via mytop, the vast majority of queries performed are of the following form:
Sending data WITH RECURSIVE folder_tree(item_id, parent_folder, type, idx) AS ( SELECT cast(1 as UNSIGNED) AS `item_id`,
cast(0 as UNSIGNED) AS `parent_folder`, cast('folder' as CHAR(20)) AS `type`, cast(0 as UNSIGNED) AS `idx` UNION ALL SELECT
`tr`.`id` AS `item_id`, `tr`.`parent_folder` AS `parent_folder`, `tr`.`type` AS `type`, `tr`.`index` AS `idx` FROM `oc_bookmarks_tree` `tr`
INNER JOIN `folder_tree` `e` ON e.item_id = tr.parent_folder AND e.type = 'folder' UNION ALL SELECT `s`.`folder_id` AS `item_id`,
`e`.`parent_folder`, 'folder' AS `type`, `e`.`idx` AS `idx` FROM `folder_tree` `e` INNER JOIN `oc_bookmarks_shared_folders` `s` ON
s.id = e.item_id AND e.type = 'share') 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(`tr2`.`parent_folder`) AS `folders`, GROUP_CONCAT(`t`.`tag`) AS `tags` FROM `oc_bookmarks` `b` LEFT JOIN
`oc_bookmarks_tree` `tr2` ON b.id = tr2.id AND tr2.type = 'bookmark' LEFT JOIN `oc_bookmarks_tags` `t` ON `t`.`bookmark_id`
= `b`.`id` INNER JOIN `folder_tree` `tree` ON tree.item_id = b.id AND tree.type = 'bookmark' WHERE `b`.`url` =
'https://www.example.com' 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
Is there any setting I can disable to reduce these queries? I have already disabled fetching data from bookmarks in the background.
MySQL 8.0.30
I renamed out /var/www/nextcloud/apps/bookmarks/lib/BackgroundJobs/ExtractFromNotesJob.php and the problem is gone. No idea what this does but doesn't seems to be required for for Floccus or Bookmarks to work.
@Xtropy74 Ah, I see. That's the background job that links bookmarks to notes that contain the same URL. I'll dial down the frequency of that job. Thanks for the feedback!
In the meanwhile, I've disabled the Notes app so that the background job doesn't trigger. Is there a way to hook to the Notes app in such a way that it runs only when notes have been modified, and only for notes modified? That might greatly reduce the amount of queries and therefore the CPU usage.
Is there a way to hook to the Notes app in such a way that it runs only when notes have been modified, and only for notes modified?
Yes, that would be the next step. For the next release I've simply reduced the frequency to once a day until I get to implementing a proper hook that listens to notes changes.
v11.0.3 now has the patch, let me know if that improves things :heart_decoration:
Looking good so far, thanks for the fix/tweak.
On Sat, Sep 24, 2022 at 10:06 AM Marcel Klehr @.***> wrote:
v11.0.3 now has the patch, let me know if that improves things 💟
— Reply to this email directly, view it on GitHub https://github.com/nextcloud/bookmarks/issues/1857#issuecomment-1256976856, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB4PGDJYPXD5TAXY3YFHVL3V74DEVANCNFSM55OUVB7Q . You are receiving this because you were mentioned.Message ID: @.***>