[Users] VE affecting all server node.

[Gregor at HostGIS]

> How can I limit this VPS to avoid the high load caused by the 
> MySQL query.

That would depend on whether it's IO or CPU that's the scarce resource.

If it's CPU load, try the --cpuunits param to vzctl. This gives the VE a 
"priority" when the CPU is otherwise maxed out. Example: Give it a 
weight of 500, versus the default 1000, and when VPSs fight over CPU 
time that one will lose.

For ourselves, we've often noticed that CPUs are rarely the bottleneck 
these days, that disk IO is usually the scarcest resource. Check your 
"top" output and see whether the CPU is showing no idle%. If the idle% 
is nice and high, then it's likely not your CPUs.

To diagnose what's sucking up all the disk throughput, we use "atop -dl 
60" to narrow down which processes are consuming the most disk IO, then 
"ionice" to deprioritize processes as appropriate.

Example:
atop -dl 60
# notice that the mysqld process (12345) is doing 75% of the IO
ionice -c2 -n5 -p 12345

Note that you can't use ionice from a VE, only from the HN.

Hope that helps, or at least points the right direction.

-- 
HostGIS, Open Source solutions for the global GIS community
Greg Allensworth - SysAdmin, Programmer, GIS Person, Security
    Network+   Server+   A+   Security+   Linux+
    PHP   PostgreSQL   MySQL   DHTML/JavaScript/AJAX

"No one cares if you can back up — only if you can recover."