my mySql db use to much resources

Discussion in 'MySQL' started by PinoyIto, Jun 13, 2006.

0
  1. #1
    I have a social networking site with more than 2000 members, I am using dedicated server and 4.4.1 mysql. If there only 10 to 20 members online the performance of the site is fast. But if the total member online is more than 20 the site is almost useless...

    I ask this problem to my server why that my site is very slow when number of user online is more than 20 and they said because my database use to much resources.

    They ask me to optimized the db I tried the tools in phpAdmin but no effect. Any other solution to fix this problem? To lessen the to much resources being use by the mySql database.
     
    PinoyIto, Jun 13, 2006 IP
  2. noppid

    noppid gunnin' for the quota

    Messages:
    4,246
    Likes Received:
    232
    Best Answers:
    0
    Trophy Points:
    135
    #2
    The coding of the application will dictate that. The table structure, indexing and query. What software are you using?

    If it's home grown, it could take a bit of work to troubleshoot. If it's a commercial package, there should be optimization tips available.
     
    noppid, Jun 13, 2006 IP
  3. arnoldcr

    arnoldcr Peon

    Messages:
    125
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #3
    you can search also how to tweak mysql, usually in /etc/my.cnf file
     
    arnoldcr, Jun 13, 2006 IP
  4. noppid

    noppid gunnin' for the quota

    Messages:
    4,246
    Likes Received:
    232
    Best Answers:
    0
    Trophy Points:
    135
    #4

    But you still have to know what the app does to tune properly it would seem. You want an app that plays nice with everything else in a perfect world.
     
    noppid, Jun 13, 2006 IP
  5. woodside

    woodside Peon

    Messages:
    182
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    0
    #5
    woodside, Jun 13, 2006 IP
  6. Chemo

    Chemo Peon

    Messages:
    146
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Try using the explain function built into MySQL which will give valuable data for how your queries are executing.

    As was said before try adding some timer functions which will output the execution time for each query.

    I'm taking a stab in the dark but am guessing it is due to inefficient queries performing entire table scans.

    Bobby
     
    Chemo, Jun 16, 2006 IP
  7. toby

    toby Notable Member

    Messages:
    6,923
    Likes Received:
    269
    Best Answers:
    0
    Trophy Points:
    285
    #7
    I agree with bobby and the rest bout application programming. Take a look at your SQL query mode. Use the visual explain to see how much CPU units your particular SQL takes.

    I think it's more related to the sql being used.
     
    toby, Jun 16, 2006 IP
  8. pelican

    pelican Well-Known Member

    Messages:
    444
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    125
    #8
    there are many factors that causes your site to be slow.

    - poor coding / poor sql statement in the script which might have many redundant database connections.

    - server hardware. processor speed and ram.

    - mysql server configuration. even if you have 8 GB RAM in the server, if you do not configure it properly for the database server to utilize the RAM, it is as if you only have 256 MB RAM or lesser. Check the following article as a start on how to configure the database variables.

    http://www.databasejournal.com/features/mysql/article.php/3367871
     
    pelican, Jun 20, 2006 IP
  9. iconv

    iconv Well-Known Member

    Messages:
    189
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    108
    #9
    Make sure that you enable the MySQL query cache (not enabled by default). Effictiveness depends on your usage pattern, but for web access this is usually a big winner.
     
    iconv, Jun 25, 2006 IP