-
- gabrielk
 -
Total Posts: 73
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?
|
Sales: Call 877.832.5289 (N America)|310.295.4144 (International)
