WSN Classifieds

Archiving Threads

Tue, 06 May 2014 15:54:28 GMT

On sites with over half a million comment posts (normally WSN Forum discussion forums, but potentially the other scripts as well), the process of rebuilding the table index after someone posts will make the post take a long time to submit, will spike the server load and will consequently slow down the whole site for a while. If you're seeing the load go up when people post while your server performs fine when nobody's posting, this may be your issue.

The solution to this issue is archiving inactive comment threads. Inactive threads will be locked so that nobody can reply to them, and their data will be moved to another database table in order to allow the regular comments table to become small enough to do fast insertions.

To begin archiving inactive threads, go to Admin Panel -> Listings -> Comment Settings -> "Archiving Old Threads" (requires WSN 9.0.5 or later). Specify your desired minimum number of days of inactivity. Do not set this too low, as it will not be possible to de-archive a thread once it's archived. Our recommendation is 365 days.

Once you've set the number of days, the process of archiving will begin automatically. If you need to speed it up, visit yoursite/index.php?action=archive and let that run to completion.

Asynchronous Javascript

Fri, 14 Mar 2014 04:43:17 GMT

On a modern web page, JavaScript can be the biggest speed bottleneck. One solution to this is to load javascript asynchronously -- that is, let the html page render first and then load all the needed javascripts in parallel in the background and finally let them run once they've all loaded. WSN's asynchronousjavascript tweak can help you with this.

When the asynchronousjavascript tweak is enabled at Admin Panel -> Settings -> Tweaks Editor, most of WSN's internal javascript calls will automatically be made asynchronously. The WYSIWYG editor and bulk uploader aren't compatible with asynchronous loading, so all loading is done synchronously on pages where those appear (the submit and edit pages, plus comment thread pages).

If you don't have any custom jquery-dependent javascript you've added to your site, you're done. If you do have any custom javascript which depends on jquery, you will need to modify how it's loaded so that it doesn't attempt to use jquery before jquery is loaded and thus break the page. You have presumably placed your javascripts in Admin Panel -> Themes -> Manage Templates -> Additional Javascript Inclusions. You'll need to remove them all from there, and instead load their URLs in the Additional Document Ready Function Content template like this:

jQl.loadjQdep('http://yoursite.com/yourjavascript.js');

Important Note: URLs from a different domain can only be loaded asynchronously if the access control origin headers on the remote domain allow the local domain to do that. In the event that the origin headers are non-permissive and you don't have the ability to change them, you'll need to copy that remote jquery-dependent javascript into a directory on your site.

Converting MyISAM to InnoDB

Sat, 05 Oct 2013 20:57:36 GMT

If your site's database has grown large -- hundreds of thousands of listings, categories or comments -- you may want to switch the database engine from MyISAM to InnoDB to improve performance. InnoDB is designed to handle large databases well.

There's just one big drawback: MySQL versions prior to 5.6.4 don't support fulltext indexes in InnoDB. This means you lose the ability to display similiar listings or similiar categories, or to perform fulltext searches. If you have a dedicated server you can update to MySQL 5.6, but if you're on a shared host it may be years before you get it.

If you'd like to use InnoDB with MySQL < 5.6.4, you'll need to remove fulltext indexes from the tables before conversion or the tables won't convert. First, on your WSN site go to Admin -> Settings -> Searches and make sure that your default search type is not full mysql fulltext -- if it is, choose another type. Then, go to Admin -> Settings -> Switches and make sure the "similiar categories" and "similiar listings" switches are turned off (you can't use these without fulltext).

Using phpmyadmin, go to the yourprefix_categories and yourprefix_links tables of your database. Click the "structure" tab, and near the bottom of the page it'll list the indexes on the table. Click the red x to remove any index which is labeled as a FULLTEXT index.

Now you're ready to convert to InnoDB. To do so, go to Admin Panel -> Miscellaneous -> Advanced on your WSN site. In the run custom PHP box on the right side, run this PHP code:

global $tables, $db; reset($tables); foreach ($tables as $t) $db->query("ALTER TABLE $t ENGINE = INNODB");

This should convert all your WSN tables, as long as their fulltext indexes have been removed first. You should now be experiencing better database performance, especially on insertions.

Server Load High / MySQL death

Tue, 21 Jun 2005 09:39:04 GMT

There are basically three types of sites with server load problems: extremely popular sites, sites where something is broken which is causing high loads, and sites which are being overrun by periodic bursts of traffic or uncontrolled spiders.

When your server load becomes high, pages become slow and a chain reaction is started where the server load keeps going higher as people keep trying to access the page while it's working, eventually resulting in Apache giving up and dying altogether until restarted. It can be useful to have an emergency method to halt this chain reaction before it reaches a critical stage, and that's one of the things this article is about.

Before continuing to read this article, please be sure that this is actually your problem. Enabling debug mode to show totals and execution time will allow you to view the server load on a Unix system (though not on a Windows server). Generally, server loads over 5 are a danger zone where there server is likely to suffer significantly and MySQL could cease responding. Anything over 3 is likely to cause noticeable slowing. If you can't check your server load, noticing page load times increase dramatically at times you have lots of guests online would be a clue. A "cannot connect to mysql through socket" sort of error message, when you're sure your config.php is correct and your host isn't doing maintenance, is also an indication.

Sometimes search engine spiders can have the same force as a DDOS attack, causing MySQL to slow and finally stop responding. Check your robots.txt to be sure you have a good crawl-delay value (at least 30 seconds). Sometimes there are bots that won't obey, or other sorts of traffic floods that can kill MySQL on a large site. You can limit the possible threat of such activity at Admin -> Settings ->SEO by selecting to block spiders from non-essential pages (pages you really don't need indexed anyhow) or non-rewritten pages.

As a precaution in severe circumstances you may wish to lock out spiders or guests entirely when the server load is above a certain level. If you have a Unix(/Linux/BSD) server and PHP is not in safe mode you may do this by going to Admin -> Settings -> System and setting the level for rejecting spiders/guests where it asks. 5 could be a good level. The load should go down quickly as the cause of it is locked out.

Issues with Exceptionally Large Sites

Wed, 02 Jun 2004 21:47:30 GMT

Sites With Huge Amounts of Data

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:

<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>

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}>

 {TYPEDISPLAYNAME}: {TYPETOTAL}

</IF>

from that template (the live totals of each listing type become very slow with a large listings table).

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"]); }
}
?>