Re: [Semediawiki-user] Slow page rendering

[Neill M. <ne...@nl...>]

  Hi Daniel.

This has made a massive difference to performance. Thanks very much for 
this. I wonder what other database optimizations can be made?

Cheers
Neill.

On 27/09/10 04:35, Daniel Ring wrote:
> Hi Christoph -
> I recently encountered the same symptoms, and posted to smw-devel
> about it.  I've linked it below, or you can find it by the subject
> "Slow/redundant refreshLinks queries".
>
> http://sourceforge.net/mailarchive/forum.php?thread_name=c6c6ccaa-5cab-4ac5-9dc1
> -667a95f17332%40email.android.com&forum_name=semediawiki-devel
>
> In short, adding a composite index on smw_rels2 allowed the queries
> to complete in about 10-20% of the previous times, which allowed
> each refreshLinks job to run about 70% faster (for my situation):
>
> ALTER TABLE smw_rels2 ADD INDEX idx_o_id_p_id USING BTREE (o_id, p_id);
>
> Sometimes MySQL didn't use this index until I removed the old one
> (which is redundant with the new index anyway).
>
> ALTER TABLE smw_rels2 DROP INDEX o_id;
>
> You'll have to remember these changes if you ever use SMW_setup.php
> to rebuild semantic data (as it drops and recreates the tables).
>
>
> Excerpts from Christoph Burgmer's message of Sun Sep 26 15:28:20 -0400 2010:
>> For the SMW installation on http://characterdb.cjklib.org I see very slow page
>> rendering times. There are 145,791 pages, most of them single pages with a
>> semantically enriched template.
>>
>> A typical runJobs.php looks like:
>>
>> 2010-09-26 19:13:42 refreshLinks 𠝪/0 t=3769 good
>> 2010-09-26 19:13:45 refreshLinks 𠝫/0 t=3538 good
>> 2010-09-26 19:13:49 refreshLinks 𠝬/0 t=3560 good
>>
>> with a single operation taking on average 5 seconds.
>>
>> Profiling especially yields SQL queries to take up most of the processing time
>> (60.79%), with the major one (44.36%) being
>>
>> query: SELECT DISTINCT smw_title,smw_sortkey FROM `smw_ids` INNER JOIN
>> `smw_relsN` AS tN ON tN.p_id=smw_id WHERE tN.o_id='X'
>>
>> This is probably used in getInProperties() which takes up 47.04% of processing
>> time.
>>
>> I am using MyISAM on MySQL. Looking into the right table of the above JOIN
>> gives me 1 million entries:
>>
>> mysql>  select count(*) from smw_rels2;
>> +----------+
>> | count(*) |
>> +----------+
>> |  1158754 |
>> +----------+
>>
>> All columns are given as KEY, i.e. are indexed.
>>
>> I know my db server to not be the fastest, but these numbers make me doubt
>> that a better (and more expensive) server would be able to quickly handle
>> 80,000 "refreshLinks" operations generated by changing one of the wiki's
>> template.
>>
>> What possibilities do I have from here?
>> Thanks for any help
>> -Christoph
>>
> ------------------------------------------------------------------------------
> Start uncovering the many advantages of virtual appliances
> and start using them to simplify application deployment and
> accelerate your shift to cloud computing.
> http://p.sf.net/sfu/novell-sfdev2dev
> _______________________________________________
> Semediawiki-user mailing list
> Sem...@li...
> https://lists.sourceforge.net/lists/listinfo/semediawiki-user