0

I have a weird issue where my system (ubuntu 19) is showing that MySql 8 is using up 600gb of space, when it should be closer to 250gb.

I ran ubuntu's baobab utility in sudo mode, and it as well, is showing that MySQL is using up 250GB . Any idea what could be taking up the other 250GB?

I also ran this query via SQL, to show table sizes - same story ~250GB, so not sure what else I could try.

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 

     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

.. I do have a few indexes set up, but most of them on columns containing 0/1s

Thank you in advance for suggestions.

Improve this question
6
  • 1
    Is your innodb_file_per_table ON or OFF? Also, what's the size of the ibdata1 file?
    – Jesus Uzcanga
    Oct 15, 2019 at 14:30
  • file per table is on. Not sure how to look up the size of ibdata1 yet for mysql 8/ubuntu . But this should get me started. thanks for pointing me, at least in A direction.
    – FlyingZebra1
    Oct 15, 2019 at 15:42
  • 1
    By default mysql install datadir is located at /var/lib/mysql so you can find ibdata1 there, if not there you can find it out by running "show variables like 'datadir';"
    – Jesus Uzcanga
    Oct 16, 2019 at 19:02
  • 1
    What are the largest few files? (From this, we can more quickly diagnose what happened.) Do this from the filesystem, not SQL.
    – Rick James
    Oct 18, 2019 at 16:01
  • Thanks everyone for tips - looks like ibdata is 'only' 20ish GB, however, there are a bunch of binlog.00### files, 1 to 14GB each, so looks like that might be the culprit. going to research what those are now.
    – FlyingZebra1
    Oct 20, 2019 at 18:53

1 Answer 1

Reset to default
0

looks like this command worked for temporary solution:

PURGE BINARY LOGS BEFORE '2019-10-19 22:46:26'; #your date

(mysql manual)

Still working on the long term solution. Per mysql manual - these were the logs of select statements (which are typically huge in my case). Not 100% yet on whether I need these :) In either case, thank you everyone for helping troubleshoot. This was blowing my mind for a bit, but all it took was browsing into the folder itself, and seeing hundreds of huge files!

Improve this answer
1
  • Binary Logs do not store SELECTs. They store actual changes (INSERT,UPDATE,DELETE either as statements or row changes). The only exception is INSERT INTO ... SELECT.
    – RolandoMySQLDBA
    Aug 13, 2020 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.