Very slow site , high server load

minitech · Nov 28, 2011

Hello
Recently my site start to be very slow , and Server load went very high.
Please help me solve this problem , its really effecting my site.
Thanks in advance.
and if you need any more information , please let me know and i will post.
BTW , i am very newbie at this stuff, so please explain me slowly.

Code:

Server load 	74.49 (4 CPUs) 	
Memory Used 	26.37% (546,920 of 2,074,012) 	
Swap Used 	        33.87% (1,398,340 of 4,128,664)

Code:

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.56
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 371M (Tables: 246)
[--] Data in InnoDB tables: 160K (Tables: 10)
[!!] Total fragmented tables: 47

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3h 27m 23s (1M q [139.913 qps], 31K conn, TX: 5B, RX: 199M)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.3G (68% of installed RAM)
[OK] Slow queries: 0% (505/1M)
[OK] Highest usage of available connections: 50% (252/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/96.0M
[OK] Key buffer hit rate: 99.9% (87M cached / 87K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (38 temp sorts / 269K sorts)
[!!] Temporary tables created on disk: 38% (90K on disk / 237K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 8K opened)
[OK] Open file limit used: 3% (95/2K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 160.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)

My server:

Code:

Processor Information
Total processors: 4
Processor #1
    Vendor
        GenuineIntel
    Name
        Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
    Speed
        2133.000 MHz
    Cache
        4096 KB
Processor #2
    Vendor
        GenuineIntel
    Name
        Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
    Speed
        2133.000 MHz
    Cache
        4096 KB
Processor #3
    Vendor
        GenuineIntel
    Name
        Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
   Speed
        1600.000 MHz
    Cache
        4096 KB
Processor #4
    Vendor
        GenuineIntel
    Name
        Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
    Speed
        1600.000 MHz
    Cache
        4096 KB

minitech · Nov 28, 2011

Please help , mysql is killing my site

11468 mysql 15 0 121m 23m 3848 S 132.2 1.2 1:23.70 mysqld

minitech · Nov 29, 2011

Please , Someone?

dalem · Nov 29, 2011

run top in the command line to see what process is eating up the cpu

ES - George · Nov 30, 2011

Can you run the following command line:

top -s -c

That will basically list the processes that running and the file path to them. You'll also see the CPU usage from them.

minitech · Nov 30, 2011

Thanks for replay.
here you go with what its shown me.

2857 mysql 15 0 161m 39m 3644 S 115.8 1.9 1560:03 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/server.minitech.ws.err --pid-file=/var/lib/mysql/server.minitech.ws.pid
31724 minitech 16 0 30656 20m 5100 R 13.5 1.0 0:00.41 /usr/bin/php /home/minitech/public_html/index.php
31744 minitech 16 0 30428 20m 5084 R 13.2 1.0 0:00.40 /usr/bin/php /home/minitech/public_html/index.php
31678 minitech 17 0 30428 20m 5084 R 9.5 1.0 0:00.39 /usr/bin/php /home/minitech/public_html/index.php
31684 minitech 17 0 29908 20m 5080 R 7.6 1.0 0:00.24 /usr/bin/php /home/minitech/public_html/index.php
31715 minitech 19 0 29092 18m 4860 R 6.3 0.9 0:00.19 /usr/bin/php /home/minitech/public_html/index.php
31738 minitech 18 0 27748 17m 4832 R 5.6 0.9 0:00.17 /usr/bin/php /home/minitech/public_html/index.php
31750 minitech 15 0 28020 17m 4848 S 5.6 0.9 0:00.17 /usr/bin/php /home/minitech/public_html/index.php
31755 minitech 16 0 26452 16m 4800 R 4.9 0.8 0:00.15 /usr/bin/php /home/minitech/public_html/index.php
31752 minitech 16 0 25908 15m 4752 R 4.6 0.8 0:00.14 /usr/bin/php /home/minitech/public_html/index.php
31754 minitech 16 0 25640 15m 4736 R 4.3 0.7 0:00.13 /usr/bin/php /home/minitech/public_html/index.php
31667 minitech 17 0 30164 20m 5092 R 3.3 1.0 0:00.31 /usr/bin/php /home/minitech/public_html/index.php
31694 minitech 20 0 22212 12m 4752 R 3.0 0.6 0:00.09 /usr/bin/php /home/minitech/public_html/index.php
31742 minitech 19 0 19804 9.8m 4736 R 1.6 0.5 0:00.05 /usr/bin/php /home/minitech/public_html/index.php
31731 minitech 17 0 18240 8472 4752 R 1.3 0.4 0:00.04 /usr/bin/php /home/minitech/public_html/index.php

storminternet · Nov 30, 2011

Hi,

Though MySQL is consuming high cpu resources most of the processes were run by minitech. Perhaps it's databases might have consumed cpu resources. You need to optimize it asap

minitech · Nov 30, 2011

Hi storminternet
I did optimize it , via ssh , few times the last days, but still , nothing changed.
please help....

pixeline · Dec 4, 2011

In your my.cnf file, i'd try upping the query_cache

Code:

query_cache_limit=4M
query_cache_size=128M
query_cache_type=1

Please post your my.cnf for further suggestions.

minitech · Dec 8, 2011

[mysqld]
set-variable = max_connections=500
safe-show-database

ruzbehraja · Dec 9, 2011

Reduce
max_connections to 200
wait_timeout = 250

In SSH type in
mysqladmin proc stat

Paste the output here.

minitech · Dec 9, 2011

minitech · Dec 9, 2011

i tried to restart mysql and now i get:
Starting MySQL......Manager of pid-file quit without updati[FAILED]
and my site is down
please help

EDIT: nm. fixed it.

minitech · Dec 13, 2011

please help someone?

alphawolf50 · Dec 14, 2011

It appears to me that your system is swapping, which kills performance. Please post the results of this command to confirm:

Code:

vmstat 5 5

Also, please post your entire my.cnf file. This is an easy way to see it:

Code:

cat /etc/my.cnf

I'd also like to see "top" one more time, with all the info at the head this time.

Code:

top -n1

If you could please post all the info inside of CODE tags like you did for your first post, that makes the data much much easier to read.

faisikhan · Dec 14, 2011

Hi,

I have also experienced the same problem before many times just because of MySQL daemon consuming too much memory and it can be due to one of the following reasons:
1. If there are huge databases involved then their population into the memory consumes much of the CPU resources and that is why website goes totally down.
2. Check the hard disk space as well, it can be one of the reasons for MySQL to be failed while restarting.
3. If swapping is involved then it is killing the memory resources of the server badly.

Solution:
1. Take the backup of the databases first, remove the unwanted ones and use indexing to reduce the memory consumption.
2. Also check the server logs via tail -f /var/log/messages and I hope it will help you a lot to get the root cause of the issue.
3. Make some free hard disk space and then restart the daemon but I don't think it may resolve the problem permanently.
4.34616925 Slow queries, that is major problem so try to fix it using Indexing or please Google it.
5. Try to extend the RAM of the server.

faisikhan · Dec 14, 2011

Hi,

One more thing that if you do not succeed after doing all these steps, then last thing is Rebooting the server

minitech · Dec 14, 2011

Hi alphawolf50
first of all thanks alot!
here the info:

Code:

[email protected] [~]# vmstat 5 5
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
16  0    112 784456  87952 858072    0    0     7    45  106  100 58  6 35  1  0
 2  0    112 830832  87968 847812    0    0     1   240 1405 4949 76  9 15  1  0
 3  0    112 836784  87980 847840    0    0     0    93 1446 3320 51  5 44  0  0
26  0    112 695300  87992 847896    0    0     0    98 1559 5723 86 10  5  0  0
 9  0    112 759904  88004 847916    0    0     0   210 1375 4952 91  9  0  0  0

Code:

cat /etc/my.cnf
[mysqld]
set-variable = max_connections=250
wait_timeout = 250
safe-show-database

Code:

 top -n1
top - 21:38:32 up 1 day,  3:30,  1 user,  load average: 11.00, 11.62, 11.53
Tasks: 326 total,   6 running, 316 sleeping,   0 stopped,   4 zombie
Cpu(s): 57.9%us,  6.2%sy,  0.1%ni, 35.1%id,  0.6%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:   2074012k total,  1256928k used,   817084k free,    88444k buffers
Swap:  4128664k total,      112k used,  4128552k free,   849036k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
28289 minitech  18   0 29916  19m 5080 R 50.6  1.0   0:00.26 php
 2858 mysql     15   0  164m  41m 4300 S 25.3  2.1   1109:17 mysqld
28291 minitech  18   0 22212  12m 4736 R 19.5  0.6   0:00.10 php
28292 minitech  24   0 19804   9m 4748 R  9.7  0.5   0:00.05 php
22765 nobody    17   0  9224 2808 1336 S  1.9  0.1   0:00.04 httpd
28288 root      15   0  2572 1060  704 R  1.9  0.1   0:00.01 top
    1 root      15   0  2160  676  584 S  0.0  0.0   0:01.19 init
    2 root      RT  -5     0    0    0 S  0.0  0.0   0:17.81 migration/0
    3 root      34  19     0    0    0 S  0.0  0.0   0:00.12 ksoftirqd/0
    4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0
    5 root      RT  -5     0    0    0 S  0.0  0.0   0:00.37 migration/1
    6 root      34  19     0    0    0 S  0.0  0.0   0:00.07 ksoftirqd/1
    7 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/1
    8 root      RT  -5     0    0    0 S  0.0  0.0   0:02.78 migration/2
    9 root      34  19     0    0    0 S  0.0  0.0   0:03.38 ksoftirqd/2
   10 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/2
   11 root      RT  -5     0    0    0 S  0.0  0.0   0:00.90 migration/3

alphawolf50 · Dec 14, 2011

Hi minitech,

The info you posted does not show any active swapping, so that is probably not your primary issue. The one thing that stands out is there is a PHP script using 50% of one CPU... that seems excessive for the typical PHP script. What kind of site are you running under user minitech? Blog/forum/e-commerce/reporting...?

Do you know which MPM you're using for Apache?
Which PHP are you running, suPHP, fcgid, ...?
Are you using a PHP accelerator like eAccelerator or APC?

Your my.cnf file definitely needs some work, but I noticed the mysqltuner output you provided earlier was only for 3 hours of operation. Could you provide the output of mysqltuner again, now that mysql has been running for over 24 hours? If it has been running less than a day, please wait until after 24 hours to provide this output.

Thanks for providing everything in CODE tags, that was MUCH easier to read

minitech · Dec 15, 2011

Hello Thanks again.
I'm running a blog , with 10000 daily users. its a WordPress.

How can i check all this? (sorry for being newbie)

Code:

Do you know which MPM you're using for Apache?
Which PHP are you running, suPHP, fcgid, ...?
Are you using a PHP accelerator like eAccelerator or APC?

Code:

[email protected] [~]# perl mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.56
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 399M (Tables: 246)
[--] Data in InnoDB tables: 160K (Tables: 10)
[!!] Total fragmented tables: 24

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 4h 24m 15s (47M q [253.249 qps], 831K conn, TX: 147B, RX: 5B)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 34.0M global + 2.7M per thread (250 max threads)
[OK] Maximum possible memory usage: 705.9M (34% of installed RAM)
[OK] Slow queries: 0% (253/47M)
[OK] Highest usage of available connections: 34% (85/250)
[OK] Key buffer size / total MyISAM indexes: 8.0M/95.1M
[OK] Key buffer hit rate: 100.0% (2B cached / 210K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 7M sorts)
[!!] Temporary tables created on disk: 37% (2M on disk / 6M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 6K opened)
[OK] Open file limit used: 7% (98/1K)
[OK] Table locks acquired immediately: 99% (50M immediate / 50M locks)
[OK] InnoDB data size / buffer pool: 160.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)

Thread starter	Similar threads	Forum	Replies	Date
V	Site loading very slow	Workarounds and Optimization	2	Jun 17, 2018
Y	Site very slow, High TTFB, what happened?	Workarounds and Optimization	3	Aug 11, 2017
T	client Web site very slow	Workarounds and Optimization	4	Nov 7, 2015
	Site very slow [wait time over 5 sec]	Workarounds and Optimization	2	Mar 25, 2015
E	Very Slow site 99% CPU consumed via PHP	Workarounds and Optimization	4	Jan 31, 2012

Very slow site , high server load

Active Member

Active Member

Active Member

Well-Known Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Registered

Active Member

Well-Known Member

Active Member

Active Member

Active Member

Well-Known Member

Well-Known Member

Well-Known Member

Active Member

Well-Known Member

Active Member