A Store Speed Optimization in Progress

[revamp]

Yes but it is best to use a prefix like idx_ as has already been explained. Also bear in mind that indices should be used sparingly as they take up a lot of space .. you can quickly end up having a detrimental effect.

I see: when i put an key on a field in phpmyadmin (change field, click on key) then the key is the same as the field, so it is better to use the SQL command from Jan and add the prefix.

That brings me to the next question: how do you know which field must be a key? Because, stupid as i am, my first idea was to put a key to all the fields as i dont know which field to put a key on...

[♥FWR Media]

I see: when i put an key on a field in phpmyadmin (change field, click on key) then the key is the same as the field, so it is better to use the SQL command from Jan and add the prefix.

That brings me to the next question: how do you know which field must be a key? Because, stupid as i am, my first idea was to put a key to all the fields as i dont know which field to put a key on...

 

If you are unsure I really wouldn't mess with it. Are you running RC2a?

[revamp]

If you are unsure I really wouldn't mess with it. Are you running RC2a?

No 2.2ms2, i have just made the index upgrade from 2.2rc2a as suggested in this topic.

I surely will not mess around, therefor i keep on asking:

When i use the Output Query Debug, how do i determine from the output which field could have an index?

[♥FWR Media]

No 2.2ms2, i have just made the index upgrade from 2.2rc2a as suggested in this topic.

I surely will not mess around, therefor i keep on asking:

When i use the Output Query Debug, how do i determine from the output which field could have an index?

 

Find the slowest queries .. there are many page options to press to guage this site wide.

 

Grab the slow query from queries debug output .. then ..

 

Run the query in phpMyAdmin adding EXPLAIN to the start.

 

EXPLAIN

SELECT whatever FROM thistable WHERE this = 'that'

 

you will then get very useful output.

 

Understanding that output is a different matter so play around .. see what the slow queries report then google the terms found.

 

Good luck.

[revamp]

Find the slowest queries .. there are many page options to press to guage this site wide.

 

Grab the slow query from queries debug output .. then ..

 

Run the query in phpMyAdmin adding EXPLAIN to the start.

 

EXPLAIN

SELECT whatever FROM thistable WHERE this = 'that'

 

you will then get very useful output.

 

Understanding that output is a different matter so play around .. see what the slow queries report then google the terms found.

 

Good luck.

 

Thank you very much for your patience and great help!

Last question: which version of the Output Queries Debug do you advice? 1.2?

[♥FWR Media]

Thank you very much for your patience and great help!

Last question: which version of the Output Queries Debug do you advice? 1.2?

 

Had to think about that one been ages since I used it .. I use my own .. having looked the last one I used was 1.6

[revamp]

Had to think about that one been ages since I used it .. I use my own .. having looked the last one I used was 1.6

Amazing!!! 500 queries at index.php, a lot of work to do, so i play around:

I had this: [493]select startdate, counter from counter

with this time: [493] => 0.023619

EXPLAIN sql returned that there was no index in table "counter" so i put an index on "startdate" and look at the time needed for that querie: [493] => 0.000295 thats about 100 times faster!

 

Is this the right way to do this?

 

Because i noticed that [6] => select banners_id, date_scheduled from banners where date_scheduled != '' had a time of [6] => 0.026885 the first time and the second time (so with no changes on that) [5] => 0.000236

Notice also that apparantly [2] => select languages_id, name, code, image, directory from languages order by sort_order dissapeared the second time, probably because i just refreshed the window after adding the index to "counter"?

I also notice that the first time the Currente Parse Time was 1.081 s and the second time 0.346 s, caching and fluctuations in querietimes causes that? When i open the page in Firefox, the first time 0.347 parse time with querie [6], refresh gives 0.295 and querie[6] has dissappeared. Another thing: the total amount of queries stays the same 500, i see now that there are a few UPDATE queries at the end, ofcourse...

[pbor1234]

I was wondering; is it still usefull to apply configuration cache? If i take a look at the online performance i don't see much delay there:

 

select configuration_key as cfgKey, configuration_value as cfgValue from configuration

Explain:

id 1

select_type SIMPLE

table configuration

type ALL

possible_keys

key

key_len

ref

rows 503

Extra

Results:

min 0,0002

max 0,0201

mean 0,0036

stddev 0,0066

 

PS: These are results from a maintenance function i wrote, it simply repeats the same query 25 times and calculates some statistics.

 

