Performance in 2.40 - truly need some help....

Hi all,

We launched a website on Friday for a client who is a TV celeb. The site is running on version 2.40, and the server spec is as so:

Intel XEON 2.33GHz
16GB RAM

The problem is that at times there are quite a few concurrent users on the site - perhaps 30-50 or so. The CPU is constantly at 90-98% which slows the site down to crawling! The category page is the worst of all.

I have disabled all of the scheduled tasks except for SendEmails.

I know there is lots of talk about the performance being improved in 2.50, 2.60 etc, but the site is live and upgrading to the latest version is not an option right now.

Has anyone come across any solutions for improving performance at all on 2.40? Even if I could get the processor down to 50% that would be a huge improvement.

Any tips, tricks or ideas would be greatly appreciated.

Thanks in advance
Al

Hello,
Please provide a live URL.

Thanks

Hi

I had a similar problem with version 2.20 until a week ago, when we spent a full day optimizing everything. My CPU is now sitting happily at 5% with some peaks which is quite normal. Still with version 2.20.

Top 3 things I checked and fixed:

1) Free HD space. It may sound silly, but limited space (specially in the C system drive) makes Win server really SLOW. I had 600Mb of free space (in a 40Gb HD), now I have 16 Gb of free space.

2) Most of this disk space was used by the database log files (.LDF) (13Gb for a 30Mb database). You can truncate them and avoid the default 'auto grow by 10%' setting. To truncate the log files I had to run some query in SQL Management Studio, just google it. For example:

http://madhuottapalam.blogspot.com.es/2008/05/faq-how-to-truncate-and-shrink.html


3) Rebuild/reorganize DB indexes to reduce index fragmentation. Again, I used a query in SSMS to return a list of indexes, their table, and their average fragmentation. This is the code:

USE [yourDbName]

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, o.name as [Table], b.name as [Index],ps.page_count,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
INNER JOIN sys.objects AS o ON ps.object_id = o.object_id
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID() AND page_count > 5
ORDER BY ps.avg_fragmentation_in_percent DESC
GO

Run this query to find your worst indexes. Having the results on the query window, I just had to go through the list, and with 2 clics on the left pane (your DB) expand the appropiate table, find the index, Right click on it, and then either REBUILD or REORGANIZE index.

A good rule of thumb is to Reorganize if you have < 40% fragmentation, or to Rebuild for > 40% fragmentation.

In fact, this index rebuilding/reorganizing was so successful we're now thinking of automating this process, as index fixing can also be done with SQL code. So a SQL Job could run the above code, iterate the results and rebuild each index. Fragmentation just happens as time goes by, specially if you add/modify/delete many entities (Products, PVariants, and so on), so be sure to check again from time to time.

Again, I'm not sure how much each of these steps adds to the overall performance gain, but the site is now like 500% faster. You can check it out at www.voipcentrix.com.

Hope this helps, best regards

cdelaorden wrote:

I hope someone can give us some advice because we are experimenting some random processor usage surges.

We have a VPS Pro in Arvixe (HD: 80 GB, RAM: 3GB, unlimited data transfer, 4 CPU cores, Windows server 2008 and MSSQL 2008) for hosting 4 minor stores based on NOP 2.6. Altogether they have around 1500 products, 1500 visitors per day, no more than 15 concurrent users. The only one particularity is that http://www.distribuidoresquimal.com.mx one of the shops has some pages with heavy images and visual effects, and sometimes it might have up to 6 concurrent administrators taking orders by impersonating customers.

We have applied all recommendations by cdelaorden and reset the server 1 or 2 times a week. The problem is that we are experimenting response time jumps from the normal (around 0.6 to 0.7 seconds, measured by pingdom.com in the shop i mentioned above) to 2 or 3 times that and some single measurements are  above 5 seconds. That lasts around 30 to 60 minutes and then goes back to normal. There can be 2 or 3 days without problems and then a day come s with one or 2 of these jumps. Most strange is that this also happens at times with no traffic at all, like midnight or very early in the morning or late evenings. We have compared response time graphs with traffic graphs and there is no  apparent relationship. We have other v1.9 shops with much more traffic/products (3 times as much) in VPS and we are not experiencing any problems.

Does anyone has a clue what might be causing this problem? Can  Arvixe VPS be the problem, for it happens at no traffic hours too?

Thanks in advance

Hi all,

cdelaorden, thanks for your tips on the indexes, it has helped somewhat. I created a simple SQL script that now executes as a SQL Job. It rebuilds or reorganises indexes based on Microsoft's best practices.

Script below for you:

SET NOCOUNT ON;

--declare variables to be used in the cursor
DECLARE @table_name nvarchar(100), @index_name nvarchar(100), @avg_fragmentation float;

--declare the cursor
DECLARE indexes_cursor CURSOR FOR

--query the indexes
SELECT o.name as [Table], b.name as [Index], ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
INNER JOIN sys.objects AS o ON ps.object_id = o.object_id
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID() AND page_count > 5
ORDER BY ps.avg_fragmentation_in_percent DESC

--open the cursor
OPEN indexes_cursor

--store the results in the cursor
FETCH NEXT FROM indexes_cursor
INTO @table_name, @index_name, @avg_fragmentation

WHILE @@FETCH_STATUS = 0
BEGIN

  IF @avg_fragmentation > 5 AND @avg_fragmentation <= 30
  BEGIN
  
    EXEC('ALTER INDEX ' + @index_name + ' ON [' + @table_name + ']' +
      ' REORGANIZE');
        
  END
  
  IF @avg_fragmentation > 30
  BEGIN

    EXEC('ALTER INDEX ' + @index_name + ' ON [' + @table_name + ']' +
        ' REBUILD');
  
  END

    -- Get the next index.
    FETCH NEXT FROM indexes_cursor
  INTO @table_name, @index_name, @avg_fragmentation
END
CLOSE indexes_cursor;
DEALLOCATE indexes_cursor;


Hope it helps.

Regards,
Al