Server suddenly became slow

I'am having big performance issues on my actual ecommerce website when I surf between categories of products ( 20 seconds for page loading ) . While my provider is telling me that everything is fine , I start to do some tunning insights :

http://bit.ly/U48eoC

Activating "page parse time" I see : Parse Time: 14.586 - Number of Queries: 2380 - Query Time: 14.114721213 on pages
That's mean 2380 queries on database for one session in order to display 20 products on a page ? It's a big number , don't you think ?

I really don't know how to solve this ... where to start . I'm asking you some help .

Your database is taking 14.11 of the 14.58 seconds used for the whole thing.

While 2380 queries might sound like a lot, there are thousands of well-tuned (even "shared") webservers out there that can handle five times that many queries in less than a second.

Your database is definitely the bottleneck.

While you might find it fun to get into a debate about how many queries it takes to draw the page, there are many technical reasons why it's like that, some of them grandfathered by os commerce. Newer versions of Zen Cart are progressively reducing the number of queries needed, and one of the big features of the upcoming v2 is a huge reduction in number of queries as a result of rewriting a large part of the core files. But, that rewrite won't help you today. So let's not get hung up on query numbers for now, at least as far as original Zen Cart core code is concerned.
You might have poorly written addons that are causing extra queries, but that's something I can't help you with, because there's no way for any of us to know what ways you've changed the core code, either by customizing it or installing addons.

There are several system settings you can change to reduce queries, but only if they relate to features you're not using.
There are a couple FAQ articles talking about performance tuning, including those system settings. One of them is the very first article in the FAQs section: http://www.zen-cart.com/content.php?63-how-do-i-speed-up-my-site
and a more advanced technical document for server admins: http://www.zen-cart.com/content.php?262-Webserver-Tuning-Tips

DrByte:

Your database is taking 14.11 of the 14.58 seconds used for the whole thing.

While 2380 queries might sound like a lot, there are thousands of well-tuned (even "shared") webservers out there that can handle five times that many queries in less than a second.

I didn't change the code or installed modules in last 2 weeks when website suddenly became slow . On my test website same number of queries ( cloned production website ) is executed in less 2 seconds . This confirm what your're saying about "webservers out there that can handle five times that many queries in less than a second" .
I believe too that database is the bottleneck .

Why pages like product details are not slow ? ( Parse Time: 2.382 - Number of Queries: 453 - Query Time: 1.81040376318 )
Why querying tables ( using phpmyadmin ) is not slow ?

solo_400:

I didn't change the code or installed modules in last 2 weeks when website suddenly became slow .
Okay, so by process of elimination:

• "you" didn't change any code
• nobody "here" changed any of the code
• Zen Cart authors didn't change your code, because they don't have any access to your site/server/software/store
  What other players are involved here?
  Oh: the server. Run by the hosting company.
  Bingo. There's the problem: something's wrong with the server.

Maybe the server's overloaded?
Maybe the database engine is damaged?
Maybe the hosting company's administrator changed database-server settings? Maybe they upgraded the version and broke something or corrupted something or didn't realize that their automatic upgrade also changed configuration settings that are now causing bottlenecks?

Only they can speak for what's happening behind closed doors.

solo_400:

Why pages like product details are not slow ? ( Parse Time: 2.382 - Number of Queries: 453 - Query Time: 1.81040376318 )
Why querying tables ( using phpmyadmin ) is not slow ?
I suppose it's possible that your database tables might need a "repair" run on them to resync/rebuild indexes and such.
You can use phpMyAdmin to select tables and choose Repair Table, at least from the Operations tab. Some hosting companies offer a "Repair Database" button which does this "repair table" on all tables at once, which is a lot more convenient.

I love Bingo .

I have to agree with DrByte: Access from the web server to the database is the slowdown. Some hosting providers may also impose "rate limits" for SQL Queries on shared hosting... And I would not recommend running an e-commerce platform on such a hosting package. Keep in mind phpMyAdmin is not always on the same web server as your website - and may be exempted from "rate limits".

I would highly recommend talking to your hosting company (or looking into other hosting providers). Make sure you make a backup of the database - Always good to have recent backups! Then have the hosting provider look into the performance issues.

If you are on Zen Cart 1.3.9 you may want to look into "Query Cache" or install Zen Cart 1.5.1 which includes "Query Cache". This change can potentially reduce the number of queries per individual page request.

You could also play around with caching the SQL responses to file (if the server can read / write to disk faster then execute queries) - but using file based SQL caching has some disadvantages (and on most hosts would be far slower).

I'm using 1.50 version . It includes "Query Cache" ?

