MYSQL runns to 100% CPU when triggered a 404 #5546

New Issue

OVERLORD · 2026-02-05T10:09:47+03:00

OVERLORD commented

2026-02-05 10:09:47 +03:00

Originally created by @knuz92 on GitHub (Dec 11, 2025).

Describe the Bug

When I call up a non-existent page in Bookstack, e.g., https://demo.bookstackapp.com/trigger404notexistent123123, the CPU goes up to 100%.

Using MariaDB > show full processlist; i discovered the following query as the cause of the error:

select *,viewable_id, viewable_type, SUM(views) as view_count from viewswhere (exists (selectentity_id, entity_type, max(owner_id) as owner_id, max(status) as status from joint_permissionswhereviews.viewable_id=joint_permissions.entity_idandrole_idin (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) andviews.viewable_type=joint_permissions.entity_typegroup byentity_type, entity_id having (status IN (1, 3) or (owner_id = ? and status != 2)))) and (viewable_type!= ? or exists (selectpage_idfromentity_page_datawhereentity_page_data.page_id=views.viewable_idandviews.viewable_type= ? andentity_page_data.draft= ?)) andviewable_typein (?) group byviewable_id, viewable_typeorder byview_count desc limit 10 offset 0

We use a lot of roles and have a larger instance running (4000 pages in 216 books).
The joint_permissions table has 980,000 entries.

Maybe that's causing the error.

I am on the newest Version: BookStack v25.11.6 with a fresh installed Debian 13 Server (the problem existed also on Deb12 with an lower BookStack Version)

Steps to Reproduce

Go to https://demo.bookstackapp.com/trigger404notexistent123123

Expected Behaviour

that the CPU does not go to 100% and performance should be better

Screenshots or Additional Context

Browser Details

No response

Exact BookStack Version

v25.11.6

Originally created by @knuz92 on GitHub (Dec 11, 2025). ### Describe the Bug When I call up a non-existent page in Bookstack, e.g., https://demo.bookstackapp.com/trigger404notexistent123123, the CPU goes up to 100%. Using MariaDB > show full processlist; i discovered the following query as the cause of the error: ` select *, `viewable_id`, `viewable_type`, SUM(views) as view_count from `views` where (exists (select `entity_id`, `entity_type`, max(owner_id) as owner_id, max(status) as status from `joint_permissions` where `views`.`viewable_id` = `joint_permissions`.`entity_id` and `role_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and `views`.`viewable_type` = `joint_permissions`.`entity_type` group by `entity_type`, `entity_id` having (status IN (1, 3) or (owner_id = ? and status != 2)))) and (`viewable_type` != ? or exists (select `page_id` from `entity_page_data` where `entity_page_data`.`page_id` = `views`.`viewable_id` and `views`.`viewable_type` = ? and `entity_page_data`.`draft` = ?)) and `viewable_type` in (?) group by `viewable_id`, `viewable_type` order by `view_count` desc limit 10 offset 0` We use a lot of roles and have a larger instance running (4000 pages in 216 books). The joint_permissions table has 980,000 entries. Maybe that's causing the error. I am on the newest Version: BookStack v25.11.6 with a fresh installed Debian 13 Server (the problem existed also on Deb12 with an lower BookStack Version) ### Steps to Reproduce Go to https://demo.bookstackapp.com/trigger404notexistent123123 ### Expected Behaviour that the CPU does not go to 100% and performance should be better ### Screenshots or Additional Context <img width="1035" height="540" alt="Image" src="https://github.com/user-attachments/assets/4356784c-b1e3-4fb3-8a28-19838559711f" /> ### Browser Details _No response_ ### Exact BookStack Version v25.11.6

OVERLORD added the 🛠️ Enhancement 💿 Database labels 2026-02-05 10:09:47 +03:00

OVERLORD closed this issue

2026-02-05 10:09:49 +03:00

OVERLORD commented

2026-02-05 10:09:50 +03:00

@knuz92 commented on GitHub (Dec 11, 2025):

The Log from the slow query log file


