SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow DB killing popular website.

    First off, I've got 15k registered users, so I really wanna keep my site running smoothly.

    It's taking longer than 2 minutes to load any page.

    I could get a bigger server, but not immediately. And I doubt that will fix the problem for long.

    I'm not certain, but I think the bottleneck is with the size of the database tables, and the fact that the SQL queries have to scan the whole table just to find the latest 10 entries.

    Basically, my site's outgrown php and mysql. What is the solution for this issue? I can't be the first person to encounter it. There are about 40,000 posts and 400,000 comments.

    I'll do whatever it takes, but I don't know what the exact problem is, and I have no clue how to fix it!

    Can I optimize the SQL queries without restructuring the whole site's code?
    Will caching the pages in static files for users that aren't logged in help?
    Or is the problem largely independant of the number of users online?

  2. #2
    SitePoint Guru
    Join Date
    Jun 2004
    Location
    Finland
    Posts
    703
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Make sure you're not opening multiple mysql connections.
    Also, I'd like to see the site myself

  3. #3
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    Basically, my site's outgrown php and mysql.
    Not likely. Perhaps you code isn't scalable or something but I very much doubt your site is more than PHP and/or MySQl can handle.

  4. #4
    + platinum's Avatar
    Join Date
    Jun 2001
    Location
    Adelaide, Australia
    Posts
    6,441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What software are you using?
    What are the specs of the server its currently on?

    Are you running the lastest version of mySQL? try turning on query caching, and make sure it's optimized to run best on your server.

    Also you say it keeps getting the last 10 posts? is that for a front page or something? You could definatly cut back a heap on that by generating it automatically every 5misn or something.

  5. #5
    SitePoint Addict pointbeing's Avatar
    Join Date
    Jun 2004
    Location
    London, UK
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jehu
    Basically, my site's outgrown php and mysql.
    Your site is bigger than Yahoo? I'm impressed.

    The first thing to look at is the indexes on your tables, I would think. With proper indexing I've seen MySQL cope with 5 million user records virtually instantaneously.

    And make sure you're not doing 15k queries where a single well-formed JOIN will do the trick.

    If you can narrow down which parts of your code are causing the problem, people here would definitely be willing and able to help you optimise it all

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "...and the fact that the SQL queries have to scan the whole table just to find the latest 10 entries."

    As pointBeing said: index index index. That should fix this problem.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •