Website sometimes very slow

[Fure]

Hi people,

 

I have done the setup of an oscommerce shop that is hosted by one.com.

The last few months the owner of the shop has some problems related to the website's access.

Most of the time it loads very fast but sometimes it loads very slow and even doesn't load at all.

The times I couldn't reach their website, the server was also not reachable trough ftp.

So these times it was a server problem.

 

But one.com let them know that they have moved the shop to another server but the problem remains.

They say to disable all the plugins one by one to see if it ins't related to a plugin.

 

Could you take a look at their site because I'm out of options?

I have no clue what they mean by plugins. Some contributions I think.

The website is http://www.doopduiker-kleinekadee.be.

[Jan Zonjee]

Could you take a look at their site because I'm out of options?

Parse times of 14 and more seconds are excessive yes.

 

Why don't you start with adding the Output Queries Debug Contribution to see how many queries a page generates and how long it takes to execute them (find excessive amount of queries or slow ones).

[Fure]

---

Parse times of 14 and more seconds are excessive yes.

 

Why don't you start with adding the Output Queries Debug Contribution to see how many queries a page generates and how long it takes to execute them (find excessive amount of queries or slow ones).

Thank you for your reply.

I have installed the contribution and this is what I got:

Parse Time: 17.155s
Current Parse Time: 17.155 s with 112 queries
QUERY DEBUG:

