RT session cleanup - session table size

Hello everybody
I want to cleanup my session table, it is already about 1 Gigabyte! I hit:

me@myserver:~$ sudo rt-clean-sessions --debug --older 1M

and it says:

[Sun Jul 7 12:20:26 2013] [warning]: Use of uninitialized value $alogoff
in multiplication (*) at /usr/sbin/rt-clean-sessions line 118.
(/usr/sbin/rt-clean-sessions:118)
[Sun Jul 7 12:20:26 2013] [info]: successfuly deleted 0E0 sessions
(/usr/share/request-tracker3.8/lib/RT/Interface/Web/Session.pm:195)

And my table is still the same size! how does it work? isn’t it supposed to
delete these rows and reduce the size of table?

Hello everybody
I want to cleanup my session table, it is already about 1 Gigabyte! I hit:

me@myserver:~$ sudo rt-clean-sessions --debug --older 1M

and it says:

[Sun Jul 7 12:20:26 2013] [warning]: Use of uninitialized value $alogoff in multiplication (*)
at /usr/sbin/rt-clean-sessions line 118. (/usr/sbin/rt-clean-sessions:118)
[Sun Jul 7 12:20:26 2013] [info]: successfuly deleted 0E0 sessions
(/usr/share/request-tracker3.8/lib/RT/Interface/Web/Session.pm:195)

And my table is still the same size! how does it work? isn’t it supposed to delete these rows
and reduce the size of table?

What is AutoLogoff set to in your RT_SiteConfig.pm file?
You can confirm the version RT sees by looking at Tools ->
Configuration -> Tools -> System Configuration and searching for
AutoLogoff. This might explain your weird warning.

As for why nothing is deleted, if you log into your database (which
database are you using?) you can run
select min(LastUpdated) from sessions;
to see what your data looks like.

-kevin

Thanks a lot Kevin. Frankly I have been working on this server for like 1
week, this is RT3.8 on Ubuntu 11.04 (terrible, I know), it has no GUI
installed (no problem for me), mysql 5.1 and the administrator has left
almost for 1 year, so no support for long time. The auto logoff was not
set, we are planning to find all the problems, announce downtime (this
server is being used globally) and make changes, I am planning to set the
autologoff too. But now, how can I at least manually clean this session
table? is there any way? (I am new to this RT, so I am really not sure how
it works yet, I am studying it and I know that this data on session table
is really useless for future)On Mon, Jul 8, 2013 at 5:01 PM, Kevin Falcone falcone@bestpractical.comwrote:

On Sun, Jul 07, 2013 at 02:21:52PM +0200, Alex Decalli wrote:

Hello everybody
I want to cleanup my session table, it is already about 1 Gigabyte! I
hit:

me@myserver:~$ sudo rt-clean-sessions --debug --older 1M

and it says:

[Sun Jul 7 12:20:26 2013] [warning]: Use of uninitialized value
$alogoff in multiplication (*)
at /usr/sbin/rt-clean-sessions line 118.
(/usr/sbin/rt-clean-sessions:118)
[Sun Jul 7 12:20:26 2013] [info]: successfuly deleted 0E0 sessions
(/usr/share/request-tracker3.8/lib/RT/Interface/Web/Session.pm:195)

And my table is still the same size! how does it work? isn’t it
supposed to delete these rows
and reduce the size of table?

What is AutoLogoff set to in your RT_SiteConfig.pm file?
You can confirm the version RT sees by looking at Tools ->
Configuration -> Tools -> System Configuration and searching for
AutoLogoff. This might explain your weird warning.

As for why nothing is deleted, if you log into your database (which
database are you using?) you can run
select min(LastUpdated) from sessions;
to see what your data looks like.

-kevin

Hello everybody
I want to cleanup my session table, it is already about 1 Gigabyte! I hit:

me@myserver:~$ sudo rt-clean-sessions --debug --older 1M

That reminds me - is there any reason RT would get upset if the sessions
table was suddenly empty after a restart?

I’m wondering if it should be an UNLOGGED table on PostgreSQL 9.1 and
above, so it doesn’t incur write-ahead logging overhead. If it doesn’t
need to be crash-safe and it’s OK to just have it truncated after a
crash recovery restart, that’d be ideal.

Thoughts?

Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

I think there is no such table.

There certainly is. From my PostgreSQL logs on the database that RT is
using:

