I install APC it makes some difference in speed. I turned on slow-message
logging for mySQL and that log file is filling up fast! Some examples:
# u...@host: reformedword[reformedword] @ localhost []
# Query_time: 9 Lock_time: 2 Rows_sent: 5 Rows_examined: 5
SELECT /* SMW::deleteSubject::Nary Aquatiki */ smw_id FROM `rw_smw_ids`
WHERE smw_title='' AND smw_namespace='712136' AND smw_iw=':smw';
# u...@host: reformedword[reformedword] @ localhost []
# Query_time: 46 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
DELETE /* SMW::deleteSubject::Atts2 Aquatiki */ FROM `rw_smw_atts2` WHERE
s_id = '230051';
# Time: 100213 3:02:55
# u...@host: reformedword[reformedword] @ localhost []
# Query_time: 55 Lock_time: 1 Rows_sent: 0 Rows_examined: 0
INSERT /* SMW::updateRel2Data 216.129.119.43 */ INTO `rw_smw_rels2`
(s_id,p_id,o_id) VALUES ('202900','4987','
202901'),('202900','5064','5065'),('202900','5463','202899'),('202900','135','289060'),('289060','225879','304'
),('202900','135','289061'),('289061','225879','118784'),('289061','225881','331'),('202900','135','289062'),('
289062','225879','119703'),('289062','225881','386'),('202900','135','289065'),('289065','225879','277'),('2029
00','135','289066'),('289066','225879','207525'),('289066','225881','385'),('202900','135','289067'),('289067',
'225879','119048'),('289067','225881','225124'),('202900','135','601368'),('601368','225879','584'),('601368','
225881','387'),('202900','1011','1417340'),('1417340','225879','1425'),('202900','1011','1417342'),('1417342','
225879','1446'),('1417342','225881','1174937'),('202900','1011','1663477'),('1663477','225879','1182228'),('166
3477','225881','209798'),('202900','1464','1663478'),('1663478','225879','926'),('1663478','225881','375867'),(
'202900','1464','1663479'),('1663479','225879','1490'),('202900','1464','1663480'),('1663480','225879','223216'
),('1663480','225881','687547'),('202900','1464','1663481'),('1663481','225879','845'),('202900','1464','222487
0'),('2224870','225879','1651'),('202900','6840','4980'),('202900','6906','5727'),('202900','223343','6503');
# Time: 100213 3:03:12
# u...@host: reformedword[reformedword] @ localhost []
# Query_time: 36 Lock_time: 19 Rows_sent: 1 Rows_examined: 1948
SELECT /* SMW::getQueryResult 216.129.119.43 */ COUNT(DISTINCT t0.smw_id)
AS count FROM `rw_smw_ids` AS t0 IN
NER JOIN `rw_smw_rels2` AS t2 ON t0.smw_id=t2.s_id INNER JOIN `rw_smw_rels2`
AS t5 ON t2.o_id=t5.s_id INNER JOI
N `rw_smw_inst2` AS t7 ON t2.s_id=t7.s_id WHERE t2.p_id='1464' AND
t5.p_id='225879' AND t5.o_id='177656' AND t
7.o_id='3304' LIMIT 10001;
# Time: 100213 3:03:30
Regarding PHP, I don't think that a memory limit of more than 50MB or
maximally 100MB can be recommended to any public site. What ever dies
beyond
this point cannot be saved. On the other hand, PHP Out of Memory issues are
hard to track since there cause is often not the function that adds the
final
byte that uses up all memory. You have seen this in your logs.
One general thing that should be done on larger sites (actually on all
sites!)
is bytecode caching, see [1]. This significantly reduces the impact that
large
PHP files as such have on your memory requirements.
Out of mem issues usually result in blank pages that can only be edited by
changing the URL manually to use the edit action. Finding these pages is
crucial to track down the problem. In the context of SMW, I have seen
memory
issues when inline queries return a long list of results each of which
contains a lot of values. This problem is worse when using templates for
formatting, but it occurs also with tables. I have tracked down this
problem
to MediaWiki in my tests: manually writing a page with the contents
produced
by the large inline query has also used up all memory, even without SMW
being
involved. If this is the case on your wiki, then my only advise is to
change
the SMW settings to restrict the size of query outputs so that pages cannot
become so large. If this is not the problem you have, then it is important
to
find out which pages cause the issues in your wiki. Note that problems that
are caused by MediaWiki jobs could also appear for random pages since they
are
not depending on the page contents.
Regarding MySQL, you should activate and check the slow query logging of
MySQL. It will create log files that show you which queries took
particularly
long. This can often be used to track down problematic queries and to do
something to prevent them.
If you experience general site overload in a burst-like fashion then it
might
be that some over-zealous crawler is visiting your site, possibly
triggering
complicated activities. Check your Apache logs to see if you have high
loads
for certain robots or suspicious user agents, especially on special pages
like
Ask. Update your robots.txt to disallow crawlers to browse all results of
an
inline query (crawlers have been observed to do this).
-- Markus