Optimize Your CPVLab Database!

I’d like to apologize up-front for the sheer lack of attention to this blog as of late. I’ve been lazy, but mostly busy, and in transit quite a lot this year. I’ve finally returned to my home soil to get a bunch of work done, relax, and plan my return to the Canadian Rockies for some more adventure (and other traveling of course!). In the year that has just passed I’ve spent most my time living in Calgary, Alberta, Canada and have had the pleasure to visit Bangkok, Phuket, Tokyo, Osaka, Vancouver, Montreal, Las Vegas,  The Grand Canyon, Miami, Anaheim, Hollywood, San Diego, San Francisco, Memphis, Nashville, New Orleans, Colorado, New York & The Bahamas and do a bunch of crazy-amazing stuff during that time (Including ASW and ASE!)

What I’m trying to get at is.. well.. I’ve been BUSY!

Enough of my personal life and onto the meaty stuff, eh?!

Recently I’ve had a lot of performance problems with my CPVLab installation. I’ve had my server upgraded, both hardware and software, but just wasn’t happy with the results. On top of that, I knew that my database was getting WAY too bloated for it’s own good, but I was afraid to cull my stats without a backup!

Unfortunately that led me to have problems logging in today and so I decided to fix the problem myself and would like to share how I did it so that others can enjoy a more reliable and speedy CPVLab! Anyone that runs a decent amount of PPV traffic (or any traffic really) will know that the data builds up relatively fast and can bloat your DB into performance-hell.

Here’s some simple steps on how to fix it!

Step 1 – Backup Your Data

  • Log in to your phpMyAdmin console (usually via cPanel on your server)
  • Find your CPVLab database (usually named _cpvlab) in the left-hand panel. Click on this.
  • Click on Operations
  • Under the panel listed as “Copy Database to:”, you’ll see a text-field. Enter an appropriate name in here. I chose, _cpvlab-bkp-dd-mm-yy
  • Hit Go

Step 2 – Clean Your Clicks Data

  • Log into your CPVLab and navigate to Settings >> Stats Management
  • From here, either select the campaign you wish to cull clicks from, or select “All Campaigns”
  • Select the date range from what you wish to cull (I do anything over 3 months old, and 1 month for campaigns with lots of data).
  • Click Save

Step 3 – Advanced Optimization

  • Within phpMyAdmin, go to your _cpvlab database once again as mentioned in Step 1.
  • In the right-hand panel, you’ll see a list of tables (affiliatesources, alerts etc..). Find the table names clicks and well.. click on it.
  • Go to Operations
  • Down the bottom left you’ll see “Table Management”
  • Click Defragment
  • Don’t click anything else. I tried to use the Optimize function and it messed with my DB.

 

This should help speed up those cluttered DB’s and hopefully improve your ROI!

 

– Andrew


If you’re looking for further Affiliate Marketing Guidance, check out StackThatMoney. Best community of experienced marketers from around the globe, exclusive meetups, follow along’s, tutorials and the knowledge of a thousand sun-gods.

-=-=-=-

Can’t Decide on Tracking Software?

I’ve recently switched over to a new tracking platform called Thrive by the guys over at iPyxel which I love. It’s still in development, but is constantly improving and making strides, and the best part about it can be self-hosted. The offer a 30-day trial and it’s $99 a month thereafter which is well worth the investment.

Those on a smaller budget can still opt to go the CPVLab route, another favorite of mine but a little more outdated. It is, however, more suitable for PPV traffic if that’s your traffic of choice.


Read More