[EP-tech] Managing Subject tree from web : very slow

[Jean-Marie Le Bechec]

Hi,

have you find a solution to improve the loading time of the web admin 
interface for create subjects ?  And yes, presenting the subjects tree 
page seems to be very very CPU-hungry on mysql (see below).

I use EPrints 3.2.4 (Victoria Sponge) on VMware

free -m :
              total       used       free     shared    buffers     cached
Mem:  3948       3923         25          0                   84       2207
-/+ buffers/cache:       1630       2318
Swap:         5951        145       5806

I see (example), mysqladmin processlist --verbose:

| 355 | oatao | localhost | oatao | Query   | 0    | Sending data | 
SELECT `eprint`.`eprintid` FROM `eprint`, (SELECT `eprint`.`eprintid` AS 
`eprintid` FROM `eprint`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM 
`eprint`, `eprint_instit` AS `eprint_instit`, `subject_ancestors` AS 
`subject_ancestors` WHERE `eprint`.`eprintid`=`eprint_instit`.`eprintid` 
AND `eprint_instit`.`instit`=`subject_ancestors`.`subjectid` AND 
(`subject_ancestors`.`ancestors` = 'ENSCCF_FR') UNION SELECT 
`eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint_divisions` AS 
`eprint_divisions`, `subject_ancestors` AS `subject_ancestors` WHERE 
`eprint`.`eprintid`=`eprint_divisions`.`eprintid` AND 
`eprint_divisions`.`divisions`=`subject_ancestors`.`subjectid` AND 
(`subject_ancestors`.`ancestors` = 'ENSCCF_FR') UNION SELECT 
`eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint_subjects` AS 
`eprint_subjects`, `subject_ancestors` AS `subject_ancestors` WHERE 
`eprint`.`eprintid`=`eprint_subjects`.`eprintid` AND 
`eprint_subjects`.`subjects`=`subject_ancestors`.`subjectid` AND 
(`subject_ancestors`.`ancestors` = 'ENSCCF_FR')) AS `or_47170576395216` 
WHERE `eprint`.`eprintid`=`or_47170576395216`.`eprintid`) AS 
`and_47170580199520_0` WHERE 
`eprint`.`eprintid`=`and_47170580199520_0`.`eprintid` AND 
`eprint`.`eprint_status` = 'archive' GROUP BY `eprint`.`eprintid` |

and top (when I load the web admin subject page):

Tasks: 128 total,   1 running, 127 sleeping,   0 stopped,   0 zombie
Cpu(s): 45.8%us,  9.0%sy,  0.0%ni, 44.4%id,  0.7%wa,  0.2%hi,  0.0%si,  
0.0%st
Mem:   4043732k total,  4017324k used,    26408k free,    87124k buffers
Swap:  6094840k total,   148544k used,  5946296k free,  2257256k cached

   PID     USER      PR  NI  VIRT   RES     SHR  S   %CPU   %MEM    
TIME+        COMMAND
  3790    mysql     15   0    378m  82m     3232 S * 101.5*    
2.1           307:53.77   mysqld
28982   apache  16    0   468m  138m   6132 S    6.3        
3.5           4:22.45       httpd
28983   apache  16    0   460m  129m   6112 S    2.3        
3.3           4:19.75       httpd
....

Thanks...

Jean-Marie

-- 

***********************************************
Jean Marie Le Bechec
Service Commun de la Documentation
Responsable ingenierie documentaire
&
Direction du Systeme d'Information
Referent Etudes

Institut National Polytechnique de Toulouse
6 allee Emile Monso - bp 34038 -
31029 Toulouse cedex 4
Tel : 05 34 32 31 16
Mail : lebechec at inp-toulouse.fr
***********************************************


Le 30/08/2011 11:03, Paolo Tealdi a écrit :
> Il 30/08/2011 10.06, Stefano Cecere ha scritto:
>
> Hi Stefano,
>
> i normally use the XML batch interface to import/export subjects (and
> the programs are quick enought) , but the web interface can be  easy and
> confortable (if not so slow), for example, to translate subjects in
> italian/english for multilanguage sites.
>
> Best regards,
> Paolo Tealdi
>
>    
>> Hi Paolo
>>
>> i managed it with XML import (see my http://files.eprints.org/553/ )
>>
>> ciao
>> s
>>
>>
>> On 30/ago/2011, at 09.28, Paolo Tealdi wrote:
>>
>>      
>>> Dear all,
>>> On our site (3.2.4) we are noticing that managing the subject tree from
>>> admin web is painfully slow, for all subjets that have  sons, both for
>>> subjects with and without records linked.
>>> Creating an btree index  on subject_parents.parents doesn't improve.
>>> Presenting the subjects tree page seems to be very CPU-hungry on mysql.
>>>
>>> Any hints ?
>>>        
>> ____    ___   __  _ _______________________
>>
>> Stefano Cecere
>> Multimedia Archive developer
>> Centro Studi Umanisti KRUR - Firenze
>> stefano.cecere at krur.com
>>
>>
>>
>>
>> *** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
>> *** Archive: http://www.eprints.org/tech.php/
>> *** EPrints community wiki: http://wiki.eprints.org/
>>
>>      
>
>    
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20120613/1d504c94/attachment.html