Speed / Performance Optimization for osCommerce [repost]

Now that you have your store up and running with products uploaded it's time to start looking at your page performance. Generally speaking customers love fast loading pages. If they have to wait on a page to load you decrease your conversion rate. So let's get down and discuss a few basic but essential performance optimizations.

IMAGES
By far the worse offender for slow loading pages! The simple answer is to install an automatic thumbnail generator with cache features. It is important to use an autothumbnailer that will cache the image instead of doing it "on the fly" as it decreases server load tremendously. The effects may not be noticeable on a store with 1 or 2 visitors per hour but if you do any kind of serious volume you will defitely want to cache the images. After all...if you've spent the server resources to create the thumbnail image why not store it and use it later instead of creating it again?

CONTRIBUTIONS
Install ONLY the ones that you need to operate and ONLY the ones that will either save time or add value to the shopping experience. What contributions will fit these requirements will depend on the type of store you have and products for sale. Each contribution you install will add overhead to process the logic whether it be pure PHP code or MySQL queries (or both). As a general rule KEEP THE NUMBER OF CONTRIBUTIONS INSTALLED TO A MINIMUM. Too many times a store owner will go contribution happy and install every toy possible. This adds tremendous bloat to the code base and performance suffers...the intent was to make a better shopping experience but the net effect is to DECREASE the experience due to slow loading pages.

REMOVE FEATURES NOT USED
There are several features of stock osCommerce that stores may or may not use that would help performance if removed. For example: banners, "requests since" footer display, who's online, etc.

If you don't use the features remove (comment out) the code.

Another benefit of eliminating those features is that it prevents a store from using the MySQL query caching features. On each insert the cache is flushed. Of course, this also means storing the sessions on the filesystem!

STS - Simple Template System
I wanted to give special attention and space to this contribution since it is one of the worse offenders for queries and is also one of the more popular template systems available for osCommerce. If you have a significant number of categories on your store this contribution will kill your page performance. It performs a full table scan on each page request. If you have more than a few hundred categories it will increase your page load time by 200-300% based on server specifications.

In short, if you want speed don't use a template system...learn to modify the monolithic application that is osCommerce.

SESSIONS
If you store the sessions in the database consider adding a (primary) multi-column index on sesskey and expiry columns. This will take up more physical space but will be much faster and will be a const query type.

CACHING - Stock Code and Page Cache
The ability to cache data is very important to performance...especially if you have a large number of categories, products, and/or orders. I recommend creating a directory ABOVE the publicly accessible document root and giving it proper permissions for the server read/write. Creating it ABOVE the document root ensures that would-be hackers cannot access it with their web browser. Set your cache directory settings to this filepath to keep it from being stored in the "/tmp" folder which will cause issues.

Once you have the cache folder created and settings configured TURN ON THE CACHE FEATURES. This is especially effective with the category box!

The Page Cache contribution was created for those stores that have so many contributions installed it would be nearly impossible to optimize the code. The correct answer is to not bloat the store with contributions that are not needed...but if you find yourself in that position then Page Cache may be for you. I use the Page Cache contribution as a last resort...not as a first line choice.

COMPRESS YOUR PAGE OUTPUT
Most are aware of page compression via GZIP. The optimal setting is compression level 1 for speed as higher levels will not result in signifant reduction of page size. However, a quick trick is to phyically compress the data being GZIP'd by removing extra whitespace and line breaks.

Here is the function that I use:

function compress_buffer ($buffer) {
	# Return the compressed buffer
	return str_replace("\n", ' ', preg_replace('/\>\s+\</', '> <', $buffer));
}

Code (markup):

Of course, this is offered as a starting point for you and you'll have to figure out how to use it in combination with GZIP.

The physical page compression with GZIP compression will reduce the output HTML code by some 60-75% and will dramatically increase page performance especially on dial connections.

CODE / QUERY OPTIMIZATION
I wanted to approach this topic last as it has the most variety. This is due to the fact that every store is different. They all start with the same code base but each store installs different contributions which results in great variation as to which code needs to be optimized.

A good starting point would be to install a per page query output contribution. This will allow you to see which queries are being executed on a per page basis and easily identify those that are redundant or taking excessive time to execute. Once you pinoint what needs to be done it's easier to actually do it...never perform ANY optimization blindly. ALWAYS modify code for a purpose. Sounds like common sense? You would be surpised...

Once again, I won't approach specific contribution optimizations but will only discuss those that are common to every osC store. One such contribution is an abstraction of the MS3 tax class for MS2. This replaces the stock MS2 tax code with the new tax class for MS3 which is much more efficient and uses less queries per page. The tax query is EXECUTED ON EACH PRICE DISPLAY EVEN IF THE SETTING TO DISPLAY TAX IS DISABLED. It's important to know that fact since most think if they turn off the option in the admin control panel it will not query for the tax...this is false and even if the setting is turned off it will STILL RUN THE TAX QUERY. Why is this important? Because the tax query is one of the most server intensive queries for all of osCommerce! For a 2 minute install it will increase the page performance / server load tremendously.

