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.

  • 1
    Is your innodb_file_per_table ON or OFF? Also, what's the size of the ibdata1 file? – Jesus Uzcanga Oct 15 '19 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 '19 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 '19 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 '19 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 '19 at 18:53
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 | |
  • 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 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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