[ic] How to determine cause of load spikes.
Bill Carr
bill at worldwideimpact.com
Tue Nov 7 16:09:56 EST 2006
On Nov 7, 2006, at 2:15 PM, DB 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.
>>>
>>> DB
>>
>> If you suspect a slow query then try turning on the mysql slow query
>> log. Let it run for a few days then optimize your most frequent and
>> slowest queries.
>>
>> Bill Carr
>> Bottlenose - Wine & Spirits eBusiness Specialists
>> (877) 857-6700
>
> Ok - I now have 3 entries in my slow query log:
>
> Time Id Command Argument
> # Time: 061106 14:03:47
> # User at Host: interch[interch] @ localhost []
> # Query_time: 15 Lock_time: 0 Rows_sent: 530898 Rows_examined:
> 530898
> use MDS_data;
> select * from products;
> # Time: 061106 16:04:19
> # User at Host: interch[interch] @ localhost []
> # Query_time: 11 Lock_time: 0 Rows_sent: 530898 Rows_examined:
> 530898
> select * from products;
> # Time: 061106 16:04:21
> # User at Host: interch[interch] @ localhost []
> # Query_time: 12 Lock_time: 0 Rows_sent: 530898 Rows_examined:
> 530898
> select * from products;
>
>
> There are indeed entries in my httpd access log at these times, but
> the
> server is fairly busy so there are many entries close together in
> time.
> I'm not certain if I should be looking at the exact times shown in the
> slow query log, or a few seconds on either side.
>
> Can anyone suggest a better way for me to identify exactly which page
> was accessed causing the "select * from products;" query to be run?
Is this on your production server? If so three slow queries would not
be a concern over a several day period.
Not sure how to answer your question. It would be fun to have a
"ictop" command ala mytop, apachetop, top, etc to take a peek at what
is going on with Interchange but I would have no idea how to make it.
Bill Carr
Bottlenose - Wine & Spirits eBusiness Specialists
More information about the interchange-users
mailing list