Page 1 of 5 123 ... LastLast
Results 1 to 10 of 42
  1. #1
    Join Date
    May 2007
    Location
    SW Florida
    Posts
    1,051
    Plugin Contributions
    2

    red flag 5 sites suddenly running very slow

    I have 5 Zen-Cart 1.3.8 sites on one hosting account and they are all running VERY VERY slow. The ones in my tagline.

    Parse Time: 127.236 - Number of Queries: 804 - Query Time: 125.874090737

    They keep telling me there are just to many queries going on. funnily enough I have 6 other zen-cart sites with another host; running the exact same version and those sites are running fast as ever.

    Any idea's what's happening on the server to make it run so slow?

    Server Information

    Server Host: box606.bluehost.com (70.40.220.106) Database Host: localhost (127.0.0.1) Server OS: Linux 2.6.32-42.1.BHsmp Database: MySQL 5.1.58-community-log Server Date: 09/08/2011 10:24:07 Database Date: 09/08/2011 10:24:07 Server Up Time: 10:24:07 up 69 days, 10:46, 2 users, load average: 20.59, 19.17, 18.24 HTTP Server: Apache PHP Version: 5.2.17 (Zend: 2.2.0) PHP Memory Limit: 128M PHP Safe Mode: Off PHP File Uploads: On Max Size: 10M POST Max Size: 8M Database Data Size: 51,621 kB Database Index Size: 3,212 kB
    Newest Site: ChargerPros - Stuff4Toys

  2. #2
    Join Date
    May 2007
    Location
    SW Florida
    Posts
    1,051
    Plugin Contributions
    2

    Default Re: 5 sites suddenly running very slow

    Logged into phpMyAdmin and the querry times are better. Yup just logging in the query times dropped to between 60 - 40 seconds.
    Parse Time: 42.615 - Number of Queries: 792 - Query Time: 41.3521166272

    STILL WAY TOOOOOOO SLOW!

    Ran Optimize and Repair and no significant speed change.
    Newest Site: ChargerPros - Stuff4Toys

  3. #3
    Join Date
    May 2007
    Location
    SW Florida
    Posts
    1,051
    Plugin Contributions
    2

    Default Re: 5 sites suddenly running very slow

    This is coming from another host and the same version of Zen-Cart and MUCH larger database. This is what it should be.
    Parse Time: 0.566 - Number of Queries: 544 - Query Time: 0.305399394226
    Newest Site: ChargerPros - Stuff4Toys

  4. #4
    Join Date
    May 2007
    Location
    SW Florida
    Posts
    1,051
    Plugin Contributions
    2

    Default Re: 5 sites suddenly running very slow

    Back up to
    Parse Time: 148.660 - Number of Queries: 793 - Query Time: 147.772943237

    This is nuts!
    Newest Site: ChargerPros - Stuff4Toys

  5. #5
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: 5 sites suddenly running very slow

    When your query time is huge and close to the same as the overall parse time, then that means the bottleneck is in the MySQL server.
    Ref: https://www.zen-cart.com/tutorials/i...hp?article=105
    .

    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.

  6. #6
    Join Date
    May 2007
    Location
    SW Florida
    Posts
    1,051
    Plugin Contributions
    2

    Default Re: 5 sites suddenly running very slow

    Quote Originally Posted by DrByte View Post
    When your query time is huge and close to the same as the overall parse time, then that means the bottleneck is in the MySQL server.
    Ref: https://www.zen-cart.com/tutorials/i...hp?article=105
    I figured that, what do I tell the hosts techs to get them to believe it's not zen-carts fault. they keep blaming the scripts and toooo many queries.

    I don't have the right vocabulary to convince them it's the servers fault.

    Thanks for your reply
    JOhn ><>
    Newest Site: ChargerPros - Stuff4Toys

  7. #7
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: 5 sites suddenly running very slow

    Didn't you already say the same site works great on another server? That should be all they need to hear if they're really looking at the issue. WHEN did the problem suddenly start? Have you verified that all your ZC files have NOT been tampered with and thus couldn't suddenly be the cause?

    Next, you'll need to work with the hosting company to identify all the slow-performing queries. THEY already know that they can turn on slow-query-logging. Analyzing those logs is VERY time-consuming, and probably something they're not interested in doing, and since the logging probably can't be isolated to *your* accounts only, they probably can't just give you the logs since they'll contain other customers' info. But, if the problem is in your database data and you really want to figure out what exactly is causing it, those logs are the best tool.

    Next you can export your database 'structure only' and compare all the fields and indexes against the default ZC sql files to see what differences exist, and then reconcile whether each of those differences is legitimate for your unique store, etc.

    In short, if the problem "suddenly" started and nothing is corrupt in your database data, then the problem is on their end. Given that you're referring to multiple different databases, that points more broadly to a server-wide problem than just a tiny bit of corrupted data in one database.
    .

    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.

  8. #8
    Join Date
    May 2007
    Location
    SW Florida
    Posts
    1,051
    Plugin Contributions
    2

    Default Re: 5 sites suddenly running very slow

    Thanks for your input. I know about the slow query logs, they are HUGE and intimidating because on this hosting account there are many domains and a ton of info there.

    Got a call from the hosts Tech Support, database manager and he took the time to explain the issues and the solutions. To bad they cannot do it for me. They seem to believe that if I parse through the slow query logs, and determine which table and field is causing the issue and ad an index for that individual field, it will help the issue.

    He explained database fragmentation and recommended a more regular repair and optimize routine. He also pointed out a few tables that had excessive overhead, like the whos_online and sessions tables.

    ?? Question: Can these tables be purged from time to time??

    Now I start the process of parsing through the slow query logs and see if I can help by adding indexes.

    FYI: my primary domain gets 6,000 - 10,000 requests per day. This seems an issue for domains working on a shared hosting server that get some good activity.

    Another thing the tech did which seems to be a help temporarily is switch from PHP5 (Single php.ini) to PHP5 (FastCGI)
    Currently that domain went from 120 second query times to 1.5 to 12 seconds.

    ?? Question: can the repair and optimization of the database be performed remotely via browser instead of going to phpMyAdmin to do it?

    Thanks for all your help, it's off to slow query log land, wake me in two hours if I don't come back online.
    Newest Site: ChargerPros - Stuff4Toys

  9. #9
    Join Date
    May 2007
    Location
    SW Florida
    Posts
    1,051
    Plugin Contributions
    2

    red flag Re: 5 sites suddenly running very slow

    So I started the daunting task of reviewing the slow_query_log and found it littered with entries like this:

    Code:
    # Fri Sep  9 05:49:08 2011
    # Query_time: 1.300991  Lock_time: 0.000094 Rows_sent: 1  Rows_examined: 3
    use database_name;
    select banners_id, banners_title, banners_image, banners_html_text, banners_open_new_windows, banners_url
      from banners
      where status = 1  and ( banners_group = 'SideBox-Banners') order by rand()
    One for each banner group for every request made. So for one product view, there are 5 entries like this for the banners.

    1.3 seconds doesn't sound like a long time, but when I only get every 300th of a clock tick to process, it takes FOREVER.

    I get these for every domain on the server. Do I need to drop the banners table and recreate it? think this table is corrupt on each domain?
    Newest Site: ChargerPros - Stuff4Toys

  10. #10
    Join Date
    May 2007
    Location
    SW Florida
    Posts
    1,051
    Plugin Contributions
    2

    Default Re: 5 sites suddenly running very slow

    This is a 1.3.9h installation
    I checked the slow_query_log on another host that only gets a few hits a day and get the same kind of entry:

    # Fri Sep 9 02:18:05 2011
    # Query_time: 1.114327 Lock_time: 0.001940 Rows_sent: 0 Rows_examined: 0
    use anotherdb_name;
    select banners_id, banners_title, banners_image, banners_html_text, banners_open_new_windows, banners_url
    from banners
    where status = 1 and ( banners_group = 'Wide-Banners') order by rand()
    This is on a completely different server with the same host.
    Newest Site: ChargerPros - Stuff4Toys

 

 
Page 1 of 5 123 ... LastLast

Similar Threads

  1. v150 My site is running very slow - help please
    By supe in forum General Questions
    Replies: 8
    Last Post: 24 Dec 2012, 07:00 PM
  2. Site running very slow... Parse time
    By sfklaas in forum General Questions
    Replies: 13
    Last Post: 27 Jan 2011, 02:42 AM
  3. Cart running very slow after 1.3.8a upgrade
    By four-point in forum General Questions
    Replies: 19
    Last Post: 19 Apr 2009, 05:57 PM
  4. Database running very slow help!!
    By Liquiddi in forum Templates, Stylesheets, Page Layout
    Replies: 0
    Last Post: 24 Mar 2008, 08:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR