From: Neill M. <ne...@nl...> - 2010-09-27 08:48:30
|
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 |