Magento Forum

   
Optimizing MySQL for Magento (getting better performance)
 
gabrielk
Sr. Member
 
Avatar
Total Posts:  83
Joined:  2007-11-30
 

This is more a tips & tricks post based on our recent experiences on www.shoebacca.com

It’s no joke that Magento requires more resources than most people are used to allocating.  While I think the Magento code could be optimized quite a bit, we’ve gotten amazing results just by optimizing MySQL.  Note that optimizing MySQL may not be possible on most shared environments; you need at least a VPS and preferably a dedicated server or combination http server + db server.

Our page loads for product details went from 3-6 seconds down to 0.6 - 2.6 seconds (averaging around 1.9 seconds to load a product page).  Note we don’t have a typical Magento install, our skin is much different, so results from the default theme will be different.

Some tools that will really help you are:
calculate mysql memory requirements based on current config settings:
http://forge.mysql.com/snippets/view.php?id=44

mysql status report:
http://hackmysql.com/mysqlreport

These are both shell scripts for your db server.

The most relevent settings from our my.cnf file are:

key_buffer = 384M
max_allowed_packet = 1M
table_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 16
query_cache_type = 1
query_cache_size = 48M
log_slow_queries=/var/log/mysqld.slowquery.log
max_connections=500
wait_timeout=120
tmp_table_size = 64M
max_heap_table_size = 64M
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 20M

The actual values should vary based on your server machine’s capabilities, obviously.  The guide at mysqlreport (above) will help you pinpoint a lot of the potential bottlenecks.  http://www.mysqlperformanceblog.com is also a great resource.

__ Key _________________________________________________________________
Buffer used   139.00k of 384.00M  
&#xUs;ed:   0.04
  
Current      68.34M            &#xUs;age:  17.80
Write hit      99.58%
Read hit       99.67%
The key_buffer we have set is really, really big compared to what’s actually being used.  The database is running off InnoDB engine, so this is really just for the temp tables MySQL creates.  We’re using the “better safe than sorry” approach, as key_buffer doesn’t actually reserve the memory you allocate to it, it just takes what it needs up to that limit.

__ Query Cache _________________________________________________________
Memory usage   38.77M of  48.00M  
&#xUs;ed:  80.76
Query Cache is HUGE in boosting performance.  The interesting thing is, we started out using a query cache of 32mb and Magento was consistently using around 80% of that, so we icnreased it to 34mb...again, 80% used.  Most recently I bumped it up to 48mb, and we’re still hovering at 80% used.  Since we have 6gb of RAM on our DB server, I’m not worried about bumping this up, but it seems like it’ll eat as much as I give it.  Every bump has produced noticeable results in performance.

__ Tables ______________________________________________________________
Open              202 of 1024    
Êche:  19.73
Opened            208     0.0/s
I’ve heard it’s good to have your table_cache to about 50% over the number of tables opened.  Before rebooting mysql last we were getting upwards of 320 tables opened.  I set this to 1024 just to have a big, round number, but after monitoring it for a while I expect to be able to lower it to around 600.

__ Connections _________________________________________________________
Max used           29 of  500      
&#xMa;x:   5.80
This is what killed our store when we sent out an e-mail blast.  We were getting well over 100 connections (the default), so we set this conservatively high.  We haven’t gone over ~130 connections, and we have some custom code that could use optimization, so YMMV on this one.

__ Created Temp ________________________________________________________
Disk table      4.55k     0.3
/s
Table          16.71k     1.2
/s    Size:  64.0M
File           10.26k     0.7
/s
Disk table = bad.  So far we’re still getting lots of disk tables no matter how much I bump the tmp_table_size and max_heap_table_size values.  I believe that some data types, like text and blob fields, will always use a temp disk table, so I think that’s what’s going on here.  This could be set to about half what it is without much problem, I just bumped it higher to see if I could lower the disk tables created (but it didn’t work).

__ Threads _____________________________________________________________
Running             3 of    4
Cached             13 of   16      
&#xHi;t:  99.81
Created            29     0.0/s
Another big one.  We have a dual processor server, so we’ve capped it at 4 threads, I haven’t seen it use more than 3 so far.  Cached & created threads are the important thing to watch here.  From my experience and what I’ve read, you basically want to keep bumping up the number of cached threads to equal the number of created threads, so that you’re maximizing the use of cache.  Next time I’ll bump this one up to 30 threads cached.

