Low Server Usage, Long Page Loads with APACHE Caching

Disabling Apache Caching improves LAMP Server Performance


On one of my virtual servers which runs LAMP on only a 1GB RAM box with a couple of quite high traffic sites I have been experiencing an intermittent problem which had the following symptoms.

1. Long page load wait time - Spinning browser window with nothing happening
2. Very low server load average < 10 despite large number of requests to website
3. Large amount of disk swapping - where the hard drive is used to temporarily store data

This also coincided with these errors in the log files

[Thu Feb 02 16:30:57 2012] [error] (103)Software caused connection abort: cache: error returned while trying to return mem cached data

Running a TOP command would return something like this

op - 16:42:06 up 3 days, 21:29,  2 users,  load average: 0.12, 0.39, 0.67, 1.39
Tasks: 219 total,   1 running, 218 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.1%sy,  0.0%ni, 99.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   1060972k total,   931768k used,   129204k free,     9668k buffers
Swap:  2097144k total,   328740k used,  1768404k free,   362284k cached

An APACHE restart always fixed this issue temporarily however on running

/etc/init.d/apache2 restart

I noticed it was carrying out an htcacheclean command and it was always on this clean commands completion that the page load would suddenly spin into action before terminating as the web-server stopped.

Both websites that ran on the server were using Wordpress systems that utilised caching plugins, namely WP-SuperCache and WP-Widget-Cache as well as .htaccess file rules that set future expiry headers on certain file types e.g JavaScript, CSS, Images etc.

I also ban over 55% of all traffic through rules that block known bad BOTS, spammers, hackers and known bad IP addresses. If you are not writing articles in Chinese or Russian and have no wish to pay for the bandwidth caused by BOTS like Yandex and Baiduspider to visit constantly then 403 them! They both pay no attention to the Robots.txt file so in my book can be labelled as "Bad BOTS" anyway.

As the main symptom was very low server loads AND hanging pages that were cured by running htcacheclean it made sense to try something recommended by someone I was in contact with at Tiger Technologies who recommended disabling the APACHE caching.

He recommended this because I was already utilising other caching technologies at the Wordpress plugin level and the errors in the log file suggested some sort of problem with APACHE's caching system. Apparently this is not a default option to be enabled and although I have no recollection of enabling it as I didn't set the server up it was obviously turned on.

After checking all the config files to ensure I wasn't making specific use of these Apache modules I disabled the following Apache Modules: disk_cache file_cache mem_cache with the following command in my console.

a2dismod

These all had to be disabled first before I could disable the main cache module that they all depended on.

After disabling all these caching modules I ran the following command to check that the syntax of all my Apache config files were correct and still in working order.

apache2ctl configtest


This returns "Syntax OK" if everything is okay.

A restart of Apache now didn't run the htcacheclean command and afterwards the problem seemed to dissipate.

One thing I have noticed coming from a Windows environment is just the pure amount of places that you can enable caching from the web-server level to MySQL, Wordpress, Plugins that make use of static files, PHP Accelerators such as APC and memcache for those with RAM to spare.

There are an awful lot of configuration possibilities with a LAMP setup and it seems that APACHE's own caching system was causing the problem probably due to the amount of memory it required for a busy site which was the cause of the large amount of disk swapping that was going on.

Maybe if I had a large amount of RAM to spare this wouldn't have been a problem but as I have shown in previous articles related to Twitter Rushes caused by large concurrent BOT visits after Tweeting links if you don't have lots of RAM and haven't lowered your MaxClients setting to an appropriate level from the default of 256 to 12-25, a 1GB RAM server can run out of memory very quickly indeed.

However whilst the Twitter Rush / MaxClients problem is easily detected by high server loads, a look at the visitor log file after Tweeting plus some maths to divide your RAM by the average amount of memory used by a page load plus any other applications that constantly run such as MySQL. This configuration issue was a bit harder to work out due to the fact I was experiencing very low server loads and thought I had done the right thing by utilising various caching techniques.

The Apache caching was obviously causing my server an issue and as always being able to methodologically diagnose a problem by stepping through the various possibilities ruling them out one by one is a technique that always works. 

SQL Performance Top Tips

Another SQL Performance Top Tips?

I know there are lots of Top Tips for improving SQL performance out there on the web but you can never have too much of a good thing so I have created my own list of issues to identify and resolve when trying to improve the performance of an SQL database.


1. Investigate and resolve dead locks or blocked processes.

All it takes is one blocked process on a commonly hit table for your whole site to hang and start reporting timeout issues.