So basically i would win no more than 7ms?

 

Paul

[♥FWR Media]

I was wondering; is it still usefull to apply configuration cache? If i take a look at the online performance i don't see much delay there:

 

select configuration_key as cfgKey, configuration_value as cfgValue from configuration

Explain:

id 1

select_type SIMPLE

table configuration

type ALL

possible_keys

key

key_len

ref

rows 503

Extra

Results:

min 0,0002

max 0,0201

mean 0,0036

stddev 0,0066

 

PS: These are results from a maintenance function i wrote, it simply repeats the same query 25 times and calculates some statistics.

 

So basically i would win no more than 7ms?

 

Paul

Configuration cache is a valid thought as it is a large table scan but is flawed as a concept because the cache by it's very nature is a configuration option itself.

[pbor1234]

Configuration cache is a valid thought as it is a large table scan but is flawed as a concept because the cache by it's very nature is a configuration option itself.

 

Robert,

I'm not understanding your remark, sorry. I'm trying to make a trade-off between having the configuration from the table (the original osc implementation) or have a copy on the filesystem and read it from there. On the pro's side i got a better performance (i guess) but furthermore there is nothing but con's: complexity, risk of having inconsistent content in the cache, multiple access to the same file and thus blocking requests.

So the performance win must be >50ms or so, otherwise the drawback outrun the benefits (to my opinion).

Paul

[revamp]

I have installed the Page Cache V1.5 and things are working out fine but when i click on a manufacturer I get ridiculous long parsing times. It seems that this querie causes it:

 

select p.products_image, m.manufacturers_name, p.products_model, pd.products_name, p.products_quantity, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products p left join specials s on p.products_id = s.products_id, products_description pd, manufacturers m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '16' order by pd.products_name limit 0, 20

 

When I put it in SQL with EXPLAIN its shows this:

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE s system idx_specials_products_id NULL NULL NULL 0 const row not found

1 SIMPLE m const PRIMARY PRIMARY 4 const 1

1 SIMPLE pd index PRIMARY products_name 240 NULL 389965 Using where

1 SIMPLE p eq_ref PRIMARY PRIMARY 8 denit_oscommerce.pd.products_id 1 Using where

 

So products_description seems to be the problem: the queries searches 389965 rows bur the PRIMARY KEY (products_name) is set...

 

Does anyone knows how to solve this problem?

[Jan Zonjee]

I have installed the Page Cache V1.5 and things are working out fine but when i click on a manufacturer I get ridiculous long parsing times. It seems that this querie causes it:

 

select p.products_image, m.manufacturers_name, p.products_model, pd.products_name, p.products_quantity, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products p left join specials s on p.products_id = s.products_id, products_description pd, manufacturers m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '16' order by pd.products_name limit 0, 20

 

When I put it in SQL with EXPLAIN its shows this:

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE s system idx_specials_products_id NULL NULL NULL 0 const row not found

1 SIMPLE m const PRIMARY PRIMARY 4 const 1

1 SIMPLE pd index PRIMARY products_name 240 NULL 389965 Using where

1 SIMPLE p eq_ref PRIMARY PRIMARY 8 denit_oscommerce.pd.products_id 1 Using where

 

So products_description seems to be the problem: the queries searches 389965 rows bur the PRIMARY KEY (products_name) is set...

 

Does anyone knows how to solve this problem?

So if you have a large amount of products it is quicker to only use the tables products and manufacturers in this query. Then get the products_id's and with that list you do queries for products_description, then specials to see if there are special prices. Combine everything et voilá, faster site :)

At least that is how I would try to go for lower parse times in your case.

[revamp]

So if you have a large amount of products it is quicker to only use the tables products and manufacturers in this query. Then get the products_id's and with that list you do queries for products_description, then specials to see if there are special prices. Combine everything et voilá, faster site :)

At least that is how I would try to go for lower parse times in your case.

Abracadabra for me...how do i do that?

[pbor1234]

I have installed the Page Cache V1.5 a...

 

I'm still having doubts about optimizing the configurations; Did it work for you? How large was the performance improvement you measured? Just for your information; my server reaches average query time of 0.2ms for the table scan of 500 items.

Thanks,

Paul

[Jan Zonjee]

Abracadabra for me...how do i do that?

I played a bit with that but found it is not as simple as I hoped because most of the times pd.products_name is used as a sorting field. So you almost always need the products_name.

