MySQL Load - Looking to the future

[aliasxneo]

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.

[Josh1billion]

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).

[mrkite]

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.

[karthikeyan123]

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

[ev0l]

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).

[aliasxneo]

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).

[s.dot]

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)

[ev0l]

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.

[aliasxneo]

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]

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.

[ev0l]

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]

[s.dot]

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.

[mrkite]

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?

[s.dot]

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.

[VladSun]

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