Related to this question.
Any recommendation on tools, plugins for optimizing a large eCommerce site on WP. Currently hitting 1000 items and 5000 more items coming. It seems the optimizations would be different than a standard blog serving thousands of users. The number of stores being developed on WP is increasing and putting a package of optimizations together would appear helpful.
MM/RC
I’ve developed a 55k product ecommerce site using WordPress with the Shopp plugin and can share what I’ve done to MySQL to eke out better performance, YMMV and some (or all) of these may not apply to your situation.
Determine how much you need to increase buffers/caches by by looking at the output from a “show status” sql command – there are tools which pretty up this output which may be useful, I often just use phpmyadmin to get an idea. http://blog.mysqltuner.com/ is a handy resource and utility too.
Make sure your query cache is on and that it’s large enough to have a very low low memory punes number. Make sure your the number of key reads is not too high (this being a relative value), increasing key_buffer_size can help with that. Make sure that the number of temporary tables being created is low, reduce that number by increasing tmp_table_size.
Turn on the slow query log and log slow queries, re-run those queries manually with explain statements, add indexes change column types, etc, as needed. For one query we were getting insane explain statements (as in comparing many, many more rows than it “should” have been), upgrading to a newer version of MySQL caused that same query to be much better optimized (and much quicker) by MySQL itself.
If you’re using full-text indexing I don’t believe InnoDB tables are an option – just don’t update products during your busy times. If your ecommerce package supports it you can minimize customer disruption (performance or otherwise) by making changes on your staging server and then moving over just the product tables with a dump from stage and loading onto production – this will not work without some additional work for the rest of your WordPress installation however.
If your site design and UX can support it guiding the users into a “browsing” mode vs. a “searching” mode (eg: clicking around a site vs typing searches into the search box) can help you take advantage of some very easy database level and WordPress level caching.
Pre-load your caches – easy caching like query caches and WordPress-level caching can also be pre-loaded by spidering your site with wget. You can also pre-load the cache for searches by using user search behavior (via your logs) and re-getting those requests when you need to fill the caches.
I was curious about my own comment and have been doing some reading, here is where I am at.
First off, I would give W3 Total Cache a try. Also, install APC PHP extension on your server and set W3 Total Cache to use it. It will use APC to cache Database objects, PHP, even help minify your CSS and JS. It might provide enough. Especially if you incorporate some MySQL cache already. W3 Total Cache also can work with Memcache as a caching backend.
For the Database, caches the queries is very useful. I found this link and he explains how he did somethings for his MU blog. To Quote:
So this is pretty cool, and will help a lot since the products themselves won’t be changing much. Another thing the article mentions is to make sure you have lot so memory to hold the database in Ram, this will help the speed the queries dramatically.
He also talks about the table types, and using MyISAM over InnoDB for the mostly read-only tables, while this he reasoning is a bit MU oriented, it could be useful.
In regards to setting up the indexes. If you have a lot of them, they can slow down the inserts, and take up more disk space. But as I don’t imagine a large amount of products being added on a regular basis, I think this is acceptable. And with disk space being cheap …