Do you want to dramatically improve PHPList perfs? READ THIS

Solutions for other advanced phplisters

Do you want to dramatically improve PHPList perfs? READ THIS

Postby vide » 11:17am, Thu 21 Dec, 2006

Ok, it sounds like a spam announce, I know :D

Today I've just found a serious performance bug in PHPList when sending email with URL tracking actived.
vide
phpLister
 
Posts: 14
Joined: 9:46am, Thu 21 Sep, 2006

Postby vide » 11:18am, Thu 21 Dec, 2006

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
vide
phpLister
 
Posts: 14
Joined: 9:46am, Thu 21 Sep, 2006

Any progress in this?

Postby jezdikm » 12:52pm, Fri 12 Jan, 2007

Hi,

is there any progress with this?

Could you post the mantis ID in here...

Thanks,
Milan
jezdikm
phpLister
 
Posts: 12
Joined: 3:31pm, Thu 16 Nov, 2006

Postby vide » 12:59pm, Fri 12 Jan, 2007

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
vide
phpLister
 
Posts: 14
Joined: 9:46am, Thu 21 Sep, 2006

Postby etv4dev » 3:41pm, Mon 12 Feb, 2007

i will test it today ;)

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

We will see..^^
etv4dev
phpLister
 
Posts: 5
Joined: 9:34am, Mon 04 Dec, 2006

Postby peterf » 4:00am, Tue 13 Feb, 2007

vide wrote: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
phpLister
 
Posts: 10
Joined: 6:45pm, Mon 22 Jan, 2007

Postby peterf » 4:27pm, Sat 17 Feb, 2007

vide wrote: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
phpLister
 
Posts: 10
Joined: 6:45pm, Mon 22 Jan, 2007

Postby peterf » 11:51pm, Mon 19 Feb, 2007

peterf wrote:
vide wrote: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.
peterf
phpLister
 
Posts: 10
Joined: 6:45pm, Mon 22 Jan, 2007

Postby vide » 5:11pm, Wed 14 Mar, 2007

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 :)
vide
phpLister
 
Posts: 14
Joined: 9:46am, Thu 21 Sep, 2006


Return to Advanced Answers, Howtos, Tips & Tricks

Who is online

Users browsing this forum: No registered users and 1 guest