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
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@lists.fedoraproject.org