Hi Daniel, thanks for your performance tuning tip, it's appreciated! I'm running a SMW-Wiki that's growing quickly, and I fear that soon a runJobs session will take longer than the alotted maintenance interval. Having runJobs during operation slows down the system too much, so that's not an option.
Patrick. "Daniel Ring" <dfr...@gmail.com> wrote: >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 -- Sent from my Android phone with K-9. Please excuse my brevity. ------------------------------------------------------------------------------ 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
