CODEWITHSUNDEEP
phpmysqlcachingscalability
Oskar Persson
Oskar Persson

Reputation: 6765

Making a database scalable

I've been developing a website for some time now and so far everything is fast and good, though that is with one active user. I don't know how many people will use my website in the first, week, month or year.

I've been looking into what scaling means and how to achieve it and caching seems to be a big part of it. So I started searching for ways to cache my content. Currently I've just developed the website in XAMPP. I'm using MySQL for the database and simple PHP with MySQLi to edit the data. I also do some simply logging with the built-in System Messages app in OS X Mountain Lion. So I'm thinking about using Memcache for the caching.

Is this a good approach?

How can I test it to really see the difference?

How do I know that it will work great even with many more users?

Are there any good benchmarking apps out there?

Upvotes: 1

Views: 186

Answers (2)

Erfa
Erfa

Reputation: 713

There are many ways to make sure that a database scales well, but I think the most important part is that you define proper indexes for your tables. At least the fields that are foreign keys should have an index defined.

For example, if you have a large forum, you might have a table of topics that looks like this:

topic_id | name
---------+--------------------------------
       1 | "My first topic!"
       2 | "Important topic"
       3 | "I really like to make topics!"
     ... | ...
 7234723 | "We have a lot of topics!"

And then another table with the actual posts in the topics:

post_id  | user       | topic_id
---------+------------+---------
       1 | "Spammer"  | 1
       2 | "Erfa"     | 2
       3 | "Erfa"     | 1
       4 | "Spammer"  | 1
     ... | ...        | ...
87342352 | "Erfa"     | 457454

When you load a topic in your application, you want to load all posts that match the topic id. In this case, you cannot afford to look through all database rows, because there are simply too many! Fortunately, you do not have to do much to make sure this is done, you just have to create an index for the field topic_id and you are done.

This is a very basic thing to do to make your database scale well, but since it is so important, I really thought someone should mention it!

Upvotes: 3

Petrogad
Petrogad

Reputation: 4423

Get and Use jMeter.

with jMeter you can test how quick responses are coming back and how pages are loading in addition to confirming that there aren't any errors currently going on. This way you can simulate a ton of load; while seeing actual performance updates when making an adjustment such as using memcache.

Upvotes: 2

Related Questions