Question
Mysql consumes High CPU&RAM even after reboot
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.
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
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
Become a contributor for community
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
DigitalOcean Documentation
Full documentation for every DigitalOcean product.
Resources for startups and SMBs
The Wave has everything you need to know about building a business, from raising funding to marketing your product.
The developer cloud
Scale up as you grow — whether you're running one virtual machine or ten thousand.
Get started for free
Sign up and get $200 in credit for your first 60 days with DigitalOcean.*
*This promotional offer applies to new accounts only.
Given the recent increase in CPU and RAM usage, this could indicate a change in traffic patterns, a heavier workload, or even resource constraints on the server due to limited CPU and memory.
Tools like htop and top can provide real-time CPU, memory, and swap usage. MySQLTuner or Tuning-Primer scripts can analyze your MySQL configuration and provide recommendations based on usage.
As mentioned you can try to tweak the my.cnf file, however an upgrade might be needed as well in order to handle the traffic which for the past few days is increasing.
https://www.digitalocean.com/community/questions/how-to-tweak-mysql-mariadb-configuration-for-increased-performance-and-stability
Hope that this helps!
Hey!
In addition to what KFSys mentioned and that the server has been stable for a long time, keep in mind that very often, a sudden spike in traffic or newly added queries can overwhelm your MySQL instance, especially on a VPS with limited resources like 1 CPU and 1 GB RAM.
It is normal that with time, as your database grows, tables can become fragmented, leading to inefficient query performance. This can especially be a problem if your tables are getting larger and you haven’t optimized them recently.
The current configuration in your
my.cnfmight not be optimized for your server’s limited resources. Also, havingmultiplekey_buffer_sizeentries in your config file is redundant and could lead to conflicts. You should remove the duplicate one!If your database tables have grown or if you’ve recently added more data, outdated or missing indexes could cause slow queries, leading to high CPU and memory usage.
With that said, try following the steps from this answer here on how to tweak your MySQL configuration:
On the ‘Too many connections’ errors, have you checked which queries are currently running?
If you see a lot of connections from the same source, it could indicate a script or bot is overloading your database. Also if there are some long-running queries, it will be a good indication that those need to be optimized, for example adding a simple index on the tables might help drastically.
The images that you’ve shared do not seem to be loading, you can reupload them here instead and share them again: https://imgur.com/upload
- Bobby
Heya,
It seems like your Droplet is getting small for you. Also, the “too many connections” error in MySQL, indicates that the server’s resources are being overwhelmed, likely due to high traffic or inefficient queries. This basically tells that MySQL handles the maximum number of connections simultaneously.
You can always increase them however that would mean more strain on your Droplet. There are 3 ways to proceed with this and I think you need to do all 3.