New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
MySQL Memory usage
dragon1993
Member
in Help
Hello.
I'm running a small blog on my VPS as a hobby but I don't understand why the MySQL is using 400MB+ memory?
According to MySQLTuner it shouldn't go above 144.2 MB.
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1481 mysql 20 0 1229716 422424 6416 S 0.0 41.5 0:51.10 mysqld
Private + Shared = RAM used Program 411.7 MiB + 302.5 KiB = 412.0 MiB mysqld 156.0 KiB + 22.5 KiB = 178.5 KiB mysqld_safe
MySQLTuner
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 4h 44m 0s (23K q [0.228 qps], 3K conn, TX: 144M, RX: 2M)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Total buffers: 88.0M global + 1.1M per thread (50 max threads)
[OK] Maximum reached memory usage: 91.4M (9.19% of installed RAM)
[OK] Maximum possible memory usage: 144.2M (14.51% of installed RAM)
[OK] Slow queries: 0% (0/23K)
[OK] Highest usage of available connections: 6% (3/50)
[OK] Aborted connections: 0.25% (8/3252)
[OK] Query cache efficiency: 79.2% (11K cached / 14K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 41 sorts)
[OK] Temporary tables created on disk: 9% (235 on disk / 2K total)
[OK] Thread cache hit rate: 99% (3 created / 3K connections)
[!!] Table cache hit rate: 17% (467 open / 2K opened)
[OK] Open file limit used: 4% (41/1K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.3% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/41.0K
[OK] Read Key buffer hit rate: 97.8% (324 cached / 7 reads)
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 32.0M/2.3M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 28.97% (593 used/ 2047 total)
[OK] InnoDB Read buffer efficiency: 98.13% (27805 hits/ 28336 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 152 writes)
-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (1024) variable
should be greater than table_open_cache ( 482)
Variables to adjust:
table_open_cache (> 482)
my.cnf
[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql explicit_defaults_for_timestamp performance_schema = 0 innodb_flush_method = O_DIRECT innodb_buffer_pool_size = 32M #slow-query-log = 1 #slow-query-log-file = /var/log/mysql/mysql-slow.log #long_query_time = 1 #log-queries-not-using-indexes # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1 log-error = /var/log/mysql/error.log # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # query_cache_size = 16M query_cache_type=1 query_cache_limit= 2M max_connections=50 !includedir /etc/mysql/conf.d/
Thanked by 1geekalot
Comments
disable innodb and set myisam as default engine.
I need innodb for ACID, transaction isolation...
innodb need extra resources to run. thats the main reason why many people disabling innodb when they need to run databases on their (especially lowend) vpses.
That's because mysql memory usage depends on other settings too. Your my.cnf only define certain variables, so others are considered defaults. Also memory depends on which version you're running and how many connections you have opened at the time.
Take a look http://dba.stackexchange.com/questions/1229/how-do-you-calculate-mysql-max-connections-variable and here https://www.percona.com/blog/2006/05/17/mysql-server-memory-usage/ and here's a calculator (not tested) http://www.mysqlcalculator.com/
I have only little connection one time, i try mysqlcalculator return MySQLTuner result 144.2M max memory usage.
I use MySQL 5.7.10
OpenVZ perhaps ?
Arubacloud and Vmware
Perhaps limit innodb max memory usage?
Try reducing innodb_buffer_pool_size to something lower?
Does it use all 400MB from the start or it increases over time?
32MB already looks very low.
It's to see if that's that variable responsible for the memory usage. Of course you can change it back to whatever it was.
Start with ~ 90 MB memory usage and increases ~400MB
I decrease innodb_buffer_pool_size, start with 41MB memory usage, now i wait few hour
UPDATE Now use 397.1 MB
you simply can't limit memory usage at all. you can only try and tune it... but there are a lot more variables involved.
good read: https://www.percona.com/blog/2014/01/24/mysql-server-memory-usage-2/
keep in mind that most of those tuning-scripts wont cover all possibilities.
I usually limit with
max_connections / max_user_connections
Then dependent on the memory per thread (and other variables) allowed , lowering it will keep MySQL from tanking the server.
Used pre Cloud Linux in early 2000's in shared cPanel hosting to keep things in-check.
CEOs loved packing shared servers, even when they're P4 2.4's.
IMO cPanel 'made' a lot of hosts in its earlier years.
ok if this is to be any help i just put it here my.cnf
http://cryptb.in/5jCb
Maybe MySQL + InnoDB need 300MB memory idle ?
Add your relevant lines but memory usage 361MB.
New my.cnf
you probably want to go after vars like http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_ft_total_cache_size which is 640 MB by default
depends heavily on your usage of innodb and search functions within though...
I try, not change memory usage.
I use only few MB database and mysql use 400 mb memory.
ok, so another thing to try for not so loaded mysql may be lowering table_definition_cache to its minimum 400 (which should act as soft-limit on some innodb settings too, according to the reference manual) and maybe table_open_cache accordingly to 400 or less, as your hit rate on that seems to be low anyway.
thread_cache_size to 8 could help a bit, query_cache_size 8M should suffer as probably would query_cache_limit 512K if you don't have big tables and a lot of select * and such ;-)
BTW: as long as it doesn't do anything to your servers performance overall and system is not swapping, I don't see an issue at all.
as far as I am aware of, mysql does manage its memory usage fairly good.
so you probably don't want to restrict it too much, as this could come at the cost of heavier disk usage (e.g. more temp tables to disk, more often garbage collection and flushing etc.) and more disk IO is probably worse than mysql consuming memory which otherwise would just be free for nothing or just by the filesysem as buffers.
if I've noticed between the lines correctly your vm has 1 GB of RAM, who cares if mysql takes 40% of it ? ;-)
I'm afraid, 1GB ram enough for PHP 7 + NGIXN + MYSQL ?
Depends on traffic, php's script & how you configure your vps. 1gb is enough for typical website. I used to to host my small websites on a 512 mb vps without any problem.
I always use this kind of setup :
Change to your needs.
cd /etc/mysql/; mv my.cnf my.cnf.bk; mv /usr/share/doc/mysql-server-5.5/examples/my-small.cnf my.cnfjust watch your swap usage. if you're server does not need to make use of swap often, everything should be fine.
some limits on max connections like you already did, imho make sense, as those make sure, that your server stays stable if there will be heavy load unexpectedly.
if your sites grow and you often will see heavy load and mysql running into connection limit, it'll be about time to change to a bigger server though.
performance wise it mstly should be good to use a lot of the available ram, as everything unnessecarily not used may be lost performance otherwise.
I remove mysql 5.7 and install mariadb.
Now use 90MB memory i'm happy