PHP Scaling w/ MySQL

[AdamCox9]

I'm trying t find some simple ways to make my code run more efficiently with a lot of traffic and a lot of entries in my DB. My pages take too long to load.

 

I've optimized the code quite a bit. I store all the data from the DB in the sessions until it is needed to be updated or 10 Mins. Are there any other quick tricks to make code perform better?

 

I've though about the Master/Slave and spreading it across multiple servers, but that might be more work than I can do. Is there anything easier that anybody knows about?

 

Thanks for any advice!

[corbin]

More infomation would be nice...

 

What hardware is your server on?

 

How many page loads do you have per second (or minute or what ever).

 

How often are SQL tables updated/rows deleted/rows inserted?  Do you use indexes where needed?  What engine are you using for most of your tables?

[AdamCox9]

There are probably 20-30K Queries a day and approximately 2/3's of them are inserting/updating.

 

The data is constantly being inserted/updated. I read that I should not use indexes if that is the case because the DB will have to reindex it every time it changes which could make it take more time.

 

I'm running it on a pretty fast dedicated linux server. How do I find out the specs on the hardware that you need?

 

Thanks for your response!

[corbin]

Hmmm.....  Ignore the specs for now...

 

How many rows are in the table?  What do you usually update/delete based on?  What do you usually select on?

What engine do your tables use?  (Probably either MyISAM or InnoDB)

 

Do you think it's MySQL or PHP causing the slow down?  (I'm personally guessing MySQL, but it could easily be PHP, or both.)

 

Does any page using MySQL come to a stop or just some?  If just some, are they just pages using a certain table/set of tables?  If so, how often if that table being altered, and once again, what engine is it?

[AdamCox9]

I'm reading and checking a value in the DB to decide if to update with 2 queries.

 

query1 = select count...

...

if ( mysql_num_rows < x )

  query2 = update....

[AdamCox9]

engine=innodb

 

It could also be the 3rd party APIs I am using that make requests and wait for responses during the execution of the script. But, I absolutely can't do nothing about that.

 

I've been doing a mysql_dump and saving the data to disk after about 75K entries. That helps a very lot. I think that is def part of the problem.

[corbin]

My first thought was that your tables are probably locking for long amounts of time, but now I think the seek time is probably the slow down if you have 75k rows.

 

You're updating just one row right?

 

Let's say the query is:

 

UPDATE users SET something = 'hi' WHERE uid = 4562;

 

Now, assuming the table is in order of user ids, that would mean MySQL would have to go through 4562 rows before it would find an answer, and unless the row was set to be unique, it would actually check all of the rows, looking for any others that have uid = 4562.

 

Now, with an index on uid on the other hand, all rows where uid = 4562 would already be known.  That would mean only the index would have to be looked through which takes a lot (I MEAN ALOT, depeding on the table size) less time.

 

So, if your query takes 3 seconds just to find the right row to update, that's obviously going to take a little time.

 

Indexes are your friend when used correctly.

 

 

It might be worth timing different parts of your script...

 

For example, make a non live copy, and just do something like this:

 

function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}

//(instead of microtime_float() you could just use microtime(true) if you're using php5

define('START', microtime_float());

//do something here, maybe conn to the DB or something
echo 'Part 1: ' . (microtime_float() - START); // this would show long long it's taken to execute until this point

//do a query or access one of the APIs or something

echo 'Part 2: ' . (microtime_float() - START); // this would show long long it's taken to execute until this point

//do something else

echo 'Part 3: ' . (microtime_float() - START); // this would show long long it's taken to execute until this point

 

Obviously if part 1 was .05 seconds, and then part two jumped to 2.5, you would know part two took 2 seconds to execute.  So you could use that method to try to find where the slow part is.  Identifying problems usually helps when trying to fix them ;p. lol