The also_purchased module (product info pages) is a powerful upsale tool however it is the absolutely most server intensive query for the osCommerce application. This is especially true for those stores that have a significant number of products and orders. I've seen this bring a dual 2.8 Ghz CPU, 1Gb RAM server to its knees...by just running that 1 query. As you accumulate more orders you will get a feel for which products would be ideal cross sell items and thus it would be advisable to install a X-Sell contribution. It may be a bit more work to maintain but the increase in product page performance is well worth the effort.

On each page request the contents of the configuration table are loaded as define statements. The implication of this is that on each page request the server must perform a table scan to generate the data...which depending on how many contributions you have installed will add overhead to each page load. An excellent contribution that addresses this issue is Faster Page Loads, Less DB Queries. This contribution caches the data and eliminates the database query...thereby saving the table scan for when the cache is not present. This contribution is HIGHLY recommended.

This post is not meant to be a difinitive guide to optimizing an osCommerce store but will hopefully give you a few pointers as to which areas should be addressed as a priority to your optimizination efforts. I hope this post will spark the creativity and discussion that makes the community the valuable resource that it is...

Cordially,

Bobby Easland

 

Bobby you are the author of all this stuff, right?

 

Yup...it's my bad spelling and broken keyboard that produced these posts.

Bobby

 

So you also code for the OScommerce platform

 

I've been accumulating parse time data with the built in stock version's parse time log and it varies considerably, ranging from pages as fast as 0.4s to as slow as 15 seconds (rather unacceptable ofcourse). The average seems to be 1.8 ish.

Bobby, do you have any of those stats you could share?

Do you know of a parsetime_log interpreter that can give me some real averages/medium/means etc.? And find the slow pages with the click of a button instead of scrolling through thousands of entries?

 

I have released close to 80 code contributions to the osCommerce project...some were released as anonymous (so I didn't have to support them) and about 30 or so under my Chemo moniker which I support publicly.

Not only do I release free code contributions but I've also optimized, created, or modified more osC stores than I can count in the past few years. I stopped updating my portfolio after I hit 500 or 600 sites. I tried to keep track again last November and stopped after about 80 or so in the first 2 weeks...and those were just the freebie jobs.

My main source of income is my manufacture business which is offline distribution and not internet based. I have 2 shift managers that handle day to day operations so it leaves basically all day to pursue interests. Some like to play sports to pass the time...I code PHP applications as a passionate hobby.

Not to blow my own horn...but I'm the one the veterans come to when they have a tough question about osCommerce.

Bobby

 

Sure glad you are a member here even if you came in through the back door, look forward to learning from you and hope you hang around long term

 

Don't use the stock query debug tool...it'll chew gigabytes of data and none of it will be meaningful.

I've released about 4 or 5 contributions directly geared to developers or those optimizing their stores. Here's a quick run down:

More Meaningful Data from Query Logging
This contribution replaces the stock logging code to only capture queries that take longer than "X" seconds to execute. Instead of storing every query it only stores the ones that are excessively resource intensive. Also, it stores more useful information such as query, time to execute, calling script, IP of browser, cookies enabled or not, and other nice data.

osC Explain - for developers
This contribution adds a real nice EXPLAIN feature to every query executed. It allows you to capture each query executed and associated MySQL EXPLAIN data. If you are serious about query optimization this tool is a must.

Queries and Parse Time Footer Output
This contribution outputs the queries executed and page parse time on a per page basis without storing it to filesystem. This is handy to optimize page performance on a per page basis. I use it to identify redundant queries or as a quick tool to diagnose performance issues. You can tell at a glance if the lack of performance is due to MySQL, PHP, or other page factors.

osC-Advanced Cache Class
This contribution was released as a standalone class since it is very versatile and robust. It forms the backbone of my Ultimate SEO URLs contribution and has the ability to cache anything you can throw at it: parsed HTML, arrays, or even executable PHP code. If you find tough areas to optimize this class will solve your problems.

Bobby

 

What can I say... I might have to spend less time here and get my ass over to the OsC forums and increase my time spent there 100 fold.

I've always found it hard to find contributions since the search isn't that powerful and sometimes you know your problem but not how to search for it. Same with the forum. They should make a directory structure for the contribs with powerful search and - I've been annoyed by this a lot - MAKE the contribution poster fill out a proper form saying in detail what the contrib is and what it isn't. Some code geniusses can make brilliant stuff but don't have a clue how to 'market' it or communicate their ideas to other fellow coders.

Anyway, thanks again - especially that first one seems very useful to start off my performance analysis.

 

I'm trying to get the project leadership to install a user feedback / ratings / review system for the contribution area. I agree with you...the contribution area is a mess.

A contribution can have minimal description and be the best thing since sliced bread...or have an incredible description of capabilities but then not function. There needs to be an option for those that install to leave feedback on ease of installation and use plus their general feedback.

Bobby

 

geez...you asked for stats and I only offered links to contributions. My bad...

OK, the golden standard thus far in terms of speed and performance is Chemo's osCommerce Corner. I put an open challenge on the forum for someone to beat my page statistics -> 0.028 seconds parse time and 9 queries executed. The reason wasn't to toot my own horn but to highlight the fact that osCommerce out of the box is pretty query and resource intensive. Of course, they would first have to install the per page performance output which will give them insight into their current code. Most of the time they find the page query count to be 100-150 per page (depending on number of contributions installed) and in the >1.0 second parse time range. Most will be able to get that down to around 30-40 queries per page and ~.150 seconds parse time with basic code optimization.

As a side note, the URL above is my osC development server where I code all of my contributions. So, from time to time it may be broken but usually means I'm on the backend turning wrenches.

All of my public (and some not so public) contributions are installed so you can see 2 in particular in action: Ultimate SEO URLs and also the per page query output data. I believe the performance output contribution will help you solve your problems faster. To see it in action you just need to append ?output=1 on any URL and ?output=0 to turn it back off. The great thing about it is that it only ouputs the data when turned on through the URL and is only active for that single web browser. I use it to debug live stores without having all the customers see me working.

Performance Output Turned On
Performance Output Turned Off

Turn on the output and then scroll to the footer where it is ouput. The basic information presented is number of queries executed and the time for execution of each. In the contribution release it also outputs the GLOBAL data such as SESSION, COOKIE, POST, and GET. I turned it off on the dev server since I use other tools.

Another great feature of the contribution is that it does not store the information to filesystem or database...it only outputs the data live and on a per page basis.

Bobby

 

Have you done any work in regards to importing data to QuickBooks?

The reason I ask is, last year we tried a few contribs that touted importing data into QB and had no luck. We eventually bought a very costly package from StoneEdge Software that a lot of people were touting. We had major problems with StoneEdge, in getting a Fedex solution working (I believe it works now), and some others I won't go into, but the bottom line is we gave up on StoneEdge.

We've been looking for a solid way to import OSC sales data to QBs to reduce time spent manually copy/pasting all the data.

 

I have some experience with QB but usually defer questions to the actual contribution coders. Although I don't live and die by money donations to me for the free code it could be a substantial part of someone else's college beer fund. Most of the time it will turn into a small donation fee to have the contribution coder install (since that person knows the code inside and out and who better to install professionally?).

If you have any direct technical questions I'll answer to the best of my abilities...but if there is even a remote chance that you would be willing to pay a few dollars to install, tweak, or mod the contribution go to the contribution author first. I'm sure they would appreciate it and will most likely be the best path to a hassle free and professionally implemented custom solution to your needs.

Bobby

 

I'm still looking for Sage Line 50 but luckily not a priority.

That dev shop speed is impressive - will definitely optimize ours soon.

 

Thanks Bobby. Paying is not an issue for us. Installing contribs on the server side is no problem. It's getting them to import correctly without QB crashing. Stability.

I never contacted the authors of either of the two we tried. When I looked on the OSC Forums I saw posts by others having similar issues we had with no response from the authors, so figured they were not supporting the contributions.

We shelled out $1350 for StoneEdge and the support package and sent it to the trash bin. Money wasn't the issue (though we don't just give it out!), but having something that actually works is what is really important.

