Just wanted to say thank you Phil for the pointers on this. I have moved the database directory for now and working on increasing the database filesystem disk space.
Cheers Chaz Chaz Vidal | ICT Infrastructure | Tel: +61-8-8128-4397 | Mob: +61-492-874-982 | chaz.vi...@sahmri.com -----Original Message----- From: Chaz Vidal <chaz.vi...@sahmri.com> Sent: Monday, 6 July 2020 2:22 PM To: Phil Stracchino <ph...@caerllewys.net> Cc: bacula-users <bacula-users@lists.sourceforge.net> Subject: Re: [Bacula-users] EXTERNAL - Re: /var/lib/mysql at 100% Very very much appreciated Phil! I'll schedule something to fix this up and we will ensure we remediate this system. I'll let you know how we go. Cheers Chaz -----Original Message----- From: Phil Stracchino <ph...@caerllewys.net> Sent: Monday, 6 July 2020 12:10 PM To: Chaz Vidal <chaz.vi...@sahmri.com> Cc: bacula-users <bacula-users@lists.sourceforge.net> Subject: Re: EXTERNAL - Re: [Bacula-users] /var/lib/mysql at 100% On 2020-07-05 19:45, Chaz Vidal wrote: > Thanks Phil, > For some reason my backups are still running and completing. Is it because > we still have table space? Probably, yes. It most likely means you have enough free space in the tablespace to keep going for now. > This is an ext4 filesystem. I have attempted a dump whilst bacula was running > and the resulting dump file was about 53% (70GB) of the existing Bacula db > directory which is 130GB. OK, but that does not mean that you have 60GB of free space to play with. Your dump contains only the data and schemas of your database, not the contents of the table indexes or unused column space. The indexes will be rebuilt by mysqld when you reload the data from your dump. A good set of indexes on a large database consumes significant space. but it is the voluminous and rapidly-traversable indexes that allow you to retrieve data from your database quickly. Indexes are at the heart of what makes a relational database better and faster than a flat file. They are what tells the database engine exactly where in your 70GB of data a single specific piece of data is stored. > > The spool directory is so much larger with 6TB available. Is this a > potential solution? > > I'm starting out with Bacula so I'm assuming that we do a "/etc/init.d/bacula > stop", run the database dump commands and then start? If you're referring to the spool directory for job spooling, it might be unwise to also put your MariaDB data directory there unless you first change your high-water setting to make sure that spooled jobs cannot encroach on your database space. With that proviso, you could do it as a temporary measure until you can make more permanent provisions to expand your database space. Here's the fastest way to accomplish the move: 1. Stop Bacula 2. Stop MariaDB 3. Create your new data directory 4. Move or copy the entire contents of /var/lib/mysql to the new directory. Make sure the new directory has the same ownership and permissions as /var/lib/mysql. 5. Edit the global MariaDB configuration files to point datadir to the new location. Also update anything else in the MariaDB configuration that refers to /var/lib/mysql (tmpdir for instance). 6. Start MariaDB and make sure it comes up correctly 7. Edit your Bacula configuration files to set a safe spool high-water mark 8. Restart Bacula This does not touch on any optimization, tuning, enabling file-per-table, or getting rid of the unwanted lost+found in the data directory, but it will get you back up and running much more quickly than reloading 70GB of data from a logical dump and does not risk missing anything during a dump-and-reload. You can prepare plans to deal with those issues later. Remember that this is a stopgap solution until you can permanently expand your dedicated database storage. A couple of things to remember there: — XFS is preferred over ext* for performance reasons (in fact, further development of the ext filesystem has been officially abandoned by Red Hat); — The MySQL/.MariaDB data directory does not have to be located at /var/lib/mysql, that's just the default; — It is advised NOT to use the root of an ext* volume as your data directory (for example, if your data volume is ext4 mounted at /mariadb, make your data directory something like /mariadb/data) — Whenever possible, database storage should be on physically separate devices from OS loads or Bacula usage to minimize I/O contention on the database. -- Phil Stracchino Babylon Communications ph...@caerllewys.net p...@co.ordinate.org Landline: +1.603.293.8485 Mobile: +1.603.998.6958 _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