On my jobboard sites I had locking issues with my main JOBS table that had to be joined to a number of other tables to get the data needed on results pages that were hit constantly by crawlers. These tables were also being updated constantly by clients and automated feeds from multi-posters which meant that locks were likely.

As I use one database for multiple sites it also meant that a lock caused by one site would cause timeout issues for the other sites.

To resolve this I created some de-normalised flat tables per site that contain all the fields needed for reporting and searching which meant:

• The JOB table is only updated at regular intervals and not instantly. SQL 2005 introduced the synonym feature which I demonstrate here. This is used to swap between tables being built and tables in use by the site so that a new table can be built behind the scenes with up to date data.
• Clustered indexes can be site specific depending on the columns the site search on the most.
• No joins needed when accessing the table and results are returned very fast.
• Any problems accessing this table do not affect other sites.

Another trick to handle instances where you are selecting from tables that maybe updated constantly such as job or banner hit tables is to look at the following:

1. If the table you are selecting from is never updated or deleted from during the times you are selecting from then you can use the WITH (NOLOCK) statement on your SQL SELECTS. You won't have to worry about dirty reads as you are not actually updating the data and you bypass all the overhead SQL has to go through to maintain the LOCKING.
2. Use the LOCK_TIMEOUT statement to reduce the time LOCKS are held for.
3. Use the TRY / CATCH statement to catch deadlocks and other blocking errors in combination with a retry loop. After X retries return from the proc. View my example of using LOCK_TIMEOUT to handle DEADLOCKS and BLOCKING.

2. Look into large batch processes that have long run times, take up I/O, have long wait times or cause blocking.

A good example is where you have to transfer lots of records from one table to another e.g from a daily table that only receives INSERTS (e.g job / banner hits) into a historical table for reporting.

If you are deleting large numbers of records from a table that is also being used then you don't want to cause blocks that will freeze your site. In conjunction with point 1 you should look into how you handle your DELETE / UPDATE statements so that they are done in small BATCHES using the SQL 2005+ TOP command.

Read this article for an example of updating or deleting in batches to prevent blocking.