Time                Id Command  Argument
# Time: 251210 22:21:28
# User@Host: handbuch[handbuch] @ localhost []
# Thread_id: 11123  Schema: handbuch  QC_hit: No
# Query_time: 15.142865  Lock_time: 0.000023  Rows_sent: 10  Rows_examined: 6200373
# Rows_affected: 0  Bytes_sent: 0
use `handbuch`;
SET timestamp=1765401688;
select *, `viewable_id`, `viewable_type`, SUM(views) as view_count from `views` where (exists (select `entity_id`, `entity_type`, max(owner_id) as owner_id, max(status) as status from `joint_permissions` where `views`.`viewable_id` = `joint_permissions`.`entity_id` and `role_id` in (1, 5, 8, 20, 21, 67, 73, 87, 131, 171, 191, 199, 201, 206, 208, 209, 217, 222, 228) and `views`.`viewable_type` = `joint_permissions`.`entity_type` group by `entity_type`, `entity_id` having (status IN (1, 3) or (owner_id = 3 and status != 2)))) and (`viewable_type` != 'page' or exists (select `page_id` from `entity_page_data` where `entity_page_data`.`page_id` = `views`.`viewable_id` and `views`.`viewable_type` = 'page' and `entity_page_data`.`draft` = 0)) and `viewable_type` in ('page') group by `viewable_id`, `viewable_type` order by `view_count` desc limit 10 offset 0;

views.viewable_type has no Index

if i add the index:

CREATE INDEX idx_views_type_id
ON views (viewable_type, viewable_id);

i got the time down to about 7sec

@knuz92 commented on GitHub (Dec 11, 2025): The Log from the slow query log file ``` Time Id Command Argument # Time: 251210 22:21:28 # User@Host: handbuch[handbuch] @ localhost [] # Thread_id: 11123 Schema: handbuch QC_hit: No # Query_time: 15.142865 Lock_time: 0.000023 Rows_sent: 10 Rows_examined: 6200373 # Rows_affected: 0 Bytes_sent: 0 use `handbuch`; SET timestamp=1765401688; select *, `viewable_id`, `viewable_type`, SUM(views) as view_count from `views` where (exists (select `entity_id`, `entity_type`, max(owner_id) as owner_id, max(status) as status from `joint_permissions` where `views`.`viewable_id` = `joint_permissions`.`entity_id` and `role_id` in (1, 5, 8, 20, 21, 67, 73, 87, 131, 171, 191, 199, 201, 206, 208, 209, 217, 222, 228) and `views`.`viewable_type` = `joint_permissions`.`entity_type` group by `entity_type`, `entity_id` having (status IN (1, 3) or (owner_id = 3 and status != 2)))) and (`viewable_type` != 'page' or exists (select `page_id` from `entity_page_data` where `entity_page_data`.`page_id` = `views`.`viewable_id` and `views`.`viewable_type` = 'page' and `entity_page_data`.`draft` = 0)) and `viewable_type` in ('page') group by `viewable_id`, `viewable_type` order by `view_count` desc limit 10 offset 0; ``` views.viewable_type has no Index if i add the index: CREATE INDEX idx_views_type_id ON views (viewable_type, viewable_id); i got the time down to about 7sec

OVERLORD commented

2026-02-05 10:09:52 +03:00

@ssddanbrown commented on GitHub (Dec 19, 2025):

Thanks for reporting and investigating @knuz92.

I've added the extra index in 48df2be0d8, to be part of the next release, therefore I'll close this off.
There are probably other efficiencies to be had in regard to how we fundamentally query out this data, but those will take much more reworking & effort.

We use a lot of roles and have a larger instance running (4000 pages in 216 books).
The joint_permissions table has 980,000 entries.

I develop on a similar joint_permissions count, but a more significant element which can affect general performance is specifically the roles in general. A high number of roles, and a high number of assigned roles per-user can impact performance as that widens the scope for each query made. If roles are being assigned just for convenience or alignment with other systems, instead of directly being used in BookStack, I'd advise reducing them down where possible to get much better performance.

@ssddanbrown commented on GitHub (Dec 19, 2025): Thanks for reporting and investigating @knuz92. I've added the extra index in 48df2be0d8b64f7a6bc41e4dedfe7831d3a47941, to be part of the next release, therefore I'll close this off. There are probably other efficiencies to be had in regard to how we fundamentally query out this data, but those will take much more reworking & effort. > We use a lot of roles and have a larger instance running (4000 pages in 216 books). > The joint_permissions table has 980,000 entries. I develop on a similar `joint_permissions` count, but a more significant element which can affect general performance is specifically the roles in general. A high number of roles, and a high number of assigned roles per-user can impact performance as that widens the scope for each query made. If roles are being assigned just for convenience or alignment with other systems, instead of directly being used in BookStack, I'd advise reducing them down where possible to get much better performance.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: starred/BookStack#5546