Optimizing Prosper202



Spy is truncated nightly. There's 6 tables with "clicks" in the name, all have the same number of records.

Can I truncate 202_clicks without losing any usable data? In other words, will I still be able to search 202 and pull out conversions, etc for campaigns?
 
Mike said:
Spy is truncated nightly. There's 6 tables with "clicks" in the name, all have the same number of records.

Can I truncate 202_clicks without losing any usable data? In other words, will I still be able to search 202 and pull out conversions, etc for campaigns?
Click to expand...

Depends how far you wanna go back
 
mattseh said:
does p202 have sensible indexes on the data?
Click to expand...

No idea what this means. Sorry Mattseh, but as usual you're too smart for me.

How about this: I don't mind taking the data out of P202 and just keeping on a harddrive somewhere (or even just dropping it), but I do want to keep the last month or so of data accessible.

What tables can I partially truncate without breaking stuff? I'm having a hard time finding any information on this.
 
What I used to do when I ran prosper was keep a copy of LAMP sitting on my local computer with a copy of P202 installed. When my server side copy of P202 gets too big/slow, I export the database and upload it to my local server, then I use the P202 admin panel to "Delete all Click data." on the remote server.

Then when I want to look up old data I just fire up Apache and MySQL on my local machine and goto http://localhost/202-login.php
 
cheshire said:
What I used to do when I ran prosper was keep a copy of LAMP sitting on my local computer with a copy of P202 installed. When my server side copy of P202 gets too big/slow, I export the database and upload it to my local server, then I use the P202 admin panel to "Delete all Click data." on the remote server.

Then when I want to look up old data I just fire up Apache and MySQL on my local machine and goto http://localhost/202-login.php
Click to expand...

Great idea!

What tables can I get away with truncating without affecting the redirection links?
 
Mike said:
Great idea!

What tables can I get away with truncating without affecting the redirection links?
Click to expand...

In P202 click the Administration link at the top right corner. If you scroll down a bit there is a section that says "Database Management" click the radio button for "Delete All Click Data" and then Submit Query. That will wipe out all your click data but leave all your campaigns/ads/redirects in place. After all thats done go do an MySQL optimize on the database.

For god sakes though have that shit backed up before you do it because its gone forever once you click that button.
 
While you're there also check to see if you have memcache running and setup properly. That does wonders for redirect speeds and pulling info from the database.
 
Here's all the "BIG" tables I'm curious about:

202_clicks
202_clicks_advance
202_clicks_counter
202_clicks_record
202_clicks_site
202_clicks_tracking
202_ips
202_site_urls

Cheshire has the perfect solution to data loss (thank you). Now, I just need to know what I can get away with killing and not screw up my redirects (202 redirect links).
 
cheshire said:
In P202 click the Administration link at the top right corner. If you scroll down a bit there is a section that says "Database Management" click the radio button for "Delete All Click Data" and then Submit Query. That will wipe out all your click data but leave all your campaigns/ads/redirects in place. After all thats done go do an MySQL optimize on the database.

For god sakes though have that shit backed up before you do it because its gone forever once you click that button.
Click to expand...

Don't have that button. Using a modified 1.6 because something in the 1.7 upgrade I didn't trust. (don't remember what now)

cheshire said:
While you're there also check to see if you have memcache running and setup properly. That does wonders for redirect speeds and pulling info from the database.
Click to expand...

Yep, have memcache running and confirmed it's set properly. Redirect speeds are fast, it's just pulling data that's taking longer and longer.
 
Here's the code from Prosper202 1.7.2 that deletes the click data while retaining everything else. It shows the tables you can safely truncate:

if ($_POST['database_management'] == 'delete_all_click_data') {
$tables = is_array($tables) ? $tables : explode(',','202_clicks,202_clicks_advance,202_clicks_record,202_clicks_site,202_clicks_spy,202_clicks_tracking,202_ips,202_keywords,202_site_urls');
$click_sql = "SELECT COUNT(*) FROM 202_clicks";
$click_result = _mysql_query($click_sql);
$clicks = mysql_result($click_result,0,0);
$click_count_sql = "Update 202_clicks_total set click_count=click_count+".$clicks;
$result = _mysql_query($click_count_sql);
foreach($tables as $table)
{
$sql = "TRUNCATE ".$table;
$sql_optimize = "OPTIMIZE TABLE ".$table;
$result = _mysql_query($sql);
$result = _mysql_query($sql_optimize);
 
Resurrecting this thread...

What alternatives are there to P202 ?

If I wanted to keep 202, what can be done to permanently speed it up?

Does data just need to be dumped? If so, how can I preserve it for later?

Problem is the DB is over 10gig now, and exporting through WHM isn't possible.

I love the instant feedback that P202 provides, but the constant headache is getting to be too much.

/edit/ is there a way to export a database of this size? That way if I have to move it to another machine I still have the data
 
You can do mysql dump from shell with mysqldump command, the restore it with
mysql -u [user] -p [database] < dump.sql
 
1. switch to ssd disks
2. replace mysql with mariadb
3. switch to tokudb tables
4. make db replication
 
You must log in or register to reply here.
Top Bottom