Styjun

My mysqld process consumes 232% CPU and and there 14000+ connections

(I'm a little new to this thing but following Stack Overflow for assistance).

top:

 PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
 3112 mysql 20 0 7061444 1.397g 15848 S 232.6 8.9 1138:06 mysqld

System:
Ubuntu 18.04,
16GB RAM,
8 Core CPU,
120GB Disk
and MySQL version 5.7.25

mysql> show status like 'Conn%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 14007 |
+-----------------------------------+-------+
7 rows in set (0.01 sec)

And show variables like "%timeout%"

mysql> show variables like "%timeout%";
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------+----------+
13 rows in set (0.01 sec)

And mysqld.cnf settings

[mysqld]
# Skip reverse DNS lookup of clients
skip-name-resolve
default-storage-engine=InnoDB

max_allowed_packet=500M
max_connections = 256
interactive_timeout=7200
wait_timeout=7200

innodb_file_per_table=1
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_open_files=5000
innodb_io_capacity=2000
innodb_io_capacity_max=4000
innodb_old_blocks_time=2000
open_files_limit=50000

query_cache_type = 1
query_cache_min_res_unit = 1M
query_cache_limit = 1M
query_cache_size = 50M

tmp_table_size= 256M
max_heap_table_size= 256M

#key_buffer_size = 128M
thread_stack = 128K
thread_cache_size = 32

slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1

Note: Corrected above mysqld.cnf values to match with below reports attached

Additional Info:

• htop:- https://pastebin.com/43f4b3fK
  


• top:- https://pastebin.com/rTh1XvUt
  


• GLOBAL VARIABLES: https://pastebin.com/K2fgKwEv (Complete)


• INNODB STATUS:- https://pastebin.com/nGrZjHAg
  


• Mysqltuner:- https://pastebin.com/ZNYieJj8
  


• [SHOW FULL PROCESSLIST], [ulimit -a], [iostat -xm], [lscpu] :- https://pastebin.com/mrnyQrXf

Server freezes when multiple db transaction is being carried out. Is there a lock like thing or any configuration flaws?

(Background: This is a WordPress blog and nobody else is accessing it right now. I somehow imported a 115K posts from an old blog but struck here with this CPU ghost)

Rate Per Second = RPS - Suggestions to consider for your my.cnf [mysqld] section,

innodb_lru_scan_depth=100 # from 1024 to reduce 90% of cpu cycles used for function every SECOND
innodb_io_capacity=3500 # from 2000 to enable higher IOPS on your SSD devices
innodb_flushing_avg_loops=5 # from 30 to reduce innodb_buffer_pool_pages_dirty overhead - count was 3183 in SGStatus
read_buffer_size=256K # from 128K to reduce handler_read_next RPS of 277,134
read_rnd_buffer_size=192K # from 256K to reduce handler_read_rnd_next RPS of 778

There are many more opportunities to improve performance through Global Variables.
Disclaimer: I am the author of web site mentioned in my profile, Network profile that includes contact information.

A likely cause of high CPU and poor performance is the poor schema for wp_postmeta. I discuss the remedy here.

Meanwhile, "you can't tune your way out of a performance problem". I did glance at the settings -- all are "reasonable".