[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
Magento tips and tricks as well as other completely unrelated items by Brent W Peterson
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!
Subscribe to:
Post Comments (Atom)
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?
ReplyDeleteDuffybelfield :)
Hi Brent,
ReplyDeleteCheers 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/
Toby, thanks for that, I have seen it and I just downloaded it!
ReplyDeleteSorry 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!
Obviously most people need to adjust this
ReplyDeleteinnodb_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.
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.
ReplyDeleteCheers for sharing.
Yes,I agree,this is the perfect setup configuration,It works. Hire Magento Designer
ReplyDeleteNice one, but if i use innodb lines my magento crashes :( had to comment them all.
ReplyDeleteThank you for this configuration.
ReplyDeletePoison, 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
It so helpful.Really appreciate for posting this type of.stufVan hire Luton
ReplyDeleteIt's really informative post dear. Zero Clients & Thin Clients
ReplyDeleteI have bookmarked your website because this site contains valuable information in it. I am really happy with articles quality and presentation. Thanks a lot for keeping great stuff. I am very much thankful for this site. plugins for Magento enterprise
ReplyDelete