launchpad-reviewers team mailing list archive
-
launchpad-reviewers team
-
Mailing list archive
-
Message #02840
[Merge] lp:~stub/launchpad/session-prune into lp:launchpad/db-devel
Stuart Bishop has proposed merging lp:~stub/launchpad/session-prune into lp:launchpad/db-devel.
Requested reviews:
Launchpad code reviewers (launchpad-reviewers)
For more details, see:
https://code.launchpad.net/~stub/launchpad/session-prune/+merge/52086
Garbo jobs to clean out the session database.
These rules match the existing rules, not currently in the tree.
- Sessions that haven't been used for 60 days are removed.
- Sessions older than 1 day that have never logged in are removed.
First rule removes old sessions. Second rule removes sessions created during an incomplete authentication process. It also used to remove sessions from anonymous connections, but they no longer get them until they follow the login links.
--
https://code.launchpad.net/~stub/launchpad/session-prune/+merge/52086
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~stub/launchpad/session-prune into lp:launchpad/db-devel.
=== modified file 'database/schema/launchpad_session.sql'
--- database/schema/launchpad_session.sql 2010-09-10 09:45:45 +0000
+++ database/schema/launchpad_session.sql 2011-03-03 15:49:25 +0000
@@ -29,3 +29,28 @@
GRANT SELECT, INSERT, UPDATE, DELETE ON TimeLimitedToken TO session;
-- And the garbo needs to run on it too.
GRANT SELECT, DELETE ON TimeLimitedToken TO session;
+
+
+-- This helper needs to exist in the session database so the BulkPruner
+-- can clean up unwanted sessions.
+CREATE OR REPLACE FUNCTION cursor_fetch(cur refcursor, n integer)
+RETURNS SETOF record LANGUAGE plpgsql AS
+$$
+DECLARE
+ r record;
+ count integer;
+BEGIN
+ FOR count IN 1..n LOOP
+ FETCH FORWARD FROM cur INTO r;
+ IF NOT FOUND THEN
+ RETURN;
+ END IF;
+ RETURN NEXT r;
+ END LOOP;
+END;
+$$;
+
+COMMENT ON FUNCTION cursor_fetch(refcursor, integer) IS
+'Fetch the next n items from a cursor. Work around for not being able to use FETCH inside a SELECT statement.';
+
+GRANT EXECUTE ON FUNCTION cursor_fetch(refcursor, integer) TO session;
=== modified file 'lib/lp/scripts/garbo.py'
--- lib/lp/scripts/garbo.py 2011-02-23 10:28:53 +0000
+++ lib/lp/scripts/garbo.py 2011-03-03 15:49:25 +0000
@@ -74,6 +74,7 @@
LaunchpadCronScript,
SilentLaunchpadScriptFailure,
)
+from lp.services.session.model import SessionData
from lp.translations.interfaces.potemplate import IPOTemplateSet
from lp.translations.model.potranslation import POTranslation
@@ -107,10 +108,14 @@
# from. Must be overridden.
target_table_class = None
- # The column name in target_table we use as the integer key. May be
- # overridden.
+ # The column name in target_table we use as the key. The type must
+ # match that returned by the ids_to_prune_query and the
+ # target_table_key_type. May be overridden.
target_table_key = 'id'
+ # SQL type of the target_table_key. May be overridden.
+ target_table_key_type = 'integer'
+
# An SQL query returning a list of ids to remove from target_table.
# The query must return a single column named 'id' and should not
# contain duplicates. Must be overridden.
@@ -119,10 +124,23 @@
# See `TunableLoop`. May be overridden.
maximum_chunk_size = 10000
+ # Optional extra WHERE clause fragment for the deletion to skip
+ # arbitrary rows flagged for deletion. For example, skip rows
+ # that might have been modified since the set of ids_to_prune
+ # was calculated.
+ extra_prune_clause = None
+
+ def getStore(self):
+ """The master Store for the table we are pruning.
+
+ May be overridden.
+ """
+ return IMasterStore(self.target_table_class)
+
def __init__(self, log, abort_time=None):
super(BulkPruner, self).__init__(log, abort_time)
- self.store = IMasterStore(self.target_table_class)
+ self.store = self.getStore()
self.target_table_name = self.target_table_class.__storm_table__
# Open the cursor.
@@ -138,12 +156,20 @@
def __call__(self, chunk_size):
"""See `ITunableLoop`."""
+ if self.extra_prune_clause:
+ extra = "AND (%s)" % self.extra_prune_clause
+ else:
+ extra = ""
result = self.store.execute("""
- DELETE FROM %s WHERE %s IN (
+ DELETE FROM %s
+ WHERE %s IN (
SELECT id FROM
- cursor_fetch('bulkprunerid', %d) AS f(id integer))
+ cursor_fetch('bulkprunerid', %d) AS f(id %s))
+ %s
"""
- % (self.target_table_name, self.target_table_key, chunk_size))
+ % (
+ self.target_table_name, self.target_table_key,
+ chunk_size, self.target_table_key_type, extra))
self._num_removed = result.rowcount
transaction.commit()
@@ -157,9 +183,7 @@
XXX bug=723596 StuartBishop: This job only needs to run once per month.
"""
-
target_table_class = POTranslation
-
ids_to_prune_query = """
SELECT POTranslation.id AS id FROM POTranslation
EXCEPT (
@@ -186,6 +210,45 @@
"""
+class SessionPruner(BulkPruner):
+ """Base class for session removal."""
+
+ target_table_class = SessionData
+ target_table_key = 'client_id'
+ target_table_key_type = 'text'
+
+
+class AntiqueSessionPruner(SessionPruner):
+ """Remove sessions not accessed for 60 days"""
+
+ ids_to_prune_query = """
+ SELECT client_id AS id FROM SessionData
+ WHERE last_accessed < CURRENT_TIMESTAMP - CAST('60 days' AS interval)
+ """
+
+
+class UnusedSessionPruner(SessionPruner):
+ """Remove sessions older than 1 day with no authentication credentials."""
+
+ ids_to_prune_query = """
+ SELECT client_id AS id FROM SessionData
+ WHERE
+ last_accessed < CURRENT_TIMESTAMP - CAST('1 day' AS interval)
+ AND client_id NOT IN (
+ SELECT client_id
+ FROM SessionPkgData
+ WHERE
+ product_id = 'launchpad.authenticateduser'
+ AND key='logintime')
+ """
+
+ # Don't delete a session if it has been used between calculating
+ # the list of sessions to remove and the current iteration.
+ prune_extra_clause = """
+ last_accessed < CURRENT_TIMESTAMP - CAST('1 day' AS interval)
+ """
+
+
class OAuthNoncePruner(TunableLoop):
"""An ITunableLoop to prune old OAuthNonce records.
@@ -977,6 +1040,8 @@
RevisionCachePruner,
BugHeatUpdater,
BugWatchScheduler,
+ AntiqueSessionPruner,
+ UnusedSessionPruner,
]
experimental_tunable_loops = []
=== modified file 'lib/lp/scripts/tests/test_garbo.py'
--- lib/lp/scripts/tests/test_garbo.py 2011-02-28 11:50:34 +0000
+++ lib/lp/scripts/tests/test_garbo.py 2011-03-03 15:49:25 +0000
@@ -10,7 +10,6 @@
datetime,
timedelta,
)
-import operator
import time
from pytz import UTC
@@ -70,12 +69,15 @@
)
from lp.scripts.garbo import (
BulkPruner,
+ AntiqueSessionPruner,
+ UnusedSessionPruner,
DailyDatabaseGarbageCollector,
HourlyDatabaseGarbageCollector,
OpenIDConsumerAssociationPruner,
)
from lp.services.job.model.job import Job
from lp.services.log.logger import BufferLogger
+from lp.services.session.model import SessionData, SessionPkgData
from lp.testing import (
TestCase,
TestCaseWithFactory,
@@ -181,6 +183,96 @@
pruner(chunk_size)
+class TestSessionPruner(TestCase):
+ layer = ZopelessDatabaseLayer
+
+ def setUp(self):
+ super(TestCase, self).setUp()
+ IMasterStore(SessionData).find(SessionData).remove()
+
+ recent = datetime.utcnow().replace(tzinfo=UTC) - timedelta(minutes=1)
+ yesterday = recent - timedelta(days=1)
+ ancient = recent - timedelta(days=61)
+
+ def make_session(client_id, accessed, authenticated=None):
+ session_data = SessionData()
+ session_data.client_id = client_id
+ session_data.last_accessed = accessed
+ IMasterStore(SessionData).add(session_data)
+
+ if authenticated:
+ session_pkg_data = SessionPkgData()
+ session_pkg_data.client_id = client_id
+ session_pkg_data.product_id = u'launchpad.authenticateduser'
+ session_pkg_data.key = u'logintime'
+ session_pkg_data.pickle = 'value is ignored'
+ IMasterStore(SessionPkgData).add(session_pkg_data)
+
+ make_session(u'recent_auth', recent, True)
+ make_session(u'recent_unauth', recent, False)
+ make_session(u'yesterday_auth', yesterday, True)
+ make_session(u'yesterday_unauth', yesterday, False)
+ make_session(u'ancient_auth', ancient, True)
+ make_session(u'ancient_unauth', ancient, False)
+
+ def tearDown(self):
+ IMasterStore(SessionData).find(SessionData).remove()
+ super(TestCase, self).tearDown()
+
+ def sessionExists(self, client_id):
+ store = IMasterStore(SessionData)
+ return not store.find(
+ SessionData, SessionData.client_id == client_id).is_empty()
+
+ def test_antique_session_pruner(self):
+ chunk_size = 2
+ log = BufferLogger()
+ pruner = AntiqueSessionPruner(log)
+ try:
+ while not pruner.isDone():
+ pruner(chunk_size)
+ finally:
+ pruner.cleanUp()
+
+ expected_sessions = set([
+ u'recent_auth',
+ u'recent_unauth',
+ u'yesterday_auth',
+ u'yesterday_unauth',
+ # u'ancient_auth',
+ # u'ancient_unauth',
+ ])
+
+ found_sessions = set(
+ IMasterStore(SessionData).find(SessionData.client_id))
+
+ self.assertEqual(expected_sessions, found_sessions)
+
+ def test_unused_session_pruner(self):
+ chunk_size = 2
+ log = BufferLogger()
+ pruner = UnusedSessionPruner(log)
+ try:
+ while not pruner.isDone():
+ pruner(chunk_size)
+ finally:
+ pruner.cleanUp()
+
+ expected_sessions = set([
+ u'recent_auth',
+ u'recent_unauth',
+ u'yesterday_auth',
+ # u'yesterday_unauth',
+ u'ancient_auth',
+ # u'ancient_unauth',
+ ])
+
+ found_sessions = set(
+ IMasterStore(SessionData).find(SessionData.client_id))
+
+ self.assertEqual(expected_sessions, found_sessions)
+
+
class TestGarbo(TestCaseWithFactory):
layer = LaunchpadZopelessLayer
=== modified file 'lib/lp/services/configure.zcml'
--- lib/lp/services/configure.zcml 2010-10-24 12:32:24 +0000
+++ lib/lp/services/configure.zcml 2011-03-03 15:49:25 +0000
@@ -15,5 +15,6 @@
<include package=".profile" />
<include package=".salesforce" />
<include package=".scripts" />
+ <include package=".session" />
<include package=".worlddata" />
</configure>
=== added directory 'lib/lp/services/session'
=== added file 'lib/lp/services/session/__init__.py'
=== added file 'lib/lp/services/session/adapters.py'
--- lib/lp/services/session/adapters.py 1970-01-01 00:00:00 +0000
+++ lib/lp/services/session/adapters.py 2011-03-03 15:49:25 +0000
@@ -0,0 +1,37 @@
+# Copyright 2011 Canonical Ltd. This software is licensed under the
+# GNU Affero General Public License version 3 (see the file LICENSE).
+
+"""Session adapters."""
+
+__metaclass__ = type
+__all__ = []
+
+
+from zope.component import adapter
+from zope.interface import implementer
+
+from canonical.database.sqlbase import session_store
+from canonical.launchpad.interfaces.lpstorm import (
+ IMasterStore, ISlaveStore, IStore)
+from lp.services.session.interfaces import ISessionStormClass
+
+
+@adapter(ISessionStormClass)
+@implementer(IMasterStore)
+def session_master_store(cls):
+ """Adapt a Session database class to an `IMasterStore`."""
+ return session_store()
+
+
+@adapter(ISessionStormClass)
+@implementer(ISlaveStore)
+def session_slave_store(cls):
+ """Adapt a Session database class to an `ISlaveStore`."""
+ return session_store()
+
+
+@adapter(ISessionStormClass)
+@implementer(IStore)
+def session_default_store(cls):
+ """Adapt an Session database class to an `IStore`."""
+ return session_store()
=== added file 'lib/lp/services/session/configure.zcml'
--- lib/lp/services/session/configure.zcml 1970-01-01 00:00:00 +0000
+++ lib/lp/services/session/configure.zcml 2011-03-03 15:49:25 +0000
@@ -0,0 +1,12 @@
+<!-- Copyright 2011 Canonical Ltd. This software is licensed under the
+ GNU Affero General Public License version 3 (see the file LICENSE).
+-->
+<configure
+ xmlns="http://namespaces.zope.org/zope";
+ xmlns:browser="http://namespaces.zope.org/browser";
+ xmlns:i18n="http://namespaces.zope.org/i18n";
+ i18n_domain="launchpad">
+ <adapter factory=".adapters.session_master_store" />
+ <adapter factory=".adapters.session_slave_store" />
+ <adapter factory=".adapters.session_default_store" />
+</configure>
=== added file 'lib/lp/services/session/interfaces.py'
--- lib/lp/services/session/interfaces.py 1970-01-01 00:00:00 +0000
+++ lib/lp/services/session/interfaces.py 2011-03-03 15:49:25 +0000
@@ -0,0 +1,15 @@
+# Copyright 2011 Canonical Ltd. This software is licensed under the
+# GNU Affero General Public License version 3 (see the file LICENSE).
+
+"""Session interfaces."""
+
+__metaclass__ = type
+__all__ = ['ISessionStormClass']
+
+
+from zope.interface import Interface
+
+
+class ISessionStormClass(Interface):
+ """Marker interface for Session Storm database classes."""
+ pass
=== added file 'lib/lp/services/session/model.py'
--- lib/lp/services/session/model.py 1970-01-01 00:00:00 +0000
+++ lib/lp/services/session/model.py 2011-03-03 15:49:25 +0000
@@ -0,0 +1,38 @@
+# Copyright 2011 Canonical Ltd. This software is licensed under the
+# GNU Affero General Public License version 3 (see the file LICENSE).
+
+"""Session Storm database classes"""
+
+__metaclass__ = type
+__all__ = ['SessionData', 'SessionPkgData']
+
+from storm.locals import Pickle, Storm, Unicode
+from zope.interface import classProvides
+
+from canonical.database.datetimecol import UtcDateTimeCol
+from lp.services.session.interfaces import ISessionStormClass
+
+
+class SessionData(Storm):
+ """A user's Session."""
+
+ classProvides(ISessionStormClass)
+
+ __storm_table__ = 'SessionData'
+ client_id = Unicode(primary=True)
+ created = UtcDateTimeCol()
+ last_accessed = UtcDateTimeCol()
+
+
+class SessionPkgData(Storm):
+ """Data storage for a Session."""
+
+ classProvides(ISessionStormClass)
+
+ __storm_table__ = 'SessionPkgData'
+ __storm_primary__ = 'client_id', 'product_id', 'key'
+
+ client_id = Unicode()
+ product_id = Unicode()
+ key = Unicode()
+ pickle = Pickle()
=== added file 'scripts/session-expiry.sql'
--- scripts/session-expiry.sql 1970-01-01 00:00:00 +0000
+++ scripts/session-expiry.sql 2011-03-03 15:49:25 +0000
@@ -0,0 +1,20 @@
+/* Expire unwanted sessions from the session database.
+
+While the session machinery automatically removes expired sessions,
+it does not do so particularly intelligently; it does not make decisions
+based on the content of the session.
+
+At the time of writing, we maintain sessions for 60 days and are getting
+nearly half a million new sessions each day. If we expire sessions of
+users who are not logged in after 1 day, we will reduce our 24 million
+sessions down to a more managable 700,000.
+
+*/
+
+DELETE FROM SessionData
+WHERE last_accessed < CURRENT_TIMESTAMP - '1 day'::interval
+ AND client_id NOT IN (
+ SELECT client_id FROM SessionPkgData
+ WHERE product_id='launchpad.authenticateduser' AND key='logintime'
+ );
+
Follow ups
