Fix for some Prosper202 Speed issues

Status
Not open for further replies.
T

testonej

New member
Nov 12, 2006
138
8
0
Austin, Tx.
Hey guys, wanted to let you know about an issue I found with Prosper202 that has the potential to slow your reporting down if you are logging more than a couple thousand clicks a day. This fix can only help people running Prosper202 under Mysql 5.1+.

Here is the summary…

When they built the install code for the 202_CLICKS table they wrote in some code to setup data partitions within the table to segregate records based on timestamps into 2 week groups. (See prosperinstall/202-config/functions.php starting on line 275). This helps to speed up the SQL parser when running reports by day. The problem is they only broke out partitions through 1/11/2009 and everything recorded after that date goes into a catch all partition (p32). To fix this, I wrote some SQL code that will modify the table and break out the p32 (catch all) partition into a bunch more partitions. I choose to group the partitions into 5 day increments after Feb. 16th (the day I wrote the fix). This code should get you through Dec. 17th, 2009. After that you can just add more partitions if need be. MySql 5.1+ supports up to 1024 partitions on a table.

If you answer YES to all of the following, this should help speed things up for you.

1.I am running prosper202 with mysql 5.1+

2.When I run the sql query “[FONT=&quot]EXPLAIN[/FONT][FONT=&quot] PARTITIONS[/FONT][FONT=&quot] SELECT[/FONT][FONT=&quot] *[/FONT][FONT=&quot] FROM[/FONT][FONT=&quot] 202_CLICKS[/FONT]“ on my prosper database I see p0,p1,p2, etc. in the partition list (if you see NULL you have no partitions).

If both those apply do the following:

1.Backup your database.

2.Connect to your propser202 database using phpmyadmin or whatever your normal method is to issue sql commands.

3.Run the following command:

ALTER TABLE 202_clicks
REORGANIZE PARTITION p32 INTO (
PARTITION p32 VALUES LESS THAN (1232089200),
PARTITION p33 VALUES LESS THAN (1232521200),
PARTITION p34 VALUES LESS THAN (1232953200),
PARTITION p35 VALUES LESS THAN (1233385200),
PARTITION p36 VALUES LESS THAN (1233817200),
PARTITION p37 VALUES LESS THAN (1234249200),
PARTITION p38 VALUES LESS THAN (1234681200),
PARTITION p39 VALUES LESS THAN (1235113200),
PARTITION p40 VALUES LESS THAN (1235545200),
PARTITION p41 VALUES LESS THAN (1235977200),
PARTITION p42 VALUES LESS THAN (1236409200),
PARTITION p43 VALUES LESS THAN (1236841200),
PARTITION p44 VALUES LESS THAN (1237273200),
PARTITION p45 VALUES LESS THAN (1237705200),
PARTITION p46 VALUES LESS THAN (1238137200),
PARTITION p47 VALUES LESS THAN (1238569200),
PARTITION p48 VALUES LESS THAN (1239001200),
PARTITION p49 VALUES LESS THAN (1239433200),
PARTITION p50 VALUES LESS THAN (1239865200),
PARTITION p51 VALUES LESS THAN (1240297200),
PARTITION p52 VALUES LESS THAN (1240729200),
PARTITION p53 VALUES LESS THAN (1241161200),
PARTITION p54 VALUES LESS THAN (1241593200),
PARTITION p55 VALUES LESS THAN (1242025200),
PARTITION p56 VALUES LESS THAN (1242457200),
PARTITION p57 VALUES LESS THAN (1242889200),
PARTITION p58 VALUES LESS THAN (1243321200),
PARTITION p59 VALUES LESS THAN (1243753200),
PARTITION p60 VALUES LESS THAN (1244185200),
PARTITION p61 VALUES LESS THAN (1244617200),
PARTITION p62 VALUES LESS THAN (1245049200),
PARTITION p63 VALUES LESS THAN (1245481200),
PARTITION p64 VALUES LESS THAN (1245913200),
PARTITION p65 VALUES LESS THAN (1246345200),
PARTITION p66 VALUES LESS THAN (1246777200),
PARTITION p67 VALUES LESS THAN (1247209200),
PARTITION p68 VALUES LESS THAN (1247641200),
PARTITION p69 VALUES LESS THAN (1248073200),
PARTITION p70 VALUES LESS THAN (1248505200),
PARTITION p71 VALUES LESS THAN (1248937200),
PARTITION p72 VALUES LESS THAN (1249369200),
PARTITION p73 VALUES LESS THAN (1249801200),
PARTITION p74 VALUES LESS THAN (1250233200),
PARTITION p75 VALUES LESS THAN (1250665200),
PARTITION p76 VALUES LESS THAN (1251097200),
PARTITION p77 VALUES LESS THAN (1251529200),
PARTITION p78 VALUES LESS THAN (1251961200),
PARTITION p79 VALUES LESS THAN (1252393200),
PARTITION p80 VALUES LESS THAN (1252825200),
PARTITION p81 VALUES LESS THAN (1253257200),
PARTITION p82 VALUES LESS THAN (1253689200),
PARTITION p83 VALUES LESS THAN (1254121200),
PARTITION p84 VALUES LESS THAN (1254553200),
PARTITION p85 VALUES LESS THAN (1254985200),
PARTITION p86 VALUES LESS THAN (1255417200),
PARTITION p87 VALUES LESS THAN (1255849200),
PARTITION p88 VALUES LESS THAN (1256281200),
PARTITION p89 VALUES LESS THAN (1256713200),
PARTITION p90 VALUES LESS THAN (1257145200),
PARTITION p91 VALUES LESS THAN (1257577200),
PARTITION p92 VALUES LESS THAN (1258009200),
PARTITION p93 VALUES LESS THAN (1258441200),
PARTITION p94 VALUES LESS THAN (1258873200),
PARTITION p95 VALUES LESS THAN (1259305200),
PARTITION p96 VALUES LESS THAN (1259737200),
PARTITION p97 VALUES LESS THAN (1260169200),
PARTITION p98 VALUES LESS THAN (1260601200),
PARTITION p99 VALUES LESS THAN (1261033200),
PARTITION p500 VALUES LESS THAN MAXVALUE
);


