Forums / General Questions / My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

Locked

Started by pcwerks on 3 Jan 2008, 10:53 PM · Views: 6,632

Results 1 to 20 of 27
This thread is locked. New replies are disabled.
3 Jan 2008, 10:53 PM
#1
pcwerks avatar

pcwerks

New Zenner

Join Date:
Jan 2008
Posts:
19
Plugin Contributions:
0

My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

I had a server with osCommerce set up and running on it. Someone just told me about Zen Cart, so I decided to try it. The install went fine, and setting up the admin went fine. What's not fine is that the catalog itself is slow. Not horribly slow like some people here have mentioned (10+ second page loads), but each page load is taking between 1.5 and 3 seconds and I can't figure out why. Here are some sample parse times

Parse Time: 1.481 - Number of Queries: 43 - Query Time: 0.04258829524231
Parse Time: 1.611 - Number of Queries: 63 - Query Time: 0.06126887361145
Parse Time: 2.401 - Number of Queries: 141 - Query Time: 0.14540380244446
Parse Time: 2.113 - Number of Queries: 167 - Query Time: 0.12346168908691
Parse Time: 2.065 - Number of Queries: 194 - Query Time: 0.13132462286377
Parse Time: 1.714 - Number of Queries: 200 - Query Time: 0.13495773448181
Parse Time: 1.932 - Number of Queries: 83 - Query Time: 0.066764063919067

As you can see, the MySQL query times are very low, like they should be. But why are my Parse Time's so high? I have turned almost every option off. There are only 3 info boxes on the page, and the banners are gone. There are only 2 items in the catalog and there are no orders placed so far. I have disabled all of the New Products, Upcoming Products, Featured Products, etc. What's confusing me is that all of my other domains are running just fine, including 3 installs of osCommerce. I'm getting Parse Times from the osCommerce installs close to 0.2 and 0.3 seconds. I realize that osCommerce is similar to Zen Cart, but Zen Cart has lots of more installed options by default, as well as a lot more tables in the database, but Zen Cart is not doing 10 times as much as osCommerce is, so why is it taking 10 times as long to parse a page load?

You can see both of my stores here, they are almost identical
osCommerce Version - http://www.mypcwerks.com/store/
Zen Cart Version - http://www.mypcwerks.com/store2/

Can anyone tell me what could possible be wrong? If you need more details about the server or any of the software or setup, please let me know. I have been searching and trying things for 8 hours straight now and I am all out of ideas!!!!! :cry:

3 Jan 2008, 11:11 PM
#2
yellow1912 avatar

yellow1912

Totally Zenned

Join Date:
Oct 2006
Posts:
5,422
Plugin Contributions:
0

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

Your picture file sizes are not good. Install image handler 2 to fix that.

PS: OOps, pardon me, was looking at osc site.

I no longer provide installation support on forum for all my modules. However, if there are real bugs with the modules please feel free to contact me

3 Jan 2008, 11:15 PM
#3
ajeh avatar

ajeh

Oba-san

Join Date:
Sep 2003
Location:
Ohio
Posts:
62,757
Plugin Contributions:
1

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

Turn on GZip in the Configuration ... GZip ...

And make sure that you have all of the indexes on your database that a new install would have ...

Linda McGrath
If you have to think ... you haven't been zenned ...

**Did YOU buy the Zen Cart Team a cup of coffee and a donut today? Just click here to support the Zen Cart Team!!**

Are you using the latest? Perhaps you've a problem that's fixed in the latest version: [Upgrade today!]
Officially PayPal-Certified! Just click here

Try our Zen Cart Recommended Services - Hosting, Payment and more ...
Signup for our Announcements Forums to stay up to date on important changes and updates!

3 Jan 2008, 11:17 PM
#4
pcwerks avatar

pcwerks

New Zenner

Join Date:
Jan 2008
Posts:
19
Plugin Contributions:
0

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

Ajeh:

Turn on GZip in the Configuration ... GZip ...

