Advanced Guestbook - Copying to Temp Table

[acenetryan]

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:

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

I think I've got a fix!

For every agbook database, add an extra INDEX:

ALTER TABLE `book_com` ADD INDEX `id` ( `id` )

(for phpmyadmin users: go to the table boom_com, create an index with the name 'id' on the column 'id').

When an extra INDEX is added, the MySQL Bug isnt triggered (tested using mysql 4.1.21).

This works, but this is not a MySQL bug.

Definition for 'Copying to tmp table'
-----------------------------------
Copying to tmp table on disk The temporary result set was larger than tmp_table_size and the thread is now changing the in memory-based temporary table to a disk based one to save memory.

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.