Review main Merged

59707[TASK] Add sql index for error counter in topbar

Christoph Lehmann CR +2 V +2 Easy +2 −1 updated 03/22/19, 21:24

Commit message

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

Review

  • Code review CR +2 Done
    • Frank Nägler +2
    • Oliver Klee +1
    • Guido Schmechel +1
    • Alexander Schnitzler +1
    • Fabien Udriot +1
  • Verified V +2 Done
    • Frank Nägler +2
    • Alexander Schnitzler +1
    • Fabien Udriot +1
    • TYPO3com +1 CI

Issues 1

Files 1

Review log 2427

Show 12 earlier messages
  1. Christoph Lehmann uploaded patch set 1 02/15/19, 13:50

  2. TYPO3com voted on patch set 1 V +1 02/15/19, 14:08 Completed build in 17m 49s on Fri, 15 Feb, 03:08 PM Test Summary: 46581 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-32225

  1. Guido Schmechel voted on patch set 1 CR +1 02/16/19, 10:37

  2. Oliver Klee voted on patch set 1 CR +1 02/16/19, 13:52

  3. Alexander Schnitzler commented on patch set 1 02/17/19, 12:59

    Could you please write a few words what this change does and why and maybe add some numbers. No complex math but sth. like: "Reduces the sql query for counting the errors from 200 to 20 ms on average."
  4. Tomas Norre Mikkelsen voted on patch set 1 CR +1 02/17/19, 15:40

  5. Rémy DANIEL commented on patch set 1 V +1 03/04/19, 10:55

    Hi Here is a benchmark. ------------------- sql> SELECT COUNT(*) FROM `sys_log` -- 1 row retrieved starting from 1 in 28 ms (execution: 5 ms, fetching: 23 ms) 5568659 sql> SELECT SQL_NO_CACHE COUNT(`error`) FROM `sys_log` WHERE (`tstamp` >= 0) AND (`error` IN (-1, 1, 2)) -- 1 row retrieved starting from 1 in 38 s 437 ms (execution: 38 s 428 ms, fetching: 9 ms) 425629 sql> ALTER TABLE sys_log ADD KEY errorlog (tstamp, error) -- completed in 22 s 312 ms sql> SELECT SQL_NO_CACHE COUNT(`error`) FROM `sys_log` WHERE (`tstamp` >= 0) AND (`error` IN (-1, 1, 2)) -- 1 row retrieved starting from 1 in 1 s 451 ms (execution: 1 s 442 ms, fetching: 9 ms) 425629 ------------------- So on my table with more than 5 million of rows, the query is executed 96% faster with the new index. Beware that the construction of the index might take some times on big table. On some system with a low max_execution_time of fcgi timeout, it might be an issue.
  6. Guido Schmechel commented on patch set 1 03/04/19, 11:01

    I think there should be a hint in an RST. Especially with the execution time otherwise can certainly provide for questions or problems.
  7. Christoph Lehmann updated the commit message in patch set 2 03/05/19, 13:34

  8. Oliver Klee voted on patch set 2 CR +1 03/05/19, 13:36

  9. TYPO3com voted on patch set 2 V +1 03/05/19, 13:49 Completed build in 14m 40s on Tue, 5 Mar, 02:49 PM Test Summary: 46812 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-32534

  10. Wouter Wolters voted on patch set 2 CR +1 03/08/19, 22:24

  11. Frank Nägler voted on patch set 2 V +1 CR +1 03/08/19, 22:31

  12. Stephan Großberndt voted on patch set 2 CR +1 03/08/19, 22:50

  13. Benjamin Franzke commented on patch set 2 V -1 03/09/19, 00:02

    How did you verify this patch? I can not reproduce the huge performance win. I've created 5 million sys_log entries, each with a different tstamp values, and error field set to 1. Querying that table with the query that is used by the SystemInformationController results in: * 1.947 sec execution time without the index * 1.692 sec execution time with the index That is not that much of an improvement which would be worth the index. The test results mentioned in the commit message rather sounds as if the second case (0.x sec) was measured when a mysql cache was hit. (In that case I can reproduce query times 0.0 sec query times, but both with and without an index) That mans, to retrieve comparable results, you should issue `RESET QUERY CACHE;` before performing the test query (or issue another `INSERT INTO sys_log …` to render the current mysql caches invalid). [1] SELECT COUNT(`error`) FROM `sys_log` WHERE (`tstamp` >= 0) AND (`error` IN (-1, 1, 2));
  14. Christoph Lehmann commented on patch set 2 03/10/19, 18:03

    > Patch Set 2: Verified-1 > > How did you verify this patch? I can not reproduce the huge performance win. I took over Daniels results (see earlier comments) and (good catch) i overlook 1s so correct were "<1,5s". > Querying that table with the query that is used by the SystemInformationController results in: > * 1.947 sec execution time without the index > * 1.692 sec execution time with the index Even 200ms less seems better to me. > > That is not that much of an improvement which would be worth the index. > The test results mentioned in the commit message rather sounds as if the second case (0.x sec) was measured when a mysql cache was hit. (In that case I can reproduce query times 0.0 sec query times, but both with and without an index) > > That mans, to retrieve comparable results, you should issue `RESET QUERY CACHE;` before performing the test query (or issue another `INSERT INTO sys_log …` to render the current mysql caches invalid). Daniel used SQL_NO_CACHE, which means the query is not used. (Offtopic: The query cache is removed in MySQL 8 and deactivated by default since it may be contra productive in multicore environments with multiple concurrent connections)

    Reply

    Done
  15. Rémy DANIEL commented on patch set 2 03/10/19, 20:19

    Hello I've tested the index in a real live environment with 5 millions rows in the sys_log table, with 30 users connected in the backend and editing content. The sys_log table is writed a lot, so the query cache is always not used. This is why I executed the query with SQL_NO_CACHE: I needed to test the new index in a situation where the query cache is quite never used. So again, I my situation, the new index *very* helpful.
  16. Benni Mack commented on patch set 2 CR -1 03/16/19, 11:32

    Hey, I also disagree on that change. a) I would like to avoid DB changes in LTS versions as much as possible, we had several issues with hundreds of installations after 9.5.5. So 8.7 is a nogo for core most definitively. The change could be added actually as an extension where this is an issue for 9, and we add it to master. b) We had a minor improvement in one of our installations (we went from 1s to 0.5-0.7s on 80K DB entries), so it's good but not as critical IMHO. I propose to do this on master-only
  17. Christoph Lehmann updated the commit message in patch set 3 03/20/19, 20:58

  18. TYPO3com voted on patch set 3 V +1 03/20/19, 21:17 Completed build in 17m 36s on Wed, 20 Mar, 10:17 PM Test Summary: 47201 passed Find logs and detail information at https://bamboo.typo3.com/browse/CORE-GTC-32868

  19. Guido Schmechel voted on patch set 3 CR +1 03/20/19, 21:19

  20. Alexander Schnitzler commented on patch set 3 V +1 CR +1 03/22/19, 08:55

    Despite the fact that the actual improvement of this patch can't really be measured and despite the fact that some see a huge benefit and some don't, I don't see any harm in adding an index that in the worst case just slightly improves the performance. Considering that the patch is master only now, I vote +1.
  21. Benjamin Franzke commented on patch set 3 03/22/19, 09:22

    Unresolved

    Sorry to nitpick here, but I really can not reproduce the 38s query – even on slow mittwald (please don't take too serious) non-ssd databases. Daniel, can you shed some light on the test system this measurements were performed on? On most system we (and others) tested we rather got 2s without and 1.5s with the index. I'm fine for the patch to be merged to master, but I'd really like to see realistic numbers in the commit message.
  22. Oliver Klee voted on patch set 3 CR +1 03/22/19, 10:34

  23. Fabien Udriot voted on patch set 3 V +1 CR +1 03/22/19, 15:07

  24. Frank Nägler voted on patch set 3 V +2 CR +2 03/22/19, 21:24

  25. Frank Nägler merged patch set 4 03/22/19, 21:24