0015194: usertracking produces high mysql serverload, causes cpu hog and kills server

Create

Capture

Maintain

Audience

Viewing Issue Simple Details [ Jump to Notes ]

[ View Advanced ] [ Print ]

Category

Severity

Reproducibility

Date Submitted

Last Update

0015194

[phplist] All Other

block

always

02-12-08 11:00

23-03-09 16:23

Reporter

user4365

View Status

public

Assigned To

Priority

normal

Resolution

fixed

Status

resolved

Product Version

2.10.5

Summary

0015194: usertracking produces high mysql serverload, causes cpu hog and kills server

Description

when enabling [USERTRACK] in outgoing newslettermails, the message opens by users kills the server.

Additional Information

huge newsletterlists, 40.000 receivers+
huge outbound: over 10.000 emails per hour...

Bug caused by:

mclicks.php:

  $req = Sql_Query(sprintf('select distinct messageid, subject, sum(clicked) as totalclicks, count(distinct userid) as users, count(distinct linkid) as linkcount from %s as linktrack, %s as message
    where clicked and linktrack.messageid = message.id %s group by messageid order by entered desc limit 50',
    $GLOBALS['tables']['linktrack'],$GLOBALS['tables']['message'],$subselect));

and

ut.php:

    Sql_Query(sprintf('update %s set viewed = now() where messageid = %d and userid = %d',
      $GLOBALS["tables"]["usermessage"],$_GET["m"],$userid[0]));
    Sql_Query(sprintf('update %s set viewed = viewed + 1 where id = %d',
      $GLOBALS["tables"]["message"],$_GET["m"]));

Server: mysql 5.0.x

Relationships