1

I'm running a large MySQL Database, in the range of 30Gb in size. The MySQL 5.5 server is running on a dedicated server with 60GB of RAM, with no other processes installed apart from monitoring tools, on Debian Wheezy.

I've read around the best way to configure the my.cnf file and opted to increase

innodb_buffer_pool_size = 40G

Adding that to the [mysqld] section of the my.cnf file resulted in the MySQL service failing to reboot. Error log shows no errors. Removing the variable change makes the server load properly.

Am I missing something or doing something that's not supported? If I am, can you point me towards the right direction on how to best optimise the MySQL configuration to make best use of the given memory and CPU processing power (16 CPU Cores). I've looked around but could only find information related to 4 or 8Gb of memory.

my.cnf file contains the following

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]

user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /var/lib/tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address            = 0.0.0.0

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP

query_cache_limit       = 1M
query_cache_size        = 16M

expire_logs_days        = 10
max_binlog_size         = 100M


#innodb_buffer_pool_size = 45G

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

Memory free on the server when MySQL is switched off is 58GB

  • Failing to restart the mysql deamon - without errors? What does the error log show for the restart attempt? – ypercubeᵀᴹ Jan 17 '15 at 18:15
  • That's the strange part. Nothing in the log file, the service returns a "failed to start" message. – Lincoln Jan 17 '15 at 18:17
  • Two Requests: 1) Please post the entire my.cnf into the question. 2) With mysqld shutdown, please run free -m so we can see how much RAM is free. – RolandoMySQLDBA Jan 17 '15 at 19:46
  • Updated the question with what you requested. The commented out line is what I added in order to be able to switch the service back on. – Lincoln Jan 19 '15 at 11:13
  • What is in mysqld.err? 70% of RAM is a reasonable value for innodb_buffer_pool_size; 40G/60GB is reasonable. Is there any chance you are running a 32-bit MySQL?? That would be a problem. – Rick James Feb 6 '15 at 6:47

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.