I think using a trimmed down temporary table (see the ENGINE = MEMORY statement in the new function osc_create_tmp_name_table) might do the trick working with large data sets. If you get errors that this is not supported leave out the ENGINE = MEMORY part.

 

In includes/functions/general.php add before the last ?> add:

<?php
/*
 $Id: product_listing.php 1739 2007-12-20 00:52:16Z hpdl $

 osCommerce, Open Source E-Commerce Solutions
 [url="http://www.oscommerce.com"]http://www.oscommerce.com[/url]

 Copyright © 2003 osCommerce

 Released under the GNU General Public License
*/
 global $random_name;
 $listing_split = new splitPageResults($listing_sql, MAX_DISPLAY_SEARCH_RESULTS, 'p.products_id');

 if ( ($listing_split->number_of_rows > 0) && ( (PREV_NEXT_BAR_LOCATION == '1') || (PREV_NEXT_BAR_LOCATION == '3') ) ) {
?>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr>
   <td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
   <td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
<?php
 }

 $list_box_contents = array();

 for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
   switch ($column_list[$col]) {
     case 'PRODUCT_LIST_MODEL':
       $lc_text = TABLE_HEADING_MODEL;
       $lc_align = '';
       break;
     case 'PRODUCT_LIST_NAME':
       $lc_text = TABLE_HEADING_PRODUCTS;
       $lc_align = '';
       break;
     case 'PRODUCT_LIST_MANUFACTURER':
       $lc_text = TABLE_HEADING_MANUFACTURER;
       $lc_align = '';
       break;
     case 'PRODUCT_LIST_PRICE':
       $lc_text = TABLE_HEADING_PRICE;
       $lc_align = 'right';
       break;
     case 'PRODUCT_LIST_QUANTITY':
       $lc_text = TABLE_HEADING_QUANTITY;
       $lc_align = 'right';
       break;
     case 'PRODUCT_LIST_WEIGHT':
       $lc_text = TABLE_HEADING_WEIGHT;
       $lc_align = 'right';
       break;
     case 'PRODUCT_LIST_IMAGE':
       $lc_text = TABLE_HEADING_IMAGE;
       $lc_align = 'center';
       break;
     case 'PRODUCT_LIST_BUY_NOW':
       $lc_text = TABLE_HEADING_BUY_NOW;
       $lc_align = 'center';
       break;
   }

   if ( ($column_list[$col] != 'PRODUCT_LIST_BUY_NOW') && ($column_list[$col] != 'PRODUCT_LIST_IMAGE') ) {
     $lc_text = tep_create_sort_heading($HTTP_GET_VARS['sort'], $col+1, $lc_text);
   }

   $list_box_contents[0][] = array('align' => $lc_align,
                                   'params' => 'class="productListing-heading"',
                                   'text' => ' ' . $lc_text . ' ');
 }

 if ($listing_split->number_of_rows > 0) {
//   echo $listing_split->sql_query;

   $rows = 0;
   $listing_query = tep_db_query($listing_split->sql_query);

   $no_of_listings = tep_db_num_rows($listing_query);

while ($_listing = tep_db_fetch_array($listing_query)) {
$listing[] = $_listing;
$list_of_prdct_ids[] = $_listing['products_id'];
}

// an extra query is needed for all the specials

$specials_query = tep_db_query("select products_id, specials_new_products_price from " . TABLE_SPECIALS . " where products_id in (" . implode(',', $list_of_prdct_ids) . ") and status = '1'");
while ($specials_array = tep_db_fetch_array($specials_query)) {
$new_s_prices[] = array ('products_id' => $specials_array['products_id'], 'products_price' => '', 'specials_new_products_price' => $specials_array['specials_new_products_price'] , 'final_price' => $specials_array['specials_new_products_price']);
}

// add the correct specials_new_products_price and replace final_price
for ($x = 0; $x < $no_of_listings; $x++) {
   if(!empty($new_s_prices)) {
    for ($i = 0; $i < count($new_s_prices); $i++) {
	    if( $listing[$x]['products_id'] == $new_s_prices[$i]['products_id'] ) {
	    $listing[$x]['specials_new_products_price'] = $new_s_prices[$i]['specials_new_products_price'];
	    $listing[$x]['final_price'] = $new_s_prices[$i]['final_price'];
	    }
    }
  } // end if(!empty($new_s_prices)
} // end for ($x = 0; $x < $no_of_listings; $x++)

//    while ($listing = tep_db_fetch_array($listing_query)) { (was original code)
for ($x = 0; $x < $no_of_listings; $x++) {
     $rows++;

     if (($rows/2) == floor($rows/2)) {
       $list_box_contents[] = array('params' => 'class="productListing-even"');
     } else {
       $list_box_contents[] = array('params' => 'class="productListing-odd"');
     }

     $cur_row = sizeof($list_box_contents) - 1;

     for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
       $lc_align = '';

       switch ($column_list[$col]) {
         case 'PRODUCT_LIST_MODEL':
           $lc_align = '';
           $lc_text = ' ' . $listing[$x]['products_model'] . ' ';
           break;
         case 'PRODUCT_LIST_NAME':
           $lc_align = '';
           if (isset($HTTP_GET_VARS['manufacturers_id'])) {
             $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . $listing[$x]['products_name'] . '</a>';
           } else {
             $lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing[$x]['products_id']) . '">' . $listing[$x]['products_name'] . '</a> ';
           }
           break;
         case 'PRODUCT_LIST_MANUFACTURER':
           $lc_align = '';
           $lc_text = ' <a href="' . tep_href_link(FILENAME_DEFAULT, 'manufacturers_id=' . $listing[$x]['manufacturers_id']) . '">' . $listing[$x]['manufacturers_name'] . '</a> ';
           break;
         case 'PRODUCT_LIST_PRICE':
           $lc_align = 'right';

           if (tep_not_null($listing[$x]['specials_new_products_price'])) {
             $lc_text = ' <s>' .  $currencies->display_price($listing[$x]['products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . '</s>  <span class="productSpecialPrice">' . $currencies->display_price($listing[$x]['specials_new_products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . '</span> ';
           } else {
             $lc_text = ' ' . $currencies->display_price($listing[$x]['products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . ' ';
           }
           break;
         case 'PRODUCT_LIST_QUANTITY':
           $lc_align = 'right';
           $lc_text = ' ' . $listing[$x]['products_quantity'] . ' ';
           break;
         case 'PRODUCT_LIST_WEIGHT':
           $lc_align = 'right';
           $lc_text = ' ' . $listing[$x]['products_weight'] . ' ';
           break;
         case 'PRODUCT_LIST_IMAGE':
           $lc_align = 'center';
           if (isset($HTTP_GET_VARS['manufacturers_id'])) {
             $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a>';
           } else {
             $lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a> ';
           }
           break;
         case 'PRODUCT_LIST_BUY_NOW':
           $lc_align = 'center';
           $lc_text = '<a href="' . tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $listing[$x]['products_id']) . '">' . tep_image_button('button_buy_now.gif', IMAGE_BUTTON_BUY_NOW) . '</a> '; // EOF Separate Pricing per Customer
           break;
       }

       $list_box_contents[$cur_row][] = array('align' => $lc_align,
                                              'params' => 'class="productListing-data"',
                                              'text'  => $lc_text);
     }
   }

   new productListingBox($list_box_contents);
 } else {
   $list_box_contents = array();

   $list_box_contents[0] = array('params' => 'class="productListing-odd"');
   $list_box_contents[0][] = array('params' => 'class="productListing-data"',
                                  'text' => TEXT_NO_PRODUCTS);

   new productListingBox($list_box_contents);
 }
 $clean_up = tep_db_query("drop table if exists " . $random_name . "");
 if ( ($listing_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '2') || (PREV_NEXT_BAR_LOCATION == '3')) ) {
?>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr>
   <td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
   <td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
<?php
 }