And make sure that you have all of the indexes on your database that a new install would have ...

I have tried it with and without GZip turned on. Either way, the times are all almost identical.

This is a new install, I just installed it yesterday and it's been doing the exact same thing since the moment I installed it, even with no products added. How do I know if something is not right with the indexes?

3 Jan 2008, 11:19 PM
#5
ajeh avatar

ajeh

Oba-san

Join Date:
Sep 2003
Location:
Ohio
Posts:
62,757
Plugin Contributions:
1

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

If this is a clean install that you later added products to then you should be okay on your data ...

I am assuming that you have installed the latest Zen Cart v1.3.8a ...
http://www.zen-cart.com/forum/showthread.php?t=81696

Linda McGrath
If you have to think ... you haven't been zenned ...

**Did YOU buy the Zen Cart Team a cup of coffee and a donut today? Just click here to support the Zen Cart Team!!**

Are you using the latest? Perhaps you've a problem that's fixed in the latest version: [Upgrade today!]
Officially PayPal-Certified! Just click here

Try our Zen Cart Recommended Services - Hosting, Payment and more ...
Signup for our Announcements Forums to stay up to date on important changes and updates!

3 Jan 2008, 11:25 PM
#6
pcwerks avatar

pcwerks

New Zenner

Join Date:
Jan 2008
Posts:
19
Plugin Contributions:
0

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

Ajeh:

If this is a clean install that you later added products to then you should be okay on your data ...

I am assuming that you have installed the latest Zen Cart v1.3.8a ...
http://www.zen-cart.com/forum/showthread.php?t=81696

Yes, in the admin section it shows
Zen Cart 1.3.8a
Database Patch Level: 1.3.8
v1.3.8 [2008-01-02 11:40:38] (Fresh Installation)

Does any of this help?

PHP Version 5.2.5
Database: MySQL 5.0.45-community-nt
Database Host: localhost (127.0.0.1)
HTTP Server: Apache/2.2.4 (Win32) mod_ssl/2.2.4 OpenSSL/0.9.8d PHP/5.2.5
PHP Memory Limit: 128M
POST Max Size: 8M
Database Data Size: 306 kB
Database Index Size: 397 kB

3 Jan 2008, 11:49 PM
#7
ajeh avatar

ajeh

Oba-san

Join Date:
Sep 2003
Location:
Ohio
Posts:
62,757
Plugin Contributions:
1

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

Here are some stats from a busy site running Zen Cart:

This site has:
340+ Categories ...
5,900 Products ...
73,000 Attributes ...
NOTE: there are also about 100 people on the site at any given time ...

Home Page:
Hit 1: Parse Time: 0.795 - Number of Queries: 411 - Query Time: 0.503991784042
Hit 2: Parse Time: 0.385 - Number of Queries: 400 - Query Time: 0.222354076126

All Products:
Hit 1: Parse Time: 1.049 - Number of Queries: 1809 - Query Time: 0.523603009018
Hit 2: Parse Time: 0.919 - Number of Queries: 1809 - Query Time: 0.499955935379

Product No Attributes: Parse Time: 0.252 - Number of Queries: 164 - Query Time: 0.0803091335907

Product with 92 Attributes: Parse Time: 0.461 - Number of Queries: 881 - Query Time: 0.221011120956

So for your situation I tend to think there is something more here than meets the eye to optimize the server for MySQL ...

osC doesn't have much to it and I would expect it to run faster ... however, your Zen Cart site doesn't have anything in it that is standing out to explain these slow times that you are experiencing ...

Linda McGrath
If you have to think ... you haven't been zenned ...

**Did YOU buy the Zen Cart Team a cup of coffee and a donut today? Just click here to support the Zen Cart Team!!**

Are you using the latest? Perhaps you've a problem that's fixed in the latest version: [Upgrade today!]
Officially PayPal-Certified! Just click here

Try our Zen Cart Recommended Services - Hosting, Payment and more ...
Signup for our Announcements Forums to stay up to date on important changes and updates!