Can you please tell me where can I check if caching the SQL responses is to file or database ?

solo_400:

I'm using 1.50 version . It includes "Query Cache" ?
Version 1.5.1 does - not 1.5.0.

solo_400:

Can you please tell me where can I check if caching the SQL responses is to file or database ?
By default I believe no SQL caching is enabled. It is usually not needed (and only a "small" boost unless you make other modifications to Zen Cart). See: configure.php file contents explained

I changed to database but no improvements .

//define('SQL_CACHE_METHOD', 'file');
define('SQL_CACHE_METHOD', 'database');

Just for my curiosity , you are running your stores on VPS or dedicated severs ? ( i can't afford such solution )

The servers I mentioned earlier are "shared" servers, NOT dedicated or VPS. Such as the hosting companies found by clicking the Services link at the top of this page.

solo_400:

I changed to database but no improvements .
Aye, what did your hosting provider say about the performance issues?

solo_400:

Just for my curiosity , you are running your stores on VPS or dedicated severs?
Most of the companies I have worked with were on shared hosting - with a parse time at or under .3 seconds and a download time for the generated HTML under .5 seconds. So fairly fast even on shared hosting.

Only one ever "needed" SQL Caching, and after calling and talking to one of the sysadmins for the hosting provider, they relaxed a SQL "rate limit" they imposed by default. Yes, a company which actually let me talk to a sysadmin... They also fixed the issues in less than 30 minutes from the start of my phone call... Quite a pleasant surprise.

Basic rule, ask the hosting provider to tell you why it is so slow... and if they cannot tell you or work with you to resolve the issues... move elsewhere.

Provider keep telling me the website is running in normal parameters and also that they can't see any problems . I had to record my desktop to prove it :(

After midnight and first morning hours is running fust ( as few weeks ago ) . Right now all pages loading in max 0.5 second . I bet tomorrow afternoon i'll have same problems !

solo_400:

Why pages like product details are not slow ? ( Parse Time: 2.382 - Number of Queries: 453 - Query Time: 1.81040376318 )
Why querying tables ( using phpmyadmin ) is not slow ?

"Slow" is a relative term. Having said that, 1.8seconds for 453 queries is far from being a speed demon. Although there is no hard and fast rule about this (there are many factors involved), I'd be expecting to see at least 5-10times as many queries in this period of time.

Almost certainly a DB problem. Do as DrByte suggests, run a 'repair' on your database. If that doesn't solve the problem you'll need to take it up with your host.

Cheers
Rod

solo_400:

I changed to database but no improvements .

//define('SQL_CACHE_METHOD', 'file');
define('SQL_CACHE_METHOD', 'database');

Just a FYI, if you are having a slow database problem, then making the change to use the DB cache rather than the file cache is quite likely to make things worse.. it's asking the overloaded database to do even more work.

If the database is performing correctly then this is the preferred method, is it a lot more efficient than a file based cache.

Cheers
Rod

I need a little help from you guys ..

Provider keep telling me that pages loads instantly . Can I ask you to navigate between categories and report here parse time / query time that you have ?
( Provider will see this )

Right now, you site is a dog ... :eek:

First hit to:
/ducashop/placate-cu-aur-18k/inele-placate-aur

Parse Time: 17.087 - Number of Queries: 2389 - Query Time: 16.3729788557

another Category:
/ducashop/placate-cu-aur-18k/bratari-placate-aur
Parse Time: 17.569 - Number of Queries: 2357 - Query Time: 17.0842838471

First hit to:
/ducashop/parfumuri/de-dama

Parse Time: 17.290 - Number of Queries: 2365 - Query Time: 16.6187695883

Second hit to:
/ducashop/parfumuri/de-dama
Parse Time: 3.350 - Number of Queries: 2366 - Query Time: 2.84564178322

Third hit (with a 60-120 second delay) to:
/ducashop/parfumuri/de-dama

Parse Time: 13.788 - Number of Queries: 2365 - Query Time: 13.4375248876

This strikes me as a combination of a slow server and the mysql has not been caches ...

If you look in your Zen Cart Admin, what do you see in the Tools ... Server Info ... in the top portion of the information from the line that reads:
SERVER INFORMATION

down to the line that reads the:
PHP VERSION

Thank you Linda and others . I think problem was solved for good , I hope . Now all pages run in less than 0.5 sec.

solo_400:

Thank you Linda and others . I think problem was solved for good , I hope . Now all pages run in less than 0.5 sec.And for others reading this thread. The solution was......?

As DrByte said "Bingo. There's the problem: something's wrong with the server"
They will never tell me the nature of the problem.