my-large.cnf is missing... help!

[jlpeifer]

I'm attempting to tune my mySQL server a little, so I tried following the instructions at http://help.directadmin.com/item.php?id=44.

Problem is... the only .cnf file in the /usr/share/mysql/ directory is my-default.cnf which is essentially empty because practically every line is commented out. my-default.cnf is the only file ending in .cnf in that directory. Very odd, I think.

How/Where do I obtain my-large.cnf?

CentOS Release	6.4 (Final)
Processor Name	Six-Core AMD Opteron(tm) Processor 2419 EE
Vendor ID	AuthenticAMD
Processor Speed (MHz)	1799.992
Total Memory	2097152 kB
Free Memory	1291120 kB
Total Swap Memory	1048576 kB
Free Swap Memory	692460 kB
Apache 2.2.25	Running
DirectAdmin 1.44.0 	Running
Exim 4.80.1	Running
MySQL 5.6.14	Running
Named 9.8.2rc1	Running
ProFTPd 1.3.4b 	Running
sshd 	Running
dovecot 2.2.6 	Running
Php 5.3.27	Installed

 

[jlpeifer]

I answered my own question about the missing my-large.cnf after a little Googling and found that "As of MySQL 5.6.8, these older files are no longer distributed." So that mystery is solved.

This whole thing started after I complained to my hosting provider that the server seemed sluggish when operating inside of Wordpress sites hosted on my DA server. The hosting provider (a friend who has terminal access to my server) noted that my server's '/etc/my.cnf' file was practically bare... and that it really should contain much more configuration content that would help to run MySQL to run faster.

After removing the commented lines of the '/etc/my.cnf' file I am left with ONLY this...

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

My friend suggested that I run the ~/tuning-primer.sh script to get a baseline.

I've tried every iteration I can think of to make this script work, but I always encounter authentication issues on user 'da_admin'. The script attempts to read the values stored in '~/.my.cnf' to retrieve the user and password details needed to run. When those credentials don't work the script prompts for login credentials to be entered manually. Here's an example of the problem I'm encountering:

# sh tuning-primer.sh 

Using login values from ~/.my.cnf
- INITIAL LOGIN ATTEMPT FAILED -
Testing for stored webmin passwords:
 None Found
Could not auto detect login info!
Found potential sockets: /var/lib/mysql/mysql.sock
Using: /var/lib/mysql/mysql.sock
Would you like to provide a different socket?: [y/N] N
Do you have your login handy ? [y/N] : y
User: da_admin
Password: YaddaYaddaYadda (not really my password)
 
Would you like me to create a ~/.my.cnf file for you? [y/N] : N
- FINAL LOGIN ATTEMPT FAILED -
Unable to log into socket: /var/lib/mysql/mysql.sock

I can successfully log into phpMyAdmin using the da_admin user and password that I'm entering for the script, so I know they're valid.

After this failed I decided to try an alternative tuning tool called mysqltuner.pl from http://mysqltuner.com/. Unfortunately I run into very similar issues when attempting to run this perl script.

# perl mysqltuner.pl 

 >>  MySQLTuner 1.3.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[!!] Attempted to use login credentials from DirectAdmin, but they failed.

I have checked the ~/.my.cnf file and find that the username and passwd stored there are correct. All commands are being run as user root and all files involved are owned by root:root.

Can anyone provide some input here on how I might go about:

• getting these scripts to run? or,
• adding some type of basic MySQL tuning to my DA server?

Thanks in advance.

P.S. This server's fresh install of CentOS and DA only occurred about 6 months ago. I haven't touched anything regarding MySQL since (assuming that it would all be configured properly after the DA install).

 

[ditto]

I answered my own question about the missing my-large.cnf after a little Googling and found that "As of MySQL 5.6.8, these older files are no longer distributed." So that mystery is solved. [...cut...]

Can anyone provide some input here on how I might go about:

• getting these scripts to run? or,
• adding some type of basic MySQL tuning to my DA server?

[...cut...]

I have never tested any of the scripts to run to optimize mysql, so I can only answer the second question. I have always just had a very minimal configuration in my.cnf, here is everything I have in my /etc/my.cnf:

[mysqld]
local-infile=0
open_files_limit=30000
max_allowed_packet=20M
default-storage-engine=MyISAM
innodb_file_format=Barracuda
innodb_file_per_table=1
performance_schema=off

Please note the line "performance_schema=off", when you run mysql 5.6.x it is on by default, previous version of mysql it was off by default. I have read alot about it, and it seems very clear that it gives overhead and is better to have off. Remember you need to restart mysql after adding anything to /etc/my.cnf

# Regarding performance_schema please see my reply #2 in this thread: http://forum.directadmin.com/showthread.php?t=47070&p=241674#post241674

# Regarding my "open_files_limit=30000" please see the same thread I describe a problem I had after upgrade to mysql 5.6.x in reply #7: http://forum.directadmin.com/showthread.php?t=47070&p=242176#post242176

Please note that the line "open_files_limit=30000" is something I raised after to this high level after upgrading to MySQL 5.6.x It was suddenly not enough with 1024 after the upgrade, it would no longer be able to make nighly backup of all databases with previous 1024 value. So one thing is very clear, and that is MySQL 5.6.x does use/need MORE open files limit.

Also if you run CentOS, your open_files_limit in my.cnf might be overruled by you os settings, it was for me, and I needed to first edit the file: /etc/security/limits.conf by adding these two lines to the bottom of the file:

*                soft    nofile          65535
*                hard    nofile          65535

You can check what limit your CentOS have by running this command:

ulimit -a

Then look for the line that looks like this:

open files                      (-n) 65535

If that line shows for example 1024 or another low value, then it will not work to set open_files_limit=30000 to my.cnf, but you first need to raise the value at the OS level, like I posted above, you just change your /etc/security/limits.conf by adding two lines, then you can raise the limit in my.cnf and restart mysql.

# Regarding innodb_file_format=Barracuda and innodb_file_per_table=1 setting, please read this thread: http://forum.directadmin.com/showthread.php?t=43886

# Regarding local-infile=0, this is for security, please see: http://help.directadmin.com/item.php?id=208

Edit: I found one more relevant link for you: Upgraded to MySQL 5.6.13 can't backup databases: http://forum.directadmin.com/showthread.php?t=47098

 

[SeLLeRoNe]

Use this command line to run mysqltuner.

./mysqltuner.pl --user da_admin --pass `cat /usr/local/directadmin/conf/mysql.conf | grep pass | cut -d\= -f2`

Regards

 

[jlpeifer]

Thanks SeLLeRoNe... I tried your suggestion...

Use this command line to run mysqltuner.

./mysqltuner.pl --user da_admin --pass `cat /usr/local/directadmin/conf/mysql.conf | grep pass | cut -d\= -f2`

The result was...

# ./mysqltuner.pl --user da_admin --pass `cat /usr/local/directadmin/conf/mysql.conf | grep pass | cut -d\= -f2`

 >>  MySQLTuner 1.3.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[!!] Attempted to use login credentials, but they were invalid

Your script is pulling the correct password from the /usr/local/directadmin/conf/mysql.conf file, so I'm again perplexed.

 

[jlpeifer]

Thanks Ditto! I will have to dig into this tonight when I can afford a little down-time on the server. I'll let you know.