mysql caused massive disk consumption - Is there a fix?

Ask questions about dedicated servers here and we and other users will do our best to answer them. Please also refer to the self-help section for tutorials and answers to the most commonly asked questions.
Post Reply
squirrelof09
This is my homepage
This is my homepage
Posts: 76
https://www.youtube.com/channel/UC40BgXanDqOYoVCYFDSTfHA
Joined: Mon Mar 14, 2011 5:09 pm

mysql caused massive disk consumption - Is there a fix?

Post by squirrelof09 »

I was requested by support to make a post on the forums regarding my recent support request about mysql causing my VDS to consume massive amounts of disk space. I will copy my initial support question so that I may receive answers to my question.
I have resolved the issue causing massive disk usage. But I have questions on how to prevent this issue in the future...

One of the websites I run for a friend had some issue with some package they run for SMF. adkportal.php

I believe that is some portal page for SMF you view before you can enter the forums. It looks like he made a forum post today and the portal page was causing some problem with mysql causing it to continue to write to a temporary file until it exhausted the disk space.

Luckily it appears to have been an easy fix because SMF takes backups of modified files before each package install and I restored those files. That was his last package installed back in july of last year.

The user for his database already has Max Queries, updates, connections, User_connections all set to 2000. I believe this was only one query causing this... How would I prevent this from happening in the future causing massive disk space to be written as a temporary file?
squirrelof09
This is my homepage
This is my homepage
Posts: 76
Joined: Mon Mar 14, 2011 5:09 pm

Re: mysql caused massive disk consumption - Is there a fix?

Post by squirrelof09 »

Webserver is Apache, by the way.
User avatar
Edge100x
Founder
Founder
Posts: 12945
Joined: Thu Apr 18, 2002 11:04 pm
Location: Seattle
Contact:

Re: mysql caused massive disk consumption - Is there a fix?

Post by Edge100x »

There are many queries that it could have used to cause extensive use of temporary tables, and it's very difficult to say what happened or how to optimize without knowing the query it was using. Were you able to pull that out?
squirrelof09
This is my homepage
This is my homepage
Posts: 76
Joined: Mon Mar 14, 2011 5:09 pm

Re: mysql caused massive disk consumption - Is there a fix?

Post by squirrelof09 »

*************************** 18. row ***************************
Id: 136
User: ***
Host: localhost:49901
db: ***
Command: Query
Time: 12
State: *** DEAD ***
Info: SELECT
ms.subject, m.ID_TOPIC, m.ID_MEMBER, m.ID_MSG, b.ID_BOARD, b.name AS bName,
IFNULL(mem.realName, m.posterName) AS posterName, 1 AS isRead, 0 AS new_from
, LEFT(m.body, 384) AS body, m.smileysEnabled, m.icon
FROM (smf1_messages AS m, smf1_topics AS t, smf1_boards AS b, smf1_messages AS ms)
LEFT JOIN smf1_members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
ORDER BY t.ID_LAST_MSG DESC
LIMIT 110
18 rows in set (0.00 sec)

mysql>
This is the query that I see every time I run show full process list.
User avatar
Edge100x
Founder
Founder
Posts: 12945
Joined: Thu Apr 18, 2002 11:04 pm
Location: Seattle
Contact:

Re: mysql caused massive disk consumption - Is there a fix?

Post by Edge100x »

In that query, it looks like the person writing it forgot to include "t", "b", and "ms" in the join operation. If that is the case, and those tables have a decent number of rows each, the result set will be ridiculously huge, and as those tables grow, it will get exponentially worse.
squirrelof09
This is my homepage
This is my homepage
Posts: 76
Joined: Mon Mar 14, 2011 5:09 pm

Re: mysql caused massive disk consumption - Is there a fix?

Post by squirrelof09 »

Wow, mysql is a bit over my head.

Okay, so I can assume the problem is the developer who wrote that package/mod for SMF, I believe from what I gather when you say "the person writing it".

Is there a way to timeout such broken queries?
User avatar
Edge100x
Founder
Founder
Posts: 12945
Joined: Thu Apr 18, 2002 11:04 pm
Location: Seattle
Contact:

Re: mysql caused massive disk consumption - Is there a fix?

Post by Edge100x »

Yes, that's correct.

In this case, you'd want to never run that query in the first place, as it will likely always cause problems. I would recommend that you leave the package in question disabled.
squirrelof09
This is my homepage
This is my homepage
Posts: 76
Joined: Mon Mar 14, 2011 5:09 pm

Re: mysql caused massive disk consumption - Is there a fix?

Post by squirrelof09 »

Thank you so much. Now I know what to say with confidence when I email him :).

So there is no way to limit the size of the temporary file being written or timeout the query?
User avatar
Edge100x
Founder
Founder
Posts: 12945
Joined: Thu Apr 18, 2002 11:04 pm
Location: Seattle
Contact:

Re: mysql caused massive disk consumption - Is there a fix?

Post by Edge100x »

There likely is -- I'd have to search further to find the settings -- but that would similarly make this software not work.
squirrelof09
This is my homepage
This is my homepage
Posts: 76
Joined: Mon Mar 14, 2011 5:09 pm

Re: mysql caused massive disk consumption - Is there a fix?

Post by squirrelof09 »

Thanks again, I've googled around a bit and can't really find what I'm looking for. :)
squirrelof09
This is my homepage
This is my homepage
Posts: 76
Joined: Mon Mar 14, 2011 5:09 pm

Re: mysql caused massive disk consumption - Is there a fix?

Post by squirrelof09 »

I'm starting to like this "max_heap_table_size=". Perhaps this is what I should be using?
User avatar
Edge100x
Founder
Founder
Posts: 12945
Joined: Thu Apr 18, 2002 11:04 pm
Location: Seattle
Contact:

Re: mysql caused massive disk consumption - Is there a fix?

Post by Edge100x »

I don't think that you need to look for a general solution here, since you know the module that caused the problem. Any energy should go into fixing that major bug instead of trying to limit MySQL. What it did is something that is very rare and I don't expect you to run into it again with other software, and especially not with mature software.
Post Reply