Koha sessions table is ridiculously huge
Developers, After performing a software upgrade and server reboot, I came across this problem. My koha.sessions table is over 13 million entries. This seems to indicate that entries into the table were never removed, and that may help to explain why my backup mysqldumps are increasing by around 4MB daily. Chris Cormack suggested that I do a mysqldump of sessions with the no-data option set, then reimport it into the database to clear it out. I've finally gotten sessions to tell me what indexes it has on it, so that can be recreated as well. Are there any risks to doing this that I'm not factoring in? More generally, what could be causing my sessions table to get so out of hand? Permissions, perhaps? Something in the code being off? Any ideas or feedback would be appreciated. Thanks! Ian Walls Systems Integration Librarian NYU Health Sciences Libraries 550 First Ave., New York, NY 10016 (212) 263-8687 </PRE> <html> <body> ------------------------------------------------------------<br /> This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain information that is proprietary, confidential, and exempt from disclosure under applicable law. Any unauthorized review, use, disclosure, or distribution is prohibited. If you have received this email in error please notify the sender by return email and delete the original message. Please note, the recipient should check this email and any attachments for the presence of viruses. The organization accepts no liability for any damage caused by any virus transmitted by this email.<br /> ================================= </body> </html> <PRE>
You can also use "truncate sessions" to do the job if you just want to clear out the contents of the table. It's faster and simpler than using mysqldump. This issue arises from the fact that the way Koha is written, if a user just walks away from a session without logging out, their session data will hang out in the the sessions table forever. A proper solution would involve adding a timestamp column to the sessions table, enabling one to cull only expired sessions entirely within SQL statements. With the current schema, you'd have to parse the contents of each row within a user program, then cull based on the expiry contained within the session data itself. Cheers, Clay 2010/2/17 Walls, Ian <Ian.Walls@med.nyu.edu>
Developers,
After performing a software upgrade and server reboot, I came across this problem. My koha.sessions table is over 13 million entries. This seems to indicate that entries into the table were never removed, and that may help to explain why my backup mysqldumps are increasing by around 4MB daily.
Chris Cormack suggested that I do a mysqldump of sessions with the no-data option set, then reimport it into the database to clear it out. I’ve finally gotten sessions to tell me what indexes it has on it, so that can be recreated as well. Are there any risks to doing this that I’m not factoring in?
More generally, what could be causing my sessions table to get so out of hand? Permissions, perhaps? Something in the code being off?
Any ideas or feedback would be appreciated. Thanks!
Ian Walls
Systems Integration Librarian
NYU Health Sciences Libraries
550 First Ave., New York, NY 10016
(212) 263-8687
------------------------------------------------------------ This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain information that is proprietary, confidential, and exempt from disclosure under applicable law. Any unauthorized review, use, disclosure, or distribution is prohibited. If you have received this email in error please notify the sender by return email and delete the original message. Please note, the recipient should check this email and any attachments for the presence of viruses. The organization accepts no liability for any damage caused by any virus transmitted by this email. =================================
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha.org http://lists.koha.org/mailman/listinfo/koha-devel
Clay, Thanks! That sounds like an easy enough enhancement to make. I'll make it a point to keep an eye on the sessions table until then, and remind my coworkers to logout properly. Cheers, -Ian From: koha-devel-bounces@lists.koha.org [mailto:koha-devel-bounces@lists.koha.org] On Behalf Of Clay Fouts Sent: Wednesday, February 17, 2010 2:04 PM To: Koha-devel Subject: Re: [Koha-devel] Koha sessions table is ridiculously huge You can also use "truncate sessions" to do the job if you just want to clear out the contents of the table. It's faster and simpler than using mysqldump. This issue arises from the fact that the way Koha is written, if a user just walks away from a session without logging out, their session data will hang out in the the sessions table forever. A proper solution would involve adding a timestamp column to the sessions table, enabling one to cull only expired sessions entirely within SQL statements. With the current schema, you'd have to parse the contents of each row within a user program, then cull based on the expiry contained within the session data itself. Cheers, Clay 2010/2/17 Walls, Ian <Ian.Walls@med.nyu.edu<mailto:Ian.Walls@med.nyu.edu>> Developers, After performing a software upgrade and server reboot, I came across this problem. My koha.sessions table is over 13 million entries. This seems to indicate that entries into the table were never removed, and that may help to explain why my backup mysqldumps are increasing by around 4MB daily. Chris Cormack suggested that I do a mysqldump of sessions with the no-data option set, then reimport it into the database to clear it out. I've finally gotten sessions to tell me what indexes it has on it, so that can be recreated as well. Are there any risks to doing this that I'm not factoring in? More generally, what could be causing my sessions table to get so out of hand? Permissions, perhaps? Something in the code being off? Any ideas or feedback would be appreciated. Thanks! Ian Walls Systems Integration Librarian NYU Health Sciences Libraries 550 First Ave., New York, NY 10016 (212) 263-8687 ------------------------------------------------------------ This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain information that is proprietary, confidential, and exempt from disclosure under applicable law. Any unauthorized review, use, disclosure, or distribution is prohibited. If you have received this email in error please notify the sender by return email and delete the original message. Please note, the recipient should check this email and any attachments for the presence of viruses. The organization accepts no liability for any damage caused by any virus transmitted by this email. ================================= _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha.org<mailto:Koha-devel@lists.koha.org> http://lists.koha.org/mailman/listinfo/koha-devel </PRE> <html> <body> ------------------------------------------------------------<br /> This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain information that is proprietary, confidential, and exempt from disclosure under applicable law. Any unauthorized review, use, disclosure, or distribution is prohibited. If you have received this email in error please notify the sender by return email and delete the original message. Please note, the recipient should check this email and any attachments for the presence of viruses. The organization accepts no liability for any damage caused by any virus transmitted by this email.<br /> ================================= </body> </html> <PRE>
Sessions are created for every OPAC session, too, even if they're not logged in with a login/password. And consequently there's no way to log those out. On Wed, Feb 17, 2010 at 11:14 AM, Walls, Ian <Ian.Walls@med.nyu.edu> wrote:
Clay,
Thanks! That sounds like an easy enough enhancement to make. I’ll make it a point to keep an eye on the sessions table until then, and remind my coworkers to logout properly.
Cheers,
-Ian
*From:* koha-devel-bounces@lists.koha.org [mailto: koha-devel-bounces@lists.koha.org] *On Behalf Of *Clay Fouts *Sent:* Wednesday, February 17, 2010 2:04 PM *To:* Koha-devel *Subject:* Re: [Koha-devel] Koha sessions table is ridiculously huge
You can also use "truncate sessions" to do the job if you just want to clear out the contents of the table. It's faster and simpler than using mysqldump.
This issue arises from the fact that the way Koha is written, if a user just walks away from a session without logging out, their session data will hang out in the the sessions table forever. A proper solution would involve adding a timestamp column to the sessions table, enabling one to cull only expired sessions entirely within SQL statements. With the current schema, you'd have to parse the contents of each row within a user program, then cull based on the expiry contained within the session data itself.
Cheers,
Clay
2010/2/17 Walls, Ian <Ian.Walls@med.nyu.edu>
Developers,
After performing a software upgrade and server reboot, I came across this problem. My koha.sessions table is over 13 million entries. This seems to indicate that entries into the table were never removed, and that may help to explain why my backup mysqldumps are increasing by around 4MB daily.
Chris Cormack suggested that I do a mysqldump of sessions with the no-data option set, then reimport it into the database to clear it out. I’ve finally gotten sessions to tell me what indexes it has on it, so that can be recreated as well. Are there any risks to doing this that I’m not factoring in?
More generally, what could be causing my sessions table to get so out of hand? Permissions, perhaps? Something in the code being off?
Any ideas or feedback would be appreciated. Thanks!
Ian Walls
Systems Integration Librarian
NYU Health Sciences Libraries
550 First Ave., New York, NY 10016
(212) 263-8687
------------------------------------------------------------ This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain information that is proprietary, confidential, and exempt from disclosure under applicable law. Any unauthorized review, use, disclosure, or distribution is prohibited. If you have received this email in error please notify the sender by return email and delete the original message. Please note, the recipient should check this email and any attachments for the presence of viruses. The organization accepts no liability for any damage caused by any virus transmitted by this email. =================================
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha.org http://lists.koha.org/mailman/listinfo/koha-devel
------------------------------------------------------------ This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain information that is proprietary, confidential, and exempt from disclosure under applicable law. Any unauthorized review, use, disclosure, or distribution is prohibited. If you have received this email in error please notify the sender by return email and delete the original message. Please note, the recipient should check this email and any attachments for the presence of viruses. The organization accepts no liability for any damage caused by any virus transmitted by this email. =================================
Hi, 2010/2/17 Clay Fouts <clay.fouts@liblime.com>:
You can also use "truncate sessions" to do the job if you just want to clear out the contents of the table. It's faster and simpler than using mysqldump.
Also, in HEAD there's a little script to truncate sessions as well as get rid of old zebraqueue entries. It's called misc/cronjobs/cleanup_database.pl, and usage is cleanup_database.pl --sessions --zebraqueue {# of days back to purge} I don't think it's in the 3.0.x branch, but it should work in any 3.0.x database without modification.
This issue arises from the fact that the way Koha is written, if a user just walks away from a session without logging out, their session data will hang out in the the sessions table forever. A proper solution would involve adding a timestamp column to the sessions table, enabling one to cull only expired sessions entirely within SQL statements. With the current schema, you'd have to parse the contents of each row within a user program, then cull based on the expiry contained within the session data itself.
I agree, this would be useful, either done within Koha itself or sent to the maintainers of CGI::Session. Regards, Galen -- Galen Charlton gmcharlt@gmail.com
2010/2/18 Clay Fouts <clay.fouts@liblime.com>:
You can also use "truncate sessions" to do the job if you just want to clear out the contents of the table. It's faster and simpler than using mysqldump.
This issue arises from the fact that the way Koha is written, if a user just walks away from a session without logging out, their session data will hang out in the the sessions table forever. A proper solution would involve adding a timestamp column to the sessions table, enabling one to cull only expired sessions entirely within SQL statements. With the current schema, you'd have to parse the contents of each row within a user program, then cull based on the expiry contained within the session data itself. Cheers, Clay
I suggested drop and recreate, because for a table that huge, truncate will take a lot lot longer. But as a cron job, definitely truncate, followed by optimize Chris
It depends, actually. For tables without FK constraints (like Koha's "sessions"), truncation is instantaneous in MySQL. On Wed, Feb 17, 2010 at 12:02 PM, Chris Cormack <chris@bigballofwax.co.nz>wrote:
2010/2/18 Clay Fouts <clay.fouts@liblime.com>:
You can also use "truncate sessions" to do the job if you just want to clear out the contents of the table. It's faster and simpler than using mysqldump.
This issue arises from the fact that the way Koha is written, if a user just walks away from a session without logging out, their session data will hang out in the the sessions table forever. A proper solution would involve adding a timestamp column to the sessions table, enabling one to cull only expired sessions entirely within SQL statements. With the current schema, you'd have to parse the contents of each row within a user program, then cull based on the expiry contained within the session data itself. Cheers, Clay
I suggested drop and recreate, because for a table that huge, truncate will take a lot lot longer. But as a cron job, definitely truncate, followed by optimize
Chris
Le 17/02/2010 21:10, Chris Cormack a écrit :
2010/2/18 Clay Fouts <clay.fouts@liblime.com>:
It depends, actually. For tables without FK constraints (like Koha's "sessions"), truncation is instantaneous in MySQL.
Not when they tried it yesterday :-)
It does depend, InnoDB can do some strange things.
including not freeing space in the file system even if you truncate the table :( -- Paul POULAIN http://www.biblibre.com Expert en Logiciels Libres pour l'info-doc Tel : (33) 4 91 81 35 08
On Thu, Feb 18, 2010 at 5:05 AM, Paul Poulain <paul.poulain@biblibre.com> wrote:
Le 17/02/2010 21:10, Chris Cormack a écrit :
2010/2/18 Clay Fouts <clay.fouts@liblime.com>:
It depends, actually. For tables without FK constraints (like Koha's "sessions"), truncation is instantaneous in MySQL.
Not when they tried it yesterday :-)
It does depend, InnoDB can do some strange things.
including not freeing space in the file system even if you truncate the table :(
Ouch! this would seem to be a mysql bug. Is there no way to ensure the drive space is freed up? Kind Regards, Chris
Hi, On Thu, Feb 18, 2010 at 6:47 AM, Chris Nighswonger <cnighswonger@foundations.edu> wrote:
including not freeing space in the file system even if you truncate the table :(
Ouch! this would seem to be a mysql bug. Is there no way to ensure the drive space is freed up?
It's an issue with the InnoDB storage engine, not MySQL per se. However, reclaiming space is easy enough, since by default a separate InnoDB file is created for each table: just drop and recreate the sessions table. You can also use MyISAM instead of InnoDB for that table. Regards, Galen -- Galen Charlton gmcharlt@gmail.com
Hi, Le 18/02/2010 13:42, Galen Charlton a écrit :
Hi,
On Thu, Feb 18, 2010 at 6:47 AM, Chris Nighswonger <cnighswonger@foundations.edu> wrote:
including not freeing space in the file system even if you truncate the table :(
Ouch! this would seem to be a mysql bug. Is there no way to ensure the drive space is freed up?
It's an issue with the InnoDB storage engine, not MySQL per se. However, reclaiming space is easy enough, since by default a separate InnoDB file is created for each table: just drop and recreate the sessions table. You can also use MyISAM instead of InnoDB for that table.
Usually, in all our installs we change the engine for "session" and "zebraqueue" tables to MyISAM. And add a cronjob that clean the both tables every night. bests, -- Nahuel ANGELINETTI
On Thu, Feb 18, 2010 at 7:46 AM, Nahuel ANGELINETTI <nahuel.angelinetti@biblibre.com> wrote:
Hi,
Le 18/02/2010 13:42, Galen Charlton a écrit :
Hi,
On Thu, Feb 18, 2010 at 6:47 AM, Chris Nighswonger <cnighswonger@foundations.edu> wrote:
including not freeing space in the file system even if you truncate the table :(
Ouch! this would seem to be a mysql bug. Is there no way to ensure the drive space is freed up?
It's an issue with the InnoDB storage engine, not MySQL per se. However, reclaiming space is easy enough, since by default a separate InnoDB file is created for each table: just drop and recreate the sessions table. You can also use MyISAM instead of InnoDB for that table.
Usually, in all our installs we change the engine for "session" and "zebraqueue" tables to MyISAM. And add a cronjob that clean the both tables every night.
It sounds to me like we ought to consider changing the engine for these tables in kohastructure.sql. Kind Regards, Chris
On 18/02/10 12:42, Galen Charlton wrote:
However, reclaiming space is easy enough, since by default a separate InnoDB file is created for each table: just drop and recreate the sessions table. You can also use MyISAM instead of InnoDB for that table. I'm not sure it is the default. It requires innodb_file_per_table to be set in your config (some distributions may do that for you).
Colin (who has cursed this feature when freeing up space in the early hours of the morning in the past) -- Colin Campbell Chief Software Engineer, PTFS Europe Limited Content Management and Library Solutions +44 (0) 208 366 1295 (phone) +44 (0) 7759 633626 (mobile) colin.campbell@ptfs-europe.com skype: colin_campbell2 http://www.ptfs-europe.com
On 18/02/10 11:47, Chris Nighswonger wrote:
On Thu, Feb 18, 2010 at 5:05 AM, Paul Poulain<paul.poulain@biblibre.com> wrote:
Le 17/02/2010 21:10, Chris Cormack a écrit :
2010/2/18 Clay Fouts<clay.fouts@liblime.com>:
It depends, actually. For tables without FK constraints (like Koha's "sessions"), truncation is instantaneous in MySQL.
Not when they tried it yesterday :-)
It does depend, InnoDB can do some strange things.
including not freeing space in the file system even if you truncate the table :(
Ouch! this would seem to be a mysql bug. Is there no way to ensure the drive space is freed up? mysql says no http://bugs.mysql.com/bug.php?id=1287
If you have everything in one ibdata file it can be difficult to recover space. Colin -- Colin Campbell Chief Software Engineer, PTFS Europe Limited Content Management and Library Solutions +44 (0) 208 366 1295 (phone) +44 (0) 7759 633626 (mobile) colin.campbell@ptfs-europe.com skype: colin_campbell2 http://www.ptfs-europe.com
Hi, On Thu, Feb 18, 2010 at 7:45 AM, Colin Campbell <colin.campbell@ptfs-europe.com> wrote:
If you have everything in one ibdata file it can be difficult to recover space.
Yes, that's why is a good idea to use a separate file for each InnoDB table; you can enable this by turning on the innodb_file_per_table option in the MySQL configuration file. If you're using a single InnoDB tablespace now, however, making the switch would involve either dropping and recreating the database from a mysqldump or loading the data into a new database or a replicate, pointing Koha to it, and dropping the old database. Regards, Galen -- Galen Charlton gmcharlt@gmail.com
And since you must backup daily your Koha DB, you should truncate sessions table just before mysqldump-ing in order to backup as few sessions data as possible. -- Frédéric
Definitely. Even though the session table may in many cases be much smaller than other tables in terms of bytes or rows, the frequency at which it is written to causes it to be heavily fragmented on the disk, which greatly reduces the average throughput during a mysqldump, increasing the backup time. On Wed, Feb 17, 2010 at 12:10 PM, Frederic Demians <frederic@tamil.fr>wrote:
And since you must backup daily your Koha DB, you should truncate sessions table just before mysqldump-ing in order to backup as few sessions data as possible. -- Frédéric
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha.org http://lists.koha.org/mailman/listinfo/koha-devel
participants (9)
-
Chris Cormack -
Chris Nighswonger -
Clay Fouts -
Colin Campbell -
Frederic Demians -
Galen Charlton -
Nahuel ANGELINETTI -
Paul Poulain -
Walls, Ian