MBM and mlm script

Chris · Joined: 15 Mar 2007 Posts: 8

Hi
I just managed to dynamically update my mlm mysql db with my payment processor (e-junkie). But using remote lists with MBM is very very slow. I have about 10'000 entries and so, it seems that this can't be handled smoothly in MBM. From the server log I also can see the following:

stanbusk · Site Admin Joined: 28 Dec 2005 Posts: 2375

Actually I did not design MLM with lists that size in mind. To load big list we would have to implement compression I think and perhaps get the list as a download. Did you try the getlist command with the option to get the list as a download?

Chris · Joined: 15 Mar 2007 Posts: 8

I tried setting the exec_timout to 240s but it still does not work.
I think a better idea would be to have two tables in mysql:
1. a list table so it first fetches the lists only
2. a client_list table to load the clients afterward from the selected list(s).

That way you don't have to cycle through all fields, only the needed ones.

The mlm integration was one reason I upgraded to MBM6, so I hope this can be sorted out.

Downloading the list would not be a great thing, as I want to automate the whole process. I can download a list directly from e-junkie, so this would not be an alternative (even this one works perfectly and in just a second).

The question is: why does the lists command take so much time and getlist is finished in a few seconds?

Chris · Joined: 15 Mar 2007 Posts: 8

I now made a test and set the $count variable to 2 (instead of mysql_num_rows) to get the first two lists.
It needed about 1.5min to get 3400 subscribers. This means I have to wait about 360s or 6min for all lists.

But even I set the timeout to 600s, I get an internal server error after about 5min. So, there is no way around it.

Is there any way to speed it up (e.g. drop some information like table last update or total subscribers? Will this affect MBM if I just set a dummy value to these variables (eg. $totalsubscribers = 0)?

Chris · Joined: 15 Mar 2007 Posts: 8

OK, I have found the critical thing is getting the Table last update time. When I drop this (or just setting a dummy date string), the information is fetched withing seconds! Right now, It works perfectly, but it would be great to see when the information was updated. At the moment I can live with this restriction, but maybe you will find some time to further look in this.
As a side note: if an update string will be fetched, it would be a good thing to not have 1 time for all lists but to show the individual last list update times.

stanbusk · Site Admin Joined: 28 Dec 2005 Posts: 2375

Not sure I understand your last post...

Chris · Joined: 15 Mar 2007 Posts: 8

The problematic (slow down) code is the following, so I simply removed it. The code is found in the ShowListNamesFromDB() function:

stanbusk · Site Admin Joined: 28 Dec 2005 Posts: 2375

Ok, it is clear. It looks like a mySQL bug, don't you think? That code has no loop and doesn't iterate afaik thru the table records...

Chris · Joined: 15 Mar 2007 Posts: 8

I think you are probably right that the problem is mySQL. But the code actually DOES iterate over the table rows. I tried to move it out of the loop but this hasn't helped. So, it does seem to be a "bug" in mySQL or maybe with the version my ISP is using. Maybe the only solution for me would be to add a timestamp field to the table whenever a record is created. That way I also will get back the exact time when the list has changed (and not an overall time when the last change to the table has been made).

Thanks for your help so far
Chris