Limit CPU Usage

[ZiaTioN]

As far as I know there has been no way, in the past, to limit cpu usage. I am wondering if anyone knows if this has chaged? I want to limit the amount of processor % a user or application can use. I do not want to restrict cpu "time" for numerous reasons but mainly because this is not what I am looking for. Limiting cpu time will still allow a process to chew up 100% of the cpu, but only for a set period of time instead of indefinately.

I think BSD has this capability but am not sure if Linux has come around yet.

[Void Main]

Huh? Do you mean "nice"?

[ZiaTioN]

Well as far as I have understood, "nice" simply gives priority assignments. Am I wrong in thinking this? And if not, how does assigning a priority level limit cpu usage to a certain %?

I may need to read the man pages again.

[Void Main]

I can't possibly imagine why anyone would want to limit CPU usage to a certain %. If no other processes are using the CPU why would you not want your process to get 100% of the CPU time? Priority is the only thing that makes sense to me. That is, you don't want that particular app to get the CPU over some other app. I may very well be missing something though. Maybe you can provide more details on why you would want this regarding a particular application.

[ZiaTioN]

If no other processes are using the CPU why would you not want your process to get 100% of the CPU time?

You are assuming that I have no other processes wanting cpu resources.

Why I would want this is due to the fact that I have a process that runs 24/7 and makes anywhere from 5 to 10 (on average) database calls to my MySQL database per second. These calls range from INSERT to UPDATE to DELETE to SELECT and so on. Due to this load, the mysql database can chew up 99% of the cpu for an extended period of time which in turn lags my apache webserver along with numerous other applications I am running (dns, postfix, ssh, etc..).

The majority of the calls are UPDATES however and I have already implement LOW_PRIORITY calls in the UPDATE call but this has only helped some. However I am not even sure if limiting mysql will help my main site because it is a front end to a database search site which searches the MySQL database so I am not sure what I can do to illeviate the lag.

[Void Main]

Setting the nice levels is supposed to do what you want, however I'm betting much of your database usage is in IO Wait. Have you optimized your queries and made sure you have indexes set up right to minimize the needed resources? Proper indexing can turn a lagged down system into a speed demon. Is there any swapping going on?

[ZiaTioN]

No, there is no memory swapping going on really (I think this is what you meant). I am only using about 50% of my memory.

As for optimizing my queiries, I have done as much as I can I think. What type of optimizing are you refering to? Also what is "proper indexing"?

An average UPDATE call would look like:

UPDATE LOW_PRIORITY BOT_STATS SET CHANNEL = 'IRON-XDCC',
BOT = '[Xdcc]-22618-[Xdcc]',
SPEED = '149.1KB/s',
LAST_SEEN = '1139798429',
NETWORK = 'irc.digipimps.com' WHERE NETWORK = 'irc.digipimps.com' AND CHANNEL = 'IRON-XDCC' AND BOT = '[Xdcc]-22618-[Xdcc]'

[Void Main]

Properly configuring the mysql server for buffer sizes etc can make a big difference (/etc/my.cnf settings). Proper column indexes can make a HUGE difference on selects however they can make inserts and updates very slow (unless you lock the table while you do batch updates or inserts. The indexes will not get updated until the end of the batch). You also might want to run "optimize table" periodically to remove the overhead.

Optimizing isn't the easiest thing to do and will take a lot of research but you really can improve your query speed a lot the more you work at it. Here is a good place to start learning about some of the concepts and basic pointers:

http://dev.mysql.com/tech-resources/pre ... -20000719/

[ZiaTioN]

I did change the buffer sizes so I hope that will help, thanks. Everything else I have already done, or decided against (indexes). I decided against indexes for the very reason you stated. Most of my database calls are updates and indexes can slow these down. I also do optimize the tables regularly.

I will contunue to try and optimize the database but would still like to limit the cpu usage allowed.

[Void Main]

You could run your database on another machine, or get a beefier one if you have optimized everything you can. nicing processes are the only other thing you can do other than putting delays in your database application or rewriting it for more efficient updates.

Use of the EXPLAIN command might help (query optimizer) and look at the comments here:

http://dev.mysql.com/doc/refman/4.1/en/ ... speed.html
http://dev.mysql.com/doc/refman/5.0/en/ ... speed.html

I personally would think that an index on the columns network, channel, and bot should increase the speed significantly as an update should be much like a select. Use the EXPLAIN command on your queries and see what comes up.

[ZiaTioN]

Well the machine I run it on now is an AMD 3500+ 64 bit with 1 gig of PC2700 DDR RAM so I think I have plenty of hardware to push the site. I did think about splitting up the database from the website by using a second machine because I actually have one I could use but then thought that it may not make that much difference. If the spider app is still hammering the database on the second machine and the front end goes to query that same database for a search I will be stuck in the same situation don't you think?

I will look into the explain call but may just try nicing it to see if that will help. What nice rating would be best? It currently is at a 0 and I set it to a +5 earlier for a bit but did not notice much. I just niced the user mysql instead of nicing the app. Should I nice the app instead?

[Void Main]

As long as the processes that are hogging the resources are set to a lower priority then that is all you can do. I don't think you'll gain a lot there though. Getting things indexed properly and having optimal queries will gain you the most. How many updates per minute are occuring and what is the size of the database (number of rows, columns, rowlength, etc)? Another thing that improves speed greatly is to reduce the size of your rows and use numeric data types for numbers rather than char/varchar (for example). The smaller you can get your rows and the more binary you can get it the better. For example, you only need 4 byte integers to store IP addresses and searching on those as integers are a lot faster than by char/varchar. Many things like that can have a significant improvement on performance. I would suggest getting on a good database forum and asking for some optimization help. I have a feeling there is a lot that you can do.

[ZiaTioN]

I personally would think that an index on the columns network, channel, and bot should increase the speed significantly as an update should be much like a select. Use the EXPLAIN command on your queries and see what comes up.

All those columns are of type "text" and therefore can not be indexed.

The database has 11 tables with 218,000 records currently. The size is only 22.2 MB as of now. I may try adding DELAYED or even LOW_PRIORITY to the INSERT calls too. Would you think using REPLACE would be faster than UPDATE?

[Void Main]

That might just be your problem. Why are all your columns type text? At worst the columns I see you mention might be better suited as varchar. That's really a tiny database. I have several MySQL databases here at work running 20-30 million rows in just a few days that are several gig. I can let you in on a little secret that might help some. Install phpMyAdmin and there is an option in there to analyze your tables and tell you what your optimum column type and sizes should be. It is a good base to work from. I think getting appropriate table types, column times and sizes, and proper indexes will help you more than anything else that has been discussed, by far. I have had queries that take 10 minutes to complete that after appropriate indexes have been created taken less than 1 second to complete.

[ZiaTioN]

Nice!

I know my db is small, which is why I was suprised by the resource usage. I used text because of the changing types of characters that could be in any of these columns along with the changing size or length of the data. I did not think varchar could handle this.

I have used phpMyAdmin for years but have never seen this option. I am off to find it and see what it tells me.