teynon Posted July 29, 2011 Share Posted July 29, 2011 Ok, so I normally am alright with handling large tables, etc... But I set up a test table on my laptop (that i'm using right now). Then I tossed in over a million records, just to recreate the high stress environment. (Then I did it again with another table for comparison purposes.) So now I have two million row tables. After that, I was still able to select and manipulate data relatively quickly. However, I realized that my laptop's fan / processor was working overtime all the time. Does anyone know why this is? This is not a slow laptop. It's an Alienware M15x. When I say it's working overtime, I mean like all last night the fan was running and the cpu was running high. -- Decided as I was typing this to check the task manager performance bar... It seems that 1 of the 8 CPU charts it shows is always running at 80% or more. It also shows memory as a solid 1.99 GB. This wasn't this high until I added those rows to the database... If anyone has any ideas, it would be helpful! I don't think mysql does anything unless it's called upon, but i'm not really certain on anything it does in the background when idle. CPU Info: Hard drive is a solid state drive. 8 GB memory Quote Link to comment https://forums.phpfreaks.com/topic/243172-large-table-issue/ Share on other sites More sharing options...
gizmola Posted July 30, 2011 Share Posted July 30, 2011 Your fan runs when the computer is hot. MySQL has nothing to do with that. It's possible that you have gone over a memory limit, and you are now swapping a lot. 2 Gig is not a lot of memory these days. Depending on whatever else you're running, that would be my first guess. Quote Link to comment https://forums.phpfreaks.com/topic/243172-large-table-issue/#findComment-1249285 Share on other sites More sharing options...
teynon Posted July 30, 2011 Author Share Posted July 30, 2011 I ended the MySQL process and my CPU immediately dropped to nothing, the fans stopped running, and the memory dropped a good 500 mb. I did execute commands to demonstrate to someone how a server can freeze if you process bad request with MySQL. However, I didn't think it would go on that long. Perhaps that can go on for... pretty much ever? I have since deleted the tables, but both tables had two million and the example query I ran was "SELECT * FROM `table`, `table2`" which would pretty much take forever. I thought PHP would stop it at a certain point though?? Quote Link to comment https://forums.phpfreaks.com/topic/243172-large-table-issue/#findComment-1249286 Share on other sites More sharing options...
gizmola Posted July 30, 2011 Share Posted July 30, 2011 You didn't mention that. The way I read your original question, it seemd like you did a query and then couldn't understand why it would be using resources, but didn't mention you had a query that never actually finished executing. Yes, that query is called a cartesian product, and generates n1 * n2 rows. A very bad idea for sure, but not much of a cpu requirement although a huge use of temp table space. MySQL uses a thread per conneciton, so you see one core getting hammered. Memory use is based on your configuration. There are huge differences between innodb and myisam for example. Quote Link to comment https://forums.phpfreaks.com/topic/243172-large-table-issue/#findComment-1249293 Share on other sites More sharing options...
teynon Posted July 30, 2011 Author Share Posted July 30, 2011 Is there a way to set a timeout in MySQL for things like that? Just curious. Quote Link to comment https://forums.phpfreaks.com/topic/243172-large-table-issue/#findComment-1249294 Share on other sites More sharing options...
gizmola Posted July 30, 2011 Share Posted July 30, 2011 Not really. The general idea is that all queries should eventually complete, so there's no reason to ever time them out. I've seen people cobble together a programmatic solution like this one: http://forge.mysql.com/tools/tool.php?id=106 which uses the mysql scheduler along with a stored proc that looks for queries that have been executing for "too long" and kill them. Quote Link to comment https://forums.phpfreaks.com/topic/243172-large-table-issue/#findComment-1249311 Share on other sites More sharing options...
teynon Posted July 30, 2011 Author Share Posted July 30, 2011 Thanks, good future reference. Quote Link to comment https://forums.phpfreaks.com/topic/243172-large-table-issue/#findComment-1249316 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.