MySQL Load - Looking to the future

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

MySQL Load - Looking to the future

Postby aliasxneo » Sun Oct 07, 2007 11:34 pm

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.
aliasxneo
Forum Contributor
 
Posts: 136
Joined: Thu Aug 31, 2006 12:01 am

Postby Josh1billion » Mon Oct 08, 2007 12:17 am

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).
User avatar
Josh1billion
Forum Contributor
 
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Postby mrkite » Mon Oct 08, 2007 2:40 am

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.
mrkite
Forum Contributor
 
Posts: 104
Joined: Tue Sep 11, 2007 4:19 am

Postby karthikeyan123 » Mon Oct 08, 2007 5:19 am

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
karthikeyan123
Forum Newbie
 
Posts: 18
Joined: Mon Sep 17, 2007 10:44 pm
Location: India

Re: MySQL Load - Looking to the future

Postby ev0l » Mon Oct 08, 2007 9:38 am

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).
ev0l
Forum Commoner
 
Posts: 56
Joined: Thu Jun 21, 2007 1:50 pm

Re: MySQL Load - Looking to the future

Postby aliasxneo » Mon Oct 08, 2007 1:13 pm

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).
aliasxneo
Forum Contributor
 
Posts: 136
Joined: Thu Aug 31, 2006 12:01 am

Postby s.dot » Mon Oct 08, 2007 1:18 pm

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)
User avatar
s.dot
Tranquility In Moderation
 
Posts: 4990
Joined: Sun Feb 06, 2005 8:18 pm
Location: Indiana

Re: MySQL Load - Looking to the future

Postby ev0l » Mon Oct 08, 2007 1:30 pm

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.
ev0l
Forum Commoner
 
Posts: 56
Joined: Thu Jun 21, 2007 1:50 pm

Postby aliasxneo » Mon Oct 08, 2007 1:38 pm

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?
aliasxneo
Forum Contributor
 
Posts: 136
Joined: Thu Aug 31, 2006 12:01 am

Postby aliasxneo » Mon Oct 08, 2007 1:40 pm

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.
aliasxneo
Forum Contributor
 
Posts: 136
Joined: Thu Aug 31, 2006 12:01 am

Postby ev0l » Mon Oct 08, 2007 1:45 pm

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]
ev0l
Forum Commoner
 
Posts: 56
Joined: Thu Jun 21, 2007 1:50 pm

Postby s.dot » Mon Oct 08, 2007 2:17 pm

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.
User avatar
s.dot
Tranquility In Moderation
 
Posts: 4990
Joined: Sun Feb 06, 2005 8:18 pm
Location: Indiana

Postby mrkite » Mon Oct 08, 2007 2:50 pm

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?
mrkite
Forum Contributor
 
Posts: 104
Joined: Tue Sep 11, 2007 4:19 am

Postby s.dot » Mon Oct 08, 2007 3:06 pm

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.
User avatar
s.dot
Tranquility In Moderation
 
Posts: 4990
Joined: Sun Feb 06, 2005 8:18 pm
Location: Indiana

Postby VladSun » Mon Oct 08, 2007 3:27 pm

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

:) :) :)
User avatar
VladSun
DevNet Master
 
Posts: 4294
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Next

Return to Databases

Who is online

Users browsing this forum: Bing [Bot] and 6 guests