2

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?

Improve this question

9 Answers 9

Reset to default
6

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.

Improve this answer
1
  • 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, 2009 at 4:57
2

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

Improve this answer
1

The command you are looking for is nice.

Improve this answer
1

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)
Improve this answer
1

"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.

Improve this answer
0

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.

Improve this answer
0

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.

Improve this answer
0

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

Improve this answer
1
  • Just posting a link is bad practice!
    – Lucas Kauffman
    Jul 22, 2012 at 18:31
-2

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)

Improve this answer
2
  • 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, 2011 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, 2012 at 13:17

You must log in to answer this question.