Array
(
   [QUERIES] => Array
       (
           [0] => select value from sessions where sesskey = '53124004b40b895b30cd07c33fea9fd9' and expiry > '1275411574'
           [1] => select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from currencies
           [2] => select languages_id, name, code, image, directory from languages order by sort_order
           [3] => delete from whos_online where time_last_click < '1275410680'
           [4] => select count(*) as count from whos_online where session_id = '53124004b40b895b30cd07c33fea9fd9'
           [5] => insert into whos_online (customer_id, full_name, session_id, ip_address, time_entry, time_last_click, last_page_url) values ('0', 'Guest', '53124004b40b895b30cd07c33fea9fd9', '78.23.244.191', '1275411580', '1275411580', '/')
           [6] => select banners_id, date_scheduled from banners where date_scheduled != ''
           [7] => select b.banners_id, b.expires_date, b.expires_impressions, sum(bh.banners_shown) as banners_shown from banners b, banners_history bh where b.status = '1' and b.banners_id = bh.banners_id group by b.banners_id
           [8] => select specials_id from specials where status = '1' and now() >= expires_date and expires_date > 0
           [9] => select * from headertags_default where language_id = '4'
           [10] => select * from headertags where page_name like 'index.php' and language_id = '4'
           [11] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = 0 and c.categories_id = cd.categories_id and cd.language_id='4' order by sort_order, cd.categories_name
           [12] => select count(*) as count from categories where parent_id = '76'
           [13] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = 76 and c.categories_id = cd.categories_id and cd.language_id='4' order by sort_order, cd.categories_name
           [14] => select count(*) as count from categories where parent_id = '92'
           [15] => select count(*) as count from categories where parent_id = '96'
           [16] => select count(*) as count from categories where parent_id = '85'
           [17] => select count(*) as count from categories where parent_id = '93'
           [18] => select count(*) as count from categories where parent_id = '86'
           [19] => select count(*) as count from categories where parent_id = '90'
           [20] => select count(*) as count from categories where parent_id = '97'
           [21] => select count(*) as count from categories where parent_id = '87'
           [22] => select count(*) as count from categories where parent_id = '88'
           [23] => select count(*) as count from categories where parent_id = '91'
           [24] => select count(*) as count from categories where parent_id = '89'
           [25] => select count(*) as count from categories where parent_id = '83'
           [26] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = 83 and c.categories_id = cd.categories_id and cd.language_id='4' order by sort_order, cd.categories_name
           [27] => select count(*) as count from categories where parent_id = '45'
           [28] => select count(*) as count from categories where parent_id = '82'
           [29] => select count(*) as count from categories where parent_id = '77'
           [30] => select count(*) as count from categories where parent_id = '95'
           [31] => select count(*) as count from categories where parent_id = '37'
           [32] => select count(*) as count from categories where parent_id = '40'
           [33] => select count(*) as count from categories where parent_id = '21'
           [34] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = 21 and c.categories_id = cd.categories_id and cd.language_id='4' order by sort_order, cd.categories_name
           [35] => select count(*) as count from categories where parent_id = '25'
           [36] => select count(*) as count from categories where parent_id = '38'
           [37] => select count(*) as count from categories where parent_id = '39'
           [38] => select count(*) as count from categories where parent_id = '41'
           [39] => select count(*) as count from categories where parent_id = '42'
           [40] => select count(*) as count from categories where parent_id = '44'
           [41] => select count(*) as count from categories where parent_id = '27'
           [42] => select count(*) as count from categories where parent_id = '28'
           [43] => select count(*) as count from categories where parent_id = '29'
           [44] => select count(*) as count from categories where parent_id = '30'
           [45] => select count(*) as count from categories where parent_id = '32'
           [46] => select count(*) as count from categories where parent_id = '33'
           [47] => select count(*) as count from categories where parent_id = '34'
           [48] => select count(*) as count from categories where parent_id = '35'
           [49] => select count(*) as count from categories where parent_id = '36'
           [50] => select count(*) as count from categories where parent_id = '47'
           [51] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = 47 and c.categories_id = cd.categories_id and cd.language_id='4' order by sort_order, cd.categories_name
           [52] => select count(*) as count from categories where parent_id = '48'
           [53] => select count(*) as count from categories where parent_id = '49'
           [54] => select count(*) as count from categories where parent_id = '51'
           [55] => select count(*) as count from categories where parent_id = '52'
           [56] => select count(*) as count from categories where parent_id = '64'
           [57] => select count(*) as count from categories where parent_id = '65'
           [58] => select count(*) as count from categories where parent_id = '67'
           [59] => select count(*) as count from categories where parent_id = '68'
           [60] => select count(*) as count from categories where parent_id = '69'
           [61] => select count(*) as count from categories where parent_id = '70'
           [62] => select count(*) as count from categories where parent_id = '71'
           [63] => select count(*) as count from categories where parent_id = '78'
           [64] => select count(*) as count from categories where parent_id = '72'
           [65] => select count(*) as count from categories where parent_id = '74'
           [66] => select count(*) as count from categories where parent_id = '46'
           [67] => select c.categories_id, cd.categories_name, c.parent_id from categories c, categories_description cd where c.parent_id = 46 and c.categories_id = cd.categories_id and cd.language_id='4' order by sort_order, cd.categories_name
           [68] => select count(*) as count from categories where parent_id = '81'
           [69] => select count(*) as count from categories where parent_id = '84'
           [70] => select count(*) as count from categories where parent_id = '79'
           [71] => select count(*) as count from categories where parent_id = '94'
           [72] => select count(*) as count from categories where parent_id = '80'
           [73] => select count(*) as count from categories where parent_id = '22'
           [74] => select count(*) as count from categories where parent_id = '23'
           [75] => select count(*) as count from categories where parent_id = '24'
           [76] => select products_id, products_image, products_tax_class_id, products_price from products where products_status = '1' order by products_date_added desc limit 10
           [77] => select products_name from products_description where products_id = '577' and language_id = '4'
           [78] => select specials_new_products_price from specials where products_id = '577' and status
           [79] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '21') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '182') and tr.tax_class_id = '3' group by tr.tax_priority
           [80] => select p.products_id, p.products_image, p.products_tax_class_id, p.products_price, pd.products_name, if(s.status, s.specials_new_products_price, p.products_price) as products_prix from products p left join specials s on p.products_id = s.products_id, products_description pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '4' and s.status = '1' order by RAND() limit 10
           [81] => select p.products_id, p.products_image, p.products_tax_class_id, p.products_price, pd.products_description from products p left join specials s on p.products_id = s.products_id left join products_showcase ps on p.products_id = ps.products_id left join products_description pd on p.products_id = pd.products_id where products_status = '1' and pd.language_id = '4' and showcase_status = '1' order by ps.sort_order asc limit 12
           [82] => select products_name from products_description where products_id = '293' and language_id = '4'
           [83] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '21') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '182') and tr.tax_class_id = '3' group by tr.tax_priority
           [84] => select products_name from products_description where products_id = '154' and language_id = '4'
           [85] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '21') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '182') and tr.tax_class_id = '3' group by tr.tax_priority
           [86] => select products_name from products_description where products_id = '460' and language_id = '4'
           [87] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '21') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '182') and tr.tax_class_id = '3' group by tr.tax_priority
           [88] => select products_name from products_description where products_id = '262' and language_id = '4'
           [89] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '21') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '182') and tr.tax_class_id = '3' group by tr.tax_priority
           [90] => select products_name from products_description where products_id = '193' and language_id = '4'
           [91] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '21') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '182') and tr.tax_class_id = '3' group by tr.tax_priority
           [92] => select products_name from products_description where products_id = '217' and language_id = '4'
           [93] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '21') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '182') and tr.tax_class_id = '3' group by tr.tax_priority
           [94] => select products_name from products_description where products_id = '501' and language_id = '4'
           [95] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '21') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '182') and tr.tax_class_id = '3' group by tr.tax_priority
           [96] => select products_name from products_description where products_id = '54' and language_id = '4'
           [97] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '21') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '182') and tr.tax_class_id = '3' group by tr.tax_priority
           [98] => select products_name from products_description where products_id = '73' and language_id = '4'
           [99] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '21') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '182') and tr.tax_class_id = '3' group by tr.tax_priority
           [100] => select products_name from products_description where products_id = '312' and language_id = '4'
           [101] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '21') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '182') and tr.tax_class_id = '3' group by tr.tax_priority
           [102] => select products_name from products_description where products_id = '360' and language_id = '4'
           [103] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '21') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '182') and tr.tax_class_id = '3' group by tr.tax_priority
           [104] => select products_name from products_description where products_id = '175' and language_id = '4'
           [105] => select sum(tax_rate) as tax_rate from tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) where (za.zone_country_id is null or za.zone_country_id = '0' or za.zone_country_id = '21') and (za.zone_id is null or za.zone_id = '0' or za.zone_id = '182') and tr.tax_class_id = '3' group by tr.tax_priority
           [106] => select p.products_id, pd.products_name, products_date_available as date_expected from products p, products_description pd where to_days(products_date_available) >= to_days(now()) and p.products_id = pd.products_id and pd.language_id = '4' order by date_expected desc limit 10
           [107] => select startdate, counter from counter
           [108] => update counter set counter = '114299'
           [109] => select banners_id, banners_title, banners_image, banners_html_text from banners where status = '1' and banners_group = '468x50'
           [110] => select count(*) as total from sessions where sesskey = '53124004b40b895b30cd07c33fea9fd9'
           [111] => insert into sessions values ('53124004b40b895b30cd07c33fea9fd9', '1275413031', 'cart|O:12:\"shoppingCart\":4:{s:8:\"contents\";a:0:{}s:5:\"total\";i:0;s:6:\"weight\";i:0;s:12:\"content_type\";b:0;}language|s:5:\"dutch\";languages_id|s:1:\"4\";currency|s:3:\"EUR\";navigation|O:17:\"navigationHistory\":2:{s:4:\"path\";a:1:{i:0;a:4:{s:4:\"page\";s:9:\"index.php\";s:4:\"mode\";s:6:\"NONSSL\";s:3:\"get\";a:0:{}s:4:\"post\";a:0:{}}}s:8:\"snapshot\";a:0:{}}')
       )

   [TIME] => Array
       (
           [0] => 0.621715
           [1] => 1.658252
           [2] => 1.650808
           [3] => 0.000703
           [4] => 0.000985
           [5] => 0.366144
           [6] => 0.000394
           [7] => 0.000447
           [8] => 0.000361
           [9] => 0.514207
           [10] => 0.001580
           [11] => 0.003267
           [12] => 0.000275
           [13] => 0.000332
           [14] => 0.000310
           [15] => 0.000387
           [16] => 0.000300
           [17] => 0.005643
           [18] => 0.000271
           [19] => 0.000257
           [20] => 0.000317
           [21] => 0.000304
           [22] => 0.000285
           [23] => 0.000283
           [24] => 0.000290
           [25] => 0.000287
           [26] => 0.000300
           [27] => 0.000269
           [28] => 0.000259
           [29] => 0.000278
           [30] => 0.000284
           [31] => 0.000250
           [32] => 0.000295
           [33] => 0.000257
           [34] => 0.000325
           [35] => 0.000256
           [36] => 0.000274
           [37] => 0.000252
           [38] => 0.000310
           [39] => 0.000255
           [40] => 0.000325
           [41] => 0.000268
           [42] => 0.000305
           [43] => 0.000263
           [44] => 0.000287
           [45] => 0.000272
           [46] => 0.000262
           [47] => 0.000281
           [48] => 0.000271
           [49] => 0.000271
           [50] => 0.000275
           [51] => 0.000294
           [52] => 0.000279
           [53] => 0.000271
           [54] => 0.000248
           [55] => 0.000284
           [56] => 0.000301
           [57] => 0.000273
           [58] => 0.000266
           [59] => 0.000256
           [60] => 0.000278
           [61] => 0.000257
           [62] => 0.000249
           [63] => 0.000269
           [64] => 0.000271
           [65] => 0.000268
           [66] => 0.000276
           [67] => 0.000291
           [68] => 0.000282
           [69] => 0.000283
           [70] => 0.000261
           [71] => 0.000281
           [72] => 0.000272
           [73] => 0.000276
           [74] => 0.000261
           [75] => 0.000255
           [76] => 0.000344
           [77] => 0.469990
           [78] => 0.237372
           [79] => 0.000349
           [80] => 0.993700
           [81] => 0.396180
           [82] => 1.289539
           [83] => 0.327814
           [84] => 0.000411
           [85] => 0.000791
           [86] => 0.710025
           [87] => 1.096673
           [88] => 0.000512
           [89] => 0.000514
           [90] => 0.000453
           [91] => 0.000968
           [92] => 0.000337
           [93] => 0.000528
           [94] => 0.337969
           [95] => 0.273205
           [96] => 0.284287
           [97] => 0.279818
           [98] => 0.194514
           [99] => 0.000353
           [100] => 0.285269
           [101] => 0.251879
           [102] => 0.389735
           [103] => 0.957442
           [104] => 1.420402
           [105] => 0.000372
           [106] => 0.002162
           [107] => 0.000403
           [108] => 0.002012
           [109] => 0.002125
           [110] => 0.532136
           [111] => 0.321907
       )

)

