Monday, September 12, 2011

Magento MySQL my.cnf perfect setup

Here is my ever evolving my.cnf file for a Magento install. If anyone has anything to add please comment, I will add it to my script and test!
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

#innodb settings
innodb_log_file_size=100M
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=1800
innodb_buffer_pool_size=20G 

#other vars
net_read_timeout=120
skip-locking
skip-name-resolve
table_cache=2048
thread_cache_size=16
back_log=100
max_connect_errors=10000
open-files-limit=20000
interactive_timeout=3600
wait_timeout=1800
max_connections=200
key_buffer_size=1G
connect_timeout=120

#skip-name-resolve
max_allowed_packet=16M
tmp_table_size=64M
max_heap_table_size=64M
query_cache_size=256M
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=8M
join_buffer_size=3M

old_passwords=1

10 comments:

  1. Brent my magento chum! Are you getting any good results with this config? Currently implementing a Magento site on Zend Server CE, would these config changes help?

    Duffybelfield :)

    ReplyDelete
  2. Hi Brent,

    Cheers for these, could really help to get the most out of our database.

    Would you be able to post up the specs of the server that is running this configuration.

    Obviously a lot of these figures depend on the amount of free memory etc. and I would be interested to know if I should scale up or down the figures for our Magento installs.

    Finally, could I suggest you have a look at MySQLTuner it is a free script you can run on the server which looks for the optimal settings for your my.cnf, you can google it to find the site or I have written up a quick overview of why I think it rocks - http://tosbourn.com/2011/09/mysql/mysqltuner/

    ReplyDelete
  3. Toby, thanks for that, I have seen it and I just downloaded it!

    Sorry about the specs, I actually meant to post it but my brain was moving a couple of steps ahead and I forgot :(

    I have a HP Quad Opteron Dual Core, 32gb Memory, Raid 5. (Old HP G1)

    You script was very helpful!

    ReplyDelete
  4. Obviously most people need to adjust this

    innodb_buffer_pool_size=20G

    to something like

    innodb_buffer_pool_size=2G (depending on your memory

    But I have a 4gb Magento database that is a pain to upgrade!

    I will take the script recommendations and update the blog post.

    ReplyDelete
  5. 32GB Memory! Nice one! Presumably all your database operations are done in memory then. We are only rocking 2GB so have to do what we can to stop any swapping out to hard disk.

    Cheers for sharing.

    ReplyDelete
  6. Yes,I agree,this is the perfect setup configuration,It works. Hire Magento Designer

    ReplyDelete
  7. Nice one, but if i use innodb lines my magento crashes :( had to comment them all.

    ReplyDelete
  8. Thank you for this configuration.

    Poison, do you adjust the value of innodb_buffer_pool_size=20G ?
    innodb_buffer_pool_size has to be max 70% of your physical memory and 20 Go is a very big value (because very big amount of memory on its configuration).

    Regards

    Romain
    croq accessoires pour chiens

    ReplyDelete
  9. It so helpful.Really appreciate for posting this type of.stufVan hire Luton

    ReplyDelete
  10. This configuration does not scale and wait_timeout=1800 with 200 connection limit makes very little sense.

    I would advice to leave your database config alone if you run a small magento shop as this would probably make it slower on modern web servers.

    ReplyDelete