MySQL table with a lot of text

Question

So I have this database (Size 3.1Gb total), but this is due to one specific table I've got, containing A LOT of console output text, from some test runs. The table itself is 2.7Gb, and I was wondering if there could be another solution for this table, so the database would get a lot smaller? It's getting a bit anoying to backup the database or even make a copy of the database to a playground, because it's so big this table.

The Table is this one

Would it be better to delete this table and make all the LogTextData <- LongText, be stored in a PDF, instead of the database? (Then I can't backup this data tho...) Do anyone have an idea on how to make this table smaller, or another solution? I'm open for suggestions, to make this table smaller.

The way this console log data gets imported to the database is by Python scipts, so I have fully access to other python solutions, if there is any.

I could each day make a gzip of all the created logfiles, and then transfer them to another location (Cloud)? The database already gets transfered to another location, IF the server breaks, so no data is lost. BUT then i can't access it regular? This data has to be accessed on a regular basis.

Answer 1

Keeping big files on disk and just link to them from the database is a valid approach used by many software solutions.

However, 3.1G isn't terrible. So, assuming you want to keep your data in the database:

You may want to consider using mariabackup instead of mysqldump as that is faster, especially when your data grows to the gigabyte range. Note however that mariabackup is a physical backup tool, as opposed to mysqldump, which performs logical backups. So although the backups are faster, they are not as portable. If you must have logical backups only, then mydumper might be an option which can be faster than mysqldump.

Another thing to consider is column compression. MariaDB 10.3 introduced the Storage-Engine Independent Column Compression feature. Assuming this is suitable to your use-case, you can simply do something like:

ALTER TABLE `Log` 
  MODIFY LogTextData LONGTEXT COMPRESSED;

The column is compressed on disk, but is automatically decompressed when you access it through SQL queries.

See also Wagner Bianchi's blog article on this topic where he compresses a 11M table to 4M using this approach: Wagner Bianchi: The MariaDB Storage-Engine Independent Column Compression