If you know someone who has a good contribution for QB that actually works, free or paid, please let me know.

 

Bobby I can see this OScommerce game is not for non-programmers, is there a list of contractors that OS makes available to install and help maintain this platform and do you also do contracting for companies that want to start

 

The osCommerce project does not support commercial coders for the application. If a member posts on the board that they are looking for something to be done and are willing to compensate the thread is usually deleted VERY quickly.

I've tried to organize some of the veterans and start an osC-Freelancers site but there hasn't been too much interest. So, I sandboxed the domain and am doing a slow link campaign now to build theme and diversity.

If you need some work done I can definitely refer you to some good coders / designers. I've been around long enough to know who are coding machines and who shouldn't be trusted with a keyboard

Bobby

 

Sounds kind of insane to me to have a policy like that, those guys need to wake up over there, I asked the same thing about finding a good host and they took that thread down and told me a good one was advertising on the banner ad on the top of the forum.

Stupid if you ask me.

Someone should start a resources directory if they want folks to adapt this technology platform, all the commercial providers like www.storesense.com have a list of contractors/hosting/asp/consultants and OS is missing the boat if they don't do the same.

This is the real world and it is a business platform and businesses are willing to pay to get professional services as part of their cost of administration/MIS budgets, if they don't do something you should simply start another directory because I and others would use it to find professionals that understand the technology.

 

Nice to see your posts here. I just stepped into osCommerce for the first time last week. I'm setting up a test site to convince a potential client use it, rather than hardcoding products into the site, and also so I can learn it.

It looks like it can do all kinds of things but installing user contributions seems a bit of a nightmare.

I'm trying to add product sizing as an option that is a radio button, rather than a dropdown. Since the user contribution has 4 or 5 updates spanning different versions, I have to go through each one and install line by line. Clumsy, and at the end it may not even work the way I want.

 

Are you talking about the attribute types contribution?

You don't have to install the archived releases...only the latest stable release. It's not as hard installing a contribution as is first assessment. Most of us create fairly thorough documentation so as to eliminate all the small support questions down the road.

Give it a try and tell me how it goes!

Bobby