Recent exchanges on "how big a hard disk do you need" (associated possibly with bug 11830) prompted me to have a look for "big" files on our production box. The worst offender appears to be /var/lib/mysql with the "oracle bug" for ibdata: -rw-rw---- 1 mysql mysql 1335885824 Feb 28 18:41 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Feb 28 18:41 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Feb 28 18:41 ib_logfile1 This has been running for 437 days, so I had a look at the sandbox (last completely rebuilt less than two months ago when I looked into git), and the results are not much better: -rw-rw---- 1 mysql mysql 1117782016 Feb 28 18:43 ibdata1 [log files identical] Two questions if I may. First, are my numbers typical? Second, has MariaDB been shelved permanently? Thanks and best regards, Paul
Hi, On Fri, Feb 28, 2014 at 10:52 AM, Paul A <paul.a@navalmarinearchive.com> wrote: [snip]
-rw-rw---- 1 mysql mysql 1117782016 Feb 28 18:43 ibdata1 [log files identical]
Two questions if I may. First, are my numbers typical?
Hard to say without knowing how many bibs you have. Regardless, if you're not using the innodb_file_per_table option, you should be, as it allows one to recover space from individual tables without having to reload the entire database.
Second, has MariaDB been shelved permanently?
Far from it. At present, I've seen no issues using MariaDB 5.5 as a drop-in replacement for MySQL, either on my personal development boxes or in production use. Regards, Galen -- Galen Charlton Manager of Implementation 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
For reference, here is the file sizes and some table sizes from my database: ls -l *.ibd|sort -k 5 -n -r|head -n 28-rw-rw---- 1 mysql mysql 2654994432 Feb 28 13:30 action_logs.ibd -rw-rw---- 1 mysql mysql 1551892480 Feb 28 12:53 import_records.ibd -rw-rw---- 1 mysql mysql 1283457024 Feb 28 13:30 biblioitems.ibd -rw-rw---- 1 mysql mysql 801112064 Feb 28 13:28 deleteditems.ibd -rw-rw---- 1 mysql mysql 511705088 Feb 26 10:28 deletedbiblioitems.ibd -rw-rw---- 1 mysql mysql 490733568 Feb 28 13:30 zebraqueue.ibd -rw-rw---- 1 mysql mysql 440401920 Feb 6 14:55 patronimage.ibd -rw-rw---- 1 mysql mysql 197132288 Feb 28 13:30 statistics.ibd -rw-rw---- 1 mysql mysql 197132288 Feb 28 13:25 auth_header.ibd -rw-rw---- 1 mysql mysql 163577856 Feb 28 13:29 old_issues.ibd -rw-rw---- 1 mysql mysql 142606336 Feb 27 15:22 import_items.ibd -rw-rw---- 1 mysql mysql 83886080 Oct 24 2012 sessions.ibd -rw-rw---- 1 mysql mysql 75497472 Feb 28 13:30 items.ibd -rw-rw---- 1 mysql mysql 58720256 Feb 28 12:53 import_biblios.ibd -rw-rw---- 1 mysql mysql 50331648 Feb 28 13:25 biblio.ibd -rw-rw---- 1 mysql mysql 30408704 Feb 26 10:28 deletedbiblio.ibd -rw-rw---- 1 mysql mysql 27262976 Feb 28 13:30 accountlines.ibd -rw-rw---- 1 mysql mysql 20971520 Feb 28 13:30 borrowers.ibd -rw-rw---- 1 mysql mysql 15728640 Feb 28 13:30 issues.ibd -rw-rw---- 1 mysql mysql 15728640 Feb 28 12:05 search_history.ibd -rw-rw---- 1 mysql mysql 15728640 Feb 28 11:15 message_queue.ibd -rw-rw---- 1 mysql mysql 12582912 Feb 27 15:21 import_record_matches.ibd -rw-rw---- 1 mysql mysql 11534336 Feb 28 11:33 old_reserves.ibd -rw-rw---- 1 mysql mysql 10485760 Feb 28 13:01 creator_batches.ibd -rw-rw---- 1 mysql mysql 9437184 Oct 24 2012 auth_subfield_structure.ibd -rw-rw---- 1 mysql mysql 9437184 Nov 7 14:27 deletedborrowers.ibd -rw-rw---- 1 mysql mysql 9437184 Feb 28 12:58 accountoffsets.ibd -rw-rw---- 1 mysql mysql 9437184 Feb 26 12:39 borrower_message_preferences.ibd mysql> select count(*) from biblio; +----------+ | count(*) | +----------+ | 121725 | +----------+ 1 row in set (0.02 sec) mysql> select count(*) from items; +----------+ | count(*) | +----------+ | 201075 | +----------+ 1 row in set (0.10 sec) mysql> select count(*) from borrowers; +----------+ | count(*) | +----------+ | 33620 | +----------+ Notice that this is with innodb_file_per_table on. I estimate close to 8 Gb of data there. And add another 2.5 Gb on another server for the Zebradb directory. Also note that this is Koha 3.2.x with a lot of local modifications by myself. On Fri, Feb 28, 2014 at 12:29 PM, Galen Charlton <gmc@esilibrary.com> wrote:
Hi,
On Fri, Feb 28, 2014 at 10:52 AM, Paul A <paul.a@navalmarinearchive.com> wrote: [snip]
-rw-rw---- 1 mysql mysql 1117782016 Feb 28 18:43 ibdata1 [log files identical]
Two questions if I may. First, are my numbers typical?
Hard to say without knowing how many bibs you have.
Regardless, if you're not using the innodb_file_per_table option, you should be, as it allows one to recover space from individual tables without having to reload the entire database.
Second, has MariaDB been shelved permanently?
Far from it. At present, I've seen no issues using MariaDB 5.5 as a drop-in replacement for MySQL, either on my personal development boxes or in production use.
Regards,
Galen -- Galen Charlton Manager of Implementation 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 _______________________________________________ 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/
Hi, On Fri, Feb 28, 2014 at 12:37 PM, Michael Hafen <michael.hafen@washk12.org> wrote: [snip]
-rw-rw---- 1 mysql mysql 1551892480 Feb 28 12:53 import_records.ibd [snip] -rw-rw---- 1 mysql mysql 490733568 Feb 28 13:30 zebraqueue.ibd
One thing to note is that if you use cleanup_database.pl to do things like trim the zebraqueue table or purge old staged import records, following up with an occasional 'optimize table' will recover the disk space -- particularly if you've never run cleanup_database.pl at all. Regards, Galen -- Galen Charlton Manager of Implementation 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
participants (3)
-
Galen Charlton -
Michael Hafen -
Paul A