?>

[revamp]

I'm still having doubts about optimizing the configurations; Did it work for you? How large was the performance improvement you measured? Just for your information; my server reaches average query time of 0.2ms for the table scan of 500 items.

Thanks,

Paul

Page Caching V1.5 works good for me: i have querie times of 0.000070 seconds for the categorie queries. I have 30.000 categories (in total with subs and subsubs) and 520.000 articles in the shop and i`m still working on a shared server...this is a particular quest of mine to see what is possible with a fairly standard OSC shop on a shared server. From there i try to optimize the queries.

Only the first time i load the page it takes about 3 seconds for 500 queries, but after that the page cache does his work: when i click the biggest categorie i get 0.592 - 0.814 seconds with 1402 queries...Refresh gives then 0.329 - 0.355 seconds with only 13 queries (the most of the queries are then cached).

[revamp]

I played a bit with that but found it is not as simple as I hoped because most of the times pd.products_name is used as a sorting field. So you almost always need the products_name.

I think using a trimmed down temporary table (see the ENGINE = MEMORY statement in the new function osc_create_tmp_name_table) might do the trick working with large data sets. If you get errors that this is not supported leave out the ENGINE = MEMORY part.

SNIP

 

Thanks Jan, you are the best.

 

I will try this in a day or so (little busy right now) and let you know the results.

[pbor1234]

Page Caching V1.5 works good for me: i have querie times of 0.000070 seconds for the categorie queries. I have 30.000 categories (in total with subs and subsubs) and 520.000 articles in the shop and i`m still working on a shared server...this is a particular quest of mine to see what is possible with a fairly standard OSC shop on a shared server. From there i try to optimize the queries.

