BIG Tables

database, performance

[s0h31l]

Hello everyone...

I was using a mysql table with lots of rows(dnt remember the exact number something around 0.5 mil) and every query was taking a lot time. (which practically made it useless)

I'm dealing with a weblog system (where ppl can create their own blog) so there will be going lots of posts...

I was wondering how big sites like Facebook manage their databases? lets just consider the wall posts... its thousands (or millions) of posts everyday...

I know they use multiple and powerful servers but still it can't be just that...

What system do they use? Do they use databases like mysql? or they have their own way of doing it.

I was wondering whats the right way of dealing with big tables?

Ill appreciate your ideas...

Soheil

[nrobidoux]

I have pretty much the same question. I am in the process of developing a FB game and would like to code it as if it were going to be popular (i.e. big tables) right from the start.

I had some ideas... some rather general... like I assume I'd be using temporary tables in memory. I haven't put much more thought into it besides that. Probably cacheable queries too. Am I wrong in assuming the values in those queries shouldn't change? If an update is executed does MySQL invalidate the cache?

Two other ideas that I had that I was wondering if they'd be useful or not:

1. Using essentially two sets of DB tables. One is the "active set"... lets say users that have used the game w/i the last 30 days. The other is the rest.

Maybe a better thing to do would be to backup inactive user sets to disk and the inactive table would only need to be one table with a uid and filename.

2. Instead of having a monolithic user table that had something like: uid, name, xp, lvl, attack, defense, energy [, etc, etc.] the data would be broken up into multiple tables essentially grouping data from different queries... i.e. xp, level, health and energy would be displayed on every page but attributes like attack, defense, stealth, visibility, and other attributes are only queried for certain user actions.

As for the original poster's post, s0h31l, I'd recommend letting us know what your DB schema is. At least enough so peeps can see why it would be slow. Otherwise people would just be shooting-in-the-dark. Their answer might be helpful, it might not be.

[s0h31l]

Thank you nrobidoux for your reply...

cacheable queries should be good...

number 1 will definitely work for your case.. because in facebook there are many users who plays for a while and then they stop playing...

im not sure about number 2 because still if you deal with many users it will slow it down...

In my case im dealing with a persian(farsi) social network.. which users have their own profile/friend list, etc...

I am adding a new feature that users can have their own blog.

I am thinking to have 3 tables for this.

1-categories (catID,blogID,catTitle)

2-posts (postID,blogId,postTitle,postText,...)

3-comments(comID,postID, comText,..)

Thats just a basic design I am considering..

This will work fine until we get huge number of posts....

Thanks for your help

[s0h31l]

Also im not sure how temp tables will help you.... :-?

[lammert]

I was wondering whats the right way of dealing with big tables?

I run some sites with databases with tables with millions of rows, and there is no noticeable delay when queries are executed on those large tables.

The most important thing is creating the right indexes on your tables. If your indexes don't match with your queries, MySQL (and the other SQL flavors) defaults to a working sequence where all the rows are read and checked against the query one by one. This can be a time consuming operation with large tables.

You can use EXPLAIN SELECT to see how MySQL executes your queries. This may give a clue if your current indexes are doing their work properly. More information can be found in the MySQL manual [dev.mysql.com].

[nrobidoux]

Thanks s0h31l for your feedback. I'm pretty "new" to MySQL as far as knowledge goes. Only thing I've read on it a few years back was the platypus book from O'Reilly. So I was just throwing out ideas :) Sounds like I should dust it off and open it again.

Thx for mentioning "EXPLAIN SELECT" lammert. That's something new to me.

[nrobidoux]

Lammert... if I understand what you're saying I don't need to add either of my ideas to my DB?

[s0h31l]

Welcome Nrobidoux, im kinda new also.. I'm architecture student but I like programming so I do it on my free time...

Lammert Thanks for the reply, I did some tests on some table.. It seems very useful... I just noticed mysql is not using my indexes(key=null).. any idea why:

EXPLAIN SELECT *
FROM msgt
WHERE user_id_from =7;

id . select_type . table . type . . possible_keys . . key . . key_len . . ref . . rows . . Extra
1 . . . SIMPLE . . msgt . . ALL . . user_id_from . . NULL . . NULL . . NULL . . 75184 . . Using where

Also I tried:

EXPLAIN SELECT *
FROM msgt
USE INDEX ( user_id_from )
WHERE user_id_from =7;

but I get the same results

Any ideas why ?

[nrobidoux]

I would do: SHOW CREATE TABLE `msgt`;

You'll probably find `user_id_from` is not a key. This should fix it:

ALTER TABLE `msgt` ADD KEY(`user_id_from`);

[s0h31l]

Thanks for your reply...

I noticed the problem after doing "SHOW CREATE TABLE `msgt`;"

user_id_from was key... but its type was VARCHAR instead of int...

After changing it it used it:

id . select_type . table . type . . possible_keys . . key . . key_len . . ref . . rows . . Extra
1 . . . SIMPLE . . msgt . . ref . . user_id_from. user_id_from . 4. . const . . 30. .

:-) Thanks

[smatts9]

Lammert is right, make sure you have them indexed correctly. But don't get too carried away with indexing everything, then writing to the table will become a problem.