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?
mysql caused massive disk consumption - Is there a fix?
-
- 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?
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.
-
- This is my homepage
- Posts: 76
- Joined: Mon Mar 14, 2011 5:09 pm
Re: mysql caused massive disk consumption - Is there a fix?
Webserver is Apache, by the way.
Re: mysql caused massive disk consumption - Is there a fix?
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?
-
- This is my homepage
- Posts: 76
- Joined: Mon Mar 14, 2011 5:09 pm
Re: mysql caused massive disk consumption - Is there a fix?
This is the query that I see every time I run show full process list.*************************** 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>
Re: mysql caused massive disk consumption - Is there a fix?
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.
-
- This is my homepage
- Posts: 76
- Joined: Mon Mar 14, 2011 5:09 pm
Re: mysql caused massive disk consumption - Is there a fix?
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?
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?
Re: mysql caused massive disk consumption - Is there a fix?
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.
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.
-
- This is my homepage
- Posts: 76
- Joined: Mon Mar 14, 2011 5:09 pm
Re: mysql caused massive disk consumption - Is there a fix?
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?
So there is no way to limit the size of the temporary file being written or timeout the query?
Re: mysql caused massive disk consumption - Is there a fix?
There likely is -- I'd have to search further to find the settings -- but that would similarly make this software not work.
-
- This is my homepage
- Posts: 76
- Joined: Mon Mar 14, 2011 5:09 pm
Re: mysql caused massive disk consumption - Is there a fix?
Thanks again, I've googled around a bit and can't really find what I'm looking for.
-
- This is my homepage
- Posts: 76
- Joined: Mon Mar 14, 2011 5:09 pm
Re: mysql caused massive disk consumption - Is there a fix?
I'm starting to like this "max_heap_table_size=". Perhaps this is what I should be using?
Re: mysql caused massive disk consumption - Is there a fix?
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.