Fix for some Prosper202 Speed issues

Status
Not open for further replies.
I think I can handle both of these questions...

If you are running a prosper database on anything less than mysql 5.1 and want to upgrade, here is one way to do it and get the benefits of partitioned tables.

1. Export your entire Prosper202 database using something like phpmyadmin and create a gziped sql file to download to your local machine as a backup.
2. Upgrade to mysql 5.1 via whatever instructions are appropriate for your installation. For most of you upgrading, this will include some scripts that should be run to tighten security that come with the install so make sure to read your howtos.
3. Connect to prosper via web interface and make sure all is up and ok.
4. Then go into phpmyadmin, connect to your database and running the following sql to get the basic partitioned tables.

PARTITION BY RANGE (click_time) (
PARTITION p0 VALUES LESS THAN (1194159600),
PARTITION p1 VALUES LESS THAN (1195369200),
PARTITION p2 VALUES LESS THAN (1196578800),
PARTITION p3 VALUES LESS THAN (1197788400),
PARTITION p4 VALUES LESS THAN (1198998000),
PARTITION p5 VALUES LESS THAN (1200207600),
PARTITION p6 VALUES LESS THAN (1201417200),
PARTITION p7 VALUES LESS THAN (1202626800),
PARTITION p8 VALUES LESS THAN (1203836400),
PARTITION p9 VALUES LESS THAN (1205046000),
PARTITION p10 VALUES LESS THAN (1206255600),
PARTITION p11 VALUES LESS THAN (1207465200),
PARTITION p12 VALUES LESS THAN (1208674800),
PARTITION p13 VALUES LESS THAN (1209884400),
PARTITION p14 VALUES LESS THAN (1211094000),
PARTITION p15 VALUES LESS THAN (1212303600),
PARTITION p16 VALUES LESS THAN (1213513200),
PARTITION p17 VALUES LESS THAN (1214722800),
PARTITION p18 VALUES LESS THAN (1215932400),
PARTITION p19 VALUES LESS THAN (1217142000),
PARTITION p20 VALUES LESS THAN (1218351600),
PARTITION p21 VALUES LESS THAN (1219561200),
PARTITION p22 VALUES LESS THAN (1220770800),
PARTITION p23 VALUES LESS THAN (1221980400),
PARTITION p24 VALUES LESS THAN (1223190000),
PARTITION p25 VALUES LESS THAN (1224399600),
PARTITION p26 VALUES LESS THAN (1225609200),
PARTITION p27 VALUES LESS THAN (1226818800),
PARTITION p28 VALUES LESS THAN (1228028400),
PARTITION p29 VALUES LESS THAN (1229238000),
PARTITION p30 VALUES LESS THAN (1230447600),
PARTITION p31 VALUES LESS THAN (1231657200),
PARTITION p32 VALUES LESS THAN MAXVALUE);

That should give you the default partitions setup by the Prosper202 team. After that I recommend you run the code I posted up top to give you a larger partition set that should help with speed issues through close to the end of 2009.

Let me know if you need help or have any issues.

Peace!
 


hey testonej,

looks like it 'almost' worked for me. I had to add an


ALTER TABLE 202_clicks

above your red code too. After that, everything was cool :)
 
ricdes said:
hey testonej,

looks like it 'almost' worked for me. I had to add an


ALTER TABLE 202_clicks

above your red code too. After that, everything was cool :)
Click to expand...

Very good call. I just cut and pasted the code from the P202 creation scripts and never tested it. Thanks for making that clear.

Outside of that, do your reports seem to be doing any better?
 
Awesome! Has anyone else tried this out, and if so, can the report an improvement? I posted this as a change I though the Prosper202 team should include in their next release.
 
Guys,

Wes is working on the next release of P202 and is open to receive suggestion on his blog. So if anyone has something to add just head to his blog and leave him a feedback.

Cheers
 
awesome thread, this code alteration should absolutely be sent to wes_p202 , v.1.3 suggestion list. i will be implemeting this on vps today, thanks alot.
 
Status
Not open for further replies.
Top Bottom