Solved MariaDB file location

fred974

fred974

Hi,
This is a very basic question with a lot different answer on the web...
Could someone please tell me if the databases/mariadb100-server configuration file reside in /usr/local/my.cnf or /usr/local/etc/my.cnf

Also I have a server with 24GB of install ram and MariaDB leave inside a jail (iocage).
In dedicated server I would set innodb_buffer_pool_size to about 80% of my install memory (20GB)...

As the server also has 1 mail jail and 2 webserver, what would you advise I set the value at?

Will 10GB be ok?

Thank you
 
Last edited by a moderator:
I usually install my configuration in /usr/local/etc but if I am not mistaken /var/db/mysql will also work too.
80% is the suggested value for a dedicated database server. In your case the number should be much lower. Of course it really depends on what type of databases you host. For WP don't go over 4GB.
I think that the most important factor here, since the FS is ZFS, is to apply the appropriate Innodb recordsize which is 16KB for datafiles and 128KB for log files.
 
If you look at /usr/local/etc/rc.d/mysql-server you'll see it defaults to /var/db/mysql/my.cnf. Personally I like to set this in /etc/rc.conf:
Code: 
mysql_optfile="/usr/local/etc/my.cnf"

In dedicated server I would set innodb_buffer_pool_size to about 80% of my install memory (20GB)...

As the server also has 1 mail jail and 2 webserver, what would you advise I set the value at?
Click to expand...
Not an easy question to answer as it depends on the load of MySQL. Set your buffer to a certain size, let the DB run for a couple of days and re-check. It's rather pointless to have Gigabytes of InnoDB cache if it's only a few percent in use. This is just something you will need to tweak as time progresses. Just remember to keep the DB running for a few days before checking again. If you change the buffer size the cache will be cleared and it will take a bit of time for it to warm up again.
 
I've been looking into this...

I understand that if I had a dedicated server I would create the zfs file system like this:
Code: 
zfs create -o recordsize=16k -o primarycache=metadata zroot/var/db/mysql-innodb
zfs create -o recordsize=128k -o primarycache=metadata zroot/var/db/mysql-innodb-logs
zfs create -o recordsize=8k zroot/var/db/mysql
How can I adapt this for a jail?
 
Last edited by a moderator:
The same way, only the actual directories where the filesystems are mounted will be different.
 
iocage list
Code: 
JID   UUID                                  BOOT  STATE  TAG
11    1585e91b-cc07-11e5-9463-f04da20055fe  off   up     mysql.mydomain.ltd
So based on the above, is this correct?
Code: 
zfs create -o recordsize=16k -o primarycache=metadata /iocage/jails/1585e91b-cc07-11e5-9463-f04da20055fe/root/var/db/mysql-innodb
zfs create -o recordsize=128k -o primarycache=metadata /iocage/jails/1585e91b-cc07-11e5-9463-f04da20055fe/root/var/db/mysql-innodb-logs
zfs create -o recordsize=8k /iocage/jails/1585e91b-cc07-11e5-9463-f04da20055fe/root/var/db/mysql

what are mysql-innodb and mysql-innodb-logs are they the names of the logs specified in my.cnf?
 
ok it goes in my.cnf
Code: 
innodb_data_home_dir = /var/db/mysql-innodb
innodb_log_group_home_dir = /var/db/mysql-innodb-logs
 
mysql-innodb is where you would store InnoDB databases, InnoDB and MyISAM have different needs regarding caching and block sizes, so it's best to store them in different directories. The mysql-innodb-logs directory is used to store the InnoDB transaction logs (not to be confused with "diagnostic" logs like mysql-err.log).

Personally I like to keep data like this separate. It's easy to create a dataset and simply set its mountpoint to where you need it.

Code: 
zroot/DATA                            74.1G  23.4G   144K  none
zroot/DATA/mysql                       164M  23.4G   164M  iocage/jails/<bignum>/root/var/db/mysql

The idea behind this is that it's now easy to remove the jail completely without running the risk of also deleting the database data. Making it easier to create a new jail and start with a fresh install but keep the data.
 
Almost, you'll want to create this structure, to keep everything inside /var/db/mysql/:
Code: 
/var/db/mysql/
/var/db/mysql/innodb/
/var/db/mysql/innodb-logs/
 
Sorry for been a pain..
I decided that you option@SirDice was best as I could also have zroot/DATA/www for my webserver files in there as well.
So here is what I have done:

#create dataset on FreeBSD host
Code: 
zfs create zroot/DATA
zfs create -o recordsize=8k zroot/DATA/mysql
zfs create -o recordsize=128k -o primarycache=metadata zroot/DATA/mysql/mysql-innodb-logs
zfs create -o recordsize=16k -o primarycache=metadata zroot/DATA/mysql/mysql-innodb
zfs list
Code: 
zroot/DATA                                                         1.12M   465G   288K  /zroot/DATA
zroot/DATA/mysql                                                    863K   465G   288K  /zroot/DATA/mysql
zroot/DATA/mysql/mysql-innodb                                       288K   465G   288K  /zroot/DATA/mysql/mysql-innodb
zroot/DATA/mysql/mysql-innodb-logs                                  288K   465G   288K  /zroot/DATA/mysql/mysql-innodb-logs
# mount dataset to the jail
Code: 
zfs create -o mountpoint=/iocage/jails/<bignum>/root/var/db/mysql zroot/DATA/mysql

I have typed all this already so I would appreciate if you could tell me if I did rigth or wrong so I can destroy the bit I did wrong

Thank you
 
I decided that you option SirDice was best as I could also have zroot/DATA/www for my webserver files.
Click to expand...
Yes, excellent, that's the idea. Separate the data.

You'll want to add this:
Code: 
zfs set mountpoint=none zroot/DATA
It's just a placeholder to keep things nice and separate, it doesn't contain data of itself.

Everything else looks to be in order.
 
Yeaaa :) :)
Thank you very much..

I've documented this process for furtur reference.
Your help is really appreciated
 
zfs create -o mountpoint=/iocage/jails/<bignum>/root/var/db/mysql zroot/DATA/mysql
return
Code: 
cannot create 'zroot/DATA/mysql': dataset already exists
 
You already created the filesystem a few steps back. Just change the mountpoint: zfs set mountpoint=/iocage/jails/<bignum>/root/var/db/mysql zroot/DATA/mysql
 
You must log in or register to reply here.
Back
Top