Mysql process consumes High CPU & RAM even after reboot

Question

My VPS on DO (1 shared CPU & 1 GB RAM) sometimes got high CPU & RAM, and sometimes I stopped MySQL services then restart then it becomes normally, but sometimes this way did not work

Today, I tried stop first then restarted both MySQL & httpd services but it did not work, then I tried reboot but it still did not work too.

After reboot a few minutes, I used top command to see the stats below, can anyone help me?

phpMyAdmin alerts #1040 - Too many connections and declined connection.

I upsized to 2GB RAM

But seems it was better just a little, not good; and here's new my.cnf, I was using from this https://github.com/t0rik/mysql-configs-samples/blob/master/1Gb-RAM-my.cnf

[mysqld]
secure-file-priv = /var/tmp
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

bind-address = 127.0.0.1

skip-name-resolve = ON
skip-external-locking

symbolic-links=0
max_connections = 100
max_user_connections=50
wait_timeout=50
interactive_timeout=50
key_buffer_size = 32M
max_allowed_packet = 16M
thread_stack = 480K
thread_cache_size = 32M

table_open_cache = 48
sort_buffer_size = 512K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
net_buffer_length = 4K
join_buffer_size = 512K
tmp_table_size = 16M
max_heap_table_size = 16M
query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 16M
#query_cache_min_res_unit = 2k
#query_cache_size=32M ## 32MB for every 1GB of RAM

innodb_stats_on_metadata=OFF  # from ON to conserve CPU cycles
#innodb_buffer_pool_dump_pct = 25
innodb_buffer_pool_size = 128M
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 1
innodb_log_file_size = 48M
innodb_file_per_table=1

log_error = /var/log/mariadb/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/slow_query.log
long_query_time = 2

expire_logs_days    = 1
max_binlog_size   = 10M

performance_schema = 0

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

New performanace

This img below is latest for 6 hrs

Old Configs with 1 GB RAM

Here's my.cnf

# custom
bind-address = 127.0.0.1

skip-name-resolve
skip-external-locking

key_buffer_size = 6M
max_allowed_packet = 4M
table_open_cache = 48
sort_buffer_size = 384K
read_buffer_size = 1536K
key_buffer_size = 32M
read_rnd_buffer_size = 1536K
net_buffer_length = 4K
thread_stack = 480K
thread_cache_size = 8

innodb_file_per_table=1
max_connections = 100
max_user_connections=50
wait_timeout=50
interactive_timeout=50
long_query_time=5

query_cache_type = 0
query_cache_size = 10M
query_cache_limit=1M
#query_cache_min_res_unit = 2k
#query_cache_size=32M ## 32MB for every 1GB of RAM

tmp_table_size= 64M
max_heap_table_size= 64M

log_error = /var/log/mariadb/error.log
expire_logs_days    = 5
max_binlog_size   = 10M
innodb_buffer_pool_size=64M
innodb_buffer_pool_instances = 1
innodb_log_file_size = 48M

#innodb_force_recovery = 1

performance_schema = 0

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Answer 1

When you restart mysql and buffer pool is not warmed up then the reads happen from Disk. The Disk IO increases to fetch the information from disk instead of buffer pool. You should check the below variables for mysql,

innodb_buffer_pool_dump_at_shutdown/innodb_buffer_pool_load_at_startup (Dynamic/Static respectively) – These variables allow you to dump the contents of the InnoDB buffer pool to disk at shutdown and load it back at startup, which will pre-warm the buffer pool so that you don’t have to start with a cold buffer pool after a restart.

innodb_buffer_pool_dump_pct (Dynamic) – The option defines the percentage of most recently used buffer pool pages to dump. By default, MySQL only saves 25% of the most actively accessed pages, which should be reasonable for most use cases, it can then be loaded faster than if you try to load every page in the buffer pool (100%), many of which might not be necessary for a general workload. You can increase this percentage if needed for your use case.

Answer 2

If the reboot caught a long DML query in progress, then after the reboot, a ROLLBACK is needed to clean up that query. It will probably involve both CPU and I/O.

1GB or RAM and innodb_buffer_pool_size=64M are extremely low. These may also impact the CPU and I/O. MariaDB can run with those low values, but may run faster if given more room.

(Yes, @yunas's Answer is valid, too. It is hard to say which points are most relevant to the OP's question.)

"Too many connections" means either:

• max_connections = 100 is too low (This seems unlikely!)
• the queries are not finishing fast enough; check the SlowLog