mysql server load

monitor it with top-i from ssh for abit and see if this is temp spike or if it is a constant loads. It looks like you are looking at the whm page that logs the top processes, which is a crap page. How many dbs are you running? If it is in like several 100's and they are fairly large and have verbose traffic then yea, your going to peak out your sql. Also cehck to see if your sql is updated and are you running suexec?

Did this happen when you were installing, or transferring new databases? Load gets high when a few long lasting sql querries run at the same time on 1 server. Better hardware if thats the case.

 

do a search on cpanel forums

hi,
you can search the forums for high memory or high cpu usage, you'll find a lot of topics where users were able to track it down.

just to name a few
http://forums.cpanel.net/showthread.php?t=56600

and

http://forums.cpanel.net/showthread.php?t=49110


do a search you'll get many of them.

see ya,
mohit

 

Hi,

I reccomend you install MyTop so you can easily monitor MySQL to find out which users are causing the high load.

wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.4.tar.gz
tar -zxf mytop-1.4.tar.gz
cd mytop*
perl Makefile.PL
make test
make
make install

Then just enter mytop in SSH and it should open.

High load is often caused by outdated scripts, therefore it may be worth checking your accounts and the script versions in there. As a bare minimum, ensure that any cPaddon and Fantastico installed scripts are updated.

Chris

 

Hi,

Its still worth installing MyTop as its an invaluable tool to monitor load.

As his site is mostly script driven, have you considered installing some PHP caching software? Eaccellerator is very good and can be used in conjunction with Zend as an extension. This can drop load quite consierably and speed up loading times.

Downloading files through Apache shouldnt cause your CPU usage to raise as high as 98%, have you checked for any possible Cronjobs etc running in the background?

 

First i would find out WHAT kind of songs people are downloading off his website, if ya know what I mean Next i would look for how they are downloading these songs. Are they downloading them from hrefs or is it some wierd php based player or some streming thing? those are rather important items to consider.

Also just FYI i would talk to this guy first off about server resources and how he is using them. If you just change things then you will get the angry customer syndrome and you dont want that now do ya? Check your max sizes for php also bro and if ppl are uploading via a script or some CMS lots of huge files then talk to him about what your going to have to do.

 

MyTop installation procudure as shown in previous post:

Eaccellerator:

If you don't already have Zend Optimizer installed, run /scripts/installzendopt and follow the intructions.

wget http://easynews.dl.sourceforge.net/sourceforge/eaccelerator/eaccelerator-0.9.4.tar.bz2
tar jvfx eaccelerator-0.9.4.tar.bz2
cd eaccelerator-0.9.4
/usr/local/bin/phpize
./configure --enable-eaccelerator=shared --with-php-config=/usr/local/bin/php-config
make
make install
mv /usr/local/lib/php/extensions/no-debug-non-zts-*/eaccelerator.so /usr/local/lib/php/extensions


edit /usr/local/lib/php.ini and add the following to the bottom. You may want to create a backup of php.ini first.:


;;eAccelerator
extension="eaccelerator.so"
eaccelerator.shm_size="16"
eaccelerator.cache_dir="/php-cache"
eaccelerator.enable="1"
eaccelerator.optimizer="1"
eaccelerator.check_mtime="1"
eaccelerator.debug="0"
eaccelerator.filter=""
eaccelerator.shm_max="0"
eaccelerator.shm_ttl="0"
eaccelerator.shm_prune_period="0"
eaccelerator.shm_only="0"
eaccelerator.compress="0"


create /php-cache


mkdir /php-cache; chmod 0777 /php-cache

This should work fine and has been tested with PHP 4.4.4. If however it does not work, you can revert to the backup of php.ini which you made earlier.

Eaccellerator will cache frequently used php files in /php-cache. Its advisable to clear this folder every few weeks or so as it will build up and can use a lot of disk space. However once installed, it can greatly improve performance.

Chris.

 

Well I dont usually bother checking out the sites, and this is a prime example of why . Some people should lay off the scripts. I was just wondering if they were like music mp3's, however they are some wierd stuff so who cares. Yes the site is as well as visually distracting also a mess of code patchwork. Who knows man, who knows. Check your top, or mytop, often and early. Also think back to your TOS in some instances. Have fun man and many prosperous moons!

 

If you do "php -v" in SSH you should be able to see whether or not eaccelerator is running.

 

By default, there may be no limit on the number of times that a client can connect to the server or the number of queries it can issue. GRANT can establish limits on an account's resource consumption for the following characteristics:

- The number of times per hour the account is allowed to connect to the server
- The number of queries per hour the account is allowed to issue
- The number of updates per hour the account is allowed to issue

The following example creates an account that can use the test database, but can
connect to the server a maximum of only 200 times per hour. The account can issue 500 queries per hour, and at
most 100 of those queries can modify data:

GRANT ALL ON test.* TO 'username'@'hostname'
IDENTIFIED BY 'SomePass'
WITH MAX_CONNECTIONS_PER_HOUR 200
MAX_QUERIES_PER_HOUR 500
MAX_UPDATES_PER_HOUR 100;

Or this same information can be changed in phpmyadmin.

This gives you an idea of how you could potentially limit this client (and future) from overwhelming your site.

Also reference http://dev.mysql.com/doc/refman/4.1/en/user-resources.html

Most likely he has poor coding [and / or requires proper indexes], which is causing mysql to query/update without need.

Hope this helps,
Nick

 

Also worth doing:

Code:

mysqladmin processlist

 

Look for the error_log in the directory where you ran the eaccelerator install script. That should give a clue.

Also, I've found the mysqlreport script to give me very good info re finetuning mysql. See http://hackmysql.com for more tips on mysql optimization.

 

Hi,

Also you can go for MySQL performance Activity Monitor

http://forums.linuxwebadmin.info/index.php/topic,103.0.html

Thx

 

If Phpsuexec is activated on your server, eAccelerator might not work. As you may know, Phpsuexec forces Php to run as a CGI. Unless things changed, all known eAcceleration products work with Php running as a module in Apache.

 

I installed mytop

When I run the command in SSH I get

Code:

root@srv1 [~]# mytop
Cannot connect to MySQL server. Please check the:

  * database you specified "test" (default is "test")
  * username you specified "root" (default is "root")
  * password you specified "" (default is "")
  * hostname you specified "localhost" (default is "localhost")
  * port you specified "3306" (default is 3306)
  * socket you specified "" (default is "")

The options my be specified on the command-line or in a ~/.mytop
config file. See the manual (perldoc mytop) for details.

Here's the exact error from DBI. It might help you debug:

Unknown database 'test'