Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

MySQL Overload


Guest

Recommended Posts

I'm having an issue with timeouts and extremely long load times. I have checked the server and it turns out that the following Mysql quarry is taking 2 minutes at 100% load to finish and this is on a server with 4 2.8Ghz processors.

 

select distinct p.products_id, p.products_image, p.products_tax_class_id , if(s.status, s.specials_new_products_price, p.products_price) as products_pric e from products p left join specials s on p.products_id = s.products_id, product s_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.

categories_id = c.categories_id and c.parent_id = '308' and p.products_status = '1' order by p.products_date_added desc limit 12

 

select distinct p.products_id, p.products_image, p.products_tax_class_id , if(s.status, s.specials_new_products_price, p.products_price) as products_pric e from products p left join specials s on p.products_id = s.products_id, product s_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.

categories_id = c.categories_id and c.parent_id = '304' and p.products_status = '1' order by p.products_date_added desc limit 12

 

I?m at my wits end trying to get this thing to stop timing out. Any help would save my sanity.

 

Thanks in advance

Link to comment
Share on other sites

What does

 

EXPLAIN select distinct p.products_id, p.products_image, p.products_tax_class_id , if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '308' and p.products_status = '1' order by p.products_date_added desc limit 12

 

output?

Contributions

 

Discount Coupon Codes

Donations

Link to comment
Share on other sites

What does

 

EXPLAIN select distinct p.products_id, p.products_image, p.products_tax_class_id , if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '308' and p.products_status = '1' order by p.products_date_added desc limit 12

 

output?

 

 

IT is the database query for the next level of catagory. So when you click on the catagory box.

Link to comment
Share on other sites

IT is the database query for the next level of catagory. So when you click on the catagory box.

 

 

Sorry I wasn't clear. I am asking you to run the query I just provided (the same query you have, with the EXPLAIN in front). This will provide some details about how MySQL is looking up the data.

Contributions

 

Discount Coupon Codes

Donations

Link to comment
Share on other sites

Sorry I wasn't clear. I am asking you to run the query I just provided (the same query you have, with the EXPLAIN in front). This will provide some details about how MySQL is looking up the data.

 

 

Here's what I got back

 

EXPLAIN select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price
from products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c
where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '304' and p.products_status ='1' order by p.products_date_added desc limit 12


+----+-------------+-------+--------+----------------------------------+---------+---------+-----------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys					| key	 | key_len | ref						 | rows | Extra										|
+----+-------------+-------+--------+----------------------------------+---------+---------+-----------------------------+------+----------------------------------------------+
|  1 | SIMPLE	  | p	 | ALL	| PRIMARY						  | NULL	|	NULL | NULL						| 3713 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE	  | p2c   | ref	| PRIMARY						  | PRIMARY |	   4 | dfcatalog.p.products_id	 |	1 | Using index								  |
|  1 | SIMPLE	  | s	 | ALL	| NULL							 | NULL	|	NULL | NULL						| 1844 |											  |
|  1 | SIMPLE	  | c	 | eq_ref | PRIMARY,idx_categories_parent_id | PRIMARY |	   4 | dfcatalog.p2c.categories_id |	1 | Using where; Distinct						|
+----+-------------+-------+--------+----------------------------------+---------+---------+-----------------------------+------+----------------------------------------------+
4 rows in set (0.00 sec)

Link to comment
Share on other sites

Line 20 in includes/modules/new_products.php, try replacing this code:

  if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
 } else {
$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
 }

 

with this:

  if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
 } else {
tep_db_query( 'create temporary table new_prod ( products_id int(11) not null primary key )' );
tep_db_query( 'insert into new_prod (select p2c.products_id from '.TABLE_PRODUCTS_TO_CATEGORIES.' p2c left join '.TABLE_CATEGORIES.' c on c.categories_id=p2c.categories_id where c.parent_id="'.(int)$new_products_category_id.'" )');
$new_products_query = tep_db_query( 'select distinct p.products_id, p.products_image, p.products_tax_class_id , if(s.status, s.specials_new_products_price, p.products_price) as products_price from new_prod left join '.TABLE_PRODUCTS.' p on p.products_id=new_prod.products_id left join '.TABLE_SPECIALS.' s on s.products_id=p.products_id where p.products_status="1" order by p.products_date_added desc limit '.MAX_DISPLAY_NEW_PRODUCTS);
tep_db_query( 'drop table new_prod' );
//$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
 }

 

