CODEWITHSUNDEEP
phpmysqlserverprocesscentos
Pixelknight1398
Pixelknight1398

Reputation: 557

MySQL process taking 150-400% CPU

My software makes a lot of MySQL queries to my server, and I have never had any issues in the past with it, but just recently nothing was loading, no webpages, no SQL was running, nothing. I managed to get on WHM for my server and kill the process, only to watch it spike back up to 300%. Nothing I have been able to do has made it go down. What information do I need to share to get help with this? I am not a sys admin nor do I have one or resources for one. I wouldn't usually be asking for help and just optimize all my queries for something like this as it wasn't a problem for the past 3 months but suddenly became one out of nowhere, at least not that I noticed. At this point my program is saying that one of my database tables has crashed and needs repaired... What can I do? Thanks in advance for any help...

I have already considered optimization but I was hoping for a quick solution to implement as I have customers waiting, then I can spend a few days working on optimizing my SQL that, like I said, wasn't having any issues before. I am confused about it.

Also I am not sure if this helps but tracing the process in WHM prints this repeatedly and nothing else:

fcntl(16, F_GETFL)                      = 0x2 (flags O_RDWR)
fcntl(16, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
accept(16, {sa_family=AF_LOCAL, NULL}, [2]) = 35
fcntl(16, F_SETFL, O_RDWR)              = 0
setsockopt(35, SOL_IP, IP_TOS, [8], 4)  = -1 EOPNOTSUPP (Operation not supported)
futex(0x13298a4, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x13298a0, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
futex(0x1327240, FUTEX_WAKE_PRIVATE, 1) = 1
poll([{fd=14, events=POLLIN}, {fd=16, events=POLLIN}], 2, -1) = 1 ([{fd=16, revents=POLLIN}])

/etc/my.conf

innodb_file_per_table=1
default-storage-engine=MyISAM
performance-schema=0
max_allowed_packet=268435456
open_files_limit=10000

This is all that is available to me as far as my.conf file. The error log doesn't exist in /var/log so I don't have anything to give in that regard...

SQL version: [Server] # mysql -V mysql Ver 14.14 Distrib 5.6.41, for Linux (x86_64) using EditLine wrapper

I have an additional question or add-on to this. I don't know if it makes much of a difference but, say my code is running using 30% CPU on the mysql process, I can actually turn off the code and the mysql process CPU usage will not change. What does this mean?

Edit: (these are all expiring within a week from 12/09/2018)

The my.cnf file contents that I listed is all that was there. Nothing else. I will get the top command and iostat -xm 5 3 when I am running the software full speed again to see the results.

Upvotes: 2

Views: 2769

Answers (1)

Wilson Hauck
Wilson Hauck

Reputation: 2343

Rate Per Second=RPS Suggestions to consider based on your Linux ulimit -a report.

ulimit -n 16384       to raise Open Files limit from 1024 to support your activities.

For this to persist over Linux Shutdown/Restart, review this url.

https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/

Your specifics may be slightly different due to version of Linux.

Suggestions to consider for your my.cnf [mysqld] section

innodb_lru_scan_depth=100  # from 1024 to reduce CPU busy every second.  93% savings for this one function.
thread_cache_size=32  # from 9 for thread breathing room and growth.
innodb_io_capacity=1800  # from 200 to take advantage of your HDD IOPS capacity
key_cache_age_threshold=7200  # from 300 seconds to reduce key_reads RPS of 16 
query_cache_size=0  # from 1M to conserve RAM - QC is OFF and not used
query_cache_limit=0  # from 1M to conserve RAM - QC is OFF and not used
key_buffer_size=128M  # from 8M which had NO free space at the end of your work day

For additional suggestions, see my profile, Network profile for contact information.

Upvotes: 1

Related Questions