Please, please, please don’t do anything if you don’t understand what I am talking about. This has helped speed up my daily reporting by a noticeable amount. Post here if you have any questions or need help so I can answer to help everyone. Feel free to repost or whatever.

DISCLAIMER – I am no DBA and am not liable… blah blah blah…..

Good Luck!

PS - I love prosper202 and am interested in doing more to keep this free version alive and working well. If anyone is interested in starting a place to modify, add on to, and support this version, PM and lets talk about it.
 
  • Like
Reactions: chatmasta, aeiouy, ImagesAndWords and 2 others


Another idea for a speed up would be to remove indexing on the generated tables sort_whatever unless you plan on sorting them by different columns after it's loaded. Otherwise you're just wasting a lot of time building indexes every time you analyze anything. Kind of a stupid design IMO.
 
d03boy said:
Another idea for a speed up would be to remove indexing on the generated tables sort_whatever unless you plan on sorting them by different columns after it's loaded. Otherwise you're just wasting a lot of time building indexes every time you analyze anything. Kind of a stupid design IMO.
Click to expand...

That's an interesting idea. I have not spent much time looking into what goes on with those indexes but just might this weekend. Thanks for the heads up!
 
d03boy said:
The code is a huge mess so I think I'm just going to write my own tracker :P
Click to expand...

You know, I actually did write my own because I love programming but I started spending so much time perfecting things that I lost sight of the big picture.... Making $$$. So I decided to try Prosper and have never looked back. I do miss coding from time to time but just go look at my bank account and know that it was worth it.

:-)
 
Coding does get in the way of looking for money sometimes. :) But you have to prioritize some time for fun stuff as well.
 
testonej said:
You know, I actually did write my own because I love programming but I started spending so much time perfecting things that I lost sight of the big picture.... Making $$$. So I decided to try Prosper and have never looked back. I do miss coding from time to time but just go look at my bank account and know that it was worth it.

:-)
Click to expand...

Funny, same thing happened here. Except I only got about 3/4 way to a finished tracking system when I decided p202 was better and big money was even better than coding.
 
nice fucking job OP. i hope at some point prosper202 goes all out open source developing (git /svn versioning, more trusted people who check in patches, plugins / extensions would be AWESOME, etc).

It would be very rewarding (in the non monetary sense) to work on it.
 
Just for the record... I am no Prosper expert but would be happy to give anyone my 2 cents on speed, installation or performance issues. Just hit me up here or PM. And if there is a trust issue, I can refer you to some big guys that I help out daily or weekly just for the fun of problem solving.

Hope I can help some of you out.
 
Just to add some more to this....

You can also go through the files and change all of the following code.

Code: 
$_SERVER['DOCUMENT_ROOT']
To

Code: 
str_replace ( '/DIR/FILENAME.php', '',__FILE__)
While changing /DIR/ to the directory name the file is under and changing FILENAME.php to the name of the file your editing.

Just be sure to double check that since I'm going off my head with it and not checking my own source code......and to test after making the changes.

After you do that it will magically work in any sub-directories you may wish to use it in.

Not sure why they didn't do that to start with though, its a simple fix.

I had to do it when I was interested in testing it out because my hosting account put all my directories in a sub-folders by default where the software didn't like that much.
 
hmmm anyone has an idea on how to get all my tables partitioned? I just upgraded to 5.1 and there doesnt seem to be any partitioning before 5.1..
 
ricdes said:
hmmm anyone has an idea on how to get all my tables partitioned? I just upgraded to 5.1 and there doesnt seem to be any partitioning before 5.1..
Click to expand...


I can give you the default partition code for 5.1 and then you can run my script or we can customize your partitions more for your needs. If you want to share a little info like how far back your data goes, avg. clicks per day, etc. I can write you something up.
 
hey testonej, just hand me out the default partition code. I dont really check stats older than 1 month often.. thx man
 
ricdes said:
hmmm anyone has an idea on how to get all my tables partitioned? I just upgraded to 5.1 and there doesnt seem to be any partitioning before 5.1..
Click to expand...

Any tips on upgrading to 5.1? I found a couple tutorials online but most of them are outdated or for a different distro than CentOS.
 
Status
Not open for further replies.
Top Bottom