PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Sun Dec 21, 2014 11:54 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 24 posts ]  Go to page 1, 2  Next
Author Message
PostPosted: Sun Oct 07, 2007 11:34 pm 
Offline
Forum Contributor

Joined: Thu Aug 31, 2006 12:01 am
Posts: 136
I am currently in the development of a site (nearly finished) that is expected to have a huge traffic rate. The website is for a game (of which I will not specify) that has an average of around 20,000-30,000 people on at any given time. My particular website is something I figure will be accessed at least once every minute.

So now that you see the amount of traffic I am talking about, it is vital that I get the upmost speed from my MySQL database. My question is this: What precautions and procedures can I take (in regards to MySQL and even PHP) to ensure I retain the upmost speed despite the huge traffic rate?

Keep in mind that this is the first "major" website I have created, so I'm very inexperienced in this matter. Thanks in advance.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Oct 08, 2007 12:17 am 
Offline
Forum Contributor
User avatar

Joined: Tue Sep 11, 2007 3:25 pm
Posts: 316
Hmm.. here are a couple of things I know to do:

1. Connect to the MySQL database server once (at the beginning of your file, perhaps in a "connect.php" include file), rather than connecting each time you need to make a query.

2. This isn't speed-related, but security-related (throwing this in since you said this is your first major site, and this may be something you don't know about): use mysql_real_escape_string() on ALL user-inputted data to protect against SQL injection.

Also, you may be interested to know of a Firefox extension called YSlow which is supposed to help you optimize your webpage's loading speeds (this is HTML-related, not PHP/MySQL-related, though-- so it's only a client-side optimization).


Top
 Profile  
 
 Post subject:
PostPosted: Mon Oct 08, 2007 2:40 am 
Offline
Forum Contributor

Joined: Tue Sep 11, 2007 4:19 am
Posts: 104
Turn on query caching. Bump up the number of simultaneous connections for mysql. It defaults to 100... which is something a 7 year old desktop could handle. Bump it to 500 or higher... you'll probably have to change this until you find the sweet spot for your hardware.

use "explain" on every query your scripts make. Make sure there are indexes used for everything. Turn on the slow query log so you can easily see which queries are taking too long.

At the same time, you'll want to muck around with the number of apache forks are running and the maximum number.

I can't remember if it's apache or mysql, but one of the two requires a recompile to increase the simultaneous connections past 512. You don't want to have to do that after you hit that wall.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Oct 08, 2007 5:19 am 
Offline
Forum Newbie

Joined: Mon Sep 17, 2007 10:44 pm
Posts: 18
Location: India
hi,

I think you can use the INDEX key in your tables, It will be used to reduce the execution time of the select query.

Regards,
Karthikeyan R


Top
 Profile  
 
PostPosted: Mon Oct 08, 2007 9:38 am 
Offline
Forum Commoner

Joined: Thu Jun 21, 2007 1:50 pm
Posts: 56
aliasxneo wrote:
I am currently in the development of a site (nearly finished) that is expected to have a huge traffic rate. The website is for a game (of which I will not specify) that has an average of around 20,000-30,000 people on at any given time. My particular website is something I figure will be accessed at least once every minute.


Don't worry. Fix your speed problems when they come up. At any rate once every minute is a very low traffic rate and all but the slowest server should be able to keep up with no trouble at all (as long as there are no major bottle necks of course).


Top
 Profile  
 
PostPosted: Mon Oct 08, 2007 1:13 pm 
Offline
Forum Contributor

Joined: Thu Aug 31, 2006 12:01 am
Posts: 136
ev0l wrote:
aliasxneo wrote:
I am currently in the development of a site (nearly finished) that is expected to have a huge traffic rate. The website is for a game (of which I will not specify) that has an average of around 20,000-30,000 people on at any given time. My particular website is something I figure will be accessed at least once every minute.


Don't worry. Fix your speed problems when they come up. At any rate once every minute is a very low traffic rate and all but the slowest server should be able to keep up with no trouble at all (as long as there are no major bottle necks of course).


Honestly I rather take care of the problems before they arise. Once every minute was probably a very low guess. Esentially what is going to happen is my website is going to be linked many times in a forum that is just as active as the game is I described in my first post. So it will most likely be a lot more than once per minute.

Besides, one of the positives to my site is that it is supposed to be fast (unlike other competitive sites).


Top
 Profile  
 
 Post subject:
PostPosted: Mon Oct 08, 2007 1:18 pm 
Offline
Tranquility In Moderation
User avatar

