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?

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.

_________________
The irony is that Bill Gates claims to be making a stable operating system and Linus Torvalds claims to be trying to take over the world.
-- seen on the net

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.

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.

(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.

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

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

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

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

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