mysqld is eating CPU at average of 300%
Hello,
The SQL is always sucking the CPU at an average of 300% and sometimes it goes to 700%. Please advise.
Server specs:
Intel(R) Core(TM) i9-9900K CPU @ 3.60GHz
Memory:
total used free shared buff/cache available
Mem: 65713840 6324576 30708140 53896 28681124 58795344
Swap: 33521660 16128 33505532
Total: 99235500 6340704 64213672
etc/my.cnf:
[mysqld]
log-error=/var/lib/mysql/host*.net.err
performance-schema=0
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=2848578698
open_files_limit=100000
local-infile=0
connect_timeout=250
wait_timeout=300
interactive_timeout=300
slow-query-log=1
long_query_time=5
slow_query_log_file="mysql-slow.log"
sql_mode = ""
key_buffer_size = 128M
thread_stack = 128K
thread_cache_size = 8
max_heap_table_size = 256M
query_cache_limit = 4M
query_cache_size = 512M
innodb_buffer_pool_size = 2G
MySQLTuner 1.8.1 - Major Hayden
>> Bug reports, feature requests, and downloads at MySQL :: MySQL Internals Manual :: 10.5 How MySQL Uses the Join Buffer Cache
(specially the conclusions at the bottom of the page).
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance schema should be activated for better diagnostics
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this:
-
Do you see any database appearing multiple times using the below command? #mysqladmin proc stat0 -
That's always my go-to check @kodeslogic ! 0 -
Do you see any database appearing multiple times using the below command?
#mysqladmin proc stat
YES I do see this: [root@host ~]# mysqladmin proc stat +--------+--------------+-----------+--------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+--------------+-----------+--------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | 1 | system user | | | Daemon | | InnoDB purge worker | | 0.000 | | 2 | system user | | | Daemon | | InnoDB purge coordinator | | 0.000 | | 3 | system user | | | Daemon | | InnoDB purge worker | | 0.000 | | 4 | system user | | | Daemon | | InnoDB purge worker | | 0.000 | | 5 | system user | | | Daemon | | InnoDB shutdown handler | | 0.000 | | 400076 | halabtech_ar | localhost | HTT_ar | Query | 0 | Sending data | SELECT option_value FROM wp_options WHERE option_name = '_transient_global_styles_svg_filters_mharty | 0.000 | | 400077 | HTT_ar | localhost | HTT_ar | Query | 0 | Sending data | SELECT option_value FROM wp_options WHERE option_name = 'mh_magazine_activated' LIMIT 1 | 0.000 | | 400078 | HTT_ar | localhost | HTT_ar | Query | 0 | Sending data | SELECT option_value FROM wp_options WHERE option_name = 'mh_magazine_activated' LIMIT 1 | 0.000 | | 400079 | HTT_ar | localhost | HTT_ar | Query | 0 | Sending data | SELECT option_value FROM wp_options WHERE option_name = 'fb_app_auth_token' LIMIT 1 | 0.000 | | 400080 | HTT_ar | localhost | HTT_ar | Query | 0 | Sending data | SELECT option_value FROM wp_options WHERE option_name = 'fb_app_auth_token' LIMIT 1 | 0.000 | | 400081 | root | localhost | | Query | 0 | Init | show processlist0 -
The current output you shared looks normal, try the same command when CPU usage is 700% that time you will be able to detect/identify the database consuming high resources. 0 -
I would change these settings in your my.cnf query_cache_limit = 512Kb # no need to have it big. it will just eat all RAM with low efficiency query_cache_size = 128M # of even 64M. Query cache gets really slow when it is bigger than 128Mb. innodb_buffer_pool_size = 8G # you have enough RAM, let InnoDB use it. 0
Please sign in to leave a comment.
Comments
5 comments