reducing CPU usage of an existing PHP script

[marcnyc]

Hello, I run a non-profit music website. Recently my host has complained that the CPU usage of my domain was too intensive. I first thought it was a problem with the indian host and so I switched to an american host that assured them they could handle my site, but after a few months they started suspending my site for excessive CPU usage.
I have done some troubleshooting (ie disabling several scripts on my site to find the CPU hog) and it seems that the problem child is a custom written script that the site relies very heavily on and which allows visitors to read music reviews that are posted by contributors. The script was written over 5 years ago by a then self-taught novice PHP coder and was hardly updated so I am sure it is not written in the most optimized or state of the art way.
I am horrified by the idea of having to re-code that entire section of the site and even if I had to I am just as self-taught and novice as that other coder was then.
So my question is: is it possible to salvage this script and make it less CPU intensive without having to completely rewrite it?
I read up the manual section about persistent connections and I was wondering: if I changed every mysql_connect into a mysql_pconnect across the entire script, would that reduce the load on the CPU because the script wouldn't have to re-open a database connection every time a user visits the site? (I should mention that it is a pretty heavily trafficked site, about 20k-30k visitors a month)
Any advice would be appreciated.
Thanks a lot

[itaym02]

Is it PHP4 or PHP5?
Optimization can be done in several levels. You can cache the output where it makes sense. You can use a CDN to load static data.
May be the hog is in the code itself. i.e. some crazy recursion where you pass objects by val and not by ref, etc.
Read/google yslow for general suggestion on how to improve performance, but do understand that real help can not be given here, as the code itself has to inspected.

[NogDog]

I would stay away from mysql_pconnect(). Unless both Apache and the database are configured correctly to handle it, at best you won't gain anything and at worst you'll actually make things worse.

Outside of just plain bad coding somewhere, one of the most likely suspects is the number of database queries being processed and how efficient those queries are. I often see novice programmers doing some sort of PHP loop that does a separate query in each loop iteration, when they could have done one query instead with a proper use of table joins and such. Along with this, a poor, non-normalized database approach and lack of column indexes where appropriate can make for inefficient database usage.

But this is all speculation at this point. Ultimately someone will have to review the code and the data design, and go from there. If you are lucky, maybe you can find someone who shares an interest in the subject matter and will do it on his/her own time. Or you may have to bite the bullet and look for someone who will do it at a pay rate that you find acceptable.

[marcnyc]

I am afraid that hiring somebody would be cost-prohibitive considering the amount of code and recording that would be necessary. It is a non-for-profit site so everything would come out of my own pockets. How much do you guys think such a job would run at?

By the way, to answer the questions, it is PHP5... I have implemented some caching myself in a rudimentary way which I copied from an online tutorial... I am not familiar with a CDN, but since you mentioned static data, most of the content is actually dynamic

[Kudose]

Even if the content is generated dynamically, if you have a lot of static images, javascript, css or anything else that doesn't change, you could have a second web server serve it from a sub-domain to reduce load and increase page load times.

[marcnyc]

That is true, I should look into that but I doubt that it would solve my problem, don't you think?

[Kudose]

It all depends on what your site does.

I would first start by adding timing points and printing them out on slow pages and narrowing down the issue. Although, before I spent more time than having the script tell me the execution time, I would look at the mysql processes and status to see if there are slow queries, or bad joins, table sorts or unused indexes and look through the script in question finding these queries and refactoring the code.

[Kudose]

In addition, if it is just one page with a problem, it is most likely a query/loop issue as suggested.

[marcnyc]

seems like it's the entire site... I mean, I don't see the slowness but the host complains about CPU usage... how/where can I monitor which queries and processes are the slowest?
thanks

[Kudose]

Using phpMyAdmin is the easiest. Login and click the Processes or Status tab.

[itaym02]

marcnyc;10926387 wrote:

I am afraid that hiring somebody would be cost-prohibitive considering the amount of code and recording that would be necessary. It is a non-for-profit site so everything would come out of my own pockets. How much do you guys think such a job would run at?

By the way, to answer the questions, it is PHP5... I have implemented some caching myself in a rudimentary way which I copied from an online tutorial... I am not familiar with a CDN, but since you mentioned static data, most of the content is actually dynamic

A dynamic data which does not change for 5 minutes, is considered static from caching point of view...cache it for 5 minutes.

aLSO go over YSlow propositions

[marcnyc]

All it says in the PHPMyAdmin Processes is:
ID User Host Database Command Time Status SQL query
Kill 7701 chaindlk localhost None Query 0 --- SHOW PROCESSLIST

I don't see a Status tab

As far as the 5 minutes, that's actually what I have my cache set to right now

[itaym02]

We really need to see your code and queries.

[marcnyc]

tell me how I can show you and I will

[itaym02]

I participate in forums to learn and for the benefit of the community, so I would say: put what you can here, in the forum. Please do small snippets each time.
If you wish me, or any one else for that matter, to privately analyse your code, you will have to hire him.

[marcnyc]

Here's the script I am using. Again this was written over 5 years ago by a then novice so I am sure it is not written in the best and most optimized way.

