Value lookups by items_id do not use the itemtype index and scan the whole values table
維護者通常 1 天內回覆
評估
- 難度
- 2/5
- 預估耗時
- 1-3 小時
- 新手友好度
- 75/100
- Issue 類型
- 缺陷
- 描述清晰度
- 描述清楚
- 活躍度
- 活躍
- 技術堆疊
- mariadb, php
- 領域
- database, performance
研究方向
先閱讀 PluginFieldsField::prepareHtmlFields() 和 PluginFieldsContainer::updateFieldsValues(),接著找出儲存路徑中提到的其他 getFromDBByCrit() 呼叫。檢查每次查詢如何取得其項目類型,並將查詢計畫與回報的 EXPLAIN 重現結果進行比較。當這些查詢可以使用現有的複合索引,並避免掃描大型資料表時,就算完成。
由索引模型根據 Issue 內容生成。
描述
GLPI Version
11.0.11
Plugin version
1.24.6. The same code is in 1.25.0 and on main.
Bug description
The values table of each container (for example glpi_plugin_fields_ticketticketdetails) has the unique index itemtype_item_container (itemtype, items_id, plugin_fields_containers_id) and no other index on items_id. Several lookups filter on items_id without itemtype, so the index cannot be used and every call reads the whole table:
PluginFieldsField::prepareHtmlFields(),$obj->find(['plugin_fields_containers_id' => ..., 'items_id' => ...]), run on every display of an item that has a container.PluginFieldsContainer,getFromDBByCrit(['items_id' => $items_id])in the save path (4 places, for example inupdateFieldsValues()).
With 500,000 tickets and about 290,000 rows in the ticket container table, the first query read about 290,000 rows per call and took 156 ms on average at low load. Under heavy load the average rose to between 3.2 and 5.1 s per call. It was the fourth most expensive statement on the server at low load, and opening a ticket was slowed down by it.
Relevant log output
calls | total ms | avg ms | rows examined | no index | statement
268 | 41881 | 156.3 | 77890984 | 268 | SELECT * FROM `glpi_plugin_fields_ticketticketdetails` WHERE `plugin_fields_containers_id` = ? AND `items_id` = ?
430 | 745460 | 1733.6 | 124974340 | 430 | SELECT `id` FROM `glpi_plugin_fields_ticketticketdetails` WHERE `items_id` = ?
(performance_schema statement digests; the second line is from a step under heavy load)
Steps To reproduce
- Create a container on tickets with a few fields.
- Have a large number of tickets with values in it (hundreds of thousands).
- Open a ticket and run
EXPLAIN SELECT * FROM glpi_plugin_fields_ticketticketdetails WHERE plugin_fields_containers_id = 1 AND items_id = 12345;
Expected: the lookup uses the unique index.
Actual: no index is used and the whole table is read.
Your GLPI setup information
Docker, PHP 8.x with Apache prefork, MariaDB 10.11 with an 8 GB buffer pool.
Anything else?
Each values table holds a single itemtype, so adding 'itemtype' => $item->getType() (or the known itemtype) to these criteria lets the existing unique index be used. An index on items_id would also work.
- 主要語言
- PHP
- 星號
- 122
- 分支
- 85
- 平均合併
- 1 天 15 小時
- 30 天內合併 PR
- 20
環境準備
這個專案沒有提供開發容器、Dockerfile 或貢獻指南,環境需要你自己搭建:先看它的 README,通用步驟見我們的新手貢獻指南。
從這裡開始
- 先讀完整個 Issue,再讀專案的貢獻指南。
- 在 Issue 下留言說明你要接手 —— 這能避免兩個人做同樣的事。
- Fork 儲存庫,在一個分支上完成修改。
- 送出 Pull Request,並在描述裡引用這個 Issue 編號。
pluginsGLPI/fields 的其他 Issue
-
Slow page loads with many entities: getEntries() checks entities and rights container by container on every request可能已有人在做 關聯的 PR 仍在進行中或已合併。 未關閉
難度 4/5 3-5 天 新手友好度 48/100
pluginsGLPI/fields#1347 ·
維護者通常 1 天內回覆
-
[GLPI 11] GenericObject plugin cannot be migrated. Please update it to the latest version可能已有人在做 關聯的 PR 仍在進行中或已合併。 未關閉
難度 3/5 1-2 天 新手友好度 30/100
pluginsGLPI/fields#1015 ·
維護者通常 1 天內回覆
查看 pluginsGLPI/fields 的全部 Issue
相似的 Issue
-
難度 1/5 1 小時以內 新手友好度 88/100
-
難度 1/5 1 小時以內 新手友好度 82/100
pluginsGLPI/carbon#396 ·
-
[BUG] Bundle products lose their price in RSS feeds: getPrices() was removed可能已有人在做 @pcvelz 今天認領。 未關閉
難度 1/5 1 小時以內 新手友好度 90/100
OpenMage/magento-lts#5869 ·
維護者通常 2 天內回覆
-
難度 1/5 1-3 小時 新手友好度 85/100
modelcontextprotocol/php-sdk#746 ·
維護者通常 1 天內回覆
-
難度 1/5 1 小時以內 新手友好度 90/100
OpenGIS/waymark-wp#82 ·