LinuxQuestions.org - Where and how to change these database settings?

- Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)

- - Where and how to change these database settings? (https://www.linuxquestions.org/questions/linux-server-73/where-and-how-to-change-these-database-settings-4175582151/)

Where and how to change these database settings?

I have a dedicated hosting plan, Centos7. Around 6 websites, with overall traffic of about 1,000 people per day. I moved some websites from a different server and they dont work correctly now. I think that the issue is this:

"First, since you just recently moved to this self-managed server, I am guessing that MySQL's (or MariaDB) configuration file are using the default settings and are not configured to your server. Since your sites sometimes connect, you know that the website's configuration files are using the correct login credentials. However, if the max_connections setting in my.cnf is too low, then when that limit is reached your visitors will not make database connections until some the connections leave. Working on your my.cnf would be a good place to start your investigation.
Another consideration regarding MySQL is it is also possbile when you were on the other server, the indexes in your tables might have been large, and your my.cnf had the correct innodb_buffer_pool_size as well as the correct Key_buffer_size settings to accomodate the large indexes. If your current my.cnf is using the default settings, then your server will use excessive disk i/o slowing the queries and raising the load."

So I need my.cnf and these:
max_connections
innodb_buffer_pool_size
Key_buffer_size

my.cnf file on Centos 7 does not have these, so where would I find it and what values would you recommend to use?

Thanks.

Put them in the [mysqld] section of /etc/my.cnf then restart the DB. If it's default CentOS 7 then it'll be MariaDB but from memory the config file should be the same name.

The values of these settings depend on your traffic, my sites serve around 45,000 pages a day and I use the following:

Code:

max_connections=600

innodb_buffer_pool_size = 4G

key_buffer_size = 2G

The buffer settings are probably overkill but work fine for me.

Thanks a lot. I did not even know that I can add it all to there. I was thinking that it is there and that I need to change the values but this file is more or less empty by default. This may help:

http://www.fromdual.com/mysql-configuration-file-sample

That's quite a nice annotated file, and yeah, the current my.cnf for things like CentOS 7 is pretty empty as the default values are usually pretty good for most use cases.

Quote:

Originally Posted by TenTenths (Post 5560200)

That's quite a nice annotated file, and yeah, the current my.cnf for things like CentOS 7 is pretty empty as the default values are usually pretty good for most use cases.

Where would I find the default values if they are not there. I've seen "show VARIABLES;" command or something like that from withing the MariaDB. Is there any file where this is stored as numbers or something like this? I will be going over these things, so it would be good to see what is what. A person told me that the default values are not very good (this is what the article at the pasted url states too), so I am not sure...

Show variables will show the current values and unless you've changed them on my.cnf you can assume the values shown are default.