Edgewall Software
Modify

Opened 3 years ago

Closed 3 years ago

#12954 closed defect (fixed)

session purge command is too slow with many sessions on PostgreSQL

Reported by: Jun Omae Owned by: Jun Omae
Priority: normal Milestone: 1.0.17
Component: general Version: 1.0.1
Severity: normal Keywords: postgres
Cc: Branch:
Release Notes:

Improve performance of session purge command when many anonymous sessions on PostgreSQL.

API Changes:
Internal Changes:

Description (last modified by Jun Omae)

Timing of "session purge" command with ~63 000 sessions is 8 minutes over. See gdiscussion:trac-users:jeiA32fZVic.

Proposed patch:

  • trac/web/session.py

    diff --git a/trac/web/session.py b/trac/web/session.py
    index a8b68fae6..103c22c4a 100644
    a b class SessionAdmin(Component):  
    500500            db("""
    501501                DELETE FROM session_attribute
    502502                WHERE authenticated=0
    503                       AND sid NOT IN (SELECT sid FROM session
    504                                       WHERE authenticated=0)
     503                      AND NOT EXISTS (SELECT * FROM session AS s
     504                                      WHERE s.sid=session_attribute.sid
     505                                      AND s.authenticated=0)
    505506                """)

Timing of deleting session_attribute records with 100,000 sessions on PostgreSQL:

Before 555742.893 ms over
After 161.881 ms

I've confirmed no changes with 100,000 sessions on SQLite and MySQL.

trac=> SELECT authenticated, COUNT(*) FROM session GROUP BY authenticated;
 authenticated | count
---------------+--------
             0 | 100000
             1 |      9
(2 rows)

trac=> SELECT authenticated, COUNT(*) FROM session_attribute GROUP BY
authenticated;
 authenticated | count
---------------+--------
             0 | 200000
             1 |     47
(2 rows)

trac=> \timing on
Timing is on.
trac=> BEGIN;
BEGIN
Time: 0.276 ms
trac=> DELETE FROM session_attribute
trac-> WHERE authenticated=0
trac->       AND sid NOT IN (SELECT sid FROM session
trac(>                       WHERE authenticated=0)
trac-> ;
^CCancel request sent
ERROR:  canceling statement due to user request
Time: 555742.893 ms
trac=>
trac=>
trac=> BEGIN;
BEGIN
Time: 0.288 ms
trac=> DELETE FROM session_attribute
trac-> WHERE authenticated=0
trac-> AND NOT EXISTS (SELECT * FROM session AS s
trac(>                 WHERE s.sid=session_attribute.sid
trac(>                 AND s.authenticated=0)
trac-> ;
DELETE 0
Time: 161.881 ms
trac=> ROLLBACK;
ROLLBACK
Time: 0.263 ms

Attachments (0)

Change History (3)

comment:1 by Jun Omae, 3 years ago

Description: modified (diff)

comment:2 by Jun Omae, 3 years ago

Owner: set to Jun Omae
Status: newassigned

comment:3 by Jun Omae, 3 years ago

Release Notes: modified (diff)
Resolution: fixed
Status: assignedclosed

Committed in [16394] and merged in [16395-16396].

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Jun Omae.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Jun Omae to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.