4 Jan 2008, 12:44 AM
#8
pcwerks avatar

pcwerks

New Zenner

Join Date:
Jan 2008
Posts:
19
Plugin Contributions:
0

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

Ajeh:

This site has:
340+ Categories ... 5,900 Products ... 73,000 Attributes ...
Product No Attributes: Parse Time: 0.252 - Number of Queries: 164 - Query Time: 0.0803091335907
So I tend to think there is something more here than meets the eye to optimize the server for MySQL ...
NOTE: there are also about 100 people on the site ...

Ajeh, first of all, thank you very much for taking the time to help me. You have no idea how much I appreciate it!!!!

Now I agree, there is something definitely wrong, but I don't think that it's the MySQL server. Look at all of my Query Times. My pages have way less number of queries than your pages, but the query times are proportionally way less than yours as well. What's not proportional is the Parse Times. Take these two examples from both of us

Mine : Parse Time: 2.113 - Number of Queries: 167 - Query Time: 0.12346168908691
Yours : Parse Time: 0.252 - Number of Queries: 164 - Query Time: 0.0803091335907

These number of queries is almost identical. My Query Time is about 1.5 times what Your Query Time is, but my Parse Time is about 8.4 times what your Parse Time is. And it only gets worse the more the numbers increase. Look at these numbers if you total up your 6 examples and my 7 examples. Line 1 is your total times. Line 2 is My total times. Line 3 is if you multiply my numbers by 6.144 to get my Num Queries to match yours.

     Parse Time    Num Queries      Query Time      Avg Query Time
1 :    3.861         5474        2.0512250591117    0.000374721421
2 :   13.317         891         0.7057690816505    0.000792108958
3 :   81.815         5474        4.3360044365374    0.000792108958

My Average Query Time is a little over 2 times what your Average Query Time is, but my Total Parse time for 5474 queries was 21 Times what your Total Parse time was for the same number of queries. I understand that some queries are more complicated than others, but do you agree that the SQL Queries don't seem to be the bottleneck?

What I need to know is what ELSE affects the Parse Time besides all of the database queries. What can I tune or adjust to shrink the Parse Time? Keep in mind that I've already turned on GZip and it made no difference at all, probably because the pages are so small. So does anyone have any ideas or suggestions?

4 Jan 2008, 12:50 AM
#9
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,511
Plugin Contributions:
176

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

What are the CPU/RAM specs of the portion of the server allocated to you?
What are the server's current Load Averages running at?

.
Zen Cart - putting the dream of business ownership within reach of anyone!
Donate to: DrByte directly or to the Zen Cart team as a whole

Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

4 Jan 2008, 1:52 AM
#10
pcwerks avatar

pcwerks

New Zenner

Join Date:
Jan 2008
Posts:
19
Plugin Contributions:
0

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

DrByte:

What are the CPU/RAM specs of the portion of the server allocated to you?
What are the server's current Load Averages running at?

Software - Windows XP SP2, Apache 2.2.4, PHP 5.2.5, MySQL 5.0.45
Hardware - Intel Celeron D 3.2Ghz, 2GB DDR RAM, 160GB IDE HD

This is my own server, I have full control of it. Besides the Apache, PHP & MySQL, it is also running a small mail server, mail filter and a virus scanner. I will get some loads data now.

4 Jan 2008, 4:20 AM
#11
pcwerks avatar

pcwerks

New Zenner

Join Date:
Jan 2008
Posts:
19
Plugin Contributions:
0

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

DrByte:

What are the server's current Load Averages running at?

I'm attaching a picture of the server's load from Task Manager's Performance Tab. I had it full-screen, so the graph is the CPU Usage for about 4 minutes. It seems to be a good example of what it usually is. The spikes are usally an email that is being received and/or scanned by the mail filter and virus scanner. As you can see, it's not doing much else.

4 Jan 2008, 4:23 AM
#12
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,511
Plugin Contributions:
176

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