This may help with the speed. By creating a temp table in between joins, we allow for the more expensive join (the one between products and specials that is not using an index) to happen with a smaller dataset. It also allows for the use of a smaller table (the temp table) to be used in sorting. Let me know if this improves the situation or not.

Contributions

 

Discount Coupon Codes

Donations

Link to comment
Share on other sites

The avobe solution worked for about a day and then I ran right into the same problem again. Has anyone else had this issue pop up with time outs ect? I've been on other OSC sites and they all seem to load pretty quickly while mine is bogged down.

Link to comment
Share on other sites

The avobe solution worked for about a day and then I ran right into the same problem again.

 

avobe?

 

You need to find the source of the problem. Database, PHP parse/execution time, transfer speeds, DNS lookups, etc. Where is the biggest bottleneck? There are so many possibilities, it's impossible to help without more information.

Contributions

 

Discount Coupon Codes

Donations

Link to comment
Share on other sites

avobe?

 

You need to find the source of the problem. Database, PHP parse/execution time, transfer speeds, DNS lookups, etc. Where is the biggest bottleneck? There are so many possibilities, it's impossible to help without more information.

 

We just spent about a day on this. The end result is that we ran all of the tables through optimization and repair. The pages that load are loading faster but the slow pages are just as slow and continue to time out. We also noticed that it appears that the problem occurs when more that one person is trying to access the site at the same time. We tested this through the server.

 

We have considered backing up the database and reloading it, but we are unsure if this is an issue of us missing some maintenance step that we should be doing, or if by backing up and reloading we will just be reloading the problem back onto the server.

Link to comment
Share on other sites

We have considered backing up the database and reloading it, but we are unsure if this is an issue of us missing some maintenance step that we should be doing, or if by backing up and reloading we will just be reloading the problem back onto the server.

 

It depends. Are you certain it's the database that is having problems? If so, identify the queries that are slow.

Contributions

 

Discount Coupon Codes

Donations

Link to comment
Share on other sites

It depends. Are you certain it's the database that is having problems? If so, identify the queries that are slow.

 

I am sure that it is the Database that is having the problem. As far as the queries go, there is no real rhyme or reason to it. It seems to be dependant on whether or not there are multiple users doing queries at the same time. If only one person is using the database it's fine but as soon as two people start working either on the catalog or admin side it starts chewing server and times out. It can happen with any database query.

Link to comment
Share on other sites

It seems to me that's it might be more of a database configuration problem. The default memory and buffer allowances that MySQL is shipped with are usually too low to run a site with substantial traffic. A shared host especially needs to take care when configuring MySQL.

 

The kind of problems you are describing could be caused by a bad disk or bad memory. Even with a poorly configured server, one additional user should not make enough difference to be noticeable. I'd take a look through the MySQL bugs, and see if there are any known problems with your OS and MySQL version. I'd also see about getting MySQL installed on a different server. Most hosts would agree that something is wrong when literally one additional user can cause such a problem.

Contributions

 

Discount Coupon Codes

Donations

Link to comment
Share on other sites

Ok, we ran the query log and narrowed it down to this.

 

Query:

 

select distinct p.products_id, p.products_image, p.products_tax_class_id,

if(s.status, s.specials_new_products_price, p.products_price) as products_price

from products p left join specials s on p.products_id = s.products_id, products_to_categories p2c,

categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and

c.parent_id = '25' and p.products_status = '1' order by p.products_date_added desc limit 6

 

 

 

explain results:

 

+----+-------------+-------+--------+----------------------------------+---------+---------+-----------------------------+------+----------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+--------+----------------------------------+---------+---------+-----------------------------+------+----------------------------------------------+

| 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 3759 | Using where; Using temporary;Using filesort |

| 1 | SIMPLE | p2c | ref | PRIMARY | PRIMARY | 4 | dfcatalog.p.products_id | 1 | Using index |

| 1 | SIMPLE | s | ALL | NULL | NULL | NULL | NULL | 1873 | |

