Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Database optimisation for Social Networking sites.

database optimisation

  • Please log in to reply
6 replies to this topic

#1 chax

chax

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 88 posts
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Perl, PL/SQL, Lisp, Assembly, Fortran, VBScript

Posted 06 September 2012 - 10:21 AM

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?
  • 0
Size does matter for science and its laws changes accordingly.

#2 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 06 September 2012 - 11:49 AM

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 :D

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

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

#3 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 06 September 2012 - 01:13 PM

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.
  • 1

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#4 chax

chax

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 88 posts
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Perl, PL/SQL, Lisp, Assembly, Fortran, VBScript

Posted 06 September 2012 - 08:45 PM

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.
  • 0
Size does matter for science and its laws changes accordingly.

#5 arabbest

arabbest

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts

Posted 07 September 2012 - 02:15 AM

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
  • -1

#6 chax

chax

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 88 posts
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Perl, PL/SQL, Lisp, Assembly, Fortran, VBScript

Posted 07 September 2012 - 03:15 AM

I don't know how to undo that rating. My wobbling mouse :c-mad: . 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.
  • 0
Size does matter for science and its laws changes accordingly.

#7 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 07 September 2012 - 11:12 AM

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.
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/






Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download