[ic] Interchange / MySQL lockups

[Bill Carr]

On Jan 23, 2007, at 2:50 PM, Aaron Berg wrote:

> Hi all,
>
> I've been encountering occasional lockups with an interchange
> instance. Possibly a MySQL issue, possibly an Interchange issue.
>
> The details:
>
> Interchange 5.4.0
> About 30 catalogs - the busiest gets about 15,000 unique visitors  
> each month.
> Database is MySQL 4.1.9
> RedHat Enterprise
> Perl 5.8.7 - non threaded
>
> What we've observed is that some queries will just never finish.  In
> mytop the query will show up for several minutes (with a status of
> sleep) before it is killed. Restarting MySQL and / or interchange will
> clear the list.
>
> Interchange is running RPC mode with the following settings:
> PreFork             Yes
> StartServers        5
> MaxServers          0
> MaxRequestsPerChild 100
> HouseKeeping        2
> PIDcheck            120
>
> The feeling I have from my troubleshooting too far is that we are
> occasionally getting too many locked up MySQL queries at one time
> which causes a huge backlog of queries waiting to be processed which
> causes more hung queries.  Restarting MySQL or Interchange clears this
> and the system works as normal.
>
> Has anyone seen behavior similar to this?  Any pointers on where I
> should look to narrow this down?
Is it always the same query that goes slow? Try to optimize that  
query. Also run the MySQL slow query log and optimize your slowest  
queries.

If that doesn't work I would suggest trying to recreate the problem  
without having Interchange involved.

I am running MySQL-5.0.32. It has been sometime since I have run  
4.xxx so I hope what I am going to say applies. Use the binary query  
log on your MySQL server. When you get the hangup replay the binary  
log on another MySQL host. See if you can recreate the lock-up just  
by running those queries from the binary log. If you can then you can  
eliminate Interchange from the problem and on focus on MySQL and your  
queries.

I have a similar problem but I think it is different enough to go in  
another thread.


Bill Carr
Bottlenose - Wine & Spirits eBusiness Specialists
(877) 857-6700
http://www.bottlenose-wine.com
  Download vCard