Modify ↓
Opened 7 years ago
Closed 7 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 |
||
API Changes: | |||
Internal Changes: |
Description (last modified by )
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): 500 500 db(""" 501 501 DELETE FROM session_attribute 502 502 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) 505 506 """)
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 , 7 years ago
Description: | modified (diff) |
---|
comment:2 by , 7 years ago
Owner: | set to |
---|---|
Status: | new → assigned |
comment:3 by , 7 years ago
Release Notes: | modified (diff) |
---|---|
Resolution: | → fixed |
Status: | assigned → closed |
Note:
See TracTickets
for help on using tickets.
Committed in [16394] and merged in [16395-16396].