High CPU and mySQL problems
The problem is that the CPU reaches around 59 and then there is the following output:
[266623.188454] Out of memory: Kill process 1511 (mysqld) score 54 or sacrifice child
[266623.189711] Killed process 1511 (mysqld) total-vm:985192kB, anon-rss:17256kB, file-rss:0kB, shmem-rss:0kB
[266640.150680] Out of memory: Kill process 15418 (php-fpm5.6) score 30 or sacrifice child
[266640.156358] Killed process 15418 (php-fpm5.6) total-vm:562272kB, anon-rss:61328kB, file-rss:1092kB, shmem-rss:3840kB
[266648.618345] Out of memory: Kill process 15404 (php-fpm5.6) score 31 or sacrifice child
[266648.619801] Killed process 15404 (php-fpm5.6) total-vm:500452kB, anon-rss:25528kB, file-rss:1172kB, shmem-rss:5144kB
This tells me that the server is running out of memory, and needed to kill a process in order to continue running. The process it killed was database server, mysqld.
I have tried to go with this manual to fix the problem: https://www.linode.com/docs/troubleshooting/troubleshooting-memory-and-networking-issues/#mysql-low-memory-settings
But I can't seem to find:
thread_stack (is not in the file, I can only find thread_cache_size, is this file correct?)
table_cache (is not in the file)
sort_buffer (is not in the file, I can only find sort_buffer_size, is this correct?
net_buffer_lenght (is not in the file and I can't find any similar files)
Will this tuning work or do someone else have any other solution?
Thankful for your reponse(s)
2 Replies
scrane
Linode Staff
Hey there!
I definitely think you're on the right track with that guide. Another option I would highly recommend taking a look at is MySQL Tuner. This will automatically output suggested changes for you so you can make the individual changes. The linked guide goes through all the steps you should need and what the individual options mean.
With those options you shared missing in your file, I believe you should be okay to add them in. If the option is missing from the file, MySQL just uses the default. There is mention of what you see over in this past Community post. I would highly recommend making a backup of your my.cnf file before making any changes, however. You can do that by running cp /etc/mysql/my.cnf ~/my.cnf.backup.
Hi, I have added the options that was missing in the file below fine tuning. It says in the guide:
Caution
The settings in this section are designed to help you temporarily test and troubleshoot MySQL. We recommend that you do not permanently use these settings.
Does that mean these settings:
key_buffer = 16K
max_allowed_packet = 1M
thread_stack = 64K
table_cache = 4
sort_buffer = 64K
net_buffer_length = 2K
I also followed the guide: https://linode.com/docs/databases/mysql/how-to-optimize-mysql-performance-using-mysqltuner/
And I got this respond after running the command: echo [PID] [MEM] [PATH] && ps aux | awk '{print $2, $4, $11}' | sort -k2rn | head -n 20
ps -eo pcpu,pid,user,args | sort -k 1 -r | head -20
4, $11}' | sort -k2rn | head -n 20
[PID] [MEM] [PATH]
1033 5.5 /usr/sbin/mysqld
849 1.9 php-fpm:
1269 0.5 nginx:
888 0.5 nginx:
1270 0.3 nginx:
1431 0.3 sshd:
679 0.3 /usr/lib/accountsservice/accounts-daemon
1 0.2 /sbin/init
1184 0.2 /usr/lib/postfix/sbin/master
1189 0.2 pickup
1190 0.2 qmgr
1278 0.2 /usr/sbin/sshd
1433 0.2 /lib/systemd/systemd
1453 0.2 -bash
1452 0.1 sshd:
1465 0.1 ps
1466 0.1 awk
391 0.1 /lib/systemd/systemd-journald
447 0.1 /lib/systemd/systemd-udevd
511 0.1 /lib/systemd/systemd-timesyncd
Thereafter I runned the command: curl -L http://mysqltuner.pl/ | perl
The response was:
curl -L http://mysqltuner.pl/ | perl
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0
0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0
100 216k 100 216k 0 0 201k 0 0:00:01 0:00:01 --:--:-- 201k
MySQLTuner 1.7.14 - Major Hayden major@mhtx.net
Bug reports, feature requests, and downloads at http://mysqltuner.com/
Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: Please enter your MySQL administrative password: [!!] Attempted to use login credentials, but they were invalid.
I also have the 512MB allocated RAM memory and if I read the guide the setting and "key_buffer" it should be: 64M. I have 16M today. Can this be the issue?
Thanks!