[ic] How to determine cause of load spikes.

[DB]

> DB <DB at m-and-d.com> wrote:
>> I'm running IC 5.4.1 using a mysql database of about 500,000 items on a
>> dual Xeon box with 4GB of RAM. Once in awhile I'll notice the site
>> become sluggish. During these periods the cpu load is always fairly
>> high.  I suspect these events are caused by an inefficient search on the
>> large products database. See below for an example 'top' output during a
>> recent event:
>> 
>> top - 11:26:03 up 31 days, 20:50,  1 user,  load average: 1.13, 0.97, 0.67
>> Tasks: 227 total,   2 running, 223 sleeping,   0 stopped,   2 zombie
>> Cpu(s): 25.1% us,  0.1% sy,  0.0% ni, 74.9% id,  0.0% wa,  0.0% hi,  0.0% si
>> Mem:   4040624k total,  3568748k used,   471876k free,   118736k buffers
>> Swap:  2031608k total,     8664k used,  2022944k free,  1453916k cached
>> 
>>   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
>> 26248 inter     25   0 1367m 1.2g 1880 R  100 32.4   0:47.43 interchange
>> 
>> So I can identify the offending process ID, but how can I determine what
>> this process is doing to cause such a load? If I can determine what
>> search is being run or which of my pages is being accessed then I can
>> probably correct the problem.
>> 
>> Bumping the RAM up to 4GB has drastically reduced the extent of the
>> problem, but I want to find and correct the real cause of the trouble.
>> 
> I imagine, from the memory usage, that the cause might be an inefficiently
> crafted Interchange search/scan operation.  The search/scan might be
> forcing all, or a large chunk, of your products table into memory.  Check
> the indexes on your table(s), check the search/scan itself and consider
> using SQL for the search - if you're not already doing so.

Hi - I suspect that you're correct, but my problem is locating which
page(s) that these poorly-crafted searches are coming from. I have
enabled the slow search log and hopefully that will provide clues.

In the mean time I've realized that indexing more fields of my products
table may help alot. My 'description' field is type TEXT however - can
TEXT columns be indexed?

Thanks to all so far for the help.

DB