I'm very familiar with the script you've noted above. I've ran it and tweaked mysql accordingly. We now need to leave mysql up/running for 24-48 hours before running it again.
The load on your server is not being caused by mysql, it's being caused by PHP. Specifically the index.php running under user rho delta. I'm not familiar with XenForo, but I recommend you looking into a page caching option within this software. If a page cache is not available in the software there may be a plugin which you can install.
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 21541 mike 16 0 102m 30m 15m R 10.9 1.5 0:00.83 /usr/bin/php /home/mike/public_html/index.php 21653 mike 17 0 100m 28m 14m R 9.7 1.4 0:00.48 /usr/bin/php /home/mike/public_html/index.php 21761 mike 20 0 99608 22m 12m R 9.7 1.1 0:00.23 /usr/bin/php /home/mike/public_html/index.php 21724 sorority 16 0 98840 20m 11m R 7.9 1.0 0:00.18 /usr/bin/php /home/sorority/public_html/index.php 21812 mike 18 0 95836 15m 8904 R 7.9 0.7 0:00.13 /usr/bin/php /home/mike/public_html/index.php 21773 mike 17 0 97504 18m 10m R 7.3 0.9 0:00.17 /usr/bin/php /home/mike/public_html/index.php 21536 mike 20 0 100m 28m 14m R 3.0 1.4 0:00.52 /usr/bin/php /home/mike/public_html/index.php 21559 mike 18 0 98.1m 22m 10m R 3.0 1.1 0:00.35 /usr/bin/php /home/mike/public_html/index.php 21756 mike 16 0 99096 21m 11m R 3.0 1.0 0:00.22 /usr/bin/php /home/mike/public_html/index.php 21650 mike 18 0 99892 22m 11m R 1.8 1.1 0:00.24 /usr/bin/php /home/mike/public_html/index.php 21711 root 17 0 32900 14m 7636 R 1.2 0.7 0:00.05 /usr/local/cpanel/whostmgr/bin/whostmgr roothtml
I've disabled APC on your server as you are running php as suphp which is not compatible with APC. Having it enabled causes additional resource usage which can now go elsewhere.
Every single page you see on your site is ultimately being included from index.php. (Think about URLs when Full Friendly URLs aren't enabled). So resource usage on it is probably not too out of the ordinary.
So with that in mind is it still using too many resources?
Here are a few things to do, or rather ask Servint guy to do (or hire someone)
- Switch to mod_fcgid for php while having apache run as prefork. This will allow you to use APC as well as use the benefits of fcgid
- Remove unnecessary modules from Apache. If you have WHM you can do this from Easy Apache config
- Configure Apache conf file and tweak settings. Specially configure your Max Servers limit depending on your apache memory usage
- Use caching with Xenforo. Using APC as the cache backend is desirable. If you are low on ram, instead use filecache with Xenforo
- There might be an addon which may be not be coded properly. Once you have done the above, disable addons one by one and note server load.
The above steps, if done properly, will significantly reduce your server's load. If the load is still high, then it means your RAM and CPU power is not sufficient for your traffic levels.