Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 31
  1. #21
    Join Date
    Nov 2004
    Location
    Norfolk, United Kingdom
    Posts
    3,036
    Plugin Contributions
    2

    Default Re: Large Slow site on VPS

    Max Number of Concurrent MySQL Queries
    should have read:

    Max Number of Concurrent MySQL Connections

    Vger

  2. #22

    Default Re: Large Slow site on VPS

    I bumped the connections from 250 to 750.

    pconnect = false and I am not logging the parse times (but I am displaying them)
    Of course I went mad with power. Have you ever tried going mad without power? It's boring. No one listens to you.

  3. #23

    Default Re: Large Slow site on VPS

    Apparently log slow queries was turned off with the last upgrade. I think I turned it back on (not super confident). Here is an example from the last log that we had. All the slow queries are similar.

    # User@Host: ***[***] @ localhost []
    # Query_time: 68 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
    select p.products_id, p.products_image
    from orders_products opa, orders_products opb, orders o, products p, hide_categories hc
    where opa.products_id = '75170'
    and opa.orders_id = opb.orders_id
    and opb.products_id != '75170'
    and opb.products_id = p.products_id
    and opb.orders_id = o.orders_id
    and p.products_status = 1
    and p.master_categories_id = hc.categories_id
    and hc.visibility_status = 0
    group by p.products_id
    order by o.date_purchased desc
    limit 6;

    This leads the host to think that Zen is the problem but I wonder if you are right and it is the news site. That is around when the problem started to arise but as I had said, I didn't think it would cause problems since it was shared before. Perhaps that is the next step, to try and move them to a different server.
    Of course I went mad with power. Have you ever tried going mad without power? It's boring. No one listens to you.

  4. #24
    Join Date
    Apr 2007
    Location
    Herts. UK
    Posts
    890
    Plugin Contributions
    4

    Default Re: Large Slow site on VPS

    Quote Originally Posted by monkeytown View Post
    # User@Host: ***[***] @ localhost []
    # Query_time: 68 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
    select p.products_id, p.products_image
    from orders_products opa, orders_products opb, orders o, products p, hide_categories hc
    where opa.products_id = '75170'
    and opa.orders_id = opb.orders_id
    and opb.products_id != '75170'
    and opb.products_id = p.products_id
    and opb.orders_id = o.orders_id
    and p.products_status = 1
    and p.master_categories_id = hc.categories_id
    and hc.visibility_status = 0
    group by p.products_id
    order by o.date_purchased desc
    limit 6;.
    I believe that particular SQL query is for the 'Also Purchased Products' on the product info page. It was slow in ZC v1.3.8a and sped up in v1.3.9 by the addition of an index. You've switched that option off for the moment so it shouldn't appear in your slow query log any more.

    At the moment the only thing to do is to wait and see what happens and see if any more queries appear in the slow query log.

    It is hard to tell if it is the other site causing the problem or not. You may never know unless you move it (if only temporarily) to another server.

    Regards,
    Christian.

  5. #25
    Join Date
    Nov 2004
    Location
    Norfolk, United Kingdom
    Posts
    3,036
    Plugin Contributions
    2

    Default Re: Large Slow site on VPS

    If you are displaying Page Parse Times then they have to be monitored (generating sql queries), so turn that off.

    If you are logging all slow sql queries on this VPS system then it will cause a signifiant drain on resources, so try turning that off too.

    Sometimes too much information can be a bad thing.

    Vger

  6. #26
    Join Date
    Apr 2007
    Location
    Herts. UK
    Posts
    890
    Plugin Contributions
    4

    Default Re: Large Slow site on VPS

    Quote Originally Posted by Vger View Post
    If you are displaying Page Parse Times then they have to be monitored (generating sql queries), so turn that off.
    The page parse time and number of SQL queries are recorded whether you display it or not. Displaying the information places no extra load on the server and generates no extra SQL queries. Right now it is useful information to have.

    Quote Originally Posted by Vger View Post
    If you are logging all slow sql queries on this VPS system then it will cause a signifiant drain on resources, so try turning that off too.
    Enabling the slow query log will not place any extra load on the MySQL server. Again, right now it is useful information to have to actually fix the problem.

    Regards,
    Christian.

  7. #27
    Join Date
    Nov 2004
    Location
    Norfolk, United Kingdom
    Posts
    3,036
    Plugin Contributions
    2

    Default Re: Large Slow site on VPS

    I guess we have to disagree, at least on the second point.

    Some time ago we had a sudden load problem on one of our servers and enabled MySQL logging (admittedly for all queries) so that we could track down the site responsible for the sudden increase in load. We tracked down the site and got it to move, but the load did not markedly decrease until we stopped logging all MySQL queries - when it dropped back to normal levels.

    So I still recommend turning off those queries for now, just to see if it makes a difference to the load. You'll soon know if it does or doesn't and if it doesn't they can be reinstated easily.

    Vger

  8. #28
    Join Date
    Apr 2007
    Location
    Herts. UK
    Posts
    890
    Plugin Contributions
    4

    Default Re: Large Slow site on VPS

    Quote Originally Posted by Vger View Post
    Some time ago we had a sudden load problem on one of our servers and enabled MySQL logging (admittedly for all queries)
    Logging all SQL queries would put a heavy load on the server but that is a lot different from using the slow query log which is usually setup to just log any query which takes more than 10 seconds to execute.

    Regards,
    Christian.

  9. #29

    Default Re: Large Slow site on VPS

    The problem existed prior to enabling the mysql logging (slow queries) and parse times. I enabled them to attempt to figure out the problem so I don't think they will help much in fixing the problem regardless of if they are affecting the load on the server or not.
    Of course I went mad with power. Have you ever tried going mad without power? It's boring. No one listens to you.

  10. #30
    Join Date
    Nov 2004
    Location
    Norfolk, United Kingdom
    Posts
    3,036
    Plugin Contributions
    2

    Default Re: Large Slow site on VPS

    In which case the other advice, to move the news website - given that the problem only began when you added that site - would seem to be your best option to resolve the problem.

    Vger

 

 
Page 3 of 4 FirstFirst 1234 LastLast

Similar Threads

  1. Replies: 1
    Last Post: 4 Sep 2012, 07:36 PM
  2. v139h slow store due to large amount of nested Categories
    By ballyc27 in forum General Questions
    Replies: 21
    Last Post: 3 Jun 2012, 10:43 PM
  3. Category section load slow after moving from shared to VPS?
    By tripflex in forum General Questions
    Replies: 14
    Last Post: 4 Jun 2011, 04:14 AM

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