Greetings, mysql> SELECT table_name, table_rows, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES where table_schema = "koha_library" and ((data_length+index_length)/1024/1024)>10 order by (data_length+index_length); +-------------------------+------------+------------+ | table_name | table_rows | Size in MB | +-------------------------+------------+------------+ | marc_subfield_structure | 48064 | 11.73 | | statistics | 26298 | 11.98 | | biblio | 96620 | 14.03 | | deleteditems | 27767 | 14.48 | | import_biblios | 136336 | 36.33 | | deletedbiblioitems | 19509 | 61.45 | | items | 122738 | 67.25 | | nozebra | 264699 | 76.66 | | import_items | 147630 | 130.19 | | biblioitems | 103188 | 343.14 | | import_records | 113028 | 659.55 | | biblioimages | 1176 | 919.47 | +-------------------------+------------+------------+ 12 rows in set (0.33 sec) So, I was looking to clean up the database a bit, so I figured I’d see what was taking up the most space. The import_{something} series of tables looked like promising clean up candidates. After all, who doesn’t want to free up 750MB or more when running in a 40GB VM? This led me to this pretty schema page: http://schema.koha-community.org/tables/import_records.html QUESTION: Would there be any problems if I deleted records associated with import_batch_id’s prior to a given date in these related import_{something} tables? Grace, Peace, Mercy, and Love, Mark Tompsett
I would suggest the cleanup_database.pl script in the cronjobs directory, except I've just looked at it and it doesn't touch the import_items or import_biblios tables. On Fri, Sep 11, 2015 at 8:22 AM, Mark Tompsett <mtompset@hotmail.com> wrote:
Greetings,
mysql> SELECT table_name, table_rows, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES where table_schema = "koha_library" and ((data_length+index_length)/1024/1024)>10 order by (data_length+index_length); +-------------------------+------------+------------+ | table_name | table_rows | Size in MB | +-------------------------+------------+------------+ | marc_subfield_structure | 48064 | 11.73 | | statistics | 26298 | 11.98 | | biblio | 96620 | 14.03 | | deleteditems | 27767 | 14.48 | | import_biblios | 136336 | 36.33 | | deletedbiblioitems | 19509 | 61.45 | | items | 122738 | 67.25 | | nozebra | 264699 | 76.66 | | import_items | 147630 | 130.19 | | biblioitems | 103188 | 343.14 | | import_records | 113028 | 659.55 | | biblioimages | 1176 | 919.47 | +-------------------------+------------+------------+ 12 rows in set (0.33 sec)
So, I was looking to clean up the database a bit, so I figured I’d see what was taking up the most space. The import_{something} series of tables looked like promising clean up candidates. After all, who doesn’t want to free up 750MB or more when running in a 40GB VM?
This led me to this pretty schema page: http://schema.koha-community.org/tables/import_records.html
*QUESTION: Would there be any problems if I deleted records associated with import_batch_id’s prior to a given date in these related import_{something} tables?*
Grace, Peace, Mercy, and Love, Mark Tompsett
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
-- Michael Hafen Washington County School District Technology Department Systems Analyst
Hi, On Fri, Sep 11, 2015 at 1:02 PM, Michael Hafen <michael.hafen@washk12.org> wrote:
I would suggest the cleanup_database.pl script in the cronjobs directory, except I've just looked at it and it doesn't touch the import_items or import_biblios tables.
Actually, it does, via the --import DAYS switch and cascading deletes. Regards, Galen -- Galen Charlton Infrastructure and Added Services Manager Equinox Software, Inc. / The Open Source Experts email: gmc@esilibrary.com direct: +1 770-709-5581 cell: +1 404-984-4366 skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org
Greetings,
I would suggest the cleanup_database.pl script in the cronjobs directory, except I've just looked at it and it doesn't touch the import_items or import_biblios tables. As Galen mentioned it does via cascades. Thanks for pointing me at the better way. GPML, Mark Tompsett
Yeah, I didn't think to check the foreign key rules. Thanks for correcting me on that :) On Fri, Sep 11, 2015 at 11:10 AM, Mark Tompsett <mtompset@hotmail.com> wrote:
Greetings,
I would suggest the cleanup_database.pl script in the cronjobs directory, except I've just looked at it and it doesn't touch the import_items or import_biblios tables.
As Galen mentioned it does via cascades. Thanks for pointing me at the better way.
GPML, Mark Tompsett
-- Michael Hafen Washington County School District Technology Department Systems Analyst
At 01:10 PM 9/11/2015 -0400, Mark Tompsett wrote:
Greetings,
I would suggest the <http://cleanup_database.pl>cleanup_database.pl script in the cronjobs directory, except I've just looked at it and it doesn't touch the import_items or import_biblios tables.
As Galen mentioned it does via cascades. Thanks for pointing me at the better way.
+1 (at least for Z39.50.) However for "cleaning up" purposes on a limited VM machine, what's the size of your inno file? On my sandbox (perhaps a tad smaller than your example): root@[my_sandbox]:/var/lib/mysql# ls -l total 1282100 -rw-rw---- 1 mysql mysql 1302331392 Sep 11 17:45 ibdata1 which is significant (nearly 4x the clean [1] sql dump at 354330649.) (To get rid of it in production, you have to delete all MySQL tables before removing this overweight monstrosity, then restore your dumps - takes only a few seconds on a fast machine, so I've set up the cron for "idle hours". Note this is a MySQL problem, not Koha; there was some talk that MySQL 5.6 would alleviate it, but my experience is that it does not -- only partiallytested, we're still using 5.5.44 in production.) Best -- Paul [1] I have things set up to run: ./bin/cronjobs/cleanup_database.pl --import 1 && ./bin/cronjobs/cleanup_database.pl --mail && ./bin/cronjobs/cleanup_database.pl --sessions && ./bin/cronjobs/cleanup_database.pl --logs 10 && ./bin/cronjobs/cleanup_database.pl --zebraqueue 1 before mysqldump --user=[munged] --password=[munged] > [munged]/backup/kohadump_{date}.sql
Paul A schreef op vr 11-09-2015 om 18:37 [-0400]:
-rw-rw---- 1 mysql mysql 1302331392 Sep 11 17:45 ibdata1
This is solved by using this option: https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb... which I'd recommend setting. It seems like it might be a default in some newer versions. -- Robin Sheat Catalyst IT Ltd. ✆ +64 4 803 2204 GPG: 5FA7 4B49 1E4D CAA4 4C38 8505 77F5 B724 F871 3BDF
+1 for "default in some newer versions". sb Robin Sheat writes:
Paul A schreef op vr 11-09-2015 om 18:37 [-0400]:
-rw-rw---- 1 mysql mysql 1302331392 Sep 11 17:45 ibdata1
This is solved by using this option:
https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb...
which I'd recommend setting. It seems like it might be a default in some newer versions.
-- Robin Sheat Catalyst IT Ltd. ✆ +64 4 803 2204 GPG: 5FA7 4B49 1E4D CAA4 4C38 8505 77F5 B724 F871 3BDF
At 04:05 PM 9/17/2015 +0200, Stefano Bargioni wrote:
+1 for "default in some newer versions". sb Robin Sheat writes:
Paul A schreef op vr 11-09-2015 om 18:37 [-0400]:
-rw-rw---- 1 mysql mysql 1302331392 Sep 11 17:45 ibdata1 This is solved by using this option: https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb...
which I'd recommend setting. It seems like it might be a default in some newer versions.
When we upgraded our servers earlier this year (12.04LTS to 14.04LTS) we tried upgrading to MySQL 5.6 (from 5.5.44). Time did not permit sorting out all the glitches (notably date/timestamps which produced hard errors, but from memory there were a few more details) which involved Koha as well as other db's on the same servers. We did try the innodb_file_per_table in 5.5 (but you have to ALTER all the tables to get the benefits) and the sum total of all the individual files was a little bigger than the original ibdata1 -- so whatever the other benefits might have been, "spacesaving" (which was my original reply) was not one of them. YMMV ... and upgrading to 5.6 is still very much on my "jobs to do" list. Paul
I also looked at MySql 5.6 when I upgraded to 14.04. I actually used it for many months. However I found that the Ubuntu packages were lagging far behind the source release when there were security updates. So I decided, for the sake of my own sanity, to downgrade back to 5.5. YMMV. The point of using the innodb_file_per_table option is that it becomes easier to reclaim space in the innodb file. Instead of dumping and re-importing all the databases to reclaim space you only have to do that with a few tables. On Thu, Sep 17, 2015 at 10:06 AM, Paul A <paul.a@navalmarinearchive.com> wrote:
At 04:05 PM 9/17/2015 +0200, Stefano Bargioni wrote:
+1 for "default in some newer versions". sb Robin Sheat writes:
Paul A schreef op vr 11-09-2015 om 18:37 [-0400]:
-rw-rw---- 1 mysql mysql 1302331392 Sep 11 17:45 ibdata1
This is solved by using this option:
https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb... which I'd recommend setting. It seems like it might be a default in some newer versions.
When we upgraded our servers earlier this year (12.04LTS to 14.04LTS) we tried upgrading to MySQL 5.6 (from 5.5.44). Time did not permit sorting out all the glitches (notably date/timestamps which produced hard errors, but from memory there were a few more details) which involved Koha as well as other db's on the same servers.
We did try the innodb_file_per_table in 5.5 (but you have to ALTER all the tables to get the benefits) and the sum total of all the individual files was a little bigger than the original ibdata1 -- so whatever the other benefits might have been, "spacesaving" (which was my original reply) was not one of them.
YMMV ... and upgrading to 5.6 is still very much on my "jobs to do" list.
Paul _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
-- Michael Hafen Washington County School District Technology Department Systems Analyst
Paul A schreef op do 17-09-2015 om 12:06 [-0400]:
We did try the innodb_file_per_table in 5.5 (but you have to ALTER all the tables to get the benefits) and the sum total of all the individual files was a little bigger than the original ibdata1 -- so whatever the other benefits might have been, "spacesaving" (which was my original reply) was not one of them.
You don't have to alter, a dump, drop database, and restore will sort it out. Space saving isn't strictly the goal. If you don't have file-per-table then free space from deleting rows can't be reclaimed, so the ibdata1 file will never shrink. This can mean that if, say, you empty that giant table of old staged records, you don't get that space back unless you're using file-per-table. -- Robin Sheat Catalyst IT Ltd. ✆ +64 4 803 2204 GPG: 5FA7 4B49 1E4D CAA4 4C38 8505 77F5 B724 F871 3BDF
Hie, nice SQL query, I keep it :D Le 11/09/2015 16:22, Mark Tompsett a écrit :
Greetings,
mysql> SELECT table_name, table_rows, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES where table_schema = "koha_library" and ((data_length+index_length)/1024/1024)>10 order by (data_length+index_length); +-------------------------+------------+------------+ | table_name | table_rows | Size in MB | +-------------------------+------------+------------+ | marc_subfield_structure | 48064 | 11.73 | | statistics | 26298 | 11.98 | | biblio | 96620 | 14.03 | | deleteditems | 27767 | 14.48 | | import_biblios | 136336 | 36.33 | | deletedbiblioitems | 19509 | 61.45 | | items | 122738 | 67.25 | | nozebra | 264699 | 76.66 | | import_items | 147630 | 130.19 | | biblioitems | 103188 | 343.14 | | import_records | 113028 | 659.55 | | biblioimages | 1176 | 919.47 | +-------------------------+------------+------------+ 12 rows in set (0.33 sec)
So, I was looking to clean up the database a bit, so I figured I’d see what was taking up the most space. The import_{something} series of tables looked like promising clean up candidates. After all, who doesn’t want to free up 750MB or more when running in a 40GB VM?
This led me to this pretty schema page: http://schema.koha-community.org/tables/import_records.html
QUESTION: Would there be any problems if I deleted records associated with import_batch_id’s prior to a given date in these related import_{something} tables?
Grace, Peace, Mercy, and Love, Mark Tompsett
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
-- Fridolin SOMERS Biblibre - Pôles support et système fridolin.somers@biblibre.com
participants (7)
-
Fridolin SOMERS -
Galen Charlton -
Mark Tompsett -
Michael Hafen -
Paul A -
Robin Sheat -
Stefano Bargioni