UPDATE sessions SET a_session = $1 WHERE id = $2

with a giant binary blob as $1"

DETAIL:  parameters: $1 = 'BQgDAAAACQQRD1

RT should really be using DBD::Pg’s native bytea support on PostgreSQL,
not base64-encoding binary for storing in the database, btw, something like:

$rv = $sth->bind_param($param_num, $bind_value,
{ pg_type => DBD::Pg::PG_BYTEA });

per http://search.cpan.org/~rudy/DBD-Pg/Pg.pm

Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hello everybody
I want to cleanup my session table, it is already about 1 Gigabyte! I hit:

me@myserver:~$ sudo rt-clean-sessions --debug --older 1M

That reminds me - is there any reason RT would get upset if the sessions
table was suddenly empty after a restart?

I’m wondering if it should be an UNLOGGED table on PostgreSQL 9.1 and
above, so it doesn’t incur write-ahead logging overhead. If it doesn’t
need to be crash-safe and it’s OK to just have it truncated after a
crash recovery restart, that’d be ideal.

Thoughts?

Hi Craig,

Truncating the table does not cause any problems. Everyone will just need
to login again. UNLOGGED is okay unless you want to be able to failover
to a DB replica in which case you want them logged to have the backup
DB updated.

Regards,
Ken

Thanks a lot Kevin. Frankly I have been working on this server for like 1 week, this is RT3.8
on Ubuntu 11.04 (terrible, I know), it has no GUI installed (no problem for me), mysql 5.1 and
the administrator has left almost for 1 year, so no support for long time. The auto logoff was
not set, we are planning to find all the problems, announce downtime (this server is being
used globally) and make changes, I am planning to set the autologoff too. But now, how can I
at least manually clean this session table? is there any way? (I am new to this RT, so I am
really not sure how it works yet, I am studying it and I know that this data on session table
is really useless for future)

The SQL I mentioned was a bit of a hint.

 As for why nothing is deleted, if you log into your database (which
 database are you using?) you can run
 select min(LastUpdated) from sessions;
 to see what your data looks like.

You can delete from sessions where LastUpdated is old, as long as you
accept that RT does not always update the session for REST clients
and some other users, so if someone has hardcoded a cookie rather than
logging in and saving the cookie, there will be problems.

-kevin

I think there is no such table.

There certainly is. From my PostgreSQL logs on the database that RT is
using:

UPDATE sessions SET a_session = $1 WHERE id = $2

with a giant binary blob as $1"

DETAIL:  parameters: $1 = 'BQgDAAAACQQRD1

RT should really be using DBD::Pg’s native bytea support on PostgreSQL,
not base64-encoding binary for storing in the database, btw, something like:

$rv = $sth->bind_param($param_num, $bind_value,
{ pg_type => DBD::Pg::PG_BYTEA });

RT uses Apache::Session which is what is storing things in the
session table in the database.

We document that you shouldn’t bother backing up the session table.

Setting it to disappear on restart should be fine as long as your
users are ok with being logged out and your external processes aren’t
storing session ids and trying to use them rather than logging in
(a surprisingly common failure case).

-kevin

Hello everybody
I want to cleanup my session table, it is already about 1 Gigabyte! I hit:

Hmm amusingly once I saw this post I thought I would check ours… 2.2M
rows = 8GB on disk :slight_smile:

-Kristian

BIG NEWS:: solution discovered:

I first hit this:

sudo rt-clean-sessions --debug --older 1M

and then:

Mysql> OPTIMIZE TABLE sessions;

Now my session table is 1K :]On Fri, Jul 12, 2013 at 1:48 PM, Kristian Davies kristian.davies@gmail.comwrote:

On Sun, Jul 7, 2013 at 1:21 PM, Alex Decalli alexdecalli71@gmail.comwrote:

Hello everybody
I want to cleanup my session table, it is already about 1 Gigabyte! I hit:

Hmm amusingly once I saw this post I thought I would check ours… 2.2M
rows = 8GB on disk :slight_smile:

-Kristian

BIG NEWS:: solution discovered:

I first hit this:

sudo rt-clean-sessions --debug --older 1M

and then:

Mysql> OPTIMIZE TABLE sessions;

Now my session table is 1K :]

That’s what I did too but I had to re-run the rt-clean-sessions script
multiple times as it kept crashing due to running out of memory multiple
times.

-Kristian