SESSION:

Array
(
   [cart] => shoppingCart Object
       (
           [contents] => Array
               (
               )

           [total] => 0
           [weight] => 0
           [content_type] => 
       )

   [language] => dutch
   [languages_id] => 4
   [currency] => EUR
   [navigation] => navigationHistory Object
       (
           [path] => Array
               (
                   [0] => Array
                       (
                           

---

 => index.php
                           [mode] => NONSSL
                           [get] => Array
                               (
                               )

                           [post] => Array
                               (
                               )

                       )

               )

           [snapshot] => Array
               (
               )

       )

)

COOKIE:

Array
(
)

POST:

Array
(
)

GET:

Array
(
)

 

I see that 1,2,82,87 and 104 are the slow ones.

What I don't know is what to do about it.

This is from loading the index page. Sometimes it loads very quickly.

[Fure]

Is it a normal amount of queries?

[Jan Zonjee]

Is it a normal amount of queries?

Yes, but it can be a lot less (less fast queries). There are a number of slow queries.

 

Will take a look at it tonight.

[♥FWR Media]

Parse times of 14 and more seconds are excessive yes.

 

Why don't you start with adding the Output Queries Debug Contribution to see how many queries a page generates and how long it takes to execute them (find excessive amount of queries or slow ones).

 

