locked
WHS periodically locking up with CPU at 100% load RRS feed

  • Question

  • For some reason, my WHS has daily seized with activity that renders it useless.  I try to log in to see what is going on and it is a slow and ineffective process.  Finally, I was able to see that the CPU in the Task Manager was maxed out at 100%.  It is hard to get into Processes to see what the problem might be when everything is locked up.  I did once and the only thing that looks real strange is something called "Demigrator" that looks like it is using a lot of memory and another that is using a lot of memory called DDNSS.exe.

    How can I figure out what is going on?

    Any and all tips or clues would be appreciated.

    Friday, September 10, 2010 4:30 PM

All replies

  • I cannot be sure, but it seems to go off when I am running a client to MySQL which is installed on WHS.
    Friday, September 10, 2010 7:51 PM
  • This continues to be an extremely serious problem.  I have been trying to remove the problem by killing the power and rebooting, but now the system resumes 100% usage as soon as it boots up.

    WHS has been extremely useful and reliable until now.  Now it is completely and utterly useless.

    Anyone with any ideas whatsoever would be appreciated.  I have never had any kind of problems like this with any other Microsoft operating systems.  This is bad.

    Friday, September 10, 2010 10:50 PM
  • Anyone with any ideas whatsoever would be appreciated.  I have never had any kind of problems like this with any other Microsoft operating systems.  This is bad.

    Sorry to say this, but the obvious next step is to remove MySQL. If that doesn't take care of the issue, server recovery is the follow-up.

    Windows Home Server isn't a general purpose server operating system. No matter how convenient it might be to install everything but the kitchen sink, it's possible that something installed from the server desktop will cause a problem. I don't know why the installation of MySQL has caused this, but it seems quite likely that it has caused this in some fashion.


    I'm not on the WHS team, I just post a lot. :)
    Saturday, September 11, 2010 12:04 AM
    Moderator
  • I managed to go through an actual shutdown through the operating system and in the slow process of shutting down, I recieved a notice about  WindowsSearch running.  I saw some references to Windows Search in regards to the same problem elsewhere and so whatever the role of MySQL in the problem, Windows Search is involved.

    I asked in another thread if it is possible to remove Windows Search 4.0 without damaging WHS.

    Is it?  I'd like to try that and see if that works.

    Or maybe there is some settings in Windows Search I can change.

    Anyone with advice or experience about removing or adjusting Windows Search 4.0?

    Saturday, September 11, 2010 3:50 AM
  • Windows Search is used to support the search functionality in the Remote Access web site, and so is an essential component (as Microsoft defines it, you may disagree) of the product. You can try to remove Windows Search, but if you do you may be unable to update your server past it's current state, it may simply be reinstalled automatically, or errors may begin to occur.

    What happens if you remove MySQL?


    I'm not on the WHS team, I just post a lot. :)
    Saturday, September 11, 2010 12:47 PM
    Moderator
  • The problem did not start with the installation of MySQL and I cannot concretely connect the two.  It sometimes appears that the problem crops up when a MySQL client connects with WHS.

    It doesn't make much sense to punish MySQL by uninstalling it when the only evidence I have for any cause for the problem is Windows Search.  And if Windows Search is an essential component, then why is it listed in the Add or Remove Programs right along with MySQL?

    What kind of errors could I expect if I remove Windows Search?  Why would Windows Search have anything at all to do with MySQL?  Why does it have anything to do with updates?

    Saturday, September 11, 2010 3:47 PM
  • ...
    What kind of errors could I expect if I remove Windows Search?  Why would Windows Search have anything at all to do with MySQL?  Why does it have anything to do with updates?

    • I haven't removed it (and won't; I do search my servers from time to time), but for sure you will no longer be able to search your server via the Remote Access web site or via the Search box in Explorer from Vista/Windows 7. Can it be removed? Probably. Will you eventually have problems as a result? Maybe.
    • I have no clue. I do know that Microsoft doesn't ship Windows Home Server with MySQL, and just up above you mentioned that your problems began after installation of MySQL.
    • Windows Search 3.0 is installed with Windows Home Server, and updated as part of Power Pack 3 to version 4.0. It's a component that's actually used by Windows Home Server, and that gets updated from time to time with Windows Home Server, so of course it has something to do with updates.
    Uninstalling MySQL may not solve your issue. However, your issue began after the installation; I would think uninstalling it would not be an unreasonable step to take to try to sort the issue out. There's no "punishment" involved. I know you want to run MySQL on Windows Home Server, and I can't think of a reason why you shouldn't be able to. Possibly it's the client application that's causing the issue, but at the end of the day, MySQL is involved.

    None of the links you posted separately relate to Windows Home Server.

    I'm not on the WHS team, I just post a lot. :)
    Saturday, September 11, 2010 10:28 PM
    Moderator
  • Yes, MySQL is definitely the source of the problem, but it is not the server alone that is the problem, it is the queries I am making to the server that are loading up the processor.  I'm thinking lack of memory is the problem, I am only running 768MB.

    The queries that are coming from the client are complicated, using queries from subqueries, and I've tested them on my other servers and they run slow on them too.  Although, they don't appear to be completely locking up like WHS is.  Even so, I'll have to declare Windows Search 4.0 innocent of all charges since I uninstalled it and it didn't make any difference.

    Thank you for your attention to this problem, Ken.  Once again you have come to some kind of relief if not rescue.

    Any advice on the memory issue?  512MB is the minimum suggested, is there a prefered minimum?  What is the maximum it will use?

    Saturday, September 11, 2010 10:52 PM
  • First, relief vs. rescue: as a general rule, if a problem comes up with a third party tool, the system builder who installed the tool is going to solve the problem, not us. In this particular case, the "system builder" is you. We generally can't solve your problems for you, only point you in the right direction, which is often that the third party tool is borked. (That's an accurate statement of your situation, BTW; the client tool is generating horribly inefficient SQL...)

    ...
    Any advice on the memory issue?  512MB is the minimum suggested, is there a prefered minimum?  What is the maximum it will use?

    lol.

    Microsoft is famous for "recommended minimum" specifications that are just barely able to support an idle workload. In the case of Windows Home Server, if you have only Windows Home Server installed, and you don't expect snappy performance from anything that runs on the desktop (including the Console, which is an application running on your server and remoted to your client via an undecorated Remote Desktop window), you can actually run reasonably well in 512 MB.

    In your case, two things should be done. First, optimized the client tool that's spitting out inefficient queries. It sounds like it's a horrible piece of work, and I can almost guarantee there's a better way to write the queries. This should be done before anything else; if the client tool is cranking out SQL as inefficient as you say it is, you're going to have issues no matter what else you do. For help with this, either the ISV supplying the software or (if you're writing it yourself) forums for MySQL programming are the right place to look.

    Second, 2 GB or more will probably be much more comfortable for the workload you're actually putting on your server, which is vastly greater than Windows Home Server is really designed for. And a faster processor wouldn't hurt. Faster and multi-core would be better yet. Avoid multi-socket, however, the next version of Windows Home Server is limited to a single socket. And look at MySQL hardware optimization tips; probably there are specific tuning recommendations.

    But realistically, Windows Home Server is just not the OS you want to use for a database server.


    I'm not on the WHS team, I just post a lot. :)
    Sunday, September 12, 2010 2:45 PM
    Moderator
  • I think the query is as tight as it's going to get.  Some queries are just a load for a big table and any server.  It's been a real wakeup call for me.  I am running these kind of queries in PHP scripts (not off of WHS) and I am going to quit doing that.  It's just a report and can be generated statically.

    And, I am looking into upgrading memory and CPU's for my little server, it is long overdue.

    Thanks again,

    PROBLEM SOLVED.

    Sunday, September 12, 2010 10:47 PM
  • I think the query is as tight as it's going to get.  ...

    (This is wwwaaayyy off topic...)

    I mean no offense, but if you're using correlated subqueries, which I understand you to be doing, then this is probably wrong. Correlated subqueries get executed many times for a single execution of the overall query, and are pretty obvious performance killers. See e.g. this article which touches on this subject. 


    I'm not on the WHS team, I just post a lot. :)
    Monday, September 13, 2010 2:47 PM
    Moderator
  • I'm happy to discuss my problem anywhere.  That is an interesting article and looks relevent to my problem, but more directly to the point, here is what I am trying to do:

    I am trying to remove duplicate records from a table based on a series of columns destined to be a primary key.  I went to O'Reilly's "SQL Cookbook" and found a ready solution,

    DELETE FROM dupes WHERE id NOT IN (SELECT MIN(id) FROM dupes GROUP BY name)
    

    And so my solution was something like,

    DELETE FROM dupes WHERE id NOT IN (SELECT MIN(id) FROM dupes GROUP BY col1, col2, col3)
    

    In order to check out how the query might run, I was running this query instead,

    SELECT * FROM dupes WHERE id NOT IN (SELECT MIN(id) FROM dupes GROUP BY col1, col2, col3)
    

    "dupes" is actually a fairly big table and this is what was bogging my processor down.  But I actually ran a delete query and I'm getting errors explaining "You can't specify target table 'dupes' for update in FROM clause".

    This is where I am currently stuck.  So if there is a more efficient solution, then that is just as important as getting any kind of solution at all.  Do you have a ready solution for removing duplications from a table based some series of columns?

    Monday, September 13, 2010 4:05 PM
  • Update.

    I went over to O'Reilly's site to see if there might have been a problem with their solution.  There was.

    http://oreilly.com/catalog/errata.csp?isbn=9780596009762

    See page 80.  The original solution was,

    DELETE FROM dupes WHERE id NOT IN (SELECT MIN(id) FROM dupes GROUP BY name)
    

    With MySQL, the solution should be,

    DELETE FROM dupes WHERE id NOT IN (SELECT MIN(id) FROM (SELECT id, name FROM dupes) tmp GROUP BY name
    
    I am running this code to see if it is more efficient, but it doesn't look like it will be.
    Monday, September 13, 2010 7:15 PM