In case anyone is following this: I found MySQL didn't always use the new index, even when it improved performance (at least in the cases I checked), but this was resolved by removing the previous index (which was redundant at this point, anyway):
ALTER TABLE smw_rels2 DROP INDEX o_id; Excerpts from Daniel Ring's message of Fri Sep 24 16:31:30 -0400 2010: > I've made a bit of progress, I think: > > Adding this composite index to smw_rels2 appears to decrease the > time of the 'SELECT DISTINCT...' query to about 10% of original, > and decreases the time of the 'runJobs' jobs to about 20-25%. > > ALTER TABLE smw_rels2 ADD INDEX idx_o_id_p_id USING BTREE (o_id, p_id); > > I assume this could replace the existing index on o_id, but I have > not actually tried it. I also don't know what compatibility issues > arise with composite indexes. > > Excerpts from Daniel Ring's message of Tue Sep 21 13:38:59 -0400 2010: > > I'm having an issue with seemingly slow and redundant queries during > > runJobs. I'm using MW 1.15.5 and SMW 1.5.2, upgraded from 1.15.1 and > > 1.5.1 respectively. (The upgrade didn't noticably change the speed > > or redundancy). > > > > There are a few templates which are used by many pages, the worst > > case so far being ~120k uses of a single template which turns the > > ~15 template parameters into ~10 properties, a category, and a page. > > It uses #if and uses other templates, (wrapping the other template > > calls in 'noinclude' doesn't appear to affect the runtime) and is > > basically unremarkable. > > > > So, running 'runJobs' gives output like the following: > > > > With --procs=1 > > > > > > 2010-09-21 16:14:17 refreshLinks2 Template:TX_Facility start=361172 > > end=362192 t=2407915 good > > 2010-09-21 16:53:19 refreshLinks2 Template:TX_Facility start=362193 > > end=363233 t=2341932 good > > > > (E.g. ~40min per refreshLinks2 with start-end range ~1000) > > > > With --procs=? (I don't remember, but with --procs=1, these were > > t=~3500 and increase proportionally with procs, so I think the DB > > is the bottleneck.) > > > > > > 2010-09-18 14:00:07 refreshLinks 42-009-34059_(TX_Facility) t=22856 good > > 2010-09-18 14:00:35 refreshLinks 42-001-31705_(TX_Facility) t=28058 good > > 2010-09-18 14:01:04 refreshLinks 42-003-01936_(TX_Facility) t=28900 good > > > > > > Watching the SQL log, I see it pause at this query: > > SELECT /* SMW::getInProperties 127.0.0.1 */ DISTINCT > > smw_title,smw_sortkey > > FROM `smw_ids` INNER JOIN `smw_rels2` AS t1 ON t1.p_id=smw_id WHERE > > t1.o_id='42720' > > > > > > And in the MySQL shell I get: > > > > > > mysql> SELECT DISTINCT smw_title,smw_sortkey FROM `smw_ids` INNER JOIN > > `smw_rels2` AS t1 ON t1.p_id=smw_id WHERE t1.o_id='42720'; > > +--------------+--------------+ > > | smw_title | smw_sortkey | > > +--------------+--------------+ > > | Well_number | Well number | > > | State_number | State number | > > +--------------+--------------+ > > 2 rows in set (5.98 sec) > > > > mysql> SELECT COUNT(*) FROM `smw_ids` INNER JOIN `smw_rels2` AS t1 ON > > t1.p_id=smw_id WHERE t1.o_id='42720'; > > +----------+ > > | COUNT(*) | > > +----------+ > > | 123037 | > > +----------+ > > 1 row in set (5.56 sec) > > > > > > The redundancy is that the SELECT DISTINCT output is the same > > every time. MySQL query cache is on, and Memcached is used, but > > neither made a noticable difference. (I assume some other query > > during runJobs invalidates it...) > > > > So, is this a bug or am I doing something totally wrong? > > > > Thanks, > > Dan ------------------------------------------------------------------------------ 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-devel mailing list Semediawiki-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/semediawiki-devel
