6 replies to this topic

[chax]

I have been assigned the project of creating a social networking site. I have been working on the schema and created one. But the problem is how to optimise it. As the users, posts, polls, friends, foes etc keep increasing day by day the Database as I think will slow down. How do I tackle it?

[BlackRabbit]

You will need to give us more information on what you are working on, how much traffic, users, etc are your target, which hardware are you working on and which database engine.

in such massive services there is a lot of things to check, one being the operating system helping the database, meaning no interferences, intensive cpu taker processes and NO swapping ! meaning avoid your server's software to use more memory than the installed physical RAM ever.

Second check your database engine is properly configured in core-wise and memory-wise terms too, it usually gives you the chance to configure the resources usage limits.

Then make sure your database is well configure in table growth parameters, same for logs and indexes.

one trick to make DB faster is to have many hard disks, and assign data to one disk, indexes and logs to another one.

after that you can check on your tables having proper indexes in regards of most common used queries, and think the database not that much in normal forms ways but in performance one, sometimes the academically approved approaches are the slowest ones.

Then make sure you find a good cache, like mem cache, web cache, some of those and make your site work with them, and the last that comes to mi mind now is to be wise about what is "to be stored" data and what is just temporary counters, you can always use memory tables and shared memory for counters and similar

oh, and the most important thing of all! give it a like to this answer someone took the time to give to you

you come back later and make us now a little bit more about that project.

[WingedPanther73]

You're going to look at the queries you use, and create indexes as a first point. If you find that you have not fully normalized your database, that may be another place you can optimize. Realize, as well, that the queries you're running may need to be optimized, too.

[chax]

The project is completely new of its kind (the intent and the requirements to some extent) and hence I have no Idea what kind of traffic I'll have to deal with. There is something more I have done to the database. I have fragmented it on the basis of time-tags as of now and working on the virality, popularity and the oldness factors.

[arabbest]

you are going to good work but please you explain me exactly what is your question???what you want to ask us ???? if you have any problem with datatbase then you can installed sql server 2010

[chax]

I don't know how to undo that rating. My wobbling mouse . But sincerely thank you for your concern. I didn't know of phpBB and phpNuke and that they are open sourced. I hope I'll be able to get around creating the social networking site using them.

[WingedPanther73]

The key thing you'll probably want to do once you go live and start getting some traffic is look at some logs of DB usage, what queries are being run, and where the slowdowns occur. Optimizing a table that is only hit by 0.1% of your queries doesn't help much, for example.