Home > Other Systems (CPVLab, iMobiTrax, P202, Track Revenue, Click, Google Analytics, etc.) > Normal Prosper202

I ran these 2 SQL commands and now Prosper202 looks more responsive (7)

08-14-2014 05:17 PM #1 dario (Member)
I ran these 2 SQL commands and now Prosper202 looks more responsive

I found this thread on the P202 forum

We had crunched through 3 different servers, all with MySQL 5.1, PHP5.3, memcached, etc... the last was a Dual Xeon Quad Core with 4gb ram.

If you are noticing that your MySQL CPU usage is abnormally high, and memory is still very low, you might be looking at an issue with the way the database is set up initially, not a hardware problem.

The 202_site_urls table has an index on the site_url_address with a Prefix set only to 75. This table can rapidly grow to hundreds of thousands if not millions of records. Unfortunately, if a lot of your urls are identical for the first 75 characters, then you are going to run into serious issues.

The solution is quite simple, just drop the Index and Create a new one with a higher prefix. We set ours to 200 and literally, instantanouesly, the mySQL function dropped from 235% CPU to 2%. Amazing.

Just run the two queries below.

ALTER TABLE 202_site_urls DROP INDEX site_url_address;

CREATE INDEX url_index USING BTREE ON 202_site_urls (`site_url_address`(200));
And to be honest it worked!
My "Group Overview" reports are now generated more quickly. Is it just a coincidence or that is a good trick?
I'm using the STM version of Prosper202

08-14-2014 05:42 PM #2 bbrock32 (Administrator)

It is a good trick indeed.

The site urls table contains referrers and in most prospers it's the biggest table.

By changing the index it speeds up things by a lot.

08-14-2014 06:26 PM #3 panicore (Member)

I also run a cronjob which cleans the site_url and IP database everyday, and optimises all tables.

<?php
$db_host = "localhost";
$db_user = "USERNAME";
$db_pass = "PASSWORD";
$db_name = "DATABASE";
$connect = @mysql_connect($db_host,$db_user,$db_pass);
@mysql_select_db($db_name);

// Empty table
$query = "TRUNCATE TABLE 202_site_urls";
$query2 = "TRUNCATE TABLE 202_ips";
mysql_query($query);
mysql_query($query2);


// optimise tables
function optimize_database($connect) {
$result = mysql_query('SHOW TABLES', $connect) or die('Cannot get tables');
while($table = mysql_fetch_row($result)) {
mysql_query('OPTIMIZE TABLE '.$table[0], $connect) or die('Cannot optimize '.$table[0]);
}
}

?>

08-15-2014 11:01 AM #4 bbrock32 (Administrator)

Keep in mind that optimize tables locks the tables so during excecution clicks wont get through

08-15-2014 02:34 PM #5 swissfactor (Member)

MySQL 5.6 is even faster. We were having 5.5 and 5.6 is a lot faster. I can imagine that mysql 5.1 is even slower.

08-15-2014 07:48 PM #6 panicore (Member)

Quote Originally Posted by bbrock32 View Post
Keep in mind that optimize tables locks the tables so during excecution clicks wont get through
optimize tables completes in 10 seconds on my VPS so this won't be a problem. Thanks for the heads up anyway, i didn't know that.

08-15-2014 09:19 PM #7 caurmen (Administrator)

As I always say, it's also worth testing MariaDB against vanilla MySQL. I've found it a lot, lot, lot more powerful.

Home > Other Systems (CPVLab, iMobiTrax, P202, Track Revenue, Click, Google Analytics, etc.) > Normal Prosper202