You might consider instead KissER for complete error reporting and management. As long as you are running a decent version of PHP ( 5.2+ )

[Jan Zonjee]

I see that 1,2,82,87 and 104 are the slow ones.

What I don't know is what to do about it.

This is from loading the index page. Sometimes it loads very quickly.

 

Some of those long query times do not make sense at all. For example query [2], that is a query of a table with four rows. Impossible that this is a problem with that table or the code.

It looks like the server has another site(s) on it that is/are very taxing on the mysql server, thereby slowing your site in the process too.

I bet if you compare the output for index.php on different moments you will see that now other queries are suddenly slow and those that were slow are now fast.

 

If there is nothing that can be done about moving to another server there is always the option of reducing the number of queries. The less queries, the less chance you are hit by the slow-down caused by the other site(s).

 

If your site doesn't use banners than there is somewhere in the thread on this topic: A Store Speed Optimization in Progress, Step by step from a vanilla install! mentioning of removing the queries used for banners.

 

Reducing the number of queries for tax rates is simple. Download the latest version of osC2 from github (click Download Source button in top right corner of the page and choose ZIP). Find catalog/includes/functions/general.php and use the functions tep_get_tax_rate and tep_get_tax_description in that file instead of the ones you now have in that file.

 

Also download RC2a from the "regular" download for osCommerce if you don't run RC2a and check if the indexes on tables that were added in RC2a have been applied. Especially the one in the table specials helps a lot:

 

