FAQ Search Today's Posts Mark Forums Read
» Video Reviews

» Linux Archive

Linux-archive is a website aiming to archive linux email lists and to make them easily accessible for linux users/developers.


» Sponsor

» Partners

» Sponsor

Go Back   Linux Archive > Redhat > Fedora Build System

 
 
LinkBack Thread Tools
 
Old 04-03-2012, 08:35 AM
Thomas Guthmann
 
Default sessions table cleanup ?

Hey guys,

We are running our own private koji instance. We are running 1.6 on el5.
Lately this query takes 20mins or so to execute :

SELECT host.id,name,arches,task_load,capacity FROM host
JOIN sessions USING (user_id)
WHERE enabled = TRUE AND ready = TRUE
AND expired = FALSE
AND master IS NULL
AND update_time > NOW() - '5 minutes'::interval

It looks like the 'sessions' table is the culprit. Indeed SELECTing
'host' is immediate whereas SELECT count(id) from 'sessions' takes 15
seconds for only 408312 rows... looks like a vacuum problem you would
say. You are right but my question is why do I have so many sessions
rows ? Can 'sessions' be truncated ? I am just wondering if it's a known
issue to not clean the sessions tables or if I need to tune my
autovacuum to work properly


Cheers,
Thomas

--
buildsys mailing list
buildsys@lists.fedoraproject.org
https://admin.fedoraproject.org/mailman/listinfo/buildsys
 
Old 04-04-2012, 03:44 PM
Mike McLean
 
Default sessions table cleanup ?

On 04/03/2012 04:35 AM, Thomas Guthmann wrote:

Hey guys,

We are running our own private koji instance. We are running 1.6 on el5.
Lately this query takes 20mins or so to execute :

SELECT host.id,name,arches,task_load,capacity FROM host
JOIN sessions USING (user_id)
WHERE enabled = TRUE AND ready = TRUE
AND expired = FALSE
AND master IS NULL
AND update_time > NOW() - '5 minutes'::interval

It looks like the 'sessions' table is the culprit. Indeed SELECTing
'host' is immediate whereas SELECT count(id) from 'sessions' takes 15
seconds for only 408312 rows... looks like a vacuum problem you would
say. You are right but my question is why do I have so many sessions
rows ? Can 'sessions' be truncated ? I am just wondering if it's a known
issue to not clean the sessions tables or if I need to tune my
autovacuum to work properly


I run the following nightly:

DELETE FROM sessions WHERE update_time < now() - '1 day'::interval;

If you are using an old enough postgres version, you may also want to
truncate sessions periodically. This will invalidate all login
credentials, so I would only recommend doing so during a maintenance
outage. This will require you to restart kojira and all kojid instances.


I'd start with just the nightly cleanup at first
--
buildsys mailing list
buildsys@lists.fedoraproject.org
https://admin.fedoraproject.org/mailman/listinfo/buildsys
 
Old 04-05-2012, 02:10 AM
Thomas Guthmann
 
Default sessions table cleanup ?

Re,


DELETE FROM sessions WHERE update_time < now() - '1 day'::interval;
Thanks Mike for your quick answer. This trick will do and indeed is
nicer than a rough truncate Ta


Thomas
--
buildsys mailing list
buildsys@lists.fedoraproject.org
https://admin.fedoraproject.org/mailman/listinfo/buildsys
 

Thread Tools




All times are GMT. The time now is 09:52 AM.

VBulletin, Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO ©2007, Crawlability, Inc.
Copyright 2007 - 2008, www.linux-archive.org