An issue of timing

For the Microsoft SQL Server version of the MediaWiki engine (at version 1.13 or so), everything was working except the Special:WantedPages and Special:AllPages. With the new design of Special:AllPages, I can't make heads or tails out of the SQL being generated, so I just reverted to the old behavior and it's fine. Special:WantedPages was different. The SQL was fine, but as the size of the wiki has grown (closing in on 200,000 articles), the database server was timing out before returning any results to the PHP. This problem was relatively easy to fix, using a not-widely-known property of the ADO Connection object, Connection::CommandTimeout. The default for Connection::CommandTimeout is 30 seconds, and no results were being returned for the (expensive) query until about 35 seconds or so. Once a row comes back from the server, ADO doesn't care how much time it takes to complete the query, but it's pretty sensitive to how long it takes for results to START coming back. I went into the ADOdb connector code and in the file adodb-ado5.inc.php added right before the return from the method ADODB_ado::_connect the single line
   $dbc->CommandTimeout = 90;
Although this fixed the timeout problem, it brought out a latent problem. For the Special pages that return true from QueryPage::isExpensive, some of them -- including this one -- were trying to cache the results in a temporary table. This clashed with ADOdb's handling of OFFSET and LIMIT clauses in the SQL, so I have disabled the caching of results from "expensive" Special pages by not performing any activity in the overriden QueryPage::preProcessResults methods for these pages (Special:ShortPages, Special:LongPages, and Special:WantedPages in particular). Down the road, perhaps I'll think of a slightly more clever way to handle SQL OFFSET and LIMIT clauses and I can re-enable the preProcessResults methods.

No comments: