[GH-ISSUE #792] MySQL troubles

kerem commented

2026-02-25 22:37:20 +03:00

Owner

Originally created by @faaaaabi on GitHub (Jan 16, 2021).
Original GitHub issue: https://github.com/floccusaddon/floccus/issues/792

As mentioned in #476 I still have massive CPU load when using Bookmarks v4.0.5, Floccus v4.4.6, Nextcloud v20.0.1 in combination with syncing floccus

Here are some database insights:

MariaDB [nextcloud]> EXPLAIN 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`, 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` = 'fabi') OR (`sf`.`user_id` = 'fabi') OR (`sf2`.`user_id` = 'fabi') 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`.`id` ORDER BY `b`.`lastmodified` DESC, `b`.`id` ASC LIMIT 300
    -> ;
+------+-------------+-------+-------+-------------------------------------------+-----------------------+---------+----------------------------------+------+--------------------------------------------------------------+
| id   | select_type | table | type  | possible_keys                             | key                   | key_len | ref                              | rows | Extra                                                        |
+------+-------------+-------+-------+-------------------------------------------+-----------------------+---------+----------------------------------+------+--------------------------------------------------------------+
|    1 | SIMPLE      | b     | ALL   | IDX_3EE1CD04A76ED395,bookmarks_deadbyuser | NULL                  | NULL    | NULL                             | 796  | Using temporary; Using filesort                              |
|    1 | SIMPLE      | tree  | ref   | PRIMARY                                   | PRIMARY               | 90      | nextcloud.b.id,const             | 1    | Using where; Using index                                     |
|    1 | SIMPLE      | t     | ref   | bookmark_tag                              | bookmark_tag          | 9       | nextcloud.b.id                   | 1    | Using index                                                  |
|    1 | SIMPLE      | tr    | ref   | PRIMARY                                   | PRIMARY               | 90      | nextcloud.b.id,const             | 1    | Using where; Using index                                     |
|    1 | SIMPLE      | sf    | ALL   | bookmarks_shared_folder                   | NULL                  | NULL    | NULL                             | 1    | Using where; Using join buffer (flat, BNL join)              |
|    1 | SIMPLE      | tr2   | ref   | PRIMARY                                   | PRIMARY               | 90      | nextcloud.tr.parent_folder,const | 1    | Using where; Using index                                     |
|    1 | SIMPLE      | sf2   | index | NULL                                      | bookmarks_shared_user | 1022    | NULL                             | 1    | Using where; Using index; Using join buffer (flat, BNL join) |
+------+-------------+-------+-------+-------------------------------------------+-----------------------+---------+----------------------------------+------+--------------------------------------------------------------+

MariaDB [nextcloud]> SHOW INDEX FROM oc_bookmarks;
+--------------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name             | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_bookmarks |          0 | PRIMARY              |            1 | id           | A         |         796 |     NULL | NULL   |      | BTREE      |         |               |
| oc_bookmarks |          1 | IDX_3EE1CD04A76ED395 |            1 | user_id      | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| oc_bookmarks |          1 | IDX_3EE1CD04DF091378 |            1 | last_preview | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| oc_bookmarks |          1 | bookmarks_modified   |            1 | lastmodified | A         |         796 |     NULL | NULL   | YES  | BTREE      |         |               |
| oc_bookmarks |          1 | bookmarks_deadbyuser |            1 | user_id      | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| oc_bookmarks |          1 | bookmarks_deadbyuser |            2 | available    | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| oc_bookmarks |          1 | bookmarks_dead       |            1 | available    | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

MariaDB [nextcloud]> SHOW INDEX FROM oc_bookmarks_tree;
+-------------------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name                | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_bookmarks_tree |          0 | PRIMARY                 |            1 | id            | A         |       55116 |     NULL | NULL   |      | BTREE      |         |               |
| oc_bookmarks_tree |          0 | PRIMARY                 |            2 | type          | A         |       55116 |     NULL | NULL   |      | BTREE      |         |               |
| oc_bookmarks_tree |          0 | PRIMARY                 |            3 | parent_folder | A         |       55116 |     NULL | NULL   |      | BTREE      |         |               |
| oc_bookmarks_tree |          1 | bookmarks_tree_parent   |            1 | parent_folder | A         |       27558 |     NULL | NULL   |      | BTREE      |         |               |
| oc_bookmarks_tree |          1 | bookmarks_tree_parent_i |            1 | parent_folder | A         |       18372 |     NULL | NULL   |      | BTREE      |         |               |
| oc_bookmarks_tree |          1 | bookmarks_tree_parent_i |            2 | index         | A         |       55116 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.001 sec)

MariaDB [nextcloud]> SHOW INDEX FROM oc_bookmarks_tags;
+-------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_bookmarks_tags |          0 | bookmark_tag |            1 | bookmark_id | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| oc_bookmarks_tags |          0 | bookmark_tag |            2 | tag         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

MariaDB [nextcloud]> SHOW INDEX FROM oc_bookmarks_shared_folders;
+-----------------------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                       | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_bookmarks_shared_folders |          0 | PRIMARY                 |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_bookmarks_shared_folders |          1 | bookmarks_shared_user   |            1 | user_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| oc_bookmarks_shared_folders |          1 | bookmarks_shared_folder |            1 | folder_id   | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

MariaDB [nextcloud]> SHOW variables like "%buffer%";
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| aria_pagecache_buffer_size          | 134217728      |
| aria_sort_buffer_size               | 268434432      |
| bulk_insert_buffer_size             | 16777216       |
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 1073741824     |
| innodb_change_buffer_max_size       | 25             |
| innodb_change_buffering             | all            |
| innodb_log_buffer_size              | 8388608        |
| innodb_sort_buffer_size             | 1048576        |
| join_buffer_size                    | 262144         |
| join_buffer_space_limit             | 2097152        |
| key_buffer_size                     | 134217728      |
| mrr_buffer_size                     | 262144         |
| myisam_sort_buffer_size             | 536870912      |
| net_buffer_length                   | 16384          |
| preload_buffer_size                 | 32768          |
| read_buffer_size                    | 2097152        |
| read_rnd_buffer_size                | 1048576        |
| sort_buffer_size                    | 4194304        |
| sql_buffer_result                   | OFF            |
+-------------------------------------+----------------+
28 rows in set (0.003 sec)

MariaDB [nextcloud]> SHOW variables like "%table_size%";
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| max_heap_table_size   | 33554432             |
| tmp_disk_table_size   | 18446744073709551615 |
| tmp_memory_table_size | 33554432             |
| tmp_table_size        | 33554432             |
+-----------------------+----------------------+
4 rows in set (0.001 sec)

Originally posted by @faaaaabi in https://github.com/marcelklehr/floccus/issues/476#issuecomment-726923253

kerem closed this issue

2026-02-25 22:37:20 +03:00

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@marcelklehr commented on GitHub (Jan 16, 2021):

You could try having mysql/mariadb rebuild the table by doing

To rebuild a table by dumping and reloading it, use mysqldump to create a dump file and mysql to reload the file:
mysqldump db_name t1 > dump.sql
mysql db_name < dump.sql

-- https://dev.mysql.com/doc/refman/8.0/en/rebuilding-tables.html

i.e.

mysqldump nextcloud oc_bookmarks > dump.sql
mysql nextcloud < dump.sql

or try one of https://stackoverflow.com/questions/30051510/how-can-i-rebuild-indexes-and-update-stats-in-mysql-innodb

@marcelklehr commented on GitHub (Jan 16, 2021): You could try having mysql/mariadb rebuild the table by doing > To rebuild a table by dumping and reloading it, use mysqldump to create a dump file and mysql to reload the file: > > ``` > mysqldump db_name t1 > dump.sql > mysql db_name < dump.sql > ``` -- https://dev.mysql.com/doc/refman/8.0/en/rebuilding-tables.html i.e. ``` mysqldump nextcloud oc_bookmarks > dump.sql mysql nextcloud < dump.sql ``` or try one of https://stackoverflow.com/questions/30051510/how-can-i-rebuild-indexes-and-update-stats-in-mysql-innodb

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@marcelklehr commented on GitHub (Jan 20, 2021):

A different option as the one above may not work is to drop the index in question and recreate it manually.

ALTER TABLE DROP KEY -> ALTER TABLE ADD KEY
You manually drop the key by name, you manually create it again. In a production environment you'll want to create it first, then drop the old version.
The upside: this can be a lot faster than optimize. The downside: you need to manually create the syntax.
"SHOW CREATE TABLE" can be used to quickly see which indexes are available and how they are called.

That would mean creating a new index first:

ALTER TABLE oc_bookmarks ADD INDEX `bookmarks_userid` (`user_id`);

@marcelklehr commented on GitHub (Jan 20, 2021): A different option as the one above may not work is to drop the index in question and recreate it manually. > ALTER TABLE DROP KEY -> ALTER TABLE ADD KEY > You manually drop the key by name, you manually create it again. In a production environment you'll want to create it first, then drop the old version. > The upside: this can be a lot faster than optimize. The downside: you need to manually create the syntax. > "SHOW CREATE TABLE" can be used to quickly see which indexes are available and how they are called. That would mean creating a new index first: ``` ALTER TABLE oc_bookmarks ADD INDEX `bookmarks_userid` (`user_id`); ```

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@panachoi commented on GitHub (Jan 21, 2021):

So, is the "proper" way to do this:

see the output of:

mysql> show index from oc_bookmarks;

oc_bookmarks | 1 | IDX_3EE1CD04A76ED395 | 1 | user_id
KEY IDX_3EE1CD04A76ED395 (user_id),

Then:

ALTER TABLE oc_bookmarks ADD INDEX bookmarks_userid (user_id);

Then:

ALTER TABLE oc_bookmarks DROP INDEX IDX_3EE1CD04A76ED395;

And thats it ?

@panachoi commented on GitHub (Jan 21, 2021): So, is the "proper" way to do this: see the output of: mysql> show index from oc_bookmarks; oc_bookmarks | 1 | IDX_3EE1CD04A76ED395 | 1 | user_id KEY `IDX_3EE1CD04A76ED395` (`user_id`), Then: ALTER TABLE oc_bookmarks ADD INDEX `bookmarks_userid` (`user_id`); Then: ALTER TABLE oc_bookmarks DROP INDEX IDX_3EE1CD04A76ED395; And thats it ?

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@marcelklehr commented on GitHub (Jan 21, 2021):

That's the idea. Before you do that, try @faaaaabi's analytical queries above to see if your indexes are actually the problem.

@marcelklehr commented on GitHub (Jan 21, 2021): That's the idea. Before you do that, try @faaaaabi's analytical queries above to see if your indexes are actually the problem.

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@panachoi commented on GitHub (Jan 21, 2021):

This gets more "interesting" for comparison:

mysql> SHOW variables like "%table_size%";
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
| tmp_table_size      | 16777216 |
+---------------------+----------+

I certainly didn't set this, buit this looks suspiciously like the issue here.
Just checked a "newer" (different) mysql server

MariaDB [nextcloud]> SHOW variables like "%table_size%";
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| max_heap_table_size   | 16777216             |
| tmp_disk_table_size   | 18446744073709551615 |
| tmp_memory_table_size | 16777216             |
| tmp_table_size        | 16777216             |
+-----------------------+----------------------+

I'm now wondering where/why @faaaaabi variables have been hand-modified (essentially doubled)

@panachoi commented on GitHub (Jan 21, 2021): This gets more "interesting" for comparison: ``` mysql> SHOW variables like "%table_size%"; +---------------------+----------+ | Variable_name | Value | +---------------------+----------+ | max_heap_table_size | 16777216 | | tmp_table_size | 16777216 | +---------------------+----------+ ``` I certainly didn't set this, buit this looks suspiciously like the issue here. Just checked a "newer" (different) mysql server ``` MariaDB [nextcloud]> SHOW variables like "%table_size%"; +-----------------------+----------------------+ | Variable_name | Value | +-----------------------+----------------------+ | max_heap_table_size | 16777216 | | tmp_disk_table_size | 18446744073709551615 | | tmp_memory_table_size | 16777216 | | tmp_table_size | 16777216 | +-----------------------+----------------------+ ``` I'm now wondering where/why @faaaaabi variables have been hand-modified (essentially doubled)

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@mnalis commented on GitHub (Jan 21, 2021):

@panachoi defaults may differ depending on GNU/Linux distribution used (or even other OS), or specific defaults for that Mysql / MariaDB version, etc. so that difference between different systems is not that important.

What is important is that:

buffers are big enough for the dataset being used
total memory usage by mysqld (determined largely by the buffer sizes) is never bigger than RAM available to system when all other services (apache, nextcloud etc) are substracted, i.e. system never should resort to using SWAP. If it does, things can easily get slowed down 100s or 1000s times.

to check this, also would be interesting:

output of free -h (memory use)
output of ps auxfwww | grep mysql (memory used by mysqld)
output of uptime or cat /proc/loadavg (system load)
output of cat /proc/cpuinfo (number of processors)
how many bookmarks do you have?

Output of those should be obtained while the slow operation is in progress.

Also, how exactly is massive CPU load being determined? And is it really only CPU load (processes in R state) or system loadavg (as returned by uptime command, which counts both Running processes, and processes in D state - often due to being swapped out due to insufficient memory or blocked by slow disk I/O)

But while I'm not using oc_bookmarks, the EXPLAIN SELECT does look suspicious - the key column should not be NULL if table contains any data and possible indexes. ANALYZE TABLE tablename (or slower OPTIMIZE table tablename) might sometimes help.

@faaaaabi if you run that "SELECT" command by hand, how long does it take to execute?

@mnalis commented on GitHub (Jan 21, 2021): @panachoi defaults may differ depending on GNU/Linux distribution used (or even other OS), or specific defaults for that Mysql / MariaDB version, etc. so that difference between different systems is not that important. What is important is that: - buffers are big enough for the dataset being used - total memory usage by mysqld (determined largely by the buffer sizes) is **never** bigger than RAM available to system when all other services (apache, nextcloud etc) are substracted, i.e. system never should resort to using SWAP. If it does, things can easily get slowed down 100s or 1000s times. to check this, also would be interesting: - output of `free -h` (memory use) - output of `ps auxfwww | grep mysql` (memory used by mysqld) - output of `uptime` or `cat /proc/loadavg` (system load) - output of `cat /proc/cpuinfo` (number of processors) - how many bookmarks do you have? Output of those should be obtained **while the slow operation is in progress**. Also, how exactly is **massive CPU load** being determined? And is it really only CPU load (processes in `R` state) or system `loadavg` (as returned by `uptime` command, which counts both `R`unning processes, and processes in `D` state - often due to being swapped out due to insufficient memory or blocked by slow disk I/O) But while I'm not using `oc_bookmarks`, the `EXPLAIN SELECT` does look suspicious - the `key` column should not be `NULL` if table contains any data and possible indexes. `ANALYZE TABLE tablename` (or slower `OPTIMIZE table tablename`) might sometimes help. @faaaaabi if you run that "SELECT" command by hand, how long does it take to execute?

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@KlugFR commented on GitHub (Mar 9, 2021):

Same issue here with the last versions of the apps (NC 20.0.8, according bookmarks and Floccus 4.6.0).

With Floccus enabled in firefox on my mac, load on the NextCloud server was over 8 all the time, with 4 to 20 php-fpm processes at 100% CPU each all the time.
With Floccus disabled, load drops down to 0.2 on the server...

You can see the CPU usage drop on the graph (4 x E2697-v3 cores, the VM is on its own on the host).
I disabled sync at 15h54 and re-enabled it for testing at 15h59 after I fixed the two "looks like broken" indexes in oc_bookmarks.

Even if the issue is named after mysql, the issue seems to be in php-fpm CPU usage, not mariadb.

@KlugFR commented on GitHub (Mar 9, 2021): Same issue here with the last versions of the apps (NC 20.0.8, according bookmarks and Floccus 4.6.0). With Floccus enabled in firefox on my mac, load on the NextCloud server was over 8 all the time, with 4 to 20 php-fpm processes at 100% CPU each all the time. With Floccus disabled, load drops down to 0.2 on the server... You can see the CPU usage drop on the graph (4 x E2697-v3 cores, the VM is on its own on the host). I disabled sync at 15h54 and re-enabled it for testing at 15h59 after I fixed the two "looks like broken" indexes in oc_bookmarks. ![VM](https://user-images.githubusercontent.com/6048396/110493490-f2454900-80f2-11eb-8ad1-955f9c001cca.jpg) Even if the issue is named after mysql, the issue seems to be in php-fpm CPU usage, not mariadb.

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@marcelklehr commented on GitHub (Mar 9, 2021):

@KlugFR If you experience different problems, this may be the wrong issue for you. The focus of this issue is MySQL/MariaDB troubles. Or did I get this wrong?

@marcelklehr commented on GitHub (Mar 9, 2021): @KlugFR If you experience different problems, this may be the wrong issue for you. The focus of this issue is MySQL/MariaDB troubles. Or did I get this wrong?

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@KlugFR commented on GitHub (Mar 9, 2021):

Initial issue (https://github.com/floccusaddon/floccus/issues/476) of the OP is the same as I encountered : massive CPU.

In the initial issue (closed) the OP suggested it could be mysql issues but nothing in his posts links "massive CPU" and "mysql (or mariadb) server".

I posted here because I think the issue is not mysql (or mariadb) related: even with broken/fixed indexes, the issue is in php-fpm, even with the current versions of the apps.

@KlugFR commented on GitHub (Mar 9, 2021): Initial issue (https://github.com/floccusaddon/floccus/issues/476) of the OP is the same as I encountered : massive CPU. In the initial issue (closed) the OP suggested it could be mysql issues but nothing in his posts links "massive CPU" and "mysql (or mariadb) server". I posted here because I think the issue is not mysql (or mariadb) related: even with broken/fixed indexes, the issue is in php-fpm, even with the current versions of the apps.

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@marcelklehr commented on GitHub (Mar 9, 2021):

Ah, I see. #476 was fixed, though. This issue was opened, because it is a separate problem. I encourage you to open a new issue detailing your problem. (Warning: Please stick to the issue template, as your issue will be closed otherwise.)

@marcelklehr commented on GitHub (Mar 9, 2021): Ah, I see. #476 was fixed, though. This issue was opened, because it is a separate problem. I encourage you to open a new issue detailing your problem. (Warning: Please stick to the issue template, as your issue will be closed otherwise.)

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@faaaaabi commented on GitHub (Mar 12, 2021):

ALTER TABLE oc_bookmarks ADD INDEX `bookmarks_userid` (`user_id`);

Thanks for the tip.
After dumping, re-importing and re-indexing, the problem still persists.

MariaDB [nextcloud]> show processlist;
+--------+-----------+------------------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
| Id     | User      | Host             | db        | Command | Time | State        | Info                                                                                                 | Progress |
+--------+-----------+------------------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
| 194981 | root      | localhost        | nextcloud | Query   |    0 | starting     | show processlist                                                                                     |    0.000 |
| 195030 | nextcloud | 172.23.0.3:55488 | nextcloud | Query   |   34 | Sending data | SELECT `b`.`id`, `b`.`url`, `b`.`title`, `b`.`description`, `b`.`lastmodified`, `b`.`added`, `b`.`cl |    0.000 |
+--------+-----------+------------------+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
2 rows in set (0.000 sec)

MariaDB [nextcloud]> show processlist;
+--------+-----------+------------------+-----------+---------+------+---------------------+------------------------------------------------------------------------------------------------------+----------+
| Id     | User      | Host             | db        | Command | Time | State               | Info                                                                                                 | Progress |
+--------+-----------+------------------+-----------+---------+------+---------------------+------------------------------------------------------------------------------------------------------+----------+
| 194981 | root      | localhost        | nextcloud | Query   |    0 | starting            | show processlist                                                                                     |    0.000 |
| 195030 | nextcloud | 172.23.0.3:55488 | nextcloud | Query   |   39 | Creating sort index | SELECT `b`.`id`, `b`.`url`, `b`.`title`, `b`.`description`, `b`.`lastmodified`, `b`.`added`, `b`.`cl |    0.000 |
+--------+-----------+------------------+-----------+---------+------+---------------------+------------------------------------------------------------------------------------------------------+----------+

@faaaaabi if you run that "SELECT" command by hand, how long does it take to execute?

300 rows in set (1 min 40.444 sec)

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@mnalis commented on GitHub (Mar 15, 2021):

@faaaaabi that is interesting; while oc_bookmarks lookup does not seem to be using indexes, it is just several hundreds rows, so it should not be that bad. Spending any amount of time in Creating sort index sounds strange though.

could you also do:

 select count(*) from oc_bookmarks;
 set profiling=1;
 SELECT [... that long query ...];
 show profile;

I'm interested in output of select count(*), and output of show profile. For main long query (which you replace with the slow query you see in show full processlist), please do not paste full data output, but just statistics at the end (like you did above - "xxx rows in a set (x min yy sec)")

Also, if do you have shell access on machine running mysqld (might be different from machine running nextcloud web server), and if so could you send outputs I asked for in https://github.com/floccusaddon/floccus/issues/792#issuecomment-764907180 (especially free -h and 'ps auxfwww | grep mysql` are important).

It might help me point you to few things you can try (but they are very dependent on memory use on the server)

@mnalis commented on GitHub (Mar 15, 2021): @faaaaabi that is interesting; while `oc_bookmarks` lookup does not seem to be using indexes, it is just several hundreds rows, so it should not be that bad. Spending any amount of time in `Creating sort index` sounds strange though. could you also do: ``` select count(*) from oc_bookmarks; set profiling=1; SELECT [... that long query ...]; show profile; ``` I'm interested in output of `select count(*)`, and output of `show profile`. For main long query (which you replace with the slow query you see in `show full processlist`), please do not paste full data output, but just statistics at the end (like you did above - "`xxx rows in a set (x min yy sec)`") Also, if do you have shell access on machine running `mysqld` (might be different from machine running nextcloud web server), and if so could you send outputs I asked for in https://github.com/floccusaddon/floccus/issues/792#issuecomment-764907180 (especially `free -h ` and 'ps auxfwww | grep mysql` are important). It might help me point you to few things you can try (but they are very dependent on memory use on the server)

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@faaaaabi commented on GitHub (Mar 15, 2021):

select count(*) from oc_bookmarks;

MariaDB [nextcloud]> select count(*) from oc_bookmarks;
+----------+
| count(*) |
+----------+
|      796 |
+----------+
1 row in set (0.003 sec)

show profile;

MariaDB [nextcloud]> show profile;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| Starting                       |  0.000072 |
| Waiting for query cache lock   |  0.000008 |
| starting                       |  0.000006 |
| Checking query cache for query |  0.000498 |
| checking permissions           |  0.000057 |
| Opening tables                 |  0.000456 |
| After opening tables           |  0.000202 |
| System lock                    |  0.000035 |
| table lock                     |  0.000015 |
| Waiting for query cache lock   |  0.000325 |
| init                           |  0.000291 |
| Optimizing                     |  0.000117 |
| Statistics                     |  0.000321 |
| Preparing                      |  0.000143 |
| Creating tmp table             |  0.000791 |
| Creating tmp table             |  0.000330 |
| Sorting result                 |  0.000151 |
| Executing                      |  0.000011 |
| Sending data                   |  0.063057 |
| Converting HEAP to Aria        |  0.021009 |
| Sending data                   | 51.227379 |
| Creating sort index            | 79.278085 |
| Creating sort index            |  0.025480 |
| Waiting for query cache lock   |  0.000024 |
| Creating sort index            |  0.001514 |
| Waiting for query cache lock   |  0.000018 |
| Creating sort index            |  0.064723 |
| Removing tmp table             |  0.193132 |
| Creating sort index            |  0.000049 |
| Removing tmp table             |  0.019110 |
| Creating sort index            |  0.000111 |
| End of update loop             |  0.000026 |
| Query end                      |  0.000005 |
| Commit                         |  0.000009 |
| closing tables                 |  0.000008 |
| Unlocking tables               |  0.000005 |
| closing tables                 |  0.000034 |
| Starting cleanup               |  0.000007 |
| Freeing items                  |  0.000254 |
| Removing tmp table             |  0.000022 |
| Freeing items                  |  0.000006 |
| Removing tmp table             |  0.000007 |
| Freeing items                  |  0.000007 |
| Updating status                |  0.001674 |
| Reset for next command         |  0.000034 |
+--------------------------------+-----------+
45 rows in set (0.002 sec)

MySQL runs in a docker container without cgroup limits. The machine also has plenty of memory. Nevertheless here is the output from inside the MySQL container.:

free -h

              total        used        free      shared  buff/cache   available
Mem:           58Gi        13Gi        43Gi       364Mi       1.9Gi        44Gi
Swap:         2.0Gi          0B       2.0Gi

ps auxfwww | grep mysql

root       32324  0.0  0.0   3304   736 pts/1    S+   15:28   0:00  \_ grep --color=auto mysql
root       24823  0.0  0.0  16832 10568 pts/0    S+   Mar12   0:01  \_ mysql -u root -p
mysql          1  0.5  1.6 4474260 1026468 ?     Ssl  Mar02 102:28 mysqld --transaction-isolation=READ-COMMITTED --binlog-format=ROW

@faaaaabi commented on GitHub (Mar 15, 2021): > select count(*) from oc_bookmarks; ```bash MariaDB [nextcloud]> select count(*) from oc_bookmarks; +----------+ | count(*) | +----------+ | 796 | +----------+ 1 row in set (0.003 sec) ``` > show profile; ```bash MariaDB [nextcloud]> show profile; +--------------------------------+-----------+ | Status | Duration | +--------------------------------+-----------+ | Starting | 0.000072 | | Waiting for query cache lock | 0.000008 | | starting | 0.000006 | | Checking query cache for query | 0.000498 | | checking permissions | 0.000057 | | Opening tables | 0.000456 | | After opening tables | 0.000202 | | System lock | 0.000035 | | table lock | 0.000015 | | Waiting for query cache lock | 0.000325 | | init | 0.000291 | | Optimizing | 0.000117 | | Statistics | 0.000321 | | Preparing | 0.000143 | | Creating tmp table | 0.000791 | | Creating tmp table | 0.000330 | | Sorting result | 0.000151 | | Executing | 0.000011 | | Sending data | 0.063057 | | Converting HEAP to Aria | 0.021009 | | Sending data | 51.227379 | | Creating sort index | 79.278085 | | Creating sort index | 0.025480 | | Waiting for query cache lock | 0.000024 | | Creating sort index | 0.001514 | | Waiting for query cache lock | 0.000018 | | Creating sort index | 0.064723 | | Removing tmp table | 0.193132 | | Creating sort index | 0.000049 | | Removing tmp table | 0.019110 | | Creating sort index | 0.000111 | | End of update loop | 0.000026 | | Query end | 0.000005 | | Commit | 0.000009 | | closing tables | 0.000008 | | Unlocking tables | 0.000005 | | closing tables | 0.000034 | | Starting cleanup | 0.000007 | | Freeing items | 0.000254 | | Removing tmp table | 0.000022 | | Freeing items | 0.000006 | | Removing tmp table | 0.000007 | | Freeing items | 0.000007 | | Updating status | 0.001674 | | Reset for next command | 0.000034 | +--------------------------------+-----------+ 45 rows in set (0.002 sec) ``` MySQL runs in a docker container without cgroup limits. The machine also has plenty of memory. Nevertheless here is the output from inside the MySQL container.: > free -h ```bash total used free shared buff/cache available Mem: 58Gi 13Gi 43Gi 364Mi 1.9Gi 44Gi Swap: 2.0Gi 0B 2.0Gi ``` > ps auxfwww | grep mysql ```bash root 32324 0.0 0.0 3304 736 pts/1 S+ 15:28 0:00 \_ grep --color=auto mysql root 24823 0.0 0.0 16832 10568 pts/0 S+ Mar12 0:01 \_ mysql -u root -p mysql 1 0.5 1.6 4474260 1026468 ? Ssl Mar02 102:28 mysqld --transaction-isolation=READ-COMMITTED --binlog-format=ROW ```

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@mnalis commented on GitHub (Mar 17, 2021):

Sending data and Creating sort index are two longest running states here. Thread states are explained here. Might be (or not) caused by slow random disk I/O.

However, preceding Converting HEAP to Aria is indicative that temporary tables did not fit into memory and were moved to disk, which could lead to slowdowns (especially if using rotational media HDDs). Should not have such drastic effects, but you never know.

@faaaaabi Could you run this in same mysql session, and let me know profiling results?

set session max_heap_table_size = 134217728;
set session tmp_table_size = 134217728;
set session join_buffer_size = 4194304;
set session join_buffer_space_limit = 67108864;
set session sort_buffer_size = 16777216;
set profiling=1;
SELECT [... that long query ...];
show profile;

@faaaaabi also, can you run select count(*) from oc_bookmarks_tree; then wait for few syncs to run and then run it again? What are the numbers? (and what version of OC bookmarks app are you currently on - still 4.0.5 or newer)?

Because it looks suspicious to me that you have just 796 bookmarks (oc_bookmarks table), but 55116 entries in oc_bookmarks_tree, which should (as I understand it) only contain extra entries for folders etc. (so should probably have less then 1000 entries or so - definitely nowhere near 55000). @marcelklehr do I get that correctly, or are there legitimate cases for oc_bookmarks_tree to grow so much bigger than oc_bookmarks?

In my opinion (but I'm not bookmarks app developer!), if that number grows while no new bookmarks are added, it might indicate a bug in Bookmarks app. Even if it does not grow, it might've been bug in previous versions on bookmarks app, (and perhaps root cause of slowness). If that is the case, maybe you'd want to try: (1) backuping bookmarks just in case, (2) removing the bookmarks app, (3) dropping old oc_bookmarks* tables , (4) reinstalling newest Bookmarks app, (5) trying to sync again and checking the counts and speed again.

@mnalis commented on GitHub (Mar 17, 2021): `Sending data` and `Creating sort index` are two longest running states here. Thread states are explained [here](https://mariadb.com/kb/en/general-thread-states/). Might be (or not) caused by slow random disk I/O. However, preceding `Converting HEAP to Aria` is indicative that temporary tables did not fit into memory and were moved to disk, which could lead to slowdowns (especially if using rotational media HDDs). Should not have such drastic effects, but you never know. 1. @faaaaabi Could you run this in same mysql session, and let me know profiling results? ``` set session max_heap_table_size = 134217728; set session tmp_table_size = 134217728; set session join_buffer_size = 4194304; set session join_buffer_space_limit = 67108864; set session sort_buffer_size = 16777216; set profiling=1; SELECT [... that long query ...]; show profile; ``` 2. @faaaaabi also, can you run `select count(*) from oc_bookmarks_tree;` then wait for few syncs to run and then run it again? What are the numbers? (and what version of OC bookmarks app are you currently on - still `4.0.5` or newer)? Because it looks suspicious to me that you have just `796` bookmarks (`oc_bookmarks` table), but `55116` entries in `oc_bookmarks_tree`, which should (as I understand it) only contain extra entries for folders etc. (so should probably have less then `1000` entries or so - definitely nowhere near `55000`). @marcelklehr do I get that correctly, or are there legitimate cases for `oc_bookmarks_tree` to grow so much bigger than `oc_bookmarks`? In my opinion (but I'm not bookmarks app developer!), if that number grows while no new bookmarks are added, it might indicate a bug in Bookmarks app. Even if it does not grow, it might've been bug in previous versions on bookmarks app, (and perhaps root cause of slowness). If that is the case, maybe you'd want to try: (1) backuping bookmarks just in case, (2) removing the bookmarks app, (3) [dropping old oc_bookmarks* tables](https://github.com/nextcloud/bookmarks/issues/1107) , (4) reinstalling newest Bookmarks app, (5) trying to sync again and checking the counts and speed again.

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@marcelklehr commented on GitHub (Jul 24, 2021):

Hey there!

It's been a while, but I've finally uncovered the reason for this peculiar behavior and will release a patch to the bookmarks app shortly: https://github.com/nextcloud/bookmarks/pull/1608

The problem was that the query in question needs to load all bookmarks in the db initially since they could all potentially be viewed by the current user (if they are shared with the current user, which however, will only be discovered after a JOIN). My solution, now, is to initially only load bookmarks by users that have shared bookmarks with the current user, which is still not ideal, but should be a much smaller set than ALL.

@marcelklehr commented on GitHub (Jul 24, 2021): Hey there! It's been a while, but I've finally uncovered the reason for this peculiar behavior and will release a patch to the bookmarks app shortly: https://github.com/nextcloud/bookmarks/pull/1608 The problem was that the query in question needs to load all bookmarks in the db initially since they could all potentially be viewed by the current user (if they are shared with the current user, which however, will only be discovered after a JOIN). My solution, now, is to initially only load bookmarks by users that have shared bookmarks with the current user, which is still not ideal, but should be a much smaller set than *ALL*.

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@marcelklehr commented on GitHub (Jul 24, 2021):

@marcelklehr do I get that correctly, or are there legitimate cases for oc_bookmarks_tree to grow so much bigger than oc_bookmarks?

the tree table should not grow much larger than the bookmarks table, except for cases where the same bookmark is in multiple folders. There is now a repair step that should get rid of all superfluous tree entries.

@marcelklehr commented on GitHub (Jul 24, 2021): > @marcelklehr do I get that correctly, or are there legitimate cases for oc_bookmarks_tree to grow so much bigger than oc_bookmarks? the tree table should not grow much larger than the bookmarks table, except for cases where the same bookmark is in multiple folders. There is now a repair step that should get rid of all superfluous tree entries.

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@marcelklehr commented on GitHub (Jul 26, 2021):

Should be fixed with https://github.com/nextcloud/bookmarks/releases/tag/v4.4.0

@marcelklehr commented on GitHub (Jul 26, 2021): Should be fixed with https://github.com/nextcloud/bookmarks/releases/tag/v4.4.0

kerem commented

2026-02-25 22:37:21 +03:00

Author

Owner

@github-actions[bot] commented on GitHub (Mar 20, 2023):

This issue has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.

@github-actions[bot] commented on GitHub (Mar 20, 2023): This issue has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.

kerem referenced this issue

2026-02-25 22:40:44 +03:00

[PR #500] [MERGED] Bump prettier from 1.19.1 to 2.0.5 #1602

Rows
Columns

[GH-ISSUE #792] MySQL troubles #500