To find out problematic queries after the fact you can utilise the SQL 2005+ Data Management Views (DMV's) which hold key information about the system.

Read this article on my top queries for performance tuning to help identify those queries that need tuning.


3. Re-Index and De-Frag tables

Make sure you regularly re-index and de-fragment your tables as over time fragmentation will build up and performance will be affected. I tend to set up a weekly MS Agent job that runs a Defrag / Re-organize or  on tables with fragmentation over a set percentage as well as rebuild index statistics.

I then also try to schedule a full re-index of all my main tables once a quarter at scheduled down times as during a full rebuild the tables can be taken off line depending on your SQL setup.


4. Identify Slow Queries

Investigate your slowest running queries to make sure they are covered adequately by indexes but also try not to over use indexes. Try to cover as many queries with as few indexes as possible.

Try running this SQL performance report which will identify a dozen or so areas which could be improved on your system including:

• Causes of the server waits
• Databases using the most IO
• Count of missing indexes, by database
• Most important missing indexes
• Unused Indexes
• Most costly indexes (high maintenance)
• Most used indexes
• Most fragmented indexes
• Most costly queries, by average IO
• Most costly queries, by average CPU
• Most costly CLR queries, by average CLR time
• Most executed queries
• Queries suffering most from blocking
• Queries with the lowest plan reuse

This is an invaluable tool for any SQL DBA or SQL performance tuning developer.



5. Split tables

If your tables are used for inserting/updating as well as selection then for each index you have on a table that's an extra update required when a record is saved. On some of my big tables that are used heavily for reporting I split the data into daily and historical data. The daily table will allow updates but the historical table will not.

At night a job will transfer the daily data into the historical table, dropping all current indexes, populating the new data and then rebuilding the indexes with a 100% fill factor. You need to balance out whether speed on data retrieval or on update is more important if the table is used for both. You should also look into points 1 and 2 about managing blocking and batch updates/deletes to handle instances where a table is being deleted whilst accessed at the same time.


6. Re-write complex queries and make WHERE clauses SARGABLE

Can you rewrite complex queries to be more efficient. Can you remove left joins to large tables
by populating temporary tables first. Are you putting functions in the WHERE, HAVING clause on the column and negating any index usage. If so can you rewrite the clause and make it SARGABLE so that you make use of the index. For example a WHERE clause like so

WHERE DateDiff(day,CreateDate,GetDate()) = 0

Which is filtering by todays date should be rewritten to:

WHERE CreateDate > '2008-sep-09 00:00:00' --where this is the previous midnight

Put the value from GetDate() into a variable before the SELECT and then use that in the filter
so that no function is used and any index on CreateDate will be available.

Read this article of mine which proves the benefits of SARGABLE clauses in SQL.


7. Query Plan Caching

Check that you are benefiting from query plan caching. If you are using stored procedures that contain branching through use of IF statements that run different SQL depending on the values passed in by parameters then the cached query plan for this procedure may not be the best for all parameter variations.

You can either rewrite the procedure so that each IF branch calls another stored procedure that contains the query required as it will be this plan that gets used.

Or you could rewrite the query to use dynamic SQL so that you build up a string containing the appropriate syntax and parameters and then execute it using sp_executesql which will take advantage of plan re-use. Do not use EXEC as this will not take advantage of plan re-use and its not as safe in terms of sql injection.

You should also look into whether your query plans are not getting used due to your SQL Server settings as the default mode is to use SIMPLE PARAMETERIZATION and not FORCED PARAMETERIZATION.

This features takes AdHoc queries containing literal values and removes values replacing them with parameters. This means that the query plan which gets cached can be re-used for similar queries that have different values which can aid performance as it will reduce compilation time.

When the system is set to SIMPLE mode only AdHoc query plans that contain the same literal values get cached and re-used which in many cases is not good enough as the values for most queries will change all the time for example in SIMPLE mode only the plan for the exact query below will be cached.

SELECT *
FROM PRODUCTS
WHERE ProductID = 10

Which means only products with the ProductID of 10 will benefit from a cached plan however with FORCED PARAMETERIZATION enabled you would have plan with parameters so that any ProductID can benefit from it e.g

SELECT *
FROM PRODUCTS
WHERE ProductID = @1 -- name of SQL parameter

For a more detailed look at the benefits of this method read my article on forced paramaterization.


8. Use the appropriate table for the situation

SQL has a variety of tables from fixed permanent tables to global and local temporary tables to table variables that are both stored in tempdb.

I have found a number of times now that the use of table variables start off being used in stored procedures as very useful memory efficient storage mechanisms but once the datasets stored within them rises above some threshold (I have not found the exact threshold amount yet) the performance drops incredibly.

Whilst useful for array like behaviour within procs and quick for small datasets they should not be used with record sets of many thousands or millions of records. The reason being that no indexes can be added to them and any joins or lookups will result in table scans which are fine with 10-100 records but with 20 million will cause huge problems.

Swapping table variables for either fixed tables or temporary tables with indexes should be considered when the size of data is too great for a table variable.

If your procedures are being called by website logins with execute permission you will need to impersonate a login with higher privileges to allow for the DDL statements CREATE and DROP as a basic website login should have nothing more than read privileges to prevent SQL injection attacks.

If you don't want to risk the rise in privileges then consider using a fixed table and the use of a stamp and unique key so that multiple calls don't clash with each other. Appropriate indexes should be used to ensure speedy data retrieval from these tables.

A real world example I have found is explained in this article on speeding up batch processes that use table variables.


9. Tuning for MySQL

MS SQL has a vast array of useful tools to help you performance tune it from it's Data Management Views and Activity Monitor to it's detailed Query Execution Plans. However if you are using MySQL and have to suffer using a tool like NAVICAT then you are not in such a good position to tune your queries.

The EXPLAIN option is nowhere near as useful as the MS SQL Query Execution Plan but it can be used to ensure the appropriate indexes are added if missing and the Slow Query Log is a useful place to identify problematic queries.

If you are using LINUX or Wordpress and running your system on MySQL then read this article of mine on performance tuning for MySQL.


These are just some tips to look at when trying to improve back end database performance and I will add to this list when I get some more time. Feel free to post your own tips for identifying problematic SQL and then fixing it.

Top 10 Tips for improving ASP performance

Top 10 Tips For Speeding up ASP Classic Sites

1. Look for nested loops and replace them with SQL stored procs. Its surprising how many times I have looked at sites that when outputting product hierarchies or menus on those old catalogue sites use nested loops to build up the content. Its usually people who got into programming on the web side with little or no SQL knowledge that was then learnt later only as and when they required somewhere to persist the data. Replace nested loops with SQL Stacks (see BOL) or in 2005 a recursive CTE. From tests I have found the Stack to perform slightly faster than the CTE but both will outperform nested loops. The data will most likely be in an adjacency table anyway so its just the method of outputting it. I remember replacing one such beast of nested loops with a stored procedure stack and it increased performance by a factor of 20.

2. Re-use those objects. Do you have helper functions that carry out regular expressions or file system object actions that instantiate the object within the function. If you call these functions
more than once on a page you are creating an unneccesary overhead. Have a global include file where you declare some often used variables. Then the first time you call the function if it doesn't exist initialise it otherwise use the existing object pointer. Then destroy them all in your global footer.

3. Simplify any complex regular expressions by breaking them down into multiple parts. I have had problems with badly written regular expressions causing CPU on our webserver to max at 100%. On our quad it would jump to 25%, four hits at the same time and 100% and then no other pages would load. Also complex expressions that do lots of lookbacks will cause overhead and the longer the string you are testing against the more overhead you will get. Remember VBs Regular Expression engine is not as good as some others so its better to break those long complex expressions up into smaller tests if possible.

4. String concatenation. In ASP.NET you have the string builder class but in ASP classic you should create your own using Arrays to store the string and then do a JOIN at the end to return it. Doing something like the following

For x = 1 to 100
strNew = strNew & strOtherString
Next

May work okay if the size of the string in strOtherString is small and you have few iterations but
once those strings get larger the amount of memory used to copy the existing string and then add it to the new one will grow exponentially.

5. Try to redim arrays as few times as possible especially if you are doing a Preserve. Its always best to dim to the exact size before hand but if you don't know how big your array will be then
dim it to a large number first and then use a counter to hold how many elements you actually add. Then at the end you redim preserve once back down to the number of elements you actually used. This uses a lot less memory than having to redim preserve on each addition to the array.

6. Use stored procedures for complex SQL and try to reduce database calls by calling multiple SQL together and then using NextRecordset() to move to the next set of records when you need to. Using stored procedures will also reduce network traffic as its much easier to connect to the DB once and pass through the names of 3 stored procedures than connect 3 times passing through the X lines of SELECT statements that each recordset requires.

strSQL = "EXEC dbo.usp_asp_get_records1; usp_asp_get_recorddetails; usp_asp_get_country_list;"

Set objRS = objConnection.Execute(strSQL)

If not(objRS.BOF AND objRS.EOF) Then
arrRecords = objRS.GetRows()
Set objRS = objRS.NextRecordset()
End If
If not(objRS.BOF AND objRS.EOF) Then
arrRecordDetails = objRS.GetRows()
Set objRS = objRS.NextRecordset()
End If
If not(objRS.BOF AND objRS.EOF) Then
arrCountryList = objRS.GetRows()
End If

7. Make sure that you don't put multiple conditions in IF statements. ASP doesn't support short circuiting which means it evaluates every condition in an IF statement even if the first one
is false. So rewrite code such as:

If intCount = 1 AND strName = "CBE" AND strDataType = "R" Then
'Do something
End If

If intCount = 1 Then
 If strName = "CBE" Then
    If strDataType = "R" Then
       'Do something
    End If
 End If
End If

8. Also make sure you evalutate conditions in the correct order if you are checking a value for True and False then you don't waste an extra check when you can just change the order of the conditions.

If Not(bVal) Then
 Response.Write("NO")
Else
 Respone.Write("YES")
End If

Should obviously be

If (bVal) Then
 Response.Write("YES")
Else
 Respone.Write("NO")
End If

8. Cache as much data as possible if it doesn't change frequently. If you have lists that come from a database but never change or very infrequently change then you could either store them in memory and reload every so often or write the array to a file and then use that static array and only re-create the file when the data changes. I always add an admin only function into my sites that on the press of a button will reload everything I need to into files and cache. This prevents un-required database lookups.

9. Pages that contain database content that's added through the website that only changes when the user saves that content can be created to static HTML files if possible. Rather than rebuilding the page from the DB each time someone views it you can use the static version. Only rebuild the page when the user updates the content. For example a lot of CMS systems store the content within the DB but create a static version of the page for quick loading.

10. Encode and format content on the way into the database not each time its outputted to be displayed. Its more cost efficent to do something once on the way in that hundreds of times on the way out.

These are just a few of the things I have found out that can be very beneficial in speeding up a classic ASP site on the web side of things. As well as any performance tweaks that can be done application side you should take a look at the database to make sure thats performing as well as possible. If your site is data driven then a lot of the performance will be related to how quick you can build those recordsets up and deliver them to the client.

Before we rewrite all those ASP Classic sites as .NET

What Language Should I use?

Although .NET is the way a large percentage of websites are going I still find a lot of them surprisingly slow. As a developer you are always wanting to work with the newest technologies getting to spend time learning new languages to boost your CV and work with whatever is cutting edge at the time just for the fun of it. However most of all developers work for someone and a lot of the time the customer your sales team sell to doesn't care if the site they are paying for is written in PHP, ASP.NET or ASP Classic as long as it works, is fast and doesn't break every time more than 10 people visit it at the same time.


The Quickest .NET Site Development in History

My boss used to joke that we should upgrade one of our biggest sites from classic ASP to .NET but rather than rewriting it all we should just change all the file extensions from .asp to .aspx. We used to laugh about this thinking it would be a good way to get a .NET site up quickly that was faster than our competitors with no development costs. However it was only a pie in the sky joke until recently when we actually sold a version of this software to a foreign company who wanted control of the source code.

A few months later when one of their management team spoke to my boss he proudly informed him that they had indeed rewritten the whole system in .NET and paid a lot of money for doing so. My boss informed him that if they had done so that quickly we would like a copy ourselves as it would save us some time re-developing the system having taken 2+ years to get to the stage we were currently at when we sold it.

We all eagerly gathered round the nearest PC and entered the URL to examine this marvel of technology that had only taken 2 months for a complete rewrite. Imagine our surprise when we were met with the exact same site not just the look and feel but the exact same site structure, page layout, CSS styling, client side JavaScript and so on. The only difference we could find was a new sub directory that used the companies name before our existing site structure and all the pages were now renamed .aspx.

We got some of our .NET developers down to view the source code and found nothing that indicated it had been generated by .NET cod and in fact it looked exactly like the classic ASP source code including some secret debug code generated by ASP that I had outputted in specific places hidden in HTML comments. Nothing had changed apart from the extensions!

We reckoned that maybe the Indian team that had been given the contract for the rewrite had taken one look at how much code there was and how complicated it would be to even understand without any training let alone rewrite that they took the decision my boss had batted around as a joke.

Of course we no longer had access to their servers so we couldn't confirm 100% that it had been a file extension change and nothing else. Maybe they had in fact re-written the whole system in .NET and reproduced every single feature that was already there in the exact same way as before including all my debug statements and client side JS code that was generated by the system. If that was the case then what was the benefit to the client? They had just forked out a huge bill for a rewrite with no visible benefit. Another indicator that the site hadn't been rewritten was that it was still as fast as it was previously so I would give pretty good odds that the Indian outfit that had the contract made a very easy £20k or so.

My point being is if you have a system that is performing well and is earning your company £££ with constant sales then does the language its written it actually make much of a difference to the sale of that product. Wouldn't the speed, reliability and development time required to get it up and running be more important in the customers mind or is that .asp extension just considered too old and unfashionable nowadays in the small percentage of customers minds who even know the difference between .asp .aspx ?

And if you do have a customer that gets all panicky about those .asp extensions you can always use ISAPI URL rewriting to remove any mention of .asp or .aspx. Remember a fast well performing site is just that and the customer is probably not as bothered as you whether its been developed in classic ASP, .NET 3, PHP, Java or the next 3 lettered acronym language that comes along.

As a developer you may want to rewrite existing sites each time a new sexy technology comes out but unless there are desirable benefits to the company that pays your wages in doing this then its not going to happen. If you work for a small company then the time and money lost by taking a developer away from new work to rewrite an existing system is not going to be cost effective.

However saying that you might have a good case in convincing your boss to put that effort in a rewrite and there will always be tech savy customers out there who pay money over fist for the latest code base just like uber rich fashionistas that only wear the same clothes once. However before you do upgrade your classic ASP system try and see if you get it to perform as fast as it possibly can first. There's a high chance that a lot of classic ASP systems have a lot of bad code contained within them that could be tweaked to improve performance with a little work.

I work on a number of large high volume traffic sites that are still using classic ASP and have spent considerable time trying to get the best performance out of them as possible as a rewrite in .NET is going to take some considerable time and effort. If I have a poorly performing site then the steps I would go through on the way to a redevelopment are these:

1. Improve the ASP application code as much as possible. See top 10 tips for improving ASP sites.

2. Make sure the database is performing as well as possible and returning data to the application as quick as it can. View my top tips for increasing SQL performance article.

3. Make sure your hardware and software is configured correctly and that you don't have problems with the servers setup.

4. Consider moving to a 64 bit server and put more RAM in. See tips for moving from 32 to 64 bit server.

5. If all the previous have failed and you still do not have a scalable site that can take large amounts of traffic then consider a rewrite in .NET.

However there maybe some fundamental issue in the applications design or site structure that is causing problems. If so then a redesign is required anyway. Creating and maintaining scalable high volume sites in ASP classic is possible but requires some skill and knowledge. You need to weigh up the cost to the company in time and money that a total redesign and redevelopment would take opposed to any gains made by performance tuning and adding extra hardware.