Do you want to dramatically improve PHPList perfs? READ THIS

[vide]

Ok, it sounds like a spam announce, I know

Today I've just found a serious performance bug in PHPList when sending email with URL tracking actived.

[vide]

The problem in sendmaillib.php when PHPlist retrieve the last inserted linkid to create the coded URL to send in the mail.

There is a query like this:

Code: Select all

$req = Sql_Fetch_Row_Query(sprintf('select linkid from %s where messageid = %s and userid = %d and forward = "%s"
        ',$GLOBALS['tables']['linktrack'],$messageid,$userdata['id'],$link));


now, note the select on the "forward" field. If you look at the mysql "linktrack" table you will notice that "url","linkid","messageid" and "userid" are all part of an index, whilst the "forward" field is not.
So, what happens here? Very simple. Every of these SELECTs (one for every message sent, so it's a LOT) do not use the index!!! slowing down everything!
and the more absurd fact is that "url" and "forward" fields do contain the SAME EXACT INFORMATION!!!

In my installation I passed from a useless 1000msg/hour to ~30k/hour !!! just verified now! only changing two words in this query!

al you have to do is change all the queries like the above you find with this one:

Code: Select all

 $req = Sql_Fetch_Row_Query(sprintf('select linkid from %s  where messageid = %s and userid = %d and url = "%s"
        ',$GLOBALS['tables']['linktrack'],$messageid,$userdata['id'],$url));


I will open a bug to correct this in the mainstream branch

[jezdikm]

Hi,

is there any progress with this?

Could you post the mantis ID in here...

Thanks,
Milan

[vide]

Yes I opened a tiket (before last point release) but no comment so far..
http://mantis.phplist.com/view.php?id=8832

I would suggest EVERYONE to apply this change cause it will never generate problems, only benefits.
Or, if you prefer, do not change the code and change the index key in the linktrack table

[etv4dev]

i will test it today

habe to send a newsletter (like every week) to more than 30.000 User...

We will see..^^

[peterf]

Yes I opened a tiket (before last point release) but no comment so far..
http://mantis.phplist.com/view.php?id=8832

I would suggest EVERYONE to apply this change cause it will never generate problems, only benefits.
Or, if you prefer, do not change the code and change the index key in the linktrack table

The 'forward' field is a text field, and apparently not indexable (at least phpmyadmin doesn't give the option)

I find the pattern

grep -r -n 'select linkid from %s where messageid = %s and userid = %d and forward = "%s"' .

in the following locations in a default installation of the current phpList:

./admin/sendemaillib.php:401:
./admin/sendemaillib.php:434:
./admin/sendemaillib.php:471:


Easy enough to change these by hand... so that

s/forward/url/

ie : select linkid from %s where messageid = %s and userid = %d and url = "%s"

Not really sure why there is the 'forward' field anyway, since as you say, it is redundant with the 'url' field!

Thanks for the tip!

PF

[peterf]

Yes I opened a tiket (before last point release) but no comment so far..
http://mantis.phplist.com/view.php?id=8832

I would suggest EVERYONE to apply this change cause it will never generate problems, only benefits.
Or, if you prefer, do not change the code and change the index key in the linktrack table

Hmm.
After applying this hack, the links in message footers are bringing up a phpList "404" (not from apache)...

All the links in the message body are fine (clickthrough links, that is)

Any ideas on why the footer links are now breaking???

thanks!
PF

[peterf]

Yes I opened a tiket (before last point release) but no comment so far..
http://mantis.phplist.com/view.php?id=8832

I would suggest EVERYONE to apply this change cause it will never generate problems, only benefits.
Or, if you prefer, do not change the code and change the index key in the linktrack table

Hmm.
After applying this hack, the links in message footers are bringing up a phpList "404" (not from apache)...

All the links in the message body are fine (clickthrough links, that is)

Any ideas on why the footer links are now breaking???

thanks!
PF

Ah hem.

The forward field is exactly the same as the url field ONLY for 'content' links.
For personalized links, the forward field includes a unique ID.
Therefore using this hack will only work without personalization...

Additionally, AFAICS, attempting to index the forward field (which is currently text) will not help, since with these unique ids, the index will grow disproportionately, if I'm not mistaken (I'm certainly not a mysql guru)

SO, THIS does create problems, sorry to report.
However the underlaying issue remains: very slow sending of large lists.

[vide]

Thanks for the report, I'm not using any unsubscribe link in the footer (we have our methods to subscribe/unsubscribe) so I was never affected by this.
As you say, "forward" is not indexable because is a text field and, at least with InnoDB, you cannot index text fields.

So this should see some more radical change in the way PHPList works, to get it full functional. Anyway, if you're not using footers in your phplist deployment, I suggest you apply this hack