Marcoil has uploaded a new change for review. https://gerrit.wikimedia.org/r/74159
Change subject: Use a better query to select a random title to offer to clients, using just one query instead of two. Based on article at http://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/ ...................................................................... Use a better query to select a random title to offer to clients, using just one query instead of two. Based on article at http://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/ Also, use Date objects correctly for MySQL's TIMESTAMP type. Bug #44652 Change-Id: Ib238e2533c1c3ca1bc9397d83f0d63b297b3ab84 --- M js/tests/server/server.js 1 file changed, 18 insertions(+), 32 deletions(-) git pull ssh://gerrit.wikimedia.org:29418/mediawiki/extensions/Parsoid refs/changes/59/74159/1 diff --git a/js/tests/server/server.js b/js/tests/server/server.js index 17bb75e..851ce77 100755 --- a/js/tests/server/server.js +++ b/js/tests/server/server.js @@ -9,8 +9,8 @@ maxTries = 6, // The maximum number of fetch retries per article maxFetchRetries = 6, - // "Random" estimate of how many pending pages we have in the db - pendingPagesEstimate = 500; + // How many pages should we consider at a time for random selection + numTitlesPreselected = 20; // Command line options var argv = require( 'optimist' ) @@ -63,26 +63,17 @@ 'LEFT JOIN stats ON stats.id = pages.latest_result ' + 'WHERE num_fetch_errors < ? AND ' + '( claims.id IS NULL OR ' + - '( claims.has_errorless_result = 0 AND claims.num_tries <= ? AND claims.timestamp < ? ) ) ' + + '( claims.has_errorless_result = 0 AND ' + + 'claims.num_tries <= ? AND claims.timestamp < ? ) ) AND ' + + '( RAND() < ( SELECT ( ( 1 / COUNT( * ) ) * ? ) FROM pages ) ) ' + 'ORDER BY stats.score DESC, ' + - 'claims.timestamp ASC LIMIT 1 OFFSET ? '; + 'claims.timestamp ASC, RAND() LIMIT 1'; var dbFindTitleForClaim = 'SELECT pages.id, pages.title, pages.prefix ' + 'FROM pages, claims ' + 'WHERE pages.id = ? AND pages.id = claims.page_id AND ' + 'claims.commit_hash= ?'; - -var dbGetTitleRandom = - 'SELECT pages.id, pages.title, pages.prefix ' + - 'FROM pages ' + - 'LEFT JOIN claims ON pages.id = claims.page_id AND claims.commit_hash = ? ' + - 'LEFT JOIN stats ON stats.id = pages.latest_result ' + - 'WHERE num_fetch_errors < ? AND ' + - '( claims.id IS NULL OR ' + - '( claims.has_errorless_result = 0 AND claims.num_tries <= ? AND claims.timestamp < ? ) ) ' + - 'ORDER BY stats.score DESC, ' + - 'claims.timestamp ASC, RAND() LIMIT 1'; var dbIncrementFetchErrorCount = 'UPDATE pages SET num_fetch_errors = num_fetch_errors + 1 WHERE title = ? AND prefix = ?'; @@ -426,7 +417,7 @@ if ( claim && claim[0] ) { // Ignoring possible duplicate processing // Increment the # of tries, update timestamp - db.query( dbUpdateClaim, [Date.now(), claim[0].id], + db.query( dbUpdateClaim, [new Date(), claim[0].id], dbUpdateErrCB.bind(null, row[0].title, row[0].prefix, commitHash, "claim", null)); if (claim[0].num_tries >= maxTries) { @@ -457,7 +448,7 @@ } } else { // Claim doesn't exist - db.query( dbInsertClaim, [ row[0].id, commitHash, Date.now() ], function(err) { + db.query( dbInsertClaim, [ row[0].id, commitHash, new Date() ], function(err) { if (!err) { console.log( ' ->', row[0].prefix + ':' + row[0].title ); res.send( { prefix: row[0].prefix, title: row[0].title } ); @@ -465,26 +456,21 @@ console.error(err); console.error("Multiple clients trying to access the same title:", row[0].prefix + ':' + row[0].title ); // In the rare scenario that some other client snatched the - // title before us, get a new title (use the randomized ordering query) - db.query( dbGetTitleRandom, [ commitHash, maxFetchRetries, maxTries, cutOffTimestamp ], + // title before us, get a new title + db.query( dbGetTitle, [ commitHash, maxFetchRetries, maxTries, cutOffTimestamp, numTitlesPreselected ], titleCallback.bind( null, req, res, false, commitHash, cutOffTimestamp ) ); } }); } }); - } else if ( retry ) { - // Try again with the slow DB search method - db.query( dbGetTitleRandom, [ commitHash, maxFetchRetries, maxTries, cutOffTimestamp ], - titleCallback.bind( null, req, res, false, commitHash, cutOffTimestamp ) ); } else { res.send( 'no available titles that fit those constraints', 404 ); } }; var fetchPage = function( commitHash, cutOffTimestamp, req, res ) { - // This query picks a random page among the first 'pendingPagesEstimate' pages - var rowOffset = Math.floor(Math.random() * pendingPagesEstimate); - db.query( dbGetTitle, [ commitHash, maxFetchRetries, maxTries, cutOffTimestamp, rowOffset ], + // This query picks a random page among the first 'numTitlesPreselected' pages + db.query( dbGetTitle, [ commitHash, maxFetchRetries, maxTries, cutOffTimestamp, numTitlesPreselected ], titleCallback.bind( null, req, res, true, commitHash, cutOffTimestamp ) ); }; @@ -508,16 +494,16 @@ db.query( dbGetTitle, [ commitHash, maxFetchRetries, maxTries, cutOffTimestamp, 0 ], function( err, row ) { if ( err ) { console.error( err ); - console.error( "Failed fetching row to update (" + [ cutOffTimestamp, commitHash, maxFetchRetries, maxTries, Date.now() ] + ')' ); + console.error( "Failed fetching row to update (" + [ cutOffTimestamp, commitHash, maxFetchRetries, maxTries, new Date() ] + ')' ); } else { console.log(row[0].title); var targetID = row[0].id; - console.log( 'Trying insert of ' + targetID + ' with ' + [ Date.now(), targetID ]); - db.query( dbInsertClaim, [ targetID, commitHash, Date.now() ], function(err) { + console.log( 'Trying insert of ' + targetID + ' with ' + [ new Date(), targetID ]); + db.query( dbInsertClaim, [ targetID, commitHash, new Date() ], function(err) { if ( err ) { db.query( 'ROLLBACK;', [], function(err){}); console.error( err ); - console.error( "Failed updating with (" + [ Date.now(), targetID ] + ')' ); + console.error( "Failed updating with (" + [ new Date(), targetID ] + ')' ); } else { db.query( 'COMMIT;', [], function(err){}); console.log( 'Update succeeded. Get detail ' + [targetID, commitHash ] ); @@ -551,9 +537,9 @@ // // Hopefully, no page takes longer than 10 minutes to parse. :) - // claimPage(req.query.commit, Date.now() - 600, req, res); + // claimPage(req.query.commit, Date.now() - 600 * 1000, req, res); - fetchPage(req.query.commit, Date.now() - 600, req, res); + fetchPage(req.query.commit, new Date(Date.now() - 600 * 1000), req, res); }; var statsScore = function(skipCount, failCount, errorCount) { -- To view, visit https://gerrit.wikimedia.org/r/74159 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: newchange Gerrit-Change-Id: Ib238e2533c1c3ca1bc9397d83f0d63b297b3ab84 Gerrit-PatchSet: 1 Gerrit-Project: mediawiki/extensions/Parsoid Gerrit-Branch: rt_testing Gerrit-Owner: Marcoil <[email protected]> _______________________________________________ MediaWiki-commits mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
