Linode.com Forum

[melancthon]

Hello,

I've recently installed ZenCart on my Linode to use as an e-commerce solution, and as I've been inserting products with attributes, I've noticed the page query times are quite insane.

To start, I have about 450 attributes for a product (we are selling Scottish goods and some products are available in over 400 different tartans, selected from a drop down list). The list is created through queries, and for a particular page the software lists around 10,000 queries. With the my.cnf posted by caker, my query time was around 120 seconds, far too long to expect people to wait for a page to load, methinks. After some tweaking, I managed to get this down to a still overly long 15 seconds (through using query cache within mysql).

I am curious if anyone else has had this issue on any software they're using, and what settings did you find worked best for your MySQL variables. I should mention that presently I'm only running a 360, but I have yet to hit the swap file (top reports 5352k swap used, but it's been at 5352k for weeks, 4344k memory free - free reports similar information with 27232 buffers and 197652 cached).

Any suggestions?

[SteveG]

I've got no mysql help for you, but are you seriously expecting people to use a 400 item drop-down list box? You may want to re-think your interface before you spend time optimizing the sql queries.

[OverlordQ]

sounds more like a problem with the software rather then MySQL, I really dont think any amount of tweaking will fix that if the software is doing stupid queries.

[melancthon]

Yes, I do expect them to when they are looking for their family name. Since they are doing it on our present e-commerce site, and we're the largest supplier of rental kilts in the US, I don't see why they wouldn't do the same when we switch to our new site.

But thank you both for your input so far - the next version of Zencart is, hopefully, supposed to handle larger amounts of attributes better. I was hoping there would be a way to manage for the time being through dealing with the mysqld variables.

[marv]

(I'm not a MySQL expert. I have more experience with MS SQL Server.)

You could try to figure which one of the queries takes the most time and add some indexes to improve the query's performance.

[OverlordQ]

marv wrote: (I'm not a MySQL expert. I have more experience with MS SQL Server.)

You could try to figure which one of the queries takes the most time and add some indexes to improve the query's performance.

I really dont think it's the length of queries, it's just that there's so many of them. Indexes really wont help in this matter.

[OverlordQ]

melancthon wrote: Yes, I do expect them to when they are looking for their family name. Since they are doing it on our present e-commerce site, and we're the largest supplier of rental kilts in the US, I don't see why they wouldn't do the same when we switch to our new site.

And you can't categorize the products by family name so you wont have 1 product with 500 attributes? Hopefully you'll realize there comes a point when a redesign is better then continuing to support something that was implemented wrong in the first place.

my $.02

[melancthon]

So I should have 500+ different kilts? I don't think that solves or simplifies anything for the customer.

[caker]

I wouldn't use that my.conf I posted years ago -- it was meant for a VERY small memory footprint.

In my opinion, performance tuning is as much an art as a science -- there's no one-size-fits-all solution that's going to solve everyone's problems. It takes some detective work to identify each bottleneck, and then the know-how (read: google) to fix that bottleneck. It takes some patience first time around, but eventually you'll have some intuition or insight as to where to look the next time around...

I suggest you start by looking at your memory variables inside your my.conf. Larger buffers and whatnot can decrease time spent performing disk scans during joins, lookups, and about a million other things. Check out how well your tables' indexing is being used by using EXPLAIN query.

How about caching this stuff inside the application?

-Chris

[caker]

One more thing:

If it takes 10,000 queries to build a page, you're doing something wrong. :) SQL freaking rocks, and I'm almost positive you'd be able to reduce that to 1 query (or at most a handful).

-Chris

[melancthon]

As an update, I have been migrated to a Xen host, and that has reduced query times quite a bit, I suppose simply because there is more power being thrown at my particular process. I will accept that for the time being :)