Home » Forums » Ubercart » Bug Reports

Major Bug: Severe load on mysql server, database disabled by dreamhost, site down :(

Submitted by sphism on Thu, 05/29/2008 - 07:04
sphism
Posts: 48
 Joined: 11/29/2007
Bug Finder

Hey everyone, I've just been on holiday for 2 weeks and 1 of my sites was taken down by dreamhost because of severe mysql slowness. This is a major problem, I'm pretty sure it's a problem with ubercart RC4 am so i'm hoping it's already been fixed in RC5, if not then this is a major 'show stopper'. The site is pretty new and not getting much traffic.

Here's the email i got from dreamhost, I hope this is easy for someone to fix.

I'd really appreciate some help with this as the client is not very impressed Sad

Cheers,

matt

__________________________

Hello,

I'm writing you about your database, *****. The database was causing
some severe

load on the mysql server, it seems from some very slow queries, which I

tried to fix but they are still taking way too long to finish. Disabling
your database drops the load from 100 to 2 on the mysql server, so
unfortunately it is your database. I'm afraid your mysql needs have
outgrown shared hosting and you will need to update to a DreamHost MySQL
PS, VPS or dedicated solution:

https://panel.dreamhost.com/index.cgi?tree=vserver.provision&

http://dreamhost.com/dedicated-hosting.html

You can write me at justin@dreamhost.com about this issue. I've had to

disable this database until this issue can be addressed. I can provide a

backup of the database, such as to your ftp directory, if you wish. Here

are the bad queries that were in the mysql slow log, please note the

"query_time" in seconds and the rows_examined.

# User@Host: ****[****] @ [***.***.***.*]

# Query_time: 99607 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SELECT po.nid, po.oid, po.cost, po.price, po.weight, po.ordering,
ao.name, ao.aid FROM dr_uc_product_options AS po LEFT JOIN
dr_uc_attribute_options AS ao ON po.oid = ao.oid AND nid = 1 WHERE aid =
1 ORDER BY po.ordering, ao.name;

# User@Host: ****[****] @ [***.***.***.*]

# Query_time: 102840 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SELECT po.nid, po.oid, po.cost, po.price, po.weight, po.ordering,
ao.name, ao.aid FROM dr_uc_product_options AS po LEFT JOIN
dr_uc_attribute_options AS ao ON po.oid = ao.oid AND nid = 4 WHERE aid =
1 ORDER BY po.ordering, ao.name;

# User@Host: ****[****] @ [***.***.***.*]

# Query_time: 43327 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SELECT po.nid, po.oid, po.cost, po.price, po.weight, po.ordering,
ao.name, ao.aid FROM dr_uc_product_options AS po LEFT JOIN
dr_uc_attribute_options AS ao ON po.oid = ao.oid AND nid = 9 WHERE aid =
1 ORDER BY po.ordering, ao.name;

# User@Host: ****[****] @ [***.***.***.*]

# Query_time: 99273 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SELECT po.nid, po.oid, po.cost, po.price, po.weight, po.ordering,
ao.name, ao.aid FROM dr_uc_product_options AS po LEFT JOIN
dr_uc_attribute_options AS ao ON po.oid = ao.oid AND nid = 1 WHERE aid =
1 ORDER BY po.ordering, ao.name;

# User@Host: ****[****] @ [***.***.***.*]

# Query_time: 99186 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SELECT po.nid, po.oid, po.cost, po.price, po.weight, po.ordering,
ao.name, ao.aid FROM dr_uc_product_options AS po LEFT JOIN
dr_uc_attribute_options AS ao ON po.oid = ao.oid AND nid = 17 WHERE aid =
1 ORDER BY po.ordering, ao.name;

# User@Host: ****[****] @ [***.***.***.*]

# Query_time: 47966 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SELECT po.nid, po.oid, po.cost, po.price, po.weight, po.ordering,
ao.name, ao.aid FROM dr_uc_product_options AS po LEFT JOIN
dr_uc_attribute_options AS ao ON po.oid = ao.oid AND nid = 27 WHERE aid =
1 ORDER BY po.ordering, ao.name;

# User@Host: ****[****] @ [***.***.***.*]

# Query_time: 99659 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SELECT po.nid, po.oid, po.cost, po.price, po.weight, po.ordering,
ao.name, ao.aid FROM dr_uc_product_options AS po LEFT JOIN
dr_uc_attribute_options AS ao ON po.oid = ao.oid AND nid = 1 WHERE aid =
1 ORDER BY po.ordering, ao.name;

# User@Host: ****[****] @ [***.***.***.*]

# Query_time: 102679 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SELECT po.nid, po.oid, po.cost, po.price, po.weight, po.ordering,
ao.name, ao.aid FROM dr_uc_product_options AS po LEFT JOIN
dr_uc_attribute_options AS ao ON po.oid = ao.oid AND nid = 14 WHERE aid =
1 ORDER BY po.ordering, ao.name;

# User@Host: ****[****] @ [***.***.***.*]

# Query_time: 86801 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SELECT po.nid, po.oid, po.cost, po.price, po.weight, po.ordering,
ao.name, ao.aid FROM dr_uc_product_options AS po LEFT JOIN
dr_uc_attribute_options AS ao ON po.oid = ao.oid AND nid = 10 WHERE aid =
1 ORDER BY po.ordering, ao.name;

# User@Host: ****[****] @ [***.***.***.*]

# Query_time: 86502 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SELECT po.nid, po.oid, po.cost, po.price, po.weight, po.ordering,
ao.name, ao.aid FROM dr_uc_product_options AS po LEFT JOIN
dr_uc_attribute_options AS ao ON po.oid = ao.oid AND nid = 11 WHERE aid =
1 ORDER BY po.ordering, ao.name;

# User@Host: ****[****] @ [***.***.***.*]

# Query_time: 14812 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SELECT po.nid, po.oid, po.cost, po.price, po.weight, po.ordering,
ao.name, ao.aid FROM dr_uc_product_options AS po LEFT JOIN
dr_uc_attribute_options AS ao ON po.oid = ao.oid AND nid = 31 WHERE aid =
1 ORDER BY po.ordering, ao.name;

# User@Host: ****[****] @ [***.***.***.*]

# Query_time: 14835 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SELECT po.nid, po.oid, po.cost, po.price, po.weight, po.ordering,
ao.name, ao.aid FROM dr_uc_product_options AS po LEFT JOIN
dr_uc_attribute_options AS ao ON po.oid = ao.oid AND nid = 31 WHERE aid =
1 ORDER BY po.ordering, ao.name;

# User@Host: ****[****] @ [***.***.***.*]

# Query_time: 79611 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SELECT po.nid, po.oid, po.cost, po.price, po.weight, po.ordering,
ao.name, ao.aid FROM dr_uc_product_options AS po LEFT JOIN
dr_uc_attribute_options AS ao ON po.oid = ao.oid AND nid = 20 WHERE aid =
11 ORDER BY po.ordering, ao.name;

# User@Host: ****[****] @ [***.***.***.*]

# Query_time: 14464 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

SELECT po.nid, po.oid, po.cost, po.price, po.weight, po.ordering,
ao.name, ao.aid FROM dr_uc_product_options AS po LEFT JOIN
dr_uc_attribute_options AS ao ON po.oid = ao.oid AND nid = 28 WHERE aid =
1 ORDER BY po.ordering, ao.name;

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

| Id | User | Host | db
| Command | Time | State | Info
|

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

| 2126539 | **** | ***.***.***.*:35910 | *****
| Query | 127138 | Sending data | SELECT COUNT(*) FROM
(dr_uc_product_options AS po1 LEFT JOIN dr_uc_attribute_options AS ao1 ON
po1.o |

| 2193571 | **** | ***.***.***.*:60814 | *****
| Query | 102773 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2193995 | **** | ***.***.***.*:51594 | *****
| Query | 102612 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2195077 | **** | ***.***.***.*:56428 | *****
| Query | 102250 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2201672 | **** | ***.***.***.*:47003 | *****
| Query | 99592 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2201820 | **** | ***.***.***.*:47044 | *****
| Query | 99540 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2202583 | **** | ***.***.***.*:39634 | *****
| Query | 99206 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2202782 | **** | ***.***.***.*:53918 | *****
| Query | 99119 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2202801 | **** | ***.***.***.*:32841 | *****
| Query | 99114 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2207631 | **** | ***.***.***.*:56378 | *****
| Query | 97229 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2207654 | **** | ***.***.***.*:38426 | *****
| Query | 97221 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2207826 | **** | ***.***.***.*:56247 | *****
| Query | 97155 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2235742 | **** | ***.***.***.*:39702 | *****
| Query | 86734 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2236732 | **** | ***.***.***.*:33087 | *****
| Query | 86435 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2258301 | **** | ***.***.***.*:59820 | *****
| Query | 79685 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2258811 | **** | ***.***.***.*:53270 | *****
| Query | 79544 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2351470 | **** | ***.***.***.*:56010 | *****
| Query | 53099 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2352877 | **** | ***.***.***.*:36578 | *****
| Query | 52719 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2366270 | **** | ***.***.***.*:45993 | *****
| Query | 47899 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2366406 | **** | ***.***.***.*:33460 | *****
| Query | 47847 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2366580 | **** | ***.***.***.*:58320 | *****
| Query | 47783 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2367037 | **** | ***.***.***.*:38689 | *****
| Query | 47600 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2367445 | **** | ***.***.***.*:53190 | *****
| Query | 47442 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2368440 | **** | ***.***.***.*:34500 | *****
| Query | 47058 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2378337 | **** | ***.***.***.*:55306 | *****
| Query | 43325 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2378369 | **** | ***.***.***.*:59616 | *****
| Query | 43311 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2378477 | **** | ***.***.***.*:36844 | *****
| Query | 43265 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2378487 | **** | ***.***.***.*:50583 | *****
| Query | 43260 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2451378 | **** | ***.***.***.*:46641 | *****
| Query | 18141 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2461147 | **** | ***.***.***.*:59220 | *****
| Query | 14768 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2461250 | **** | ***.***.***.*:40009 | *****
| Query | 14745 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2462260 | **** | ***.***.***.*:38359 | *****
| Query | 14430 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2462358 | **** | ***.***.***.*:35547 | *****
| Query | 14397 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2462461 | **** | ***.***.***.*:60575 | *****
| Query | 14362 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2462792 | **** | ***.***.***.*:55042 | *****
| Query | 14269 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

| 2468294 | **** | ***.***.***.*:33347 | *****
| Query | 12474 | Waiting for table | SELECT po.nid, po.oid, po.cost,
po.price, po.weight, po.ordering, ao.name, ao.aid FROM dr_uc_product |

Thanks!

Justin

--

Drupal Freelance : Brighton uk
permalink #1 Thu, 05/29/2008 - 08:40
alien73

Posts: 146
 Joined: 11/10/2007
Bug Finder

Dreamhost... They actually kicked us off their servers saying our site was putting to much load.. which was nonsense and not good for business. We moved to another webhost with the same website with no problems. Do yourself a favor and find a better host...

--

Give and you will receive, you will be given much. Pressed Down, Shaken together, Running Over, it will spill into your lap.

permalink #2 Thu, 05/29/2008 - 09:38
Ryan

Posts: 5367
 Joined: 08/07/2007
AdministratorHead Code Monkey - I eat bugs.

These look to be like basic product loading queries... they aren't really that complex. Do you have a lot of products or a lot of attributes/options?

permalink #3 Thu, 05/29/2008 - 11:46
zsiswick

Posts: 20
 Joined: 05/05/2008

One of my client's sites is served by Dreamhost, they were current customers when I redesigned their site. When I got Drupal installed, I was concerned about slow page loads - especially because it was a small site with very few modules installed.

I looked on the forums at drupal.org as to why the pages were loading so slowly and found lots of complaints about Dreamhost's MySQL limitations. Most people recommend steering clear of them for Drupal sites.

permalink #4 Thu, 05/29/2008 - 12:10
vincent@drupal.org
Posts: 7
 Joined: 05/18/2008

I have a site hosted by Dreamhost and running on Drupal 4.7. Never had any complains.
It has 100+ unique visitor a day (i know that's not a lot) and the home page has multiple feeds from forums and content types.

Having said that I won't be surprised...Dreamhost is cheap!

Cheap no good
Good no cheap !

By the way...can anyone recommend a good alternative to dreamhost also doing rails hosting?

_V

permalink #5 Fri, 05/30/2008 - 06:21
sphism
Posts: 48
 Joined: 11/29/2007
Bug Finder

Yeah there's quite a few attributes. Probably up to 7 different attributes with 6-8 options in each.

\that's for the totally custom clothes, the other pre-set products have only a few attributes + options.

Is there any point where the system is trying to get a total of all the possible selections? Thats' what stopped me using the ecommerse shop originally because it tried to deal with every single possible selection as a different product.

Yeah lots of people say dreamhost not good for drupal but this is pretty rediculous. I mean it really shouldn't be that much of a load on the server, right? I'm wondering if there's just a little bug in the code somewhere that's mucking it all up?

Hmmm, i don't really want to switch hosts because i really like dreamhost support and the flexibility i get.

Maybe i could just take out the custom products for the time being, that might be a quick fix?

Any help is really appreciated right now, this is starting to turn into a freakin nightmare.

cheers,

matt

--

Drupal Freelance : Brighton uk

permalink #6 Fri, 05/30/2008 - 08:28
vincent@drupal.org
Posts: 7
 Joined: 05/18/2008

You don;t want to try the PS option with dedicated Mysql DB?

permalink #7 Fri, 05/30/2008 - 09:02
Lyle

Posts: 2267
 Joined: 08/07/2007
AdministratoreLiTe!

The Adjustments tab of the product edit form calculates all the possible combinations, but only for that particular product. Also, it uses a pager, so it's only trying to get 20 rows at a time anyway.

Having 6 or so attributes on product is unusual, but I don't think it's an unreasonable load. I think what you really need is some dedicated resources, and not a shared space. If you can, I'd see how many other sites were on that particular server. They might be crowding you out.

permalink #8 Sun, 06/01/2008 - 05:35
sphism
Posts: 48
 Joined: 11/29/2007
Bug Finder

hmm. Yeah there are quite a few options. It's a range of clothing and you can choose 2 or 3 colors, different hood shapes, graphic design and print color, etc. It really starts to add up once you consider all the combinations.

I'll stick RC5 up there later today and see what dreamhost say. I may have to just take down the totally custom products for a while, but if it's the admin pages causing the bug then it shouldn't be a big deal.

Lyle, are you sure there's no little bug in there somewhere thats doing something naughty? Sounds like it shouldn't be problem???

Thanks for helping out everyone.

matt

--

Drupal Freelance : Brighton uk

permalink #9 Mon, 06/02/2008 - 08:08
sphism
Posts: 48
 Joined: 11/29/2007
Bug Finder

Well i've updated everything and contacted dreamhost to keep an eye on it for me.

It looks like i was using beta6 not rc4 at all. So maybe there was a little bug back then. Who knows.

I'll let you know if there's still a problem.

I'm quite interested in using Dreamhost PS Mysql server for $15 a month. Do you think drupal sites would run much faster?

--

Drupal Freelance : Brighton uk