Website very slow

Ashutosh Pandey asked on July 1, 2020 10:32

We have a Kentico 10 based website and having performance issues. We have large number of documents in pages application (1000+) We have two servers: 8 GB Web server, 8 GB DB server (Both VPS)

The pages application with node having large number of documents is very slow on checkout and checkin (no events handled manually)

Website is very slow when testing with load testing for 20 users.

Our database size is around 14 GB (I don't know why, we are using file system for files)

Sum of database size from the following query is only 2717 MB

select sum(TotalSpaceMB) from(
SELECT
t.Name                                       AS TableName,
s.Name                                       AS SchemaName,
p.Rows                                       AS RowCounts,
(SUM(a.total_pages) * 8/1024)                AS TotalSpaceMB 
FROM
sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.Name NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.object_id > 255
GROUP BY
t.Name, s.Name, p.Rows
)a;

The following query report size as 14361 MB

SELECT      sys.databases.name,  
            CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Total disk space]  
FROM        sys.databases   
JOIN        sys.master_files  
ON          sys.databases.database_id=sys.master_files.database_id  
where sys.databases.name = 'ourdbname' 
GROUP BY    sys.databases.name  
ORDER BY    sys.databases.name  

Here are the top tables in terms of size:

TableName               SchemaName  RowCounts   TotalSpaceMB    UsedSpaceMB 
CMS_ObjectVersionHistory    dbo     2714        709             703         
OM_Activity                 dbo     164890      403             231     
Analytics_HourHits          dbo     2111833     341             314 
CMS_VersionHistory          dbo     23153       292             279 
Analytics_DayHits           dbo     1457484     228             199 
CMS_Relationship            dbo     1326892     223             178 
Analytics_WeekHits          dbo     669597      113             89  
OM_Contact                  dbo     62728       106             57  

We have read the performance documents provided from Kentico and implemented them.

Questions:

  1. Is the hardware we are using fine for the website?
  2. What is causing extra 11-12 GB of data when sum of data tables is only 2717 MB
  3. Do we need to clear log files?
  4. Is the database size causing the website to go slow?

Thanks

Recent Answers


David te Kloese answered on July 1, 2020 18:01

I'd recommend have a look at those top tables at first...

2 things that it shows that are having a lot of data.

1 is your version history both pages and objects (templates, page types etc). Check settings for versioning in the admin interface. Note that changing these settings only has impact from that point forward, it won't clean up al versions that now fall out of your range, but as soon as you touch that object.

secondly you OM data has a lot of data, check the settings and tasks for cleaning up old OM data. Inactive contacts, Activities over a year old usually don't have a lot of value.

1 votesVote for this answer Mark as a Correct answer

Dmitry Bastron answered on July 1, 2020 18:48

In addition to David's answer it's worth checking Web Analytics module and it's data cleanup. Are you actually using it in CMS or are you using GA for the website? If you don't use Kentico Web Analytics - just disable it and clean the data.

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on July 1, 2020 23:01

The size if your database doesn't always dictate what performance will be. That being said if you're in v10 and have millions of contacts, analytics, etc. records, that may have an affect on your site.

If you're concerned about performance, look at how your site is implemented. If you're using Portal Engine, is your site optimized? Are your web parts configured correctly? Is your custom code using caching? Just a few things off the top of my head.

If you're worried about database size, it's most likely your log file that's huge in size. If you don't need to have transactional data backed up, change your backup type from Full to Simple. There are a million things to look at for performance and database size is only a minor piece.

1 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.