"SQL Optimization", Sudden onset of slow page rendering and Site Management.

[David M. Graham]

In responding to a question about a sudden onset of slow page rendering which affected a CRE Loaded installation on Siteground I realized I had generated a fairly useful list of guidance for dealing with this type of scenario. The question was "what can I do to optimize the SQL, and can this be done with CRE Loaded." So, here it is:

It probably is an optimization issue - and certainly it CAN be done.  But the issue is not necessarily with your cart software alone.

If this was a sudden change, not linked to a software update to your application software file set - then it had to be related to something else.

While reaching a tipping point on the size of the order or product table sets could cause this, it is also possible that a server change of some sort at siteground (or any other host for that matter.).  It could also be customizations added to your site.  MySQL is a complex beast.  Movement of your site to a different server software set, or upgrading of the software on the existing server is a definite suspect here.

Here are some questions to ask:

1. Have you added a large number of products to the cart recently?
2. Have you been adding products continuously without ever deleting any?
3. Have you been steadily accruing sales records without deleting them?
4. Have you changed templates recently?
5. Have you had any custom work done lately?
6. Have you added any banner advertising recently?
7. Have you been linking to offsite images for any links on your site?
8. Have you patched your site recently?  If not, what revision is it, and why haven't you?
9. If you have patched the site - did you MERGE THE PATCHES to your template?
10. Has SiteGround updated your MySQL server recently?

These are all issues which could affect the speed of your cart.

The first 3 would be good problems to have.  They can also be addressed fairly simply by removing inactive or poorly performing products or clearing out orders over a certain age into a storage location elsewhere. 

Numbers 4 and 5 are things you would certainly remember.  Users rarely consider templates as custom work - but in the osCommerce arena that is what you need to do.  Addition of a new template invariably results in the installation of stale SQL queries which have not been subjected to analysis and optimization by the development team.  In this case, reversion of the cart to a stock template would speed things up (Useful from a diagnostic standpoint - not a recommended solution).  Given that you experience the same affect on the backend, I don't think these are likely

Numbers 6 and 7 are chronic snakes in the grass.  Both of them are just waiting there to bite you in a sensitive spot without warning.  DON'T DO THESE THINGS.  Off site links like this are attractive in that they save you storage, but they slow page rendering time EVEN WHEN THEY SAVE YOUR BANDWIDTH! Again - you can fix these by obtaining the images and installing them on your site where ever it is possible.

Numbers 8 and 9 deserve careful attention. Take the software update first. Your software needs to be kept current.  This type of event is one reason why!  However, in practice it rarely is - why?  This is usually because even seasoned pro's tend to modify an existing template and don't want to over write their modifications with the patch. This can be avoided by creating your own template - instructions are available for free on the creloaded forums, or in my book; more costly but certainly quicker.

Next, consider the process of merging the patch with your template.  This means a competent programmer or daring webmaster uses a code comparison tool to check the new code set against your template and change your code so that the key portions work the same way as the new patch while preserving your HTML markup and CSS styles.  The concept is simple though the practice sometimes is not.  This is ESSENTIAL both when installing the TEMPLATE and when installing ANY PATCHES issued after template installation.  Why?  osCommerce and all derivatives currently existing mix business and site operation logic freely with the HTML markup.  You can not fix one without touching the files holding the other.   Systems such as BTS and ATS minimize this affect but do not eliminate it.  I say this as a designer of the systems involved (especially ATS which I highly recommend though it is a conflict of interest).  If you install a patch, but do not merge changes with the template YOU HAVE NOT INSTALLED THE FULL PATCH.  That bears repeating - you have not installed the full patch.  If this is the case on your site - get this taken care of as soon as you can.  It may help you a lot or just a litte, but it remains essential to cart performance and security.

Number 10.  Ah - good old number 10, the MySQL server upgrade.  The MySQL team is constantly at work on their server making it more compliant with ISO standards for Structured Query Language behavior, UTF-8 (multi-lingual character set) and other standards.  In the long run, all of these things help you reach more customers in more places.  In the short run, it often results in just such problems as you are facing as PHP comparisons suffer and code breaks, or simply slows under the weight of more character set conversions.  One thing you can do about this is to make the situation known to the development team in case preventive measures could be taken.  Another is check for character set mismatches - and have someone convert your database back to the carts Latin1: Swedish default if this is needed.  If the slowness is resulting from having to convert character sets, this would help fix the problem.  But there is another aspect to this kind of event which your host COULD help you with.  Very often, MySQL server installations are "tweaked" to best accommodate the types of databases they are serving - and sometimes when a new upgrade is installed these modifications to the configuration are lost.  You should ask your host to check on this and restore any changed settings - though with SiteGround I would not be surprised if this had not already been done.

Also - check your catalog site.  Make sure all links are what you would expect them to be.  I have seen this type of scenario before in cases of security penetrations where the site was being used to serve links to other sites which had paid for traffic.  While this might be revealed by your customers, its not unlikely that anyone browsing the site who noticed it would not say anything.  Check this at once.  Just make sure any external links go to the manufacturer or link partner they should go to, and any product, category or other links to the right page. If this is the case, it won't take long to find.  It is not a terribly likely possibility but it happens to the best, so watch for it.

So - there you are.  This should give you some information to work with as you manage the situation.  Lots of possibilities there, but this should give you a start.