SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update Row Making Loads Skyrocket

    Okay,

    Every time a page is opened on my site we update the row of that page in the database with an additional view:

    PHP Code:
    $addview mysql_query("UPDATE video SET views=views+1 WHERE video_id=$videoid LIMIT 1",$cont); 
    We've never really had an issue with MySQL skying our loads or slowing page views. Here is an example of a busy day (w/loadavg):



    Okay, not too bad. Now, only recently have been having weird load issues. Our tech says it's because we're updating the "views" column so often, but we've been three times as busy and never had issues with table locking before. Here is our load graphs today, and notice we are less than 2/3rds as busy as the day with good loads:



    The weird part is, notice that the load drops at the start of each new hour? I have no cron running at the beginning of each hour. What can be causing these timed drops in load?

    Thanks
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  2. #2
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    Could be spiders or other things hitting your site in a timed manner?

    What table type are you using?

  3. #3
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MyIsam on the table.

    The problem is, I'll shut off all the updating rows and the load remains high. So something is causing it to spike, and only then do the row updates begin to really lock the table.

    You think it's a bot doing this? Hitting me hard in the middle of each hour?

    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.


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
  •