[CentOS] mariadb server memory usage

Mon Nov 6 13:49:14 UTC 2017
Mark Haney <mark.haney at neonova.net>

On 11/04/2017 10:05 AM, hw wrote:
>
> Hi,
>
> is this ok for a database server, or do I need to turn the memory 
> allowance
> down?  The machine has 48GB and mariadb is allowed about 40. The
> machine is a dedicated database server.
>
> Mysql seems to go up to what top says is virtually allocated under some
> circumstances; I don´t know what mariadb does.  I don´t want anything
> get killed because memory runs out.  Swap should prevent that anyway,
> but perhaps I went a bit higher than I should?
>
> Usual advice is to use 80%, and there are probably reasons for that.
> Perhaps it´s better to allow for more disk cache?
>
>
> KiB Mem : 49449424 total,   291772 free, 45891836 used,  3265816 
> buff/cache
> KiB Swap: 16777212 total, 16742928 free,    34284 used.  2985816 avail 
> Mem
>
>   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM TIME+ COMMAND
>  4138 mysql     20   0 48.908g 0.042t  14064 S   0.0 91.8 111:23.17 
> mysqld
>
What exactly do you mean by 'memory allowance'?  That's a pretty open 
question.  It all depends on your databases and usage.  The typical 
'best practices' answer is to set the buffer pool to ~75-80% of RAM if 
the total size of all your DBs exceeds the total amount of RAM on the 
server.  The idea being that you want to have as much (or all) of the 
databases in RAM as that's much faster than going to disk for it. If the 
total size of your DBs is less than the total RAM, I typically allocate 
enough to take all DBs into RAM  +10-20% for projected growth.  You 
haven't mentioned the size of the DB(s) you're running on the server, 
nor what your usage pattern might be (heavy reads, heavy writes or a 
balance of the two).  You also didn't post the config file either.  I 
know this is a bit OT, but config files are very helpful for any issue.  
Just looking at the RAM usage you posted, I would be a bit worried by 
the fact you only have ~300MB of 'free' RAM.  If the total in the cache 
is all buffer pool, it should be fine, but I typically don't run a DB 
server that low on physical RAM.  Swap is fine, but much slower than 
RAM, even on SSDs.

We can take this offlist if you like, I'll be happy to help you take a 
look at the configs and offer suggestions.


-- 
Mark Haney
Network Engineer at NeoNova
919-460-3330 option 1
mark.haney at neonova.net
www.neonova.net