Only the first time i load the page it takes about 3 seconds for 500 queries, but after that the page cache does his work: when i click the biggest categorie i get 0.592 - 0.814 seconds with 1402 queries...Refresh gives then 0.329 - 0.355 seconds with only 13 queries (the most of the queries are then cached).

 

Carlo, uhhhh, sorry i mixed up the configuration cache with page cache. So my question was about the configuration cache and not about the page cache.

PS: I know what you are trying to do (from the dutch forum ;).

Paul

[Jan Zonjee]

Refresh gives then 0.329 - 0.355 seconds with only 13 queries (the most of the queries are then cached).

Try logging-in as a customer on that point. I expect the cache can't be used anymore then.

[revamp]

Try logging-in as a customer on that point. I expect the cache can't be used anymore then.

That`s right, Jan. You are a step ahead because that was the next question: how to fix this?

[revamp]

Carlo, uhhhh, sorry i mixed up the configuration cache with page cache. So my question was about the configuration cache and not about the page cache.

PS: I know what you are trying to do (from the dutch forum ;).

Paul

No problem, Paul, I have noticed that but I wanted to give the results till now anyway...

[revamp]

I played a bit with that but found it is not as simple as I hoped because most of the times pd.products_name is used as a sorting field. So you almost always need the products_name.

I think using a trimmed down temporary table (see the ENGINE = MEMORY statement in the new function osc_create_tmp_name_table) might do the trick working with large data sets. If you get errors that this is not supported leave out the ENGINE = MEMORY part.

SNIP

Hello Jan,

 

I tried this but then the pages are disrupted(no left column anymore and the querie debug does not show up anymore...) and when I refresh the page I get:

 

1114 - The table 'temp_H1hoCXn5QB1C' is full

 

insert into temp_H1hoCXn5QB1C select pd.products_id, pd.products_name from products_description pd where pd.language_id = '1'

 

[TEP STOP]

 

Am I doing something wrong, or?

 

I have page cache from Chemo on and the standard OSC cache off

[Jan Zonjee]

I tried this but then the pages are disrupted(no left column anymore and the querie debug does not show up anymore...) and when I refresh the page I get:

 

1114 - The table 'temp_H1hoCXn5QB1C' is full

 

insert into temp_H1hoCXn5QB1C select pd.products_id, pd.products_name from products_description pd where pd.language_id = '1'

 

[TEP STOP]

 

Am I doing something wrong, or?

Of course you have a lot more products than I could ever test with. Perhaps there is maximum limit for such temporary tables or perhaps you have run out of disk space for your database?. Try leaving the ENGINE=MEMORY part out then it becomes an ordinary MySQL table (which should be "dropped" after having done it's job so you should never see those tables in your database).

[revamp]

Of course you have a lot more products than I could ever test with. Perhaps there is maximum limit for such temporary tables or perhaps you have run out of disk space for your database?. Try leaving the ENGINE=MEMORY part out then it becomes an ordinary MySQL table (which should be "dropped" after having done it's job so you should never see those tables in your database).

I have tried that too: the same result but without the tep stop. When I look at the database I see a lot of temp_xxx_tabels, so they weren't dropped...

[Jan Zonjee]

I have tried that too: the same result but without the tep stop. When I look at the database I see a lot of temp_xxx_tabels, so they weren't dropped...

At least drop them to get some room in your database. It could be that your database is close to the limit of the filesystem. Ever tried to back the database up?