__ InnoDB Buffer Pool __________________________________________________
Usage         295.86M of   2.00G  
&#xUs;ed:  14.45
Another big one: the InnoDB buffer.  The default one is TINY.  We also got a HUGE boost by bumping this innodb buffer to 2gb (what I’ve seen recommended is about 70% of your RAM, but that’s based on a 2gb ram size).  Again, since we’ve got an obscene amount of RAM on this box, I set it really high.  It’s currently using a little less tahn 300mb of this, and I am “overselling” the resources a bit (if we max out on threads and connections we’d need 8gb of RAM rather than 6, but we’re nowhere near that yet, so I’ll worry about it when I finish getting the performance where we want it).

So to sum up, if Mage is running slow to you, increase your buffers and caches! 

Anyone else have optimization tips?

 
Magento Community Magento Community
Magento Community
Magento Community
 
srinigenie
Guru
 
Avatar
Total Posts:  539
Joined:  2008-02-04
 

whao! Gabriel!! Thanks ...I am sure that will be a lot of help for most of the members here smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
cpetrauskas
Jr. Member
 
Avatar
Total Posts:  6
Joined:  2007-09-04
Portland, Oregon, USA
 

@gabrielk

Wow, great info, thank you! I never knew about the mysqlreport tool. Very handy!

I’m just dropping in a cross-reference to a blog post that Yoav from Varien wrote regarding optimization:

http://www.magentocommerce.com/blog/comments/performance-is-key-notes-on-magentos-performance

Thanks,
Chris

 
Magento Community Magento Community
Magento Community
Magento Community
 
chiefair
Mentor
 
Avatar
Total Posts:  1839
Joined:  2009-06-04
 

Addtional tuning pointers…

Check your daemon.log for the following:

InnoDBERRORthe age of the last checkpoint is 9452119,
InnoDBwhich exceeds the log group capacity 9433498.
InnoDB
: If you are using big BLOB or TEXT rowsyou must set the
InnoDB
combined size of log files at least 10 times bigger than the
InnoDB
largest such row.

InnoDB is a transactional database and this indicates your parameters for transaction buffering and logging are way out of whack.

Some InnoDB tuning parameters, this was done on a relatively unloaded dev server, the buffer pools and transaction logs need to be bumped way up for production:

# Run a 64-bit operating system. Do not exceed 2GB memory for the following
# combination on 32-bit systems:
# innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size +
# read_buffer_size + binlog_cache_size) + max_connections*2MB

# On dedicated db server assign up to 80% of free memory to innodb buffer
# On combined web/db server take into account apache instances and php
innodb_buffer_pool_size         768M  #Data Page Memory Buffer

# Table metadata requires 2-16M, Magento only has abt 330 tables
innodb_additional_mem_pool_size 2M    #Data Dictionary < 16M

# SHOW GLOBAL STATUS Innodb_log_waits should be 0 or the next setting
# innodb_log_file_size is too small. Holds changed data for lazy write to
# innodb database. To change size, database must be flushed to disk, database
# backed up, mysqld server must be downed, log files renamed, change
# my.cnf and mysqld brought back up. Log files will be recreated in
# proper number and size
innodb_log_file_size            100M  #Transaction Log up to 1/4 Buffer Pool
#innodb_log_files_in_group      = 2     #Default is 2

# Buffer for transactions to be written to log file
innodb_log_buffer_size          2M    #Transaction Memory Buffer 1-8M

#Recommended (2xcores)+disks, KEEP LOW < 16 More is not better tune for
#best performance
innodb_thread_concurrency       3

#Set this up before you install Magento. Create primary innodb table at 2GB
#and secondary autoextend table for growth
#Changing this after you've gone live requires eliminating the ibdata files and log files
innodb_data_file_path ibdata1:2048M;ibdata2:50M:autoextend

#Amount to add on autoextend. Keep this large to keep blocks in file contiguous
innodb_autoextend_increment 512M

Note! Changing InnoDB data storage file size after the fact will blow away your database, see MySQL recommendations for changing or adding your InnoDB file space. It requires a complete backup and restore of all your data.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
    Back to top