1

I have a small VPS that handles my 3 personal WordPress websites.

At a certain time MySQL started consuming more and more memory and actually (for months) the server has been running at 99% RAM usage with MySQL eating more than 2 GB alone.

My VPS is an Ubuntu 14.04.05 with MySQL 5.7.23 (I upgraded to avoid the memory eating bug), 2 amd64 CPU@3.9GHZ and 3 GB of RAM.

The webserver is configured by Virtualmin, with little to no modifications applied by myself (read after).

I tried MySQLTuner, and some other scripts but nothing helps.

I have only these 3 websites and every time the traffic spikes (these are personal websites, so "spikes" are maybe 3 users at the same time) the VPS crashes.

Can you please help me configure it to use lesser RAM or diagnose if there is an issue? I'm thinking about switching to MariaDB, but without changing the configuration I don't think that anything will change.

All the relevant informations are in this pastebin (posted Aug 7, 2018) for readability:

https://pastebin.com/UasHBcH1

According to MySQL Calculator it should only require 293 MB and I don't have so much visitors to justify this usage.

  • The server probably isn't sitting at 99% RAM used, it'll be using a large proportion of that memory for filesystem caching. Is there an actual problem you're trying to solve apart from the apparent memory usage? – Philᵀᴹ Aug 7 '18 at 14:41
  • If your server does indeed crash, you will need to determine the reason for that. Check the syslog and the MySQL error log for more information. – mustaccio Aug 7 '18 at 14:54
  • 1
    @mustaccio the webserver crashes because it cannot find free ram to manage the spikes in traffic, I already checked logs. I have an increased memory usage and my objective is to solve it. If you check mysql calculator my configuration should use 293MB and uses more than 2GB, I can assure you that I don't have that much traffic – Aleritty Aug 7 '18 at 15:16
  • 1
    @Philᵀᴹ The memory usage isn't apparent, and maybe is caching. # free -h Mem: 3,0G 3,0G 0B 3,3G 0B 185M -/+ buffers/cache: 2,8G 185M But this memory isn't released by mysql and I can see the single mysqld process using that amount – Aleritty Aug 7 '18 at 15:24
  • 1
    @WilsonHauck I added the pastebin to my original answer, I executed everything except iostat because this server isn't really "busy"... Everything is unedited this time – Aleritty Aug 9 '18 at 13:58
0

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

thread_cache_size=20  # from 8  mysqld takes 8 minimum, keep breathing room
read_buffer_size=1M  # from 256K to reduce handler_read_next count
table_open_cache=500  # from 80  to support 327 opened since startup
innodb_buffer_pool_size=1G # from 128M for effective 750M for growth over time
innodb_log_file_size=64M  # from 8M to be larger than innodb_log_buffer_size
innodb_io_read_threads=16  # from 3 to speed up reads
innodb_io_write_threads=16  # from 3 to speed up writes
innodb_io_capacity=1000  # from 200 to increase iops limit
max_heap_table_size=16M  # from 12M to raise RAM capacity
tmp_table_size=16M  # from 12M should be same size and will reduce created_tmp_disk_tables
innodb_buffer_pool_dump_pct=90  # from 25 for reduced WARM up time
query_cache_limit=0  # from 525312 conserve RAM QC not being used

for additional suggestions, view profile, Network Profile for contact info, including Skype ID.

  • You made two typos in innodb_read_io_threads and innodb_write_io_threads – Aleritty Aug 11 '18 at 10:06
  • After some hours I can tell that this configuration use MORE ram, the process mysqld is using 3.3GB of ram (swapping of course) – Aleritty Aug 11 '18 at 11:09
  • My apologies on the typos. What is response time compared to last week? Do you have Skype available? – Wilson Hauck Aug 11 '18 at 12:11
  • I don't need to tune performances, I only need to understand why I have so high memory usage – Aleritty Aug 16 '18 at 9:36
  • @aleritty What is your response time compared to 2 weeks ago, please. MySQL was designed to provide access to data as fast as possible many years ago. If you want to reduce memory usage at the expense of data access speed, I can do that for you by reducing all the caching options in my.cnf, starting with innodb_buffer_pool_size going back to 128M rather than 1G, which is 1/3 of your TOTAL RAM of 3G. Please answer my first question, how is response time compared to 2 weeks ago. Thanks – Wilson Hauck Aug 16 '18 at 9:52
0

With only 3G of RAM, plus running other processes, innodb_buffer_pool_size=1G may be too high. Set it to 500M. (128M is dangerously low for newer versions of MySQL.)

That change is the easiest 'quick fix' to get the system stable if it running out of RAM.

Also completely turn off the Query Cache via query_cache_type = OFF and query_cache_size = 0 (not _limit).

If you can keep the server up for a day, please provide SHOW GLOBAL STATUS and SHOW VARIABLES; there may be other things to tune to cut back on excess memory usage. http://mysql.rjweb.org/doc.php/mysql_analysis#tuning

Meanwhile, I find that MySQLTuner and various 'calculators' all leave some details unaddressed. My link is offering yet another 'tuner'; it may find something else.

  • I'm trying right now your suggestion, starting with the standard configuration, also I'll check your calculator, I'll give you the results tomorrow. Thank you – Aleritty Aug 27 '18 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.