My.cnf also usable for MariaDB

[Richard G]

I'm on a new Centos 7 server which now has MariaDB 10.0.35.

Previous on our Centos 6 server I used this in my.cnf:

[mysqld]
local-infile=0
bind-address = 127.0.0.1
max_connections = 150
default-storage-engine=MyISAM
innodb_file_format=Barracuda
innodb_file_per_table=1
open_files_limit = 2048
innodb_max_dirty_pages_pct = 0
performance_schema=off
slow_query_log = 1
slow_query_log_file=/var/log/mysql/slow-queries.log
long_query_time = 3

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

Some of these I'm now using under /etc/my.cnf.d/server.cnf but I did not enter the ones below yet.

But I would like to use if it's a good idea to also use these in a MariaDB database (which I used with msyql in cento 6):

max_connections = 150
default-storage-engine=MyISAM
innodb_file_format=Barracuda
innodb_file_per_table=1
open_files_limit = 2048
innodb_max_dirty_pages_pct = 0
performance_schema=off

or if it's better not to use these or some of them with MariaDB?

 

[ViAdCk]

Hello,

Some options like

innodb_file_format=Barracuda
innodb_file_per_table=1

are depreciated in Mariadb 10.3. Just a heads up if you update Mariadb at some point, as this happened to us recently and the mysql server didn't start correctly.

 

[Richard G]

Thank you.
Oke so I can better leave those out then. Or should they be replaced by something else?
Is it best to put this in my.cnf as it says there:

# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

which is for both client and server as it looks, or is it better to put these where I was intending to put them in /etc/my.cnf.d/server.cnf?

Edit: Just read that "innodb_file_per_table=1" is default for MariaDB 10.x versions. So I don't need to set it anyway.

 

[wattie]

It's not deprecated. Read here:

https://mariadb.com/kb/en/library/xtradbinnodb-file-format/

 

[wattie]

By the way your "open files limit" is really low.

Here is what I use (but not that my server has a lot of ram):

local-infile=0
innodb_file_per_table
max_connections = 512
max_user_connections = 100
wait_timeout = 30
interactive_timeout = 30
key_buffer_size = 2048M
max_allowed_packet = 64M
table_open_cache = 15288
table_definition_cache = 15288
open_files_limit = 24576
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache_size = 32
#query_cache_size = 80M
#query_cache_limit = 256K
#query_cache_min_res_unit = 2k
#query_cache_type = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow-queries.log
long_query_time = 10
max_heap_table_size = 512M
tmp_table_size = 512M
innodb_buffer_pool_size = 4096M
skip-name-resolve
innodb_log_file_size = 512M
innodb_buffer_pool_instances = 4
sql_mode = "NO_AUTO_CREATE_USER"

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Also using MyISAM nowadays is way outdated. InnoDB is much better. Switch your default to InnoDB.

A good SELECT query to show you a close result to how much potential maximum ram will be used by MariaDB is:

SELECT ( @@key_buffer_size + @@query_cache_size +
@@tmp_table_size + @@innodb_buffer_pool_size +
@@innodb_log_buffer_size +
@@max_connections * (
@@read_buffer_size + @@read_rnd_buffer_size +
@@sort_buffer_size + @@join_buffer_size +
@@binlog_cache_size + @@thread_stack
)
) / 1073741824 AS MAX_MEMORY_GB

 

[Richard G]

Thank you for sharing and replying Wattie!

Also using MyISAM nowadays is way outdated. InnoDB is much better. Switch your default to InnoDB.

I did some reading stuff last night after I made the post and found that InnoDB is the default nowadays.

We have 32 GB of RAM in the server, so we can do some more too, but the database is not used very heavily. However, allowing more open files can do no harm.
I couldn't use anything alse but "local-infile=0" in the my.cnf otherwise I got errors when doing a rewrite_confs for apache, for every line in there, until I put them all in the mysqld section of the /etc/my.cnf.d/server.cnf where they are now.
This is in there now:

bind-address = 127.0.0.1
performance_schema=off
max_connections = 150
open_files_limit = 8192
innodb_max_dirty_pages_pct = 0
slow_query_log = 1
slow_query_log_file=/var/log/mysql/slow-queries.log
long_query_time = 3
symbolic-links=0

Any advice on what to put in the [mariadb-10.0] section of the server.cnf or security protection in client.cnf or mysql-clients.cnf?
I only worked with mysql before, did not expect so many different configs.