Extremely large site showing significant performance slow down

[sioux]

This site www.allinfoaboutautorepairs.com currently contains 6,500 pages and is set to grow to 18,000 (check the sitemap!). However just over the last few days the pages are taking up to 25 seconds to load causing a huge drop in traffic.

I've taken all the page elements out one at a time and re-submitted the page each time with nothing making any difference to the page load times. Now I'm wondering if it's an sql problem? If so (or if it's anything else) is there any cure for this or will we (shudder) have to convert all pages back to html?

[tsw]

its only 17 mysql queries..

you could try optimizing the db

http://dev.mysql.com/doc/refman/5.0/en/ ... table.html

[Ted]

Hmm.  I've never tried running it on a site that big before, to be honest.

There are several issues that could be a factor here, most of them probably relating to the contentcache file in tmp/cache.  I'm guessing that it's taking a decent chunk of time to convert that back to a php object.  Sounds like the caching is a detrement at this point...

Not sure what I could do without rewriting a decent chunk of code.

[Piratos]

17 Mysql queries ist not a big value - there is no need to optimize mysql.

It is

a. your server ping with 1713ms  maxi and  471ms average

b. the contentcache - system of the cms is slow, but with 5000 testpages on local webserver there is enough speed.

c. you have several banners from other domains .

[sioux]

As this site is due to expand to 19,000 pages - are there any suggestions you can give as to how to optimise it for faster loading?

Or would it be better before going further to cut our losses and revert back to html?

I'd rather know than risk losing SE placements due to slow loading times. Having said that though - the site seems to have picked up a bit of speed over Saturday. It's very frustrating!

[tsw]

17 Mysql queries ist not a big value - there is no need to optimize mysql.

Even one mysql query with a large database can be very time consuming.

[tsw]

This site has also been handy when checking for slow loading times:

http://www.websiteoptimization.com/serv ... pairs.com/


and can you check your server loads? if you are on a hosted environment they might be limiting your cpu usage and so on.


and if you dont need much dynamic data you could set wget in cron to create html pages from cmsms content every night.


I think we'll do some testing with large datasets..

piratos: interested in doing some stress testing?

[sioux]

Thanks for the advice.

Is the conversion to html a cmsms command that can be run through Cron jobs (fingers crossed), or something horribly techie I'd have to find someone to do for me?

In the meantime I'm going to compress the css files as much as possible and remove a few placeholder jscripts which aren't currently loading anything and see what else I can do to makethe pages smaller.

If you want a copy of our database to do load testing on please let me know as I'd be happy to help.

[Piratos]

Here is a small script by me to repair and optimize the mysql tables:

Code: Select all

<?php
require_once('config.php');
if ($config['use_adodb_lite'] == false || (isset($USE_OLD_ADODB) && $USE_OLD_ADODB == 1 && file_exists(dirname(__FILE__)."/lib/adodb/adodb.inc.php")))
	require("lib/adodb/adodb.inc.php");
else
	require("lib/adodb_lite/adodb.inc.php");

$db = ADONewConnection($config['dbms'], 'pear:cmsms:date');

$conn = $db->Connect($config['db_hostname'],$config['db_username'],$config['db_password'],$config['db_name']);

$result=$db->Execute('SHOW TABLES');

if ($result)
{ $tin='Tables_in_'.$config['db_name'];
  while ($dbr = $result->FetchRow())
  {
      $pos = strpos ($dbr[$tin], $config['db_prefix']);

      if ($pos === false)
      {
      }
      else
      {
        if ($pos == 0)
        {
          $o = $db->Execute('REPAIR TABLE '.$dbr[$tin]);
          if ($o) echo 'Repair .. ';
          $o = $db->Execute('OPTIMIZE TABLE '.$dbr[$tin]);
          if ($o) echo 'Optimize .. ';
          echo $dbr[$tin].' done <br />';
        }
      }
   }


}
else
{
  echo 'Error - broken connection or no tables found.';
}

?>

[Piratos]

i have register a new module projekt dboptimizer  and i'm waiting to get the ok.

so every cms user can  easy repair and optimize his database .

module is ready to upload after the ok.



[attachment deleted by admin]

[sioux]

I have optimised the database and still no improvement in load time (thanks for the script).

I ftp'd a plain html page to the server and that appears within half a second or so when typed into the url bar, so now having tried every possibility as to why it's a slow load, I must reluctantly conclude that the giant database is the cause.

29 seconds to load a page with 15 sql queries is going to cause us to lose lots of visitors, so does anyone know of a way that the pages could be converted to html automatically?

[Russ]

1. Have you tried loading it without the adverts?
2. Are you on a shared host where limitation may apply?
3. Is the database hosted on the same server?
4. I'm showing 68 errors in Tidy and page is not valid, probably due to the ads, but that can cause problems, especially if some of them are in iframes.
5. Do you have anything odd in your .httaccess or with mod-rewrites?

You may well be limited, the W3C validator coughed up this...
Apache/1.3.36 (Unix) mod_auth_passthrough/1.8 mod_log_bytes/1.2 mod_bwlimited/1.4 PHP/4.4.2 FrontPage/5.0.2.2635.SR1.2 mod_ssl/2.8.27 OpenSSL/0.9.7a

Russ

[sioux]

1) Yes I have removed all the adverts in turn and re-submitted the page without them. None made any difference.
2) We have a brand new dedicated server
3) The database is on the same server
4) Check out www.allinfoaboutfamilyscreenscene.com which loads in 1.5 seconds with 34 Mysql queries and umpteend ads, but uses the same template!!
5) It's on the latest version of CMSMS and was an upgrade so I haven't touched the config.php and the .htaccess is the one provided with the previous version of CMSMS. I'm not using the internal pretty urls as I haven't taken the time to figure out how they work.

And being a non-techie I have no idea what your last comment means

[Russ]

I think mod_bwlimited is an apache module for limiting use - can't remember if it's connections or bandwidth. This can apply to individual instances  of web sites - I think, but check out the apache web site for more information. Not sure why you are loading FrontPage?

Have you changed anything it seems to be a 'bit' better this morning?


<!-- Generated in 14.420626 seconds by CMS Made Simple using 17 SQL queries --

But it doesn't seem to be caching? I presume you have turned this off?

Russ

[Ted]

Static caching is off since it never gave us any significant increases.  We still cache the content and templates coming out of the database, but not the composited output.  I don't think that would help in this case.

The only way that I can see this being resolved is writing some kind of optional routine into CMSMS that caches the entire output of the page and spits it out if it hasn't changed.

However, this is going to lead to other issues on items being stale.  Like if there's a current time/date on the page, it'll never work and new pages wouldn't show up in the menus right away.  It would definitely have to be an optional thing and would only apply to certain types of sites (allof...  would apply, I think).

It's a decent chunk of work, though...