P202 Blows Up Every 2-3 days... Need Decent Advise :( (17)
Hey guys,
Recently I'm running into a problem that my P202 database blows up every 2-3 days, like, the database can be as huge as 15GB in 3 days time. Well the problem is, with this HUGE data, it is slowing down my server, especially when I'm trying to pull a report to optimize my campaigns. The load can takes up to 10 minutes sometime... And if I forget to clear up my database in 3 days, it is totally impossible for me to pull the data - even on the "Overview" page - by then I'll have no choice but to clear up everything that I collected. :'(
I've talked to Tyler on this issue and he said it was the nature of the tracking system, that uses MYSQL database (something like that...).
I'm very headache now because I have no way to optimize my campaigns due to too huge data. I know there are guys who have gotten their tracking system custom built and is not based on MYSQL.
Can any of the expert in this field give me some advise as to how much would a custom built tracking system costs (that are not based on MYSQL like P202 and CPVlab), and if you can recommend me a decent guy in coding that?
Caurmen I would love to hear what you got to say on this too. Thanks a lot guys!
You could try Memcached and hire someone to optimise the queries on 202.
Yeah this is the problem with the way reporting is built in p202. I have a fix for this and it should be live on the hosted tracking202 platform pretty soon.
In my last test I was able to run a report on over 30 million clicks in about 3 seconds (Before caching).
It's not really a mysql problem, more of an architecture issue.
why not just switch to mobaff?
Originally Posted by polarbacon
Originally Posted by nitroman
Originally Posted by Mr Baffoe
Originally Posted by polarbacon
*Sorry never knew it has been made available to public - but I would love to own the tracker myself for a one-time-fee. Paying additional $0.005/click would burn out all my campaign money at my volume.
Aha, the Bat-Signal!
This is a fairly common problem with Prosper, although thanks to the volume you're doing you're hitting it much earlier than most people do. It's not actually an issue with MySQL, but as Mr Baffoe says (and obviously, he should know!) is an issue with the architecture of Prosper.
How many clicks are you getting over those three days? There ARE hard limitations on the size of a MySQL database, as well as situations where it'll slow down a lot, and they might become a problem for you down the road. However, right now it's almost certainly a Prosper issue.
There are a few things you could do to mitigate the problem before you need to move to a full custom tracking solution:
- First and foremost, I'd recommend setting up a cron job on your system to archive and clear out your data. What that does is set up a task for the system to automatically clean your database in the same way you've been doing it manually. You can set that to any frequency you like - sounds like every 2 days would be a decent compromise - and it'll prevent you ever having to remember to do it manually again. This is a simple task for any sysadmin.
- If you want a quicker way of getting your data out of Prosper's databases, you can query the database directly. I've been doing that myself recently - it's at least 10 times faster than fetching the data from Prosper in most cases. If you don't know SQL, you'll need to have a programmer set you up a bunch of simple queries through some web pages that you can access - but that's not a huge task. That should get you performance similar to what Mr Baffoe is quoting - 3 seconds for 30 million queries. However, obviously that will limit the reports you can access a bit, as you'll need custom SQL for each one.
- If you need more than 2 days' worth of data, you can set up another machine and import your MySQL data from your main machine when you archive it. Obviously, there's not much point running the current version of Prosper on there, because it'll just fall over, but as mentioned above, you can use MySQL to access the data directly or via some custom scripts.
- Obviously, it's also worth checking out other tracking solutions. I'm guessing you're not comfortable with tracking that's hosted elsewhere, but you might want to see if CPVLab has this problem. I've not used it yet, and don't actually know if it does or not!
If your databases are growing to that sort of size, I'd also recommend you do a check to make sure that they're not slowing down your landing pages and general funnel with slow INSERTs. You can use the MySQL slow queries log for this - Tyler should be able to sort that out. Don't panic about this - chances are they aren't - but it's worth making sure.
As far as writing a custom tracking solution goes - writing a basic tracker isn't very hard. If you trust your traffic source and you're not worried about filtering out fake clicks etc, it's a few days' work for a competent programmer. (I've written simple trackers at this level before). If you're willing to work with something fairly bare-bones, then a custom tracking solution is Very Doable. I believe Zeno on these forums has written a couple of tracking systems in the past - he may be able to chime in further on this point.
But the devil's in the details. If you want more sophisticated reporting or additional reports, that's a bit of extra work. If you want click filtering, that's additional work - and that's the point where my knowledge of the field starts to thin out a bit, as I don't know the inner workings of the click filtering on Prosper or its competitors. Overall, if you're looking for a replacement for Prosper, that's going to be quite a bit of work - I'd budget a month or two of programmer time, minimum. (Tom Fang's recently been working on a full-featured tracking system, so may have further insight here.).
A simpler approach may be to attempt to fix the problem inside Prosper. If Mr Baffoe will sell you his fix, that's obviously the simplest solution - otherwise, you can always hire a programmer and ask them to speed Prosper up. It's a big codebase, but not a huge one - it'll probably be quicker to patch Prosper than to recreate it.
Hope that helps!
Originally Posted by kokofai
may want to re-read things..... "*Additional clicks over included clicks will be assessed at .0005 for every tracked click."
just a wee bit different from you what you stated above
also I can tell you from personal experience with Alex ask him for a plan that will work for you...it wont be cheap but it will fix the issues you are having and afford you more options as well....
lastly your approach to things seems....umm well rather cheap...considering tracking is the core of your biz.....when you start to run volume theses are the issues you face....solutions cost money....buying a "once off" solution will not solve your problem long term.....
I find it funny that so many people are willing to skimp on infrastructure as there business grows....look at the amount of money you spend daily on traffic and look at the cost of your infrastructure.....are you taking a portion of your profits and investing them back in the biz to build out your infrastructure? ...my guess is no....
prosper is fine for a fair amount of what we do....but once you start run into the millions of clicks a day....problems like the above will rear its ugly head....and you need to either re-code it, find work arounds or move to a dif platform entirely....
personally myself I prefer SAS solutions....they make my life easy and I can focus on the things that matter....driving more traffic.....I know many of you don't share my views and are proud of the fact you run $xx,xxx in traffic a day on a $45 box running 202....I just find that foolish....
sorry if i sound like a dick.....but I just like to speak bluntly on the issue from my own personal experiences.....
Originally Posted by caurmen
Originally Posted by polarbacon
I have no idea where did you get the impression that I am trying to be "cheap" on my tracking solution. In case you missed my initial post, I would ask you to go back and read it again. I was asking for "Recommendations on custom built tracking system".
And by the way, I really don't find it comfortable having my tracking system hosted else where. Shit always happen in this business and if anything goes wrong/server downtime, who should be responsible for the traffic cost loss? However, if I am having it hosted on my end, and if shits happens, I myself is the one that to be blamed. To me, having your tracking solution hosted else where (especially at my volume) is just like building a SEO site, you're putting your money at third party instead of your own hand.
Just my 2 cent.
If you are running that many clicks a day then you dont need to pull reports that much because you should have enough data to optimize. Just clear the database with the cron job every 2-3 days.. i think that is the best advice! 
On a side note, here's some food for thought.
If the community contributed code back to the original codebase, like other open source communities do (wordpress etc) this issue would have been fixed a long time ago, and Prosper202 would be a million times better than anything on the market, all at no cost.
Right now everyone is paying for their own dev wrk, making updates for their own use and not giving back. So it ends up that everyone has to keep reinventing the wheel paying people to work one the same or similar fixes/mods over and over again.
Did you know that some of the cool features you enjoy in Prosper202 right now are because people like Mark, Jasper and more were generous enough to work on stuff and share with everyone?
http://ctrtard.com/affiliate-marketi...ction-buttons/
http://nerdyaffiliate.com/affiliate-...xt-adskeywords
The other option would be to start charging for Prosper202.
Start charging all you want, I dont mind paying for it but I want it to be self hosted!
Originally Posted by Mr Baffoe
Originally Posted by Mr Baffoe
Exactly, people keep paying devs to fix the same problems but then they keep that to themselves (reasonably so, I guess, since they paid for it.)
Perhaps a good idea would be to join together, make a list of things we ALL want done / fixed to prosper202, and then ALL chip in a little cash for a dev or devs to contribute to these problems and then we can all reap the rewards, like doing a report on 3 million clicks in 3 seconds ;-) Would anyone be down / interested for this?
Originally Posted by kokofai
Mr Baffoe - I'd be happy to contribute code changes and fixes as and when I make them.
I must admit, one of the reasons I didn't suggest it was I wasn't sure what license Prosper was under - is it OSS?
It'd be great to get it up and hosted on Github or something if it isn't already, and that might help people make code additions.
Home > Other Systems (CPVLab, iMobiTrax, P202, Track Revenue, Click, Google Analytics, etc.) > Normal Prosper202