search

in2code-de / luxletter

Newsletter system for TYPO3
https://www.in2code.de/agentur/typo3-extensions/luxletter/
24 stars 25 forks source link

[Bug] with link tracking enabled the sending time rises with the number of mails already sent #90

Closed rr-it closed 3 years ago

rr-it commented 3 years ago

The time to send a newsletter rises with the number of mails already sent:

newsletter mails_total time_sending_total time_avg_per_mail
16 (fixed) 2655 3121 1.1755
15 2633 44760 16.9996
14 (short special*) 2613 20521 7.8534
13 2608 45780 17.5537
12 2594 41220 15.8905
11 2572 30780 11.9673
10 (short special*) 2540 11280 4.4409
9 2531 25680 10.1462
8 (short special*) 2522 8101 3.2121
7 2519 29340 11.6475
6 2521 13380 5.3074
3 2509 10920 4.3523
2 2492 9059 3.6352

* short special: mailing with ~10 links instead of ~50

SQL to reproduce table (click to open) ~~~ sql SELECT `newsletter`, `mails_total`, `time_end` - `time_start` AS 'time_sending_total', (`time_end` - `time_start`) / `mails_total` AS 'time_avg_per_mail' FROM ( SELECT `newsletter`, COUNT(`uid`) AS 'mails_total', MIN(`tstamp`) as 'time_start', MAX(`tstamp`) AS 'time_end' FROM `tx_luxletter_domain_model_queue` GROUP BY `newsletter` ) AS `queue_tmp` ORDER BY `newsletter` DESC ~~~

grafik

SQL to reproduce data (click to open) ~~~ sql SELECT `newsletter`, COUNT(`uid`) AS 'mails_total', `tstamp` FROM `tx_luxletter_domain_model_queue` GROUP BY `newsletter`, `tstamp` ORDER BY `tstamp` ASC ~~~

How to reproduce

Root cause

Slow db query to check if a link entry already exists - taking up to 0.5 s for each link:

SELECT `tx_luxletter_domain_model_link`.* FROM `tx_luxletter_domain_model_link` `tx_luxletter_domain_model_link`
WHERE (`tx_luxletter_domain_model_link`.`hash` = '0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef')
AND (`tx_luxletter_domain_model_link`.`sys_language_uid` IN (0, -1)) AND ((`tx_luxletter_domain_model_link`.`hidden` = 0) AND (`tx_luxletter_domain_model_link`.`starttime` <= 9999999999) AND ((`tx_luxletter_domain_model_link`.`endtime` = 0) OR (`tx_luxletter_domain_model_link`.`endtime` > 1111111111)) AND tx_luxletter_domain_model_link.deleted=0) ORDER BY `tx_luxletter_domain_model_link`.`crdate` DESC LIMIT 1

E.g. a newsletter has:

This results in 2.400 * 50 = 120.000 total link checks

Total time for link checks: 120.000 * 0,5 s = 60.000 s or ~17 h

How to fix

PR follows.

System

rr-it commented 3 years ago

Quick fix

ALTER TABLE `tx_luxletter_domain_model_link` ADD INDEX `hash` (`hash`(8));