If you have thousands of categories/subcategories, the regular category selector on the submission/edit pages will fail because the full selector options can't be generated due to memory or time limitations. Switch on the level-based category selector option at Admin Panel -> Settings -> Switches -- the level-based selector will work with millions of categories. Then reduce the number of levels in the submit selector to 0 at Admin -> Categories -> Category Settings.
If you don't have any categories set to be invisible to specific usergroups, go to Admin Panel -> Settings -> Tweaks Editor and turn on the 'noinvisiblegroups' tweak. This will speed up many queries as it no longer has to check if the listing is in a visible forum for the viewing member.
If you have huge numbers of members, you may need to switch off the "keyword subscriptions" at Admin -> Settings -> Switches to reduce slow queries of the members table. You may also want to disable instant subscriptions there, as daily digests create far fewer emails (and thus far less server load) than instant subscriptions.
If you have hundreds of thousands of listings, the listing details page may become slow due to previous/next links. In WSN 9.0 or later, switch off the "Previous/Next links" switch at Admin -> Settings -> Switches... in earlier versions you'll need to remove this code from the listing details template instead:
<p class="center">
<IF {PREVIOUSEXISTS}><a href="{PREVIOUSURL}">{LANG_DETAILS_PREVIOUS}</a></IF>
<IF {PREVIOUSEXISTS} and {NEXTEXISTS}> | </IF>
<IF {NEXTEXISTS}><a href="{NEXTURL}">{LANG_DETAILS_NEXT}</a></IF>
</p>
Switching off the 'secondary categories' switch will speed things up on the details page by making the views update query faster, and will also speed the submit/edit pages. Retrievals may also become slow, and you can speed these up by switching off expirations and effective times.
With large numbers of listings in a directory, the effective time and expiration switches slow things down due to their need to get a live running total of listings that haven't expired or have become effective -- turn them off if you don't need them.
If the 'show listings in category' page is slow, try removing
<IF {SWITCH_TOPICTYPES}>
<!-- BEGIN TYPE TOTALS -->
<br ><span class="minilabels">{TYPEDISPLAYNAME}:</span> {TYPETOTAL}
<!-- END TYPE TOTALS -->
</IF>
from that template (the live totals of each listing type become very slow with a large listings table).<!-- BEGIN TYPE TOTALS -->
<br ><span class="minilabels">{TYPEDISPLAYNAME}:</span> {TYPETOTAL}
<!-- END TYPE TOTALS -->
</IF>
If you have millions of listings, insertions (the addition of a new link) may become impractically slow to the point of breakage. To fix this you need to switch off 'similar listings' and use phpmyadmin to delete the fulltext index from the _links table of the database. You may then also convert the table from MyISAM to InnoDB for a further performance boost.
To speed up search results, you can enable the 'nosearchsort' tweak at Admin -> Settings -> Tweaks Editor. If combined with setting mix listing types to 'yes' in the type manager, this will stop all sorting of search results, which typically improves speed very much, though decreases relevance (note it doesn't apply to distance searches).
You may also need to be careful about the member rating forumla and listing importance formula you use. The default formula is a simple one that won't be a problem, but if you've changed it to something server-intensive (or originally installed a very old version which had a more intensive formula):
1) Go to Admin Panel -> Members -> Settings and change the rating formula to something simple.
2) Go to Admin Panel -> Listings -> Listing Settings and change the importance formula to something simple.
If searches are running slow, go to Admin -> Settings -> Search and turn the "Faster Any/All Words Search" option on. Then selected fewer searchable fields, if possible. The mysql fulltext search type will be the fastest but only for titles and descriptions. Of the others, 'contains' should be fastest.
Sites With Heavy Traffic
If your load levels have grown excessively high due to your large amount of traffic, consider whether or not you can use the cache system for guests. The cache system will make repeated loads of the same page by different people drastically faster.
If pages take too long to load sites should ensure that they do not have an excessive number of toplists on a page. If server load is an issue you may also want to consider turning on caching for the guest usergroup (Admin -> Settings -> General), perhaps triggered by a server load over 3.
If excessive RAM is being used, turning off some switches (such as private messaging and instant messaging) helps. Upgrading to PHP 5.4 will also save memory.
Admin Panel -> Settings -> System Configuration also offers an emergency load control mechanism, and Admin Panel -> Settings -> SEO allows you to throw 404s to any rogue bots or denial-of-service attackers.
Hung Queries
If hung queries are causing high server load and snowballing poor performance on your server, you can place a file (let's name it mysqlkill.php) in WSN's modifications directory to terminate all processes which have been hung for more than 60 seconds:
<?php
if (rand(1, 10) == 5)
{ // this bit autoterminates mysql processes that have been hung for more than a minute, to keep the server responsive:
$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) { if ($row["Time"] > 60 ) mysql_query("KILL ".$row["Id"]); }
}
?>
if (rand(1, 10) == 5)
{ // this bit autoterminates mysql processes that have been hung for more than a minute, to keep the server responsive:
$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) { if ($row["Time"] > 60 ) mysql_query("KILL ".$row["Id"]); }
}
?>