I have written a Java program that is quite database intensive (MySQL). The CPU on my computer (Windows) goes up to 100% and stays there pretty much the whole time the program is executing. If I look at my processes I notice that it is the mysql server that is consuming most of the cpu.
Is it possible to lower the cpu usage? The reason why I want to do that is because I can't really work with my computer while the program is running. I have a fairly new computer with 1GB of RAM.
1. Use Windows Task Manager (or better, Windows Performance Monitor) to identify which process is consuming resources, and specifically which resources. <= IT SOUNDS LIKE YOU'VE ALREADY DONE THIS: THE PROCESS IS "MYSQL", AND THE RESOURCE IS %CPU BUT DOUBLE-CHECK: YOU MIGHT BE SURPRISED...
2. Try to identify specifically which query is causing you problems. You might already know the query, you might have to put "printf's" (excuse me - "System.out.println's" ;-)) in your Java program.
3. Run the query manually, directly from the "mysql" command monitor. Use "EXPLAIN".
Perhaps your tables need some extra indexes; perhaps you might consider denormalizing them; perhaps you can just make a simpler (less expensive) SQL query.
My program runs for 20 minutes and I don't think that any of my queries are that expensive. I think the problem is that there are so many of them. Is there any way that you can tell a program that it can only use a certain percentage of the cpu? Something like nice on unix..
Originally posted by Tomas Nilson: Is there any way that you can tell a program that it can only use a certain percentage of the cpu? Something like nice on unix..
While I doubt this will have much effect (but that's just be being cynical about Windows), you can set a processes priority in the Task Manager. Switch to the "Processes" tab, right click the mysql.exe process, and select "Priority : Low".
Another option would be to sprinkle Thread.sleep() calls in your Java program. The problem, sadly, is that since your machine is so fast, both MySQL and Java are able to slam the processor. Normally disk or network I/O would be the bottleneck, which doesn't tax the processor while it's waiting, leaving it free to handle the UI and other tasks.
Are you donig queries in a tight loop? That's a perfect place for sleeping.
Paul Santa Maria
Joined: Feb 24, 2004
You should definitely try to understand the underlying problem better, first.
Please do yourself a favor and see if you can understand what's happening at the SQL level. Definitely identify the offending query (if possible), and definitely look at the mySQL "EXPLAIN" for that query.
Along the way, look at any mySQL functions you might be calling as part of those queries (for example, unnecessarily expensive string encryption/decryption).