How to Control CPU usage

[kfmfe04]

I've noticed recently that mysqld seems to be able use 20-50% CPU - how is it doing that internally from a programming POV? Do you use sleep() or something else? I'm mostly interested in UNIX solutions.

Any ideas how to minimize CPU usage or throttle back a program?

[3hM2Nwbp]

From a horrible personal experience, be sure the database indices are set up correctly. If they aren't and you're dealing with a large data-set, then it's not uncommon to see 99-100% usage on any system you're running. (Yes, a contributor on a project I was working on 'fixed' the database without telling me and then I spent a few weeks debugging the CODE! Needless to say, I didn't find the problem, until yet another contributor found that the database structure had been messed with.)

To answer your question about what MySQL is doing, in my case it was an extremely inefficient search.

[kfmfe04]

ouch - do you have any suggestions on what to do about tables that have a lot of rows and just two columns? say, on the order of 1 billion rows? an int and a char[8]?

I want to put a unique index on that second char[8] but I have a feeling I'm going to regret it...

edit: you are right - the CPU should have been pinned to 100%! I gave up on the unique index build that took 10+ hours, which makes no sense at all when the primary key build took 10 minutes... going to try a larger key_buffer_size and sort_buffer_size setting

[3hM2Nwbp]

I'm afraid that I can't really be helpful when it comes to SQL and database management. It's beyond the scope of my knowledge at this point.

Knowing what you don't know is just as important as knowing what you do :P