[ic] Too very slow pages on ic 5.10.0

[marco]

Il 12/03/2018 21:03, marco ha scritto:
> Il 12/03/2018 05:01, Mike Heins ha scritto:
>> Quoting Peter (peter at pajamian.dhs.org):
>>> On 11/03/18 01:44, marco wrote:
>>>> after 3 months of installation and light use my old production
>>>> catalogue
>>>> with:
>>>
>>>> I notice that for the pages with heavy query became very slow and
>>>> sometimes bring to "Gateway Timeout"
>>>> "top" utility display cpu to 100% during page wait sometimes for mysql
>>>> and sometimes for interchange
>>>
>>> You need to benchmark the code on the problematic pages and find out
>>> exactly which lines or blocks of code are causing the issue.
>>>
>>> This is most likely going to be the result of lots of DB hits from some
>>> very inefficient code.  Look for calls to [data] or [field], or similar
>>> tags that waste db hits inside of a [loop] or similar.  Rewrite your
>>> code so that it does a single query to the db to get all the data you
>>> need instead and it will end up being way more efficient.
>>
>> Don't discount the possibility that the I/O on this virtual instance
>> is slower than on the other one. Are there demanding applications which
>> show the same speed on the new container? Try doing some large DB queries
>> on both the old and the new and comparing.
>
> Same queries on the old and the new servers from mysql workbench. Timing
> is good namely not too long, only with join on not primary key becomes
> high but in both server.
>
> In the new server (debian 9, ic 5.10.0) mysql query is faster from 10%
> to 20% than old server (debian 6, ic 5.6.3) in the opposite way getting
> ic pages.
>
> I repeat myself: getting ic pages the new server is a little faster than
> the old (as mysql above query says) for the first queries/pages then
> pages becomes very slow only on the new server.

Final summary: two server
old) debian6, ic 5.6.3, mysql
new) debian9, ic 5.6.10, mariadb
Same db data, same catalogue that has run for more then 10 years with 
different os (debian, ubuntu), ic (more than 5 version), mysql versions.
With new) some pages slow down also 20 times.
With the help of David Christensen we found 2 indipendent problems:

1) ic 5.6.10 with utf8 enabled make an heavy regex processing.
Solution by David, old catalogue dont need utf8:
a) in /usr/local/interchange/bin/interchange perl script before "use 
strict":
$ENV{MINIVEND_DISABLE_UTF8}=1
b) in catalogue.cfg:
DatabaseDefault MYSQL_ENABLE_UTF8=0

2) the new) on heavy and multiple sql query, after less than 10 and more 
than 2 request of the same page with different query, mariadb suddenly 
has great slow down due sql processing.
We haven't find any problem on mariadb configuration and so not found 
any direct and complete solution. My quick/2 and dirty/2 solution was to 
run db in a separate vm server with debian6 and mysql. I dont know if 
with debian9, ic 5.10.0 and oracle mysql the problem persist.

-- 
"Fino alla bara sinpara"
"Up to demise we rize"