Zen-Cart admin slowing

Hi Guys,
we've recently become very busy and are getting nearly to the stage of getting 400 orders per day. We have noticed considerable latency occuring recently when looking in the admin system within zen-cart.

Key pages that take a long time to load. Category/Products, customers, orders.

We are on a dedicated server.
running Zen-Cart V1.3.8a

is there anything we can do to zencart that may help refine this?
i noticed an option under 'store-manager' called Optimize Database. I've read some things on the zen-cart forums that say this feature doesn't work correctly. Are there any updates to this that i should apply before continuing? + is there any other things within zen-cart that i could do in order to speed up the admin? likes alteration to core code that fetches the customers/orders/categories etc.

regards
Andy.

Usually when this question arises ... there are numerous things that can be done to speed up a site but often times they are unique to a site ...

Without reviewing your site it makes it hard to speculate where the slow downs really exist ...

How many Products?

How many Categories?

How many Attributes?

How many Attributes per Product low count? average count? high count?

Do you use Linked Products?

Specials?

SaleMaker?

Where do you see the slow downs, in the Catalog? In the Admin? Both?

You say a dedicated server ... is this a Virtual dedicated server or a real dedicate server?

What are the specs on the server? Memory? Cache? Processor? etc.

Have you an URL that we could perhaps peek at? :smile:

Hi Ajeh

Server is not virtual.

Spec:
Memory: 3GB DDR RAM
Hard Drive: 250GB SATA
Processor: Single Socket Dual Core AMD Opteron 1212
according to our hosts there isn't any cause for concern at that end.

The Main Latency we experience is in Admin. When we look at the Customers Page, Orders Page and Catagories/Products

Current No. of ACTIVE products: 2678
Categories: 821
Customers: 60,325
Yes we use Linked Products
Specials: Used
Salemaker: Used

We don't use Attributes - from what i understand pretty much everyone uses but we havn't ever really ecountered a reason to use them over anything else.

what has been mentioned is the query that calls the orders, customers, categories/products looks through absolutely everything, and ofc as we grow it gets progressively worse.

regards
Andy.

Andy_GS:

i noticed an option under 'store-manager' called Optimize Database. I've read some things on the zen-cart forums that say this feature doesn't work correctly.
I'm not sure where exactly you're seeing anything about "doesn't work correctly."
All the "Optimize Database" option does is loop through all your MySQL database tables and run the "OPTIMIZE TABLE" command in MySQL against the table, causing it to remove any blank space left from deleting records. You can study the effects of that on the MySQL website if you're interested.
That's it. It does nothing special beyond that.
The only reported "problems" I know of with respect to it is where hosting companies insert hyphens in the database-name, which results in problems since a hyphen is a reserved character unless enclosed inside backquotes. That should have no effect on you unless you're one of those unlucky ones. The only negative effect there would be that you couldn't use that option from within your Zen Cart admin without a tiny code alteration, which is documented in numerous places on the forum.

In short: Go ahead and use Optimize Database on a periodic basis. But it's not going to solve any problems you have with growing data volume.

Andy_GS:

Current No. of ACTIVE products: 2678
Categories: 821
Customers: 60,325
Yes we use Linked Products
Specials: Used
Salemaker: UsedHow many orders?

Andy_GS:

We don't use AttributesHave you turned off all attribute options under Admin->Configuration->Attribute Settings?
This can reduce some needless code execution if you're not using those features.
Andy_GS:

what has been mentioned is the query that calls the orders, customers, categories/products looks through absolutely everything, and ofc as we grow it gets progressively worse.
Probably. Although I've not seen that cause any serious problems on most sites.

I have seen the Super Orders addon and the PDF Order Center addon have serious problems with large numbers of orders in the database due to some of the filtering options they offer: They build pulldown menus of all orders, which become humongous and not only take forever to build but also to stream to the browser. One must hack the code in those addons to disable those features.

I suppose you could write your own pagination logic to pull smaller datasets instead.

And maybe your MySQL server needs a bit of tuning. Can you post your my.cnf config file?

DrByte:

I have seen the Super Orders addon and the PDF Order Center addon have serious problems with large numbers of orders in the database due to some of the filtering options they offer: They build pulldown menus of all orders, which become humongous and not only take forever to build but also to stream to the browser. One must hack the code in those addons to disable those features.
Clean Zen Cart (without addons) also has performance problems on orders.php page (I'm not sure about customers.php page) when orders/orders_products table gets big (10k+ entries). Only solution I'm aware of is to modify orders.php and change the way order list is created.

I would look at a configuration issue of the server or the need for additional indexes ...

I have a client on a dedicated server with 4 gig memeory and:
Processors 2
Model Intel(R) Xeon(R) CPU 3060 @ 2.40GHz
Chip MHz 2400.09 MHz
Cache Size 4096 KB

Which does make a difference ...

Some of the database tables are:

customers 100,308 25.6 MiB

orders 139,776 111.5 MiB
orders_products 443,691 68.0 MiB
orders_products_attributes 387,270 57.6 MiB
orders_products_download 18 4.4 KiB
orders_status 15 3.4 KiB
orders_status_history 360,853 39.6 MiB
orders_total 433,912 43.9 MiB

products 8,650 2.0 MiB 	28 B
products_attributes 124,989 15.6 MiB

products_description 8,650 38.5 MiB

Hi guys,

well our hosts have given us some RAM cache, which seems to have touch wood sorted things out temporaryly.

We do use super orders/ batch status update, however we were still experiencing latency even on the standard orders page and customers page. Are these hard hacks written anywhere so i can try to implement these as well?

I've removed all the attributes things, and did the store manager > optimise database, which updated 137 tables but this had no obvious results.

I'll keep this ticket updated if there are any other developments or solutions at this end.

regards
Andy.

Note to self touch wood makes things break. It's slowed up again.

Ajeh, with this large site you maintain, do you backup orders from say a year + ago and then move them to a new table?

regards
Andy.

Currently, all orders are still in the database tables ...

The backup idea could be achieved and that would speed things up in some areas, but often times the slow downs are how MySQL/memory is configured on the server ... a missing index could cause issues etc.

Hi Ajeh,

it would appear it was a server spec issue, (my hosts lie!!!) apparently our 2ghz dual core processors were capped at 1ghz...... they've been uncapped and with noted remarkable improvement.

one thing that did come back was on slow queries when %% are used both in front of and after keywords. Which means indexing on tables isn't used.

e.g.
SELECT ... WHERE myfield LIKE '%search%';

I removed these and heard from the office that this mean't they couldn't look up stuff. So i've replaced these.

any suggestions how we can uterlise indexing but at the same time allow for 'like' searches?

regards
Andy.

You would need the exact slow queries to see if they can be sped up any better ...

Right we're going to get a wonderful new shiney server in the new year so we're gonan cope till then and hope that nothing goes horrifically wrong touches anything wooden lol

there shouldn't be any problems with zencart running on a 64bit machine is there?

regards
Andy.

Andy_GS:

there shouldn't be any problems with zencart running on a 64bit machine is there?
Not unique to Zen Cart, no.

But I have heard of some people having "issues" with the latest version of PHP on Windows 7 in 64-bit mode.

i think that maybe down more to the php version, we tried upgrading from php4 to 5 and we encountered issues with certain joins.

But thanks for the advise.

Andy.