From 59ebdf2f880b38aba9192115eb8fe377a0cfc179 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Rapha=C3=ABl=20Barrois?= Date: Mon, 25 Nov 2013 01:32:12 +0100 Subject: [PATCH] Try to improve the performance of end-of-month session cleanup. --- ChangeLog | 3 ++ bin/cron/compliance.php | 56 +++++++++++++++------- upgrade/1.1.10/01_clean_bankaccounts.sql | 10 ++++ .../1.1.10/02_add_group_disable_global_mails.sql | 4 ++ upgrade/1.1.10/connect.db.inc.php | 1 + upgrade/1.1.10/update.sh | 9 ++++ 6 files changed, 66 insertions(+), 17 deletions(-) create mode 100644 upgrade/1.1.10/01_clean_bankaccounts.sql create mode 100644 upgrade/1.1.10/02_add_group_disable_global_mails.sql create mode 120000 upgrade/1.1.10/connect.db.inc.php create mode 100755 upgrade/1.1.10/update.sh diff --git a/ChangeLog b/ChangeLog index b6cafc1..93ad79f 100644 --- a/ChangeLog +++ b/ChangeLog @@ -1,6 +1,9 @@ ================================================================================ VERSION 1.1.10 XX XX XXXX + * Misc: + - Improve performance of end-of-month sessions cleanup -XEL + ================================================================================ VERSION 1.1.9 15 10 2013 diff --git a/bin/cron/compliance.php b/bin/cron/compliance.php index 6fd195f..16f29fe 100755 --- a/bin/cron/compliance.php +++ b/bin/cron/compliance.php @@ -31,6 +31,7 @@ require './connect.db.inc.php'; * no matter what. */ function discardExpiredSessions($userPerms, $retentionPeriod, $minimalBacklog) { + $begin = time(); switch ($userPerms) { case 'user': $state = 'active'; @@ -48,24 +49,45 @@ function discardExpiredSessions($userPerms, $retentionPeriod, $minimalBacklog) { return; } - XDB::execute( - "DELETE s - FROM log_sessions AS s - JOIN (SELECT a.uid, - (SELECT us.start - FROM log_sessions AS us - WHERE us.uid = a.uid AND (us.suid IS NULL OR us.suid = 0) - ORDER BY us.start DESC - LIMIT {?}, 1) AS no_discard_limit - FROM #x5dat#.accounts AS a - WHERE a.state = {?} AND a.is_admin = {?} - ORDER BY a.uid ASC) AS ut ON (ut.uid = s.uid) - WHERE s.start < DATE_SUB(NOW(), INTERVAL {?} MONTH) - AND s.start < ut.no_discard_limit", - $minimalBacklog - 1, $state, $isAdmin, $retentionPeriod); + list($low, $high) = XDB::fetchOneRow( + "SELECT MIN(uid), MAX(uid) + FROM #x5dat#.accounts + WHERE state = {?} AND is_admin = {?}", + $state, $isAdmin); - $affectedRows = XDB::affectedRows(); - echo "Users with permission '$userPerms': removed $affectedRows sessions.\n"; + $batchSize = 500; + $nbBatches = 0; + $affectedRows = 0; + + // Run in batches. + for ($lowUID = $low; $lowUID <= $high; $lowUID += $batchSize) { + + // Slight optimization for last loop: adjust to exactly what's necessary. + $highUID = min($high + 1, $lowUID + $batchSize); + + XDB::execute( + "DELETE s + FROM log_sessions AS s + JOIN (SELECT a.uid, + (SELECT us.start + FROM log_sessions AS us + WHERE us.uid = a.uid AND (us.suid IS NULL OR us.suid = 0) + ORDER BY us.start DESC + LIMIT {?}, 1) AS no_discard_limit + FROM #x5dat#.accounts AS a + WHERE a.state = {?} AND a.is_admin = {?} + AND a.uid >= {?} AND a.uid < {?} + ORDER BY a.uid ASC) AS ut ON (ut.uid = s.uid) + WHERE s.start < DATE_SUB(NOW(), INTERVAL {?} MONTH) + AND s.start < ut.no_discard_limit", + $minimalBacklog - 1, $state, $isAdmin, $lowUID, $highUID, $retentionPeriod); + + $nbBatches += 1; + $affectedRows += XDB::affectedRows(); + } + + $duration = time() - $begin; + echo "Users with permission '$userPerms': removed $affectedRows sessions in $duration seconds ($nbBatches batches).\n"; } /** diff --git a/upgrade/1.1.10/01_clean_bankaccounts.sql b/upgrade/1.1.10/01_clean_bankaccounts.sql new file mode 100644 index 0000000..eb218f8 --- /dev/null +++ b/upgrade/1.1.10/01_clean_bankaccounts.sql @@ -0,0 +1,10 @@ +UPDATE payments SET rib_id = NULL; +UPDATE payment_transfers SET account_id = NULL; +DELETE FROM payment_bankaccounts; +ALTER TABLE payment_bankaccounts CHANGE account iban varchar(33) NOT NULL DEFAULT 'FRkk BBBB BGGG GGCC CCCC CCCC CKK'; +ALTER TABLE payment_bankaccounts ADD COLUMN bic varchar(11) NOT NULL DEFAULT 'XXXXXXXXXXX' AFTER iban; +INSERT INTO payment_bankaccounts (id, asso_id, iban, bic, owner, status) VALUES (1, 53, DEFAULT, DEFAULT, 'RIB inconnu', 'new'); +UPDATE payments SET rib_id = 1; +ALTER TABLE payments CHANGE rib_id rib_id int(11) NOT NULL DEFAULT 1; + +-- vim=set syntax=mysql diff --git a/upgrade/1.1.10/02_add_group_disable_global_mails.sql b/upgrade/1.1.10/02_add_group_disable_global_mails.sql new file mode 100644 index 0000000..da19adf --- /dev/null +++ b/upgrade/1.1.10/02_add_group_disable_global_mails.sql @@ -0,0 +1,4 @@ +ALTER TABLE groups ADD COLUMN disable_mails int(1) NOT NULL DEFAULT 1; +UPDATE groups SET disable_mails = 0; + +-- vim:set syntax=mysql: diff --git a/upgrade/1.1.10/connect.db.inc.php b/upgrade/1.1.10/connect.db.inc.php new file mode 120000 index 0000000..442fab7 --- /dev/null +++ b/upgrade/1.1.10/connect.db.inc.php @@ -0,0 +1 @@ +../../bin/connect.db.inc.php \ No newline at end of file diff --git a/upgrade/1.1.10/update.sh b/upgrade/1.1.10/update.sh new file mode 100755 index 0000000..9230e34 --- /dev/null +++ b/upgrade/1.1.10/update.sh @@ -0,0 +1,9 @@ +#!/bin/bash + +. ../inc/pervasive.sh + +########################################################### +[ "$DATABASE" != "x4dat" ] || die "Cannot target x4dat" + +confirm "* Running database upgrade scripts" +mysql_run_directory . -- 2.1.4