Mysql – the recommended the.cnf settings for 32 GB Server

linuxmy.cnfMySQLoptimization

I am just moving my website to a new server and ı want to know what is the recommended settings for 32 GB server?

Mysql tunning for my old server (16 GB)

Best Answer

Suggestions to consider for your my.cnf [mysqld] section

thread_cache_size=100  # to reduce threads_created count of 16K in 42d per MSTuner
table_open_cache=6000  # from 2500 to reduce opened_tables count
innodb_open_files=6000  # from 2500 to be paired with table_open_cache

Welcome to dba.stackexchange.com

Related Solutions

Mysql – some InnoDB settings in My.cnf are messing up everything, why

The next time you start mysql successfully, please run these

SHOW VARIABLES LIKE 'innodb_log_file_size';

This will show you the size of the InnoDB Log Files. The names of those files are (by default)

/var/lib/mysql/ib_logfile0
/var/lib/mysql/ib_logfile1

In order to change the size of the InnoDB Log Files, you must

Add innodb_log_file_size = 15M to my.cnf
service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start (InnoDB Log Files Get Recreated)

I wrote about this before : How to safely change MySQL innodb variable 'innodb_log_file_size'?

This will recreate the InnoDB Log Files.

With regard to

I should note that Horde_groupware database have innoDB tables, when everything is messed up and I SELECT * FROM Horde_groupware.tblname I get the error says bad information in .frm file.

If Horde_groupware.tblname is an InnoDB table and you get this error, run this:

SHOW ENGINES;

You should see all the supported storage engines for the mysql instance. With the erro you are see on that table, there is one of three possiblilities:

InnoDB is not listed as one of the storage engines
InnoDB is marked as DISABLED
The table itself is corrupt

Mysql – What settings should I use in the.cnf

InnoDB

You need to run this query:

SELECT
    CONCAT(IB_BB/POWER(1024,1),'K') IB_KB,
    CONCAT(IB_BB/POWER(1024,2),'M') IB_MB,
    CONCAT(IB_BB/POWER(1024,3),'G') IB_GB
FROM (SELECT SUM(data_length+index_length) IB_BB
FROM information_schema.tables WHERE engine='InnoDB') A;

This will tell you what to set for innodb_buffer_pool_size or 75% of the DB Server's total memory, whichever is smaller.

MyISAM

You need to run this query:

SELECT
    CONCAT(KeyBuffer_BB/POWER(1024,1),'K') KeyBuffer_KB,
    CONCAT(KeyBuffer_BB/POWER(1024,2),'M') KeyBuffer_MB,
    CONCAT(KeyBuffer_BB/POWER(1024,3),'G') KeyBuffer_GB
FROM (SELECT SUM(index_length) KeyBuffer_BB FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN
('information_schema','performance_schema','mysql')) A;

This will tell you what to set for key_buffer_size.

CAVEAT #1

Use common sense about using both buffers in relation to the total installed RAM (See my answer to What are the main differences between InnoDB and MyISAM?)

CAVEAT #2

Once you set these values and start mysql, please download mysqltuner.pl and run it. It will forecast how much RAM is need for all buffers and for connections (it terms of buffers joins, sort, reads)

CAVEAT #3

As for InnoDB, there are other settings you need to set for getting InnoDB to use

Multiple CPUs/cores:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance

InnoDB Tablespaces for Individual Tables

Dec 09, 2011 : What is the best way to reduce the size of ibdata in mysql?
Feb 22, 2012 : Ibdata usage and Recommendations?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Apr 19, 2012 : MySql - Clean ibdata1
Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?