[ic] Interchange using lots of cpu...

interchange-users@icdevgroup.org interchange-users@icdevgroup.org
Wed Dec 18 08:23:00 2002


> 
> I've recently used interchange to whip up a demo accessing mysql data, but   
> have noticed that it seems to consume lots of cpu for a long time filling out
> templates.  Admittedly we've brute-forced the demo; it's using
> a couple of nested [query] type statements with some [if]s inside to generate
> the output list.  But it can take 90 seconds of spinning to generate
> a few hundred lines of results.
> 
> We're using interchange 4.8.6 on a redhat 7.3 system.  It's a 1ghz
> processor with 512mb of ram.  Running top shows that's it's really interchange
> sitting there using the cpu.  It's not idle, using lots of memory or using
> the disk.  I've looked at the 'list optimization' page on the icdev site
> which was quite interesting, but didn't really solve this problem.  Mysql
> can find the raw data in fractions of a second.
> 
> Is there some tuning parameter that I've missed?  Any perl-related installation
> issues?  I can't believe it's this slow normally but can't find anything
> obvious.
> 

I've investigated further and, from what I can see, there's some rather
nasty slow-downs somewhere when an sql query using [query] returns
lots of results.

For testing, I've got a single [query ...type=list] tag; inside I dump
out the content line by line using a few [if] tags, a few of which are
type=explicit with simple substring tests, and a few very simple
perl things used mainly to extract substrings.  These perl routines
have been set up with [prefix-sub] and used with [prefix-exec] as mentioned
in the optimization notes I've read.

If there are a handful of results, each loop takes between 0 and 0.01
seconds.  Pretty good!  If there are 100 results, each loop *starts off*
taking 0.05 seconds and then, towards the end, starts taking between 0 and
0.01 seconds again.  Hmmm, a sign of things to come.  If there are several
hundred results, resulting in several hundred lines of output (perfectly
viewable with a scroll bar) things run like a pig.  For a long time
during list processing each time round the loop can take say 0.5
seconds!  Couple this long loop time with many iterations and it's no
wonder that things take so long.  Remember that it's interchange that's
the busy culprit here, according to 'top'.

This is rather nasty behaviour.  I was initially expecting that the time
through this loop should remain unchanged, independant of how many
articles were found but it's like something's being copied that's
initially big and gradually gets smaller, resulting in faster
copies towards the end.  What gives?  Here's a copy of the loop I'm using.
No comments on its beauty please, as it's been badly hacked about
during this testing.  What should I be avoiding or doing differently
to get rid of this very nasty slow-down behavior where initial loops
take significantly longer than final loops?

- gordon


[query
    sql="select code, col0, col4, col5, col6, col7, col8, col9, col10, col16 from articles where grp=1191 and (col0 = 6 or col0 = 7)"
    type=list
    prefix=ARTICLE
    list_prefix=ARTICLES
    ]

[tmp name=shownimage][/tmp]
[tmp name=h6][/tmp]
[tmp name=h7][/tmp]
[ARTICLES]
    [ARTICLE-sub getcode]
	my $code=shift;
	return substr($code, 0, 3) . substr($code, -3)
    [/ARTICLE-sub]
    [ARTICLE-sub getimagename]
	my $name=shift;
	return substr($name, 0, 3) . "/" . substr($name, 3, 3) . ".jpg"
    [/ARTICLE-sub]
[benchmark start=1]
    [tmp name=internalcode interpolate=1][ARTICLE-exec getcode][ARTICLE-param col5][/ARTICLE-exec][/tmp]
    [tmp interpolate=1 tcol0][ARTICLE-param col0][/tmp]
    [tmp interpolate=1 tcol7][ARTICLE-param col7][/tmp]
    <tr>

    [if scratch tcol7]
	[if scratch tcol0 eq 6]
	    [if scratch shownimage]
	    [then]
	    [/then]
	    [else]
		<td rowspan="999" valign="top">
		[tmp name=img]__IMAGEBASE__/[ARTICLE-exec getimagename][ARTICLE-param col7][/ARTICLE-exec][/tmp]
		<img src="[scratch img]" alt="[scratch img]">
		</td>
		[tmp name=shownimage]1[/tmp]
	    [/else]
	    [/if]
	[/if]
    [/if]
    [if scratch col0 eq 6]
	<!-- description. -->
	[set name=h6][/set]
	[set name=h7][/set]
	<td colspan="3">[ARTICLE-param col6]</td>
    [else]
	<!-- article code. -->
	<td>[scratch internalcode] [ARTICLE-param col9]</td>
	    <td>[ARTICLE-param col6]</td>
	    <td>[ARTICLE-param col7]</td>
	<td>[ARTICLE-param col10]</td>
	<td>[ARTICLE-param col16]EUR</td>
	<td></td>
    [/else]
    [/if]
    </tr>

[benchmark verbose=1]<br>
[/ARTICLES]
[/query]