Joined: Sun Feb 06, 2005 8:18 pm
Posts: 4990
Location: Indiana
From what I've learned, premature optimization only causes more problems. Don't optimize until a situation which needs optimized presents itself.

(this is not saying don't use best practices, by the way)


Top
 Profile  
 
PostPosted: Mon Oct 08, 2007 1:30 pm 
Offline
Forum Commoner

Joined: Thu Jun 21, 2007 1:50 pm
Posts: 56
aliasxneo wrote:
Honestly I rather take care of the problems before they arise.


It is very difficult to predict problems. Knowing how you can do something and fully understanding the tradeoffs you are making is something that only comes from years of experience. The learning never stops only the problems are different. Software development is all about fixing problems, it is what makes the profession so enjoyable.

Write your application the best you can then figure out where it is slow and might need to be optimized. You might be surprised, the bottlenecks might not be where you thought they would be.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Oct 08, 2007 1:38 pm 
Offline
Forum Contributor

Joined: Thu Aug 31, 2006 12:01 am
Posts: 136
So I should just release my website as is (without any prior modifications) to the public and only try to fix it when the public complains?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Oct 08, 2007 1:40 pm 
Offline
Forum Contributor

Joined: Thu Aug 31, 2006 12:01 am
Posts: 136
scottayy wrote:
From what I've learned, premature optimization only causes more problems. Don't optimize until a situation which needs optimized presents itself.

(this is not saying don't use best practices, by the way)


Exactly, and what's a best practice to ensure speed in MySQL?

*Edit*

Sorry for double post. Use too many forums that add double posts together.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Oct 08, 2007 1:45 pm 
Offline
Forum Commoner

Joined: Thu Jun 21, 2007 1:50 pm
Posts: 56
aliasxneo wrote:
Exactly, and what's a best practice to ensure speed in MySQL?
.


The MySQL manual is good. Chapter 7 deals with optimization. But you have to have something that is slow before you can optimize it.

Do you have a specific problem ? Can you post code, schema, behavior, expected behavior, etc?


[/list]


Top
 Profile  
 
 Post subject:
PostPosted: Mon Oct 08, 2007 2:17 pm 
Offline
Tranquility In Moderation
User avatar

Joined: Sun Feb 06, 2005 8:18 pm
Posts: 4990
Location: Indiana
I'll give you a perfect example that actually happened to me once.

In my forums, I was displaying users posts counts by simply doing a select count(*) from the forum entries table. With only a few thousand entries, this was perfect. Then as the database grew to hundreds of thousands of entries, this started becoming painfully slow.

Then I went in and added code to store users posts count in the user table and increment it each time they posted. This avoided querying the very large forum entries table.

Had I tried to do that from the beginning, I would've probably made mistakes in calculating post counts, and it wouldn't have affected the code very much speed wise at the time, anyways.

So as soon as it became a problem, i fixed it. And waited for the next problem to come along.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Oct 08, 2007 2:50 pm 
Offline
Forum Contributor

Joined: Tue Sep 11, 2007 4:19 am
Posts: 104
scottayy wrote:
Had I tried to do that from the beginning, I would've probably made mistakes in calculating post counts, and it wouldn't have affected the code very much speed wise at the time, anyways.


So you had to restructure your database, write a whole bunch of new code on top of a live site, and you think that's a better solution than a little planning ahead?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Oct 08, 2007 3:06 pm 
Offline
Tranquility In Moderation
User avatar

Joined: Sun Feb 06, 2005 8:18 pm
Posts: 4990
Location: Indiana
I didn't restructure my database. And I didn't write that much code. But even if I had to do so, yes. I couldn't pinpoint that that was going to be the problem ahead of time. Plus I would've been making php run more code at the beginning than I had to. Plus there's a lot more room for error. The point of that example is you can't fix a problem before it exists. Let it exist first, and then optimize.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Oct 08, 2007 3:27 pm 
Offline
DevNet Master
User avatar

Joined: Wed Jun 27, 2007 9:44 am
Posts: 4294
Location: Sofia, Bulgaria
Quote:
The question, therefore, is not whether to build a pilot system and throw it away. You will do that. The question is whether to plan in advance to build a throwaway, or to promise to deliver the throwaway to customers.


source: The Mythical Man-Month: Essays on Software Engineering, 20th Anniversary Edition

:) :) :)

_________________
Image
http://openfmi.net/projects/flattc/ Linux is better :)


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 24 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 4 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group