mysql server load

[ephlox]

Hi,
My server load is going high again n again after every few days. it was using 98%



/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.evirtualservers.com.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

Cause of server load i have written these two lines in httpd.conf still have server load problem.
RLimitMEM 203241813
RLimitCPU 240



How i can fix this ? Your help will be appreciated thanks.

Best Regards from,
Farrukh

 

[jollymon]

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.

 

[mohit]

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

 

[celliott]

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

 

[ephlox]

Sql Problem

Hi,
I got this from WHM "Show Current CPU Usage " , No databases are not too many. there are only few databases and also those are not heavy.

I think i am heaving load cause of this website "http://www.pakheaven.com" i say "Apache status" there are many threads for his websites. And those are heavey cause people are downloading songs froms his website.


How i can limit his website resources ?

I already added as global,
RLimitMEM 203241813
RLimitCPU 240


I have 2.8GHz with 1 GB RAM.

Your help will be appreciated thanks.

Regards,
Farrukh

 

[celliott]

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?

 

[ephlox]

Hi

Hi,
No . i didn't install any cache software for PHP. Where i can get "Eaccellerator" and how i can install it? please tell me same as installation procedure of "mytop" .

I checked there is no cronjobs are running in background.

Regards,
Farrukh

 

[jollymon]

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.

 

[celliott]

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.

 

[ephlox]

MySql

Hi,
People are downloading mp3 songs. he is not providing streaming,


Srv PID Acc M CPU SS Req Conn Child Slot Host VHost Request
0-3 9691 0/10757/16826 W 184.16 149 0 0.0 166.45 236.69 203.101.174.122 www.pakheaven.com GET /Songs/Pakistani_Drama/Tum-Kaisi-Muhabbat-Karte-Ho(PakHeave



PHP maximum file upload size is 32 MB. But nobody is uploading file by CMS.

Regards ,
Farrukh

 

[ephlox]

Mysql

Hi,
Thanks for help, I have installed eaccelerator-0.9.4. How to check php-chache is loading /caching files? i checked by ls command
there is no file. Also i have restarted https service.


Regards ,
Farrukh

 

[jollymon]

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!

 

[celliott]

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

 

[nwilkens]

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

 

[ephlox]

mysql

Hi,
I typed php -v and i got this,

php -v
PHP 4.4.3 (cli) (built: Aug 23 2006 09:31:13)
Copyright (c) 1997-2006 The PHP Group
Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies
with Zend Extension Manager v1.0.10, Copyright (c) 2003-2006, by Zend Technologies
with Zend Optimizer v3.0.0, Copyright (c) 1998-2006, by Zend Technologies



i think eaccelerator is not working. i did everything as you said. i also edited php file. but no idea why it is not working. Do you have any idea ?


Regards from,
Farrukh

 

[carluk]

Also worth doing:

mysqladmin processlist

 

[dafut]

i think eaccelerator is not working. i did everything as you said. i also edited php file. but no idea why it is not working. Do you have any idea ?


Regards from,
Farrukh

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.

 

[Manuel_accu]

Hi,

Also you can go for MySQL performance Activity Monitor

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

Thx

 

[AndyReed]

i think eaccelerator is not working. i did everything as you said. i also edited php file. but no idea why it is not working. Do you have any idea ?

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.

 

[d-woo]

I installed mytop

When I run the command in SSH I get

[email protected] [~]# 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'