[ic] Interchange / MySQL lockups

[Aaron Berg]

> 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
>
> _______________________________________________
> interchange-users mailing list
> interchange-users at icdevgroup.org
> http://www.icdevgroup.org/mailman/listinfo/interchange-users
>

We hadn't been running the slow query log, but have turned it on now.
Thanks for the tip. The queries I'm suspicious of won't show up
though, as they are in the sleep state.  I haven't found a way to log
those yet.

It's only one instance of Interchange on the server that has this
problem.  The other instances with less catalogs are fine.  When the
main instance hangs the others are still accessible as are the
PHP/MySQL sites on that server.  So MySQL is still working, but
somehow that instance of Interchange can't talk to it.

Maybe all of that instances DB handles are busy with queries in the
sleep state?  We've bumped up the size of the MySQL query cache and
reduced the wait_timeout which seems to have helped reduced the number
of sleeping queries.  When we do get the sleeping queries they die
much faster.

Is there a way to change the number of DB handles available to an
instance or a way to limit the time interchange will wait for a
sleeping query?

The other part of this problem is the catalogs generating too many
queries due to the use of [item-field].  Is it possible to have
interchange record how many queries it does per page view?  If we
could track that it would be really helpful in tracking down which
catalogs put the most load on MySQL.