How does the graph spike differently while you're doing transactions on your store which feel like they're running slowly?

.
Zen Cart - putting the dream of business ownership within reach of anyone!
Donate to: DrByte directly or to the Zen Cart team as a whole

Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

4 Jan 2008, 5:34 PM
#13
pcwerks avatar

pcwerks

New Zenner

Join Date:
Jan 2008
Posts:
19
Plugin Contributions:
0

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

DrByte:

How does the graph spike differently while you're doing transactions on your store which feel like they're running slowly?

Here are 4 screen captures

osCommerce - Loading pages quickly one after another

osCommerce - Loading pages with a few second pause in between

Zen Cart - Loading pages quickly one after another

Zen Cart - Loading pages with a few second pause in between

Does anyone have any ideas now? The problem has to be with Apache, PHP, MySQL or Zen Cart, right? And I know that Zen Cart is running quickly for other people, so I think that rules it out, right? So how do I figure out where the bottleneck is?

4 Jan 2008, 9:48 PM
#14
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,511
Plugin Contributions:
176

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

Bizarre.

Let's try paring it down a bit. - In the Admin->Tools->Layout Boxes Controller, turn off the currencies, languages, whos_online boxes.

  • Admin->Modules->Order Total --- remove the Discount Coupon, and Gift Certificates modules
  • Admin->Configuration->Layout Settings:
    --- Always Show Categories on Main Page -- set to 0
    --- Categories-Tabs Menu ON/OFF -- set to 0
  • Admin->Tools->Banner Manager --- click all the green dots to turn off all your banners (they'll turn red)
  • Admin->Catalog --- click the Green Dot on the Hard Drives category to disable it. Confirm the choice on the next screen. (this is to turn off your products which have attributes, since your osc store doesn't have any attributes)

Now how does performance operate?

.
Zen Cart - putting the dream of business ownership within reach of anyone!
Donate to: DrByte directly or to the Zen Cart team as a whole

Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

7 Jan 2008, 3:27 PM
#15
pcwerks avatar

pcwerks

New Zenner

Join Date:
Jan 2008
Posts:
19
Plugin Contributions:
0

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

DrByte:

Bizarre. Let's try paring it down a bit.
Now how does performance operate?

Well That didn't seem to make any change at all in performance.
Parse Time: 2.284 - Number of Queries: 204 - Query Time: 0.14787280299377
Parse Time: 2.109 - Number of Queries: 161 - Query Time: 0.15567979673767
Parse Time: 1.791 - Number of Queries: 81 - Query Time: 0.073664363693237
Parse Time: 1.873 - Number of Queries: 112 - Query Time: 0.098703315841675

I added 5 attributes to 2 parts in osCommerce and added every combination of all parts to the shopping cart and it is still going just as fast as it was before too. I know that the stock osC doesn't have as many options as Zen cart does when both are right out of the box, but is Zen Cart that heavily loaded that it would be that much of a difference? I've seen Zen Cart on other peoples sites and it is really quick, so would osC be that much quicker on their sites too?

Is there anything else you can think of that I can change in my Zen Cart to speed it up? Or is there any other tests I can do to see if the problem is just with the Zen Cart on my server or if it is my server itself?

7 Jan 2008, 9:25 PM
#16
pcwerks avatar

pcwerks

New Zenner

Join Date:
Jan 2008
Posts:
19
Plugin Contributions:
0

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

Ok, here's an idea to see if it's an Apache, PHP or MySQL issue. I wrote the code below to perform 64million iterations of nothing. Basically it loops itself 64 million times and tests to see how long it takes to do it. If I test my times against other people who have similar server specs and mine is pretty close, then I know that PHP and Apache are not the issue, which means that it's probably my MySQL server. But if my times are much longer than others' times with the same server specs, then I know the problem is either PHP or Apache (or the server itself) and it has nothing to do with Zen Cart or MySQL.

So if anyone is reading this and has an extra 60 seconds to spare, could you please save a .PHP file on your server with the following code in it and run it a few times and post some of your times, or at least the Hight, Low and Average of a few runs. And don't for get to add your server's specs like Apache Version, PHP Version, Server CPU & RAM and anything else you think is relevant!

Script is here: http://www.zen-cart.com/forum/showthread.php?t=84930

9 Jan 2008, 12:00 AM
#17
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,511
Plugin Contributions:
176

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

pcwerks:

Can anyone tell me what could possible be wrong?Zen Cart does a lot of file includes, moreso than oscommerce.

Windows versions of Apache have a less-efficient file-threading model than the Linux versions do. And Windows is more susceptible to file fragmentation. It's possible that you're victim of those issues.

What kind of hard drive do you have installed on that server?
How often do you defragment?

You might consider a PHP cache/encoder -- that can be a huge boon where disk I/O is concerned. Something like APC is quite easy to install.

.
Zen Cart - putting the dream of business ownership within reach of anyone!
Donate to: DrByte directly or to the Zen Cart team as a whole

Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

9 Jan 2008, 10:45 PM
#18
pcwerks avatar

pcwerks

New Zenner

Join Date:
Jan 2008
Posts:
19
Plugin Contributions:
0

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

DrByte:

What kind of hard drive do you have installed on that server? How often do you defragment?

Great, one more thing that could be causing the problem. :)
I have a brand new 160GB ATA133 hard drive on an ATA133 controller.
I guess it's possible, but I don't think that it's the hard drive causing this much delay, but only with Zen Cart. I checked the drive and it says that it doesn't need to be defragged.

DrByte:

You might consider a PHP cache/encoder -- that can be a huge boon where disk I/O is concerned. Something like APC is quite easy to install.
I will definitely consider that when I get this problem fixed, but if others are not having these issues right out of the box, and they're not all using PHP cache/encoders, then I want to find out why rather than just put a band-aid on it.

If I haven't mentioned it enough, I really really appreciate your help on this issue. There doesn't seem to be a lot of help in the forums here. :(

Do you have any other suggestions (if we rule out MySQL being the issue in the other thread).

10 Jan 2008, 7:58 AM
#19
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,511
Plugin Contributions:
176

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

pcwerks:

There doesn't seem to be a lot of help in the forums here. :(Well, this forum isn't exactly geared towards optimizing servers and diagnosing server speed/performance issues.
Most of the support here is related to using and customizing Zen Cart.
If your questions were related to that, you'd have a lot of responses.

I don't mind helping; granted, my goal is largely to determine for sure whether you've stumbled upon a legitimate Zen Cart issue or not. A by-product will hopefully be resolution of your problem and either relief or dread on my end! As to optimizing your own server, while I have some knowledge of many different aspects of it and of where things commonly go wrong, I'm certainly not the expert. There are many others around here who run and tune servers for a living. I don't. I write Zen Cart. :smartalec:

.
Zen Cart - putting the dream of business ownership within reach of anyone!
Donate to: DrByte directly or to the Zen Cart team as a whole

Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

10 Jan 2008, 1:58 PM
#20
banswidthjunkie avatar

banswidthjunkie

Zen Follower

Join Date:
Nov 2007
Posts:
167
Plugin Contributions:
1

Re: My ZenCart is Slow, but it's not MySQL! What is it?!? (Hosting on my own server)

I am running zc locally on a development server with better specs than your server (3ghz pentium D, 2gb 600mhz ram, SATA HD) and it is apache 1.3 running on windows XP and it is worryingly slow. This is on top of the fact that I have deleted quite a lot of code that I don't need.

However, I sort of have faith that once I put the site on a dedicated Linux server that the site will speed up dramatically.

If you are doing your own hosting, you might want to consider Gentoo Linux because it is the most customisable type of linux and has the best development community (this somes at a price, but you will find out about that in your own time!)

My ZenCart Mods: Define Pages Generator | Fual Slimbox (Lightbox)
A site I did for a mate: http://richard-moon.co.uk/