From: Christoph B. <cbu...@ir...> - 2010-09-27 20:14:31
|
Hi Daniel, thanks a bunch. I changed the index the way you described and got the operations down to 30% of the original time. This will make the data way more manageable. -Christoph Am Montag, 27. September 2010 schrieb Daniel Ring: > 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 |