Bug #87723

Missing Index on sys_log table

Added by Christoph Lehmann about 1 month ago. Updated 10 days ago.

Status:
Under Review
Priority:
Must have
Assignee:
-
Category:
Performance
Target version:
Start date:
2019-02-15
Due date:
% Done:

0%

TYPO3 Version:
9
PHP Version:
Tags:
Complexity:
no-brainer
Is Regression:
Sprint Focus:

Description

While analysing the slow.log with pt-query-digest i see

# Query 1: 0.17 QPS, 0.11x concurrency, ID 0xD52AD4861404CBA7 at byte 195329
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-02-15T10:34:39 to 2019-02-15T10:34:51
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0       2
# Exec time     64      1s   636ms   687ms   661ms   687ms    36ms   661ms
# Lock time      0   104us    46us    58us    52us    58us     8us    52us
# Rows sent      0       2       1       1       1       1       0       1
# Rows examine  92   2.69M   1.35M   1.35M   1.35M   1.35M       0   1.35M
# Query size     0     172      86      86      86      86       0      86
# String:
# Databases    dbname
# Hosts        localhost
# Users        dbname
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `dbname` LIKE 'sys_log'\G
#    SHOW CREATE TABLE `dbname`.`sys_log`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(`error`) FROM `sys_log` WHERE (`tstamp` >= 0) AND (`error` IN (-1, 1, 2))\G

The query is done for the error counter in the topbar in backend. 600ms for a query is fatal. It may be okay to have big tables leading to longer execution time, but queries should be optimized

I suggest using a further index on that table:

CREATE INDEX errorcount ON sys_log (tstamp,error)



without-index.png View (33.2 KB) Christoph Lehmann, 2019-02-15 12:06

errorcounter.png View (5.29 KB) Christoph Lehmann, 2019-02-15 12:06

with-index.png View (39.1 KB) Christoph Lehmann, 2019-02-15 12:06

History

#1 Updated by Christoph Lehmann about 1 month ago

  • Description updated (diff)

#2 Updated by Gerrit Code Review about 1 month ago

  • Status changed from New to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/59707

#3 Updated by DANIEL Rémy 15 days ago

Hi.

I confirm the issue: on my db, this query is the only one that triggers mysql slow log.
I've attached in the review a benchmark without/with the index.

Cheers

#4 Updated by Gerrit Code Review 14 days ago

Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/59707

#5 Updated by Stephan Großberndt 10 days ago

  • Description updated (diff)

Also available in: Atom PDF