alter table specials add index idx_specials_products_id (products_id);

 

For reducing the number of queries needed for the categories (select count(*) as count from categories where parent_id = '##') you can use the Optimize categories box contribution.

 

For the admin side you can add the tep_get_category_tree optimization.

[Fure]

Some of those long query times do not make sense at all. For example query [2], that is a query of a table with four rows. Impossible that this is a problem with that table or the code.

It looks like the server has another site(s) on it that is/are very taxing on the mysql server, thereby slowing your site in the process too.

I bet if you compare the output for index.php on different moments you will see that now other queries are suddenly slow and those that were slow are now fast.

 

If there is nothing that can be done about moving to another server there is always the option of reducing the number of queries. The less queries, the less chance you are hit by the slow-down caused by the other site(s).

 

If your site doesn't use banners than there is somewhere in the thread on this topic: A Store Speed Optimization in Progress, Step by step from a vanilla install! mentioning of removing the queries used for banners.

 

Reducing the number of queries for tax rates is simple. Download the latest version of osC2 from github (click Download Source button in top right corner of the page and choose ZIP). Find catalog/includes/functions/general.php and use the functions tep_get_tax_rate and tep_get_tax_description in that file instead of the ones you now have in that file.

 

Also download RC2a from the "regular" download for osCommerce if you don't run RC2a and check if the indexes on tables that were added in RC2a have been applied. Especially the one in the table specials helps a lot:

 

alter table specials add index idx_specials_products_id (products_id);

 

For reducing the number of queries needed for the categories (select count(*) as count from categories where parent_id = '##') you can use the Optimize categories box contribution.

 

For the admin side you can add the tep_get_category_tree optimization.

Thanks, I will take a look at it this week.