| 1 | SIMPLE | c | eq_ref | PRIMARY,idx_categories_parent_id | PRIMARY | 4 | dfcatalog.p2c.categories_id | 1 | Using where; Distinct |

+----+-------------+-------+--------+----------------------------------+---------+---------+-----------------------------+------+----------------------------------------------+

 

 

Query results:

 

+-------------+---------------------+-----------------------+----------------+

| products_id | products_image | products_tax_class_id | products_price |

+-------------+---------------------+-----------------------+----------------+

| 2658 | sardsweeper.jpg | 1 | 200.0000 |

| 2657 | XA64008.jpg | 1 | 110.0000 |

| 2656 | XA62301.jpg | 1 | 110.0000 |

| 2654 | XA19301.jpg | 1 | 30.0000 |

| 2352 | XA64028.jpg | 1 | 313.5000 |

| 1175 | IMAGECOMINGSOON.jpg | 1 | 17.1000 |

+-------------+---------------------+-----------------------+----------------+

6 rows in set (54.36 sec)

 

results from show full processlist:

 

| 542845 | XXXXXX | localhost:2278 | dfcatalog | Query | 21 | Copying to tm

p table | select distinct p.products_id, p.products_image, p.products_tax_class_

id, if(s.status, s.specials_new_products_price, p.products_price) as products_pr

ice from products p left join specials s on p.products_id = s.products_id, produ

cts_to_categories p2c, categories c where p.products_id = p2c.products_id and p2

c.categories_id = c.categories_id and c.parent_id = '25' and p.products_status =

'1' order by p.products_date_added desc limit 6 |

 

| 542854 | XXXXXXX | localhost:2287 | dfcatalog | Query | 5 | Copying to tm

p table | select distinct p.products_id, p.products_image, p.products_tax_class_

id, if(s.status, s.specials_new_products_price, p.products_price) as products_pr

ice from products p left join specials s on p.products_id = s.products_id, produ

cts_to_categories p2c, categories c where p.products_id = p2c.products_id and p2

c.categories_id = c.categories_id and c.parent_id = '256' and p.products_status

= '1' order by p.products_date_added desc limit 6 |

Link to comment
Share on other sites

I did make the change, and I've double checked to make sure that it is still there.

 

The other thing I noticed that seemed a little strange was that the products table and specials table show the key as NULL. I would think that with as much as is going on in those tables the key should be working, no?

Link to comment
Share on other sites

Do you have a template system, like STS, which may be generating the New Products box elsewhere?

 

The key ref columns in the output are null because though MySQL knows the key exists, it cannot use it due to the nature of the query. My rewrite was to adjust the quer(ies) so that they could make use of the keys. If you can figure out how to get the rewrite I suggested working, you should see better results from an EXPLAIN.

Contributions

 

Discount Coupon Codes

Donations

Link to comment
Share on other sites

Hmmm, I know the change is reflected in new products. Perhaps there is something in the admin section that uses the same query? I'm not using a template system.

Link to comment
Share on other sites

Even then I have best sellers disabled. I have the Indvidual shipping contribution, the Authorize.net AIM contribution, and CCGV isn't installed yet. Not until I can work through the time out errors.

Link to comment
Share on other sites

I just did a search on "TABLE_SPECIALS" and looked through all the lines that had it. I could find no other places where this query runs.

 

I'd suggest downloading the code for your entire site to your hard drive, and doing a Windows search within files for the phrase "TABLE_SPECIALS." You'll have to look through it and see if there are any other places where this query runs.

Contributions

 

Discount Coupon Codes

Donations

Link to comment
Share on other sites

I just thought of something, I had cool menu installed before I figured out I couldn't use it with my page sizing. That was a long time ago but do you think that could be messing with things? I never uninstalled it, only made it inactive.

Link to comment
Share on other sites

It seems to be fixed now. The problem appears to be that there were several tables inc products that were not indexed at all. Once we added the index to each of those tables the problem seems to have stopped.

Link to comment
Share on other sites

It seems to be fixed now. The problem appears to be that there were several tables inc products that were not indexed at all. Once we added the index to each of those tables the problem seems to have stopped.

That did not reduce the number of queries executed but rather made them faster to execute (since the comparative columns are now indexed). I have written several contributions to help reduce the number of per page queries...use them.

 

Bobby

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...