Truncating the nozebra table in koha DB

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Truncating the nozebra table in koha DB

sheldon_tappin
Hi All,


 Is it OK to truncate the nozebra table in koha database. The table is too big for a database backup.
Your response is highly appreciated.


Sheldon
Asst Systems Librarian
Reply | Threaded
Open this post in threaded view
|

Re: Truncating the nozebra table in koha DB

Henri-Damien LAURENT
Le 02/09/2011 14:49, sheldon_tappin a écrit :

> Hi All,
>
>
>  Is it OK to truncate the nozebra table in koha database. The table is too
> big for a database backup.
> Your response is highly appreciated.
>
>
> Sheldon
> Asst Systems Librarian
>
> --
> View this message in context: http://koha.1045719.n5.nabble.com/Truncating-the-nozebra-table-in-koha-DB-tp4762232p4762232.html
> Sent from the Koha - Discuss mailing list archive at Nabble.com.
> _______________________________________________
> Koha mailing list  http://koha-community.org
> [hidden email]
> http://lists.katipo.co.nz/mailman/listinfo/koha
>
Hi It is ok.
In fact, there is/was a cleanup_database script which does that in
misc/cronjobs... could be a scheduled task
check that the table is MyISAM and not innodb, unless you want to keep a
huge innodb file......
Hope that helps.
--
Henri-Damien LAURENT

_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: Truncating the nozebra table in koha DB

Robin Sheat-2
Op zaterdag 3 september 2011 01:35:42 schreef LAURENT Henri-Damien:
> check that the table is MyISAM and not innodb, unless you want to keep a
> huge innodb file......

Alternately, use innodb_file_per_table

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

--
Robin Sheat
Catalyst IT Ltd.
✆ +64 4 803 2204

_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha

signature.asc (205 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Truncating the nozebra table in koha DB

Paul A
In reply to this post by Henri-Damien LAURENT
At 04:35 PM 9/2/2011 +0200, you wrote:

>Le 02/09/2011 14:49, sheldon_tappin a écrit :
> > Hi All,
> >
> >
> >  Is it OK to truncate the nozebra table in koha database. The table is too
> > big for a database backup.
>[snip]
> >
>Hi It is ok.
>In fact, there is/was a cleanup_database script which does that in
>misc/cronjobs... could be a scheduled task
>check that the table is MyISAM and not innodb, unless you want to keep a
>huge innodb file......

Yup - that script seems to have disappeared ... my innodb is now 5.5
Gigabytes ... what on earth is happening?  (end of day, I'll have a closer
look tomorrow)

Best
Paul
---
Tired old sys-admin

_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: Truncating the nozebra table in koha DB

Chris Cormack-6

That script is for the zebraqueue table, truncating the nozebra table is only a good idea if you are using zebra, because you dont need that table with zebra.

And like Robin suggested, you should set file per table or innodb cannot reclaim the space. Or follow Henri-Damiens advice and set the table to myisam. Without doing one of these 2 things, deleting rows or truncating tables does not reclaim space.

We run all our koha with file per table, that way you dont end up with an ever increasing ibdata file.

Chris

On 3 Sep 2011 12:53, "Paul" <[hidden email]> wrote:
> At 04:35 PM 9/2/2011 +0200, you wrote:
>>Le 02/09/2011 14:49, sheldon_tappin a écrit :
>> > Hi All,
>> >
>> >
>> > Is it OK to truncate the nozebra table in koha database. The table is too
>> > big for a database backup.
>>[snip]
>> >
>>Hi It is ok.
>>In fact, there is/was a cleanup_database script which does that in
>>misc/cronjobs... could be a scheduled task
>>check that the table is MyISAM and not innodb, unless you want to keep a
>>huge innodb file......
>
> Yup - that script seems to have disappeared ... my innodb is now 5.5
> Gigabytes ... what on earth is happening? (end of day, I'll have a closer
> look tomorrow)
>
> Best
> Paul
> ---
> Tired old sys-admin
>
> _______________________________________________
> Koha mailing list http://koha-community.org
> [hidden email]
> http://lists.katipo.co.nz/mailman/listinfo/koha

_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: Truncating the nozebra table in koha DB

Paul A
In reply to this post by Paul A
At 02:00 PM 9/3/2011 +1200, Chris Cormack wrote:

>That script is for the zebraqueue table, truncating the nozebra table is
>only a good idea if you are using zebra, because you dont need that table
>with zebra.
>
>And like Robin suggested, you should set file per table or innodb cannot
>reclaim the space. Or follow Henri-Damiens advice and set the table to
>myisam. Without doing one of these 2 things, deleting rows or truncating
>tables does not reclaim space.
>
>We run all our koha with file per table, that way you dont end up with an
>ever increasing ibdata file.

Chris - thanks ... I'll try and wrap my head around this, but do you know
of a detailed description of how zebra is *supposed* to work, vice how it
*actually* works, particularly with Koha.  [the myisam vice innodb is
complex but well documented, but not wrt zebra.]

tnx - p.


>Chris
>On 3 Sep 2011 12:53, "Paul" <<mailto:[hidden email]>[hidden email]> wrote:
> > At 04:35 PM 9/2/2011 +0200, you wrote:
> >>Le 02/09/2011 14:49, sheldon_tappin a écrit :
> >> > Hi All,
> >> >
> >> >
> >> > Is it OK to truncate the nozebra table in koha database. The table
> is too
> >> > big for a database backup.
> >>[snip]
> >> >
> >>Hi It is ok.
> >>In fact, there is/was a cleanup_database script which does that in
> >>misc/cronjobs... could be a scheduled task
> >>check that the table is MyISAM and not innodb, unless you want to keep a
> >>huge innodb file......
> >
> > Yup - that script seems to have disappeared ... my innodb is now 5.5
> > Gigabytes ... what on earth is happening? (end of day, I'll have a closer
> > look tomorrow)
> >
> > Best
> > Paul
> > ---
> > Tired old sys-admin

_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: Truncating the nozebra table in koha DB

Henri-Damien LAURENT
In reply to this post by Robin Sheat-2
Le 02/09/2011 21:58, Robin Sheat a écrit :
> Op zaterdag 3 september 2011 01:35:42 schreef LAURENT Henri-Damien:
>> check that the table is MyISAM and not innodb, unless you want to keep a
>> huge innodb file......
>
> Alternately, use innodb_file_per_table
>
> http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html
Well, even with Innodb_file_per_table, the innodb data files are not
resized on truncation. For instance, if your session or zebraqueue file
grow to 1 Million entries, and taking up to 1 G for data, then
truncating would not change that file size. This can be considered as a
problem.
Since zebraqueue or sessions are tables without external constraints, I
would consider it safer to have them in MyIsam.
innodb_file_per_table is also very nice to tune your mysql access. But
those tables sessions and zebraqueue are to be considered apart from any
other.
--
Henri-Damien LAURENT

_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: Truncating the nozebra table in koha DB

Chris Cormack-6
On 5 Sep 2011 02:53, "LAURENT Henri-Damien"
<[hidden email]> wrote:

>
> Le 02/09/2011 21:58, Robin Sheat a écrit :
> > Op zaterdag 3 september 2011 01:35:42 schreef LAURENT Henri-Damien:
> >> check that the table is MyISAM and not innodb, unless you want to keep a
> >> huge innodb file......
> >
> > Alternately, use innodb_file_per_table
> >
> > http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html
> Well, even with Innodb_file_per_table, the innodb data files are not
> resized on truncation. For instance, if your session or zebraqueue file
> grow to 1 Million entries, and taking up to 1 G for data, then
> truncating would not change that file size. This can be considered as a
> problem.
> Since zebraqueue or sessions are tables without external constraints, I
> would consider it safer to have them in MyIsam.

Ahh but it does allow to reclaim the space, if you read the page Robin
linked to, thats one of the reasons to use it. It is also the reason
we use it.

After 5.1 it will reclaim the size automatically,
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-other-changes-truncate.html

Prior to 5.1 you need to run the  OPTIMIZE TABLE command after your
truncate to reclaim it.

Chris
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
http://lists.katipo.co.nz/mailman/listinfo/koha