Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

This is a relatively simple question, which hopefully has a simple answer. The almighty google isn't muchhelp on this one thus far.

I run a fairly active web hosting business. I have a client who is importing a somewhat large database (a vbulletin to ipb conversion ~ 4gb) which uses a PHP script to grab everything from the vbulletin database and slap it into the ipb software.

It essentially cripples my server while it's running.

Now I'd like ot be a good host and let him do this, but not at the consequence of the other users on my box.

Any ideas?

share|improve this question
add comment

9 Answers 9

You can use [re]nice to adjust a process' priority - this won't "limit" things per-se but it will let other operations on the server continue to run. The command renice will change the priority of a running process From man renice "Useful priorities are: 20 (the affected processes will run only when nothing else in the system wants to), 0 (the ‘‘base’’ scheduling priority), anything negative (to make things go very fast)."

renice N PID

eg: renice +5 987 this will make process 987 less favorable to scheduling.

Edit: I wouldn't renice your mysql server process as that will certainly affect other users - look for the mysql client process and renice that instead.

share|improve this answer
    
Excellent suggestion. If you can work with the user to run the PHP script from an interactive prompt, you should easily be able to nice the process at launch or renice it when it starts. This will be much harder if he's calling it from a web page where it may appear to be your HTTP/MYSQL processes. –  Kevin Kuphal Aug 3 '09 at 4:57
add comment

If the resource in question is processor then try cpulimit. cpulimit is a simple program that attempts to limit the cpu usage of a process.

refer to

http://webupd8.blogspot.com/2009/05/limit-cpu-usage-by-process-linux.html

or

http://www.cyberciti.biz/faq/cpu-usage-limiter-for-linux/

Hope that helps

share|improve this answer
add comment

The command you are looking for is nice.

share|improve this answer
add comment

Can you not use per-account resource limiting to limit the number of connection the client can make, and therefore limit the system resources used. In MySQL 5.0, you can limit the following server resources for individual accounts:

  • The number of queries that an account can issue per hour
  • The number of updates that an account can issue per hour
  • The number of times an account can connect to the server per hour
  • The number of simultaneous connections to the server an account can have (as of MySQL 5.0.3)
share|improve this answer
add comment

"Nice" is good to know about as others have suggested.

But here's the thing: if you "nice" anything except the mysql server itself all inserts will be done at Mysql's priority, which is rather high.

For example, if a "nice" php script generates a massive INSERT statement in low-priority then sends it to the mysql server, the mysql server will execute that insert in its typical high-priority mode regardless of the "niceness" of the PHP script. This is because the Mysql server is a separate process with its own niceness and PRI.

On then other hand, if you "nice" the mysql server itself everybody's DB ops will go slower. Not good, as stated in other answers.

It seems to me the best solution is to ask this customer to set up his PHP script to do inserts in small bits (over a loop) and tell him to put sleep() statements inside the loop. Inserts will be full priority, but they will be manageable in size, and then the sleep statement should let other process run and give the database a chance to catch up.

If you're picky about RAM usage you can ask customer to "unset" unneeded variables just before the sleep statement.

If customer runs this script from the command line, "nicing" it in addition to the above is of course a good idea.

share|improve this answer
add comment

Nice is good for letting a process use only unused resources. If you actually want to limit it further than that, 'ulimit' for the web/db server's user might be what you're looking for.

Edit: this might not be such a good idea after all. I think setting a ulimit will result in processes being killed if they exceed the limits.

share|improve this answer
add comment

This may be something to consider for the future - virtualisation.

If you use something like OpenVZ or VServer, you can essentially put a cap on the amount of CPU time consumed by each virtual server. You could create a virtual server that merely runs MySQL and then cap that. Does not change much otherwise.

share|improve this answer
add comment

http://dev.mysql.com/doc/refman/5.0/en/user-resources.html have a look at this

share|improve this answer
    
Just posting a link is bad practice! –  Lucas Kauffman Jul 22 '12 at 18:31
add comment

sorry for late answer :)

Here is your solution: http://docs.redhat.com/docs/en-US/Red_Hat_Enterprise_Linux/6/html/Resource_Management_Guide/ch01.html

e.i. Use control groups feature (cgroups)

share|improve this answer
    
That really only applies to newer RHEL 6 systems. Given the age of this question, the original posted was probably on an earlier operating system that did not have the groups feature. –  ewwhite Dec 3 '11 at 20:33
    
There is no other way, kernel doesn't support it. Sending SIGSTOP and SIGCONT signals constantly (cpulimit) is a VERY BAD WAY. Virtualization might be a solution, but on old OS? Ineffective. –  GioMac Jun 13 '12 at 13:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.