Bug #87723

Missing Index on sys_log table

Added by Christoph Lehmann 6 months ago. Updated 3 months ago.

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

100%

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

Associated revisions

Revision 28acbd81 (diff)
Added by Christoph Lehmann 5 months ago

[TASK] Add sql index for error counter in topbar

The index reduces the sql query time and thus is a performance improvement.

On a table with 5 million rows the query time is ~ 38s without and less than 0.5s with the index

Resolves: #87723
Releases: master
Change-Id: I53b24ad0d09a2627a77720ec151dea30a14f5011
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/59707
Tested-by: TYPO3com <>
Tested-by: Alexander Schnitzler <>
Tested-by: Fabien Udriot <>
Tested-by: Frank Naegler <>
Reviewed-by: Guido Schmechel <>
Reviewed-by: Alexander Schnitzler <>
Reviewed-by: Oliver Klee <>
Reviewed-by: Fabien Udriot <>
Reviewed-by: Frank Naegler <>

History

#1 Updated by Christoph Lehmann 6 months ago

  • Description updated (diff)

#2 Updated by Gerrit Code Review 6 months 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 6 months 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 6 months 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 5 months ago

  • Description updated (diff)

#6 Updated by Gerrit Code Review 5 months 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

#7 Updated by Christoph Lehmann 5 months ago

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

#8 Updated by Benni Mack 3 months ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF