Project

General

Profile

Actions

Bug #87723

closed

Missing Index on sys_log table

Added by Christoph Lehmann over 5 years ago. Updated about 5 years ago.

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

100%

Estimated time:
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)




Files

without-index.png (33.2 KB) without-index.png Christoph Lehmann, 2019-02-15 12:06
errorcounter.png (5.29 KB) errorcounter.png Christoph Lehmann, 2019-02-15 12:06
with-index.png (39.1 KB) with-index.png Christoph Lehmann, 2019-02-15 12:06
Actions #1

Updated by Christoph Lehmann over 5 years ago

  • Description updated (diff)
Actions #2

Updated by Gerrit Code Review over 5 years 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

Actions #3

Updated by Rémy DANIEL over 5 years 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

Actions #4

Updated by Gerrit Code Review over 5 years 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

Actions #5

Updated by Stephan Großberndt over 5 years ago

  • Description updated (diff)
Actions #6

Updated by Gerrit Code Review about 5 years ago

Patch set 3 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

Actions #7

Updated by Christoph Lehmann about 5 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100
Actions #8

Updated by Benni Mack about 5 years ago

  • Status changed from Resolved to Closed
Actions

Also available in: Atom PDF