The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Advanced Guestbook - Copying to Temp Table

Discussion in 'General Discussion' started by acenetryan, May 4, 2007.

  1. acenetryan

    acenetryan Well-Known Member
    PartnerNOC

    Joined:
    Aug 21, 2005
    Messages:
    197
    Likes Received:
    1
    Trophy Points:
    18
    Hello All,

    This issue was covered on another thread which was pretty old and was closed so I can't update it.

    To summarzie, advanced guestbook can really kill your mysql server under certain circumstances. You'll often see:

    Code:
    +-------+------------------+-----------+------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
    | Id    | User             | Host      | db               | Command | Time | State                | Info                                                                                                 |
    +-------+------------------+-----------+------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
    | 67838 | user_agbook2    | localhost | user_agbook2    | Query   |  370 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 67849 | user_agbook2    | localhost | user_agbook2    | Query   |  350 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 67854 | user_agbook2    | localhost | user_agbook2    | Query   |  322 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    | 67879 | user_agbook2    | localhost | user_agbook2    | Query   |  251 | Copying to tmp table | select x.*, y.p_filename, y.width, y.height, z.comments from book_data x left join book_pics y on (x |
    +-------+------------------+-----------+------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
    

    wardv had given us a nice solution:

    This works, but this is not a MySQL bug.

    This is the intended behavior. When the table is too large to process in memory, it is paged out to disk, thus the slowdown and performance loss.

    More often than not, I have found that the reason is because Advanced Guestbook does not have a referrer check on it and the guestbook is being spammed to death.

    At this point, you can add the index but the guestbook should really be disabled. When the spam gets too high, the joins are too large and you see the above results.

    Tell your client to either use another guestbook, setup a referrer check, or stop using the guestbook period.
     
Loading...

Share This Page