If necessary I can also post the database structure or maybe that plus some db data if any of you really would want to test it.

[bretticus]

By all means, setup some profiling. It may give you clues to where the bottlenecks are.

Profiling with xdebug

[Weedpacket]

But just with a quick glance (and a run through the virus scanner 🙂) I can say

		if ( $type == "books" ) {
				$query = "SELECT id, category FROM reviews2_category WHERE category LIKE 'book_%' ORDER BY id ASC";
			} else if ( $type == "magazines" ) {
				$query = "SELECT id, category FROM reviews2_category WHERE category LIKE 'magazine_%' ORDER BY id ASC";
			} else if ( $type == "concerts" ) {
				$query = "SELECT id, country  FROM reviews2_countries ORDER BY id ASC";
			} else {
				$query = "SELECT id, category FROM reviews2_category WHERE category NOT LIKE 'book_%'
										       && category NOT LIKE 'magazine_%'
											   ORDER BY id ASC";

Use two columns to store the category, instead of one and a load of string processing.

and

				// ------------------ SQL Query ----------------------------------------
					$query = "SELECT id FROM reviews2_countries";
				// ---------------------------------------------------------------------
				if ( !($result = mysql_query($query, $this->mysql_stream)) ) {
					$this->add_Error("<b>".mysql_errno()."</b>: ".mysql_error()."");
				} else {
					if ( $num = mysql_num_rows($result) )
						return $num;		// Questo &#65533; il risultato se tutto va bene!!!
					else {
						$this->add_Error("<b>".mysql_errno()."</b>: ".mysql_error()."");
						return 1;			// Serve per dare almeno una categoria...
					}
				}

Look up SELECT COUNT.

Oh, and

			$query .= "(  reviews2_reviews.text_review LIKE '%".$search."%' OR
						reviews2_reviews.artist LIKE '%".$search."%' OR
						reviews2_reviews.work_title LIKE '%".$search."%' OR
						reviews2_reviews.label LIKE '%".$search."%' OR
						reviews2_reviews.distributor LIKE '%".$search."%' OR
						reviews2_reviews.label_url LIKE '%".$search."%' OR
						reviews2_reviews.distributor_url LIKE '%".$search."%' OR
						reviews2_users.signature LIKE '%".$search."%' OR
						reviews2_users.email LIKE '%".$search."%' OR
						reviews2_reviews.artist_email LIKE '%".$search."%' OR
						reviews2_reviews.label_email LIKE '%".$search."%' ) ";

Ouch.

[mpb001]

$query .= "(  reviews2_reviews.text_review LIKE '%".$search."%' OR
                        reviews2_reviews.artist LIKE '%".$search."%' OR
                        reviews2_reviews.work_title LIKE '%".$search."%' OR
                        reviews2_reviews.label LIKE '%".$search."%' OR
                        reviews2_reviews.distributor LIKE '%".$search."%' OR
                        reviews2_reviews.label_url LIKE '%".$search."%' OR
                        reviews2_reviews.distributor_url LIKE '%".$search."%' OR
                        reviews2_users.signature LIKE '%".$search."%' OR
                        reviews2_users.email LIKE '%".$search."%' OR
                        reviews2_reviews.artist_email LIKE '%".$search."%' OR
                        reviews2_reviews.label_email LIKE '%".$search."%' ) "; 

The best thing to do with that is add an extra column and then concatenate those values into it( seperated by | or something ) on update and creation.

Then the WHERE clause would just become

WHERE reviews2_reviews.search_index LIKE '%".$search."%'

For a wildcard search( LIKE searches in character subsets so it is quite bad, especially across that amount of columns as it is is full table scanning multiple times( that is what is chewing your server up ) as it cannot use indexes. Eg. typing in a in the search box could return banana.

Or

WHERE MATCH( reviews2_reviews.search_index  ) AGAINST ( '".$search."' )

(http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html)
For a fulltext search that uses an index. The only loss in this is searching for a would not find banana any more as it looks for whole words, the benefit is there is a lot less degradation as the site gets larger once it gets fixed. There are a few things that can be involved in setting up FULLTEXT searches like lowering the minimum search count from 3 so it can find U2 and stuff.

There is going to have to be someone with some degree of ability to do this, though it is not necessarily that complex to do. If you do find someone and they think it is the PHP code( ie. blames it on an inefficiant function call ) and doesn't spot the database queries then don't let them near it.

Where is the code that adds and updates reviews?

The steps to fix the search are
1. Add a search index column, decide whether you want to use a FULLTEXT index( heavily recommended)
2. Update the code that manages the add/updating of reviews to populate the index from that point on.
3. Run an SQL statement to populate your new index column
4. Change the search code to use this new index column

Back everything up before hand if there is no dev box.

[mpb001]

Just a quick note, duplicating the content into an extra column will increase the database size due to the duplication, that is a downside depending on the maximum size of database you are allowed and at what point you are at.

Though on further inspection it fulltext search would break the url and email searches ad wildcard searches could be quite handy. The wildcard searches could be broken off into a seperate probably less used option.