Re: [rt-users] RT session cleanup - session table size
On Mon, Jul 15, 2013 at 12:37 PM, Alex Decalli wrote: > 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
Re: [rt-users] RT session cleanup - session table size
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 wrote: > On Sun, Jul 7, 2013 at 1:21 PM, Alex Decalli wrote: > >> 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 :-) > > -Kristian >
Re: [rt-users] RT session cleanup - session table size
On Sun, Jul 7, 2013 at 1:21 PM, Alex Decalli wrote: > 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 :-) -Kristian
Re: [rt-users] RT session cleanup - session table size
On Mon, Jul 08, 2013 at 08:05:32PM +0200, Alex Decalli wrote: >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 pgpbfX88siDXk.pgp Description: PGP signature
Re: [rt-users] RT session cleanup - session table size
On Tue, Jul 09, 2013 at 03:23:53PM +0800, Craig Ringer wrote: > On 07/09/2013 03:05 PM, Alex Decalli wrote: > > 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 = 'BQgDCQQRD1 > > 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 pgpSWGEAB3QsL.pgp Description: PGP signature
Re: [rt-users] RT session cleanup - session table size
On Tue, Jul 09, 2013 at 12:02:17PM +0800, Craig Ringer wrote: > On 07/07/2013 08:21 PM, 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 > > 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
Re: [rt-users] RT session cleanup - session table size
On 07/09/2013 03:05 PM, Alex Decalli wrote: > 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 = 'BQgDCQQRD1 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
Re: [rt-users] RT session cleanup - session table size
On 07/07/2013 08:21 PM, 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 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
Re: [rt-users] RT session cleanup - session table size
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 wrote: > 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 >
Re: [rt-users] RT session cleanup - session table size
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 pgpXrhG3fKY0H.pgp Description: PGP signature
[rt-users] 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?