Help with mysql/mariadb configuration

B

burekas

Verified User
Joined
Feb 9, 2020
Messages
47
Hi,

I would like to get your advice regarding my Mysql/Mariadb configuration.

I ran this tool of MySQLTuner.
That is their diagnostic:
https://paste.ubuntu.com/p/h3v6FFWYg5

And that is my currently configuration:
https://paste.ubuntu.com/p/CpDyjY5Y6D

* Basically the majority of my tables in my database are set as InnoDB engine.

Can you tell me what do you recommand to change?
Because I have performance problems, it takes high memory.

Thanks for your help.
 
Last edited:
from SSH as root:
wget -O mysqltuner.pl mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl
than show result here (screen or copy text)
 
sorry :)
You have only 4g RAM? there only mysql or all DA environment?
query_cache_type=0
query_cache_size=0
key_buffer_size=8M

innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=1

-----
[--] Total buffers: 704.0M global + 1.0G per thread (151 max threads)
[!!] Maximum possible memory usage: 153.5G (4209.51% of installed RAM)
because You have: max_allowed_packet = 1024M
so don't worry it will be used only when all 150 connections will work with 1gb table each simultaneously
-----
also let Maria free RAM from inactive connections set something like this or lower:
wait_timeout = 60
interactive_timeout = 90
connect_timeout = 90

#net_write_timeout = 100 commented to use default
#net_read_timeout = 100 commented to use default
#net_buffer_length = 200M commented to use default

at my server there are no need to hold connections so long so I have timeouts 10-30 sec.
 
@Zhenyapan

Yes.
Current RAM size is 4GB.
What do you mean by "there only mysql or all DA environment?" ? I think so.

What are the default settings of InnoDB parameters when those parameters are commented?

btw, some of my tables are really big size.

I will check these settings.

Thanks.
 
Last edited:
What do you mean by "there only mysql or all DA environment?" ? I think so.
---
this VPS have only OS and MariaDB or this is regular DA install (with webserver/php/exim/etc.)?
I mean if there is only MariaDB - so you can give all resources to it, but if there are also all nginx/apache/php/mail stuff - you can give to mysql only the small part of total resources. I give you recommendations for case when there are all stuff.
also if there are a lot of domains/users and you don't want to allow one user use all connections/resources (ddos or bot atack for one website/db) you can also limit it by:
max_user_connections=20
of course check normal users connections count, and set limit twice larger to prevent connection drops at short load/visit spikes.
 
@Zhenyapan
Yes, I have there other stuff also, but usually it seems that mysql takes the most memory.
Basically I have two main users, one holds all the database and the second holds the PHP etc which read/write to the database.

A long time ago the database wasn't in InnoDB, I changed to InnoDB a long time ago but it seems that I forgot to set its parameters since then.

In addition I use MariaDB.

The issue is that since a month ago I started to get "system load" warnings from DA.
I think it started when I updated MariaDB from 10.4.15 to 10.4.17, I don't so sure (Maybe it's also attacks or something)
Thread that I opened about that:

[Mariadb] Since updating to 10.4.17 getting system load notifications

Hi, Recently I updated MariaDB from version 10.4.15 to 10.4.17 through CustomBuild in DA. Since then I'm starting to get "Warning: The system load average is..." I suspect that the update is the reason and not a big amount of connection etc.. * Log of the Mariadb update...
forum.directadmin.com forum.directadmin.com
 
Last edited:
@Zhenyapan

Question: If I will upgrade the RAM from 4GB to 16GB, how the parameters you advised to set would be changed now?
 
innodb_buffer_pool_size=6G
innodb_buffer_pool_instances=6
tmp_table_size=256M
max_heap_table_size=256M
 
Zhenyapan said:
innodb_buffer_pool_size=6G
innodb_buffer_pool_instances=6
tmp_table_size=256M
max_heap_table_size=256M
Click to expand...
You didn't mention these parameters before: tmp_table_size, max_heap_table_size
So if I'm still with 4G, does it mean to not add them yet?

And regarding the timeout parameters:
wait_timeout = 1000000
interactive_timeout = 1000000
connect_timeout = 1000000
net_write_timeout = 1000000
net_read_timeout = 1000000
net_buffer_length = 200M
I put high values because I was need to update all my tables to InnoDB and the tables were so big.
I will update them according to you recommandations.
 
Last edited:
now you have:
[OK] Temporary tables created on disk: 2% (32 on disk / 1K total)
that's why there is no need to tune this yet:
tmp_table_size=256M
max_heap_table_size=256M
if Temporary tables created on disk: will be more than 20% after at least 4-8h of mysql uptime - then will be time to tune this (according to free RAM and database/tables size)
 
@Zhenyapan

I think the updated parameters made it worse a little,
I have started to get more 'system load' warnings from DA since the changing,
more then the avarage as it was with the previous parameters before in a time period of ~15h.

Here is the current configuration:

Ubuntu Pastebin

paste.ubuntu.com paste.ubuntu.com

Here is the updated diagnostic:

Ubuntu Pastebin

paste.ubuntu.com paste.ubuntu.com

sar -q command output: (Part of it when the warnings were sent, you can see spikes in the numbers)

Ubuntu Pastebin

paste.ubuntu.com paste.ubuntu.com

Here I copy some parts from the last few warning messages:

Ubuntu Pastebin

paste.ubuntu.com paste.ubuntu.com

Can you tell me what can I do in order to improve this? (Until the RAM will be upgraded)
Are you sure the parameters are correct?
Do you see any diffrence or improvment in the mysqltuner diagnostic?

* There is also option of attacks on the server but sometimes I don't see anything which is unusual,
Generally if I see a lot of connection of specific IP or from a subnet I block them.
But as I said in some of the warning messages there wasn't anything unusual.

Thanks.
 
show me output:
sysctl -a | grep swa
I think there are default 60% vm_swappiness
you need to change it to 10%
 
at your TOP logs always disk's overload and swap used, so you need to optimize DB or requests, maybe some hot cache, or add more RAM.
WAits looks like this is an hdd, not ssd or nvme.
 
Yes, the RAM is on the way, I have already made an order for upgrade from 4GB to 16GB.
Until then I want to find other ways to improve the performance and get rid of those "system load" warnings.
I will try to find other ways also to optimize DB, requests, hot cache etc.

Do you have maybe any kind of useful and helpful links that I can use? Or some tips about this subject...

Thanks
 
no, I don't have saved links, it's easier each time to find needed thing with google, than in hundreds saved tabs :)
 
@Zhenyapan
Why did you recommand to set this parameters with zero?
query_cache_type=0
query_cache_size=0

Is it not better to have cache in this case?
 
Last edited:
@Zhenyapan
I don't know why but few hour ago my server get very busy.

I set back these values to:
key_buffer_size = 512M
query_cache_size = 32M
innodb_buffer_pool_size = 512M
wait_timeout = 120
interactive_timeout = 180
connect_timeout = 180
net_write_timeout = 1000000
net_read_timeout = 1000000
net_buffer_length = 200M

And it back to normal for now.

But I think the RAM upgrade will solve everything, hope so.
 
You must log in or register to reply here.
Back
Top