0

I am running a high traffic websites on a Dedicated-Virtual 24GB RAM CentOS server hosted by the planet.

Very often the server dies because of high CPU usage (%1500)

When i check the running processes using "top" command i see a hight load average more than 100 always and cpu mysql often more than 1500%

I have tried to tune mysql, tune apache, my.cnf, httpd.cnf, removed Apache modules that i do not use but that does not solve the problem.

Please let me know what values do you need to know in my server config in order to help me diagnosis the problem.

Thanks in advance.

    Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                16
On-line CPU(s) list:   0-15
Thread(s) per core:    2
Core(s) per socket:    4
Socket(s):             2
NUMA node(s):          2
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 44
Stepping:              2
CPU MHz:               2400.093
BogoMIPS:              4799.88
Virtualization:        VT-x
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              12288K
NUMA node0 CPU(s):     0-3,8-11
NUMA node1 CPU(s):     4-7,12-15


top - 05:37:00 up 4 days, 22:04,  2 users,  load average: 136.61, 145.42, 139.03
Tasks: 565 total,   8 running, 554 sleeping,   0 stopped,   3 zombie
Cpu(s): 28.6%us, 70.6%sy,  0.1%ni,  0.2%id,  0.0%wa,  0.0%hi,  0.5%si,  0.0%st
Mem:  24596732k total, 13172200k used, 11424532k free,   630632k buffers
Swap:  2097144k total,        0k used,  2097144k free,  7648348k cached
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
  4850 mysql     15  -5 12.4g 1.1g 6036 S 1545.1  4.7  12992:15 mysqld
  15846 root      20   0  162m  22m  916 R  4.2  0.1   0:00.62 lfd
  15509 apache    20   0  632m 129m 5460 S  3.0  0.5   0:01.59 httpd
  15276 apache    20   0  632m 128m 5504 R  2.7  0.5   0:01.85 httpd
  15505 apache    20   0  634m 130m 5460 R  2.1  0.5   0:01.49 httpd
  15792 apache    20   0  630m 125m 4816 S  2.1  0.5   0:00.43 httpd
  3058 nginx     20   0 76576  34m 2268 S  1.8  0.1 107:48.32 nginx
  15585 apache    20   0  632m 128m 5268 S  1.8  0.5   0:01.25 httpd
  15827 apache    20   0  629m 124m 3752 S  1.8  0.5   0:00.10 httpd


cat /proc/meminfo
MemTotal:       24596732 kB
MemFree:        13807688 kB
Buffers:         1108524 kB
Cached:          4888972 kB
SwapCached:            0 kB
Active:          4929640 kB
Inactive:        3225108 kB
Active(anon):    1523440 kB
Inactive(anon):   635484 kB
Active(file):    3406200 kB
Inactive(file):  2589624 kB
Unevictable:           0 kB
Mlocked:               0 kB
SwapTotal:       2097144 kB
SwapFree:        2097144 kB
Dirty:             16032 kB
Writeback:             0 kB
AnonPages:       2114752 kB
Mapped:            52792 kB
Shmem:              1648 kB
Slab:            2155004 kB
SReclaimable:    2028484 kB
SUnreclaim:       126520 kB
KernelStack:        5184 kB
PageTables:       105004 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    14395508 kB
Committed_AS:   16484584 kB
VmallocTotal:   34359738367 kB
VmallocUsed:      463816 kB
VmallocChunk:   34345823536 kB
HardwareCorrupted:     0 kB
AnonHugePages:    800768 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:        5696 kB
DirectMap2M:     2082816 kB
DirectMap1G:    23068672 kB


my.cnf
[mysqld]
tmpdir=/var/tmpfs
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#general_log=1
slow_query_log= 0
long_query_time =9
log_queries_not_using_indexes =0
max_connections = 400
key_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 2048M
max_heap_table_size = 600M
tmp_table_size = 600M
read_buffer_size = 320M
sort_buffer_size = 320M
join_buffer_size = 3G
table_definition_cache = 8000
table_open_cache = 220000
thread_cache_size = 384
wait_timeout = 50
interactive_timeout = 30
connect_timeout = 10
max_allowed_packet = 265M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 256M
query_cache_size = 200M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
range_alloc_block_size = 4096
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
open_files_limit = 3G
max_write_lock_count = 8
innodb_open_files = 500
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 60M
innodb_additional_mem_pool_size = 32M
innodb_log_files_in_group = 2
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0



trafic shown by phpmyadmin for just one hour and half
Trafic réseau depuis le démarrage : 123 Gio

Ce serveur MySQL fonctionne depuis 0 jours, 1 heures, 33 minutes et 58 secondes. Il a démarré le Jeu 28 Janvier 2016 à 13:21.
Trafic  ø par heure
Reçu    445 Mio 284,2 Mio
Envoyé  122,6 Gio   78,3 Gio
Total   123 Gio 78,6 Gio
Connexions  ø par heure %
max. de connexions simultanées  170 ---     ---
Tentatives échouées 3 253   2 077,12    0,55%
Arrêts prématurés   306     195,39  0,05%
Total   591 k   377,53 k    100,00%
  • What is the output of iostat? Is your MySQL using innodb tables at all? I see default table type 'MyISAM', but If innodb is used, then you need to look at your variables - and correct the invalid ones such as open_files_limit = 3G. innodb_buffer_pool_size = 60M is very low for a busy server. innodb_flush_log_at_trx_commit = 2 is a bad idea too if the server is crashing, you could be loosing data. There are tons of resources on MySQL tuning - here is one to start https://www.percona.com/blog/2014/01/28/10-mysql-settings-to-tune-after-installation/ – Gmck Jan 28 '16 at 14:57
  • avg-cpu: %user %nice %system %iowait %steal %idle 28.28 0.12 63.93 0.04 0.00 7.63 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 93.39 1457.66 1833.18 648275580 815283098 sdb 1.75 4.86 626.23 2159818 278509440 – user3437296 Jan 28 '16 at 17:06
  • database using only MyISAM – user3437296 Jan 28 '16 at 17:07
  • innodb_buffer_pool_size = 60M is small because we using only myisam not a table using innodb engine – user3437296 Jan 28 '16 at 17:08
  • I've changed innodb_buffer_pool_size to 15G and innodb_log_file_size=4G but still nothing changed in the hight load average and the high mysql cpu, I don't see the utilitity of changing innodb config, because I'm not using it ! – user3437296 Jan 29 '16 at 6:13
0

thanks, changing myisam tables to innodb solved the problem, now mysql CPU is at most 300% and normal load average peak 5%

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.