MySQL – Resolving Hidden Files Taking Up Space on Ubuntu

MySQLUbuntu

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.

Best Answer

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!

Related Solutions

Mysql – Moving MySQL database from UniServer to Ubuntu Server

The simplest option is to just back up everything in the filesystem and carry it over.

To find out where your data is, look at the datadir variable with in mysql

test> show variables like 'datadir';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| datadir       | /var/lib/mysql/   |
+---------------+-------------------+
1 row in set (0.00 sec)

Note Your actual value may differ.

Then from your shell shut down mysql, back it up and copy it over. Something like:

> sudo service mysql shutdown
> cd /var/lib/mysql
> sudo tar -czf /path/with/enough/space/db.tgz *

Copy your db.tgz over to your new server and uncompress it on your new servers datadir while it is shutdown

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Best Answer

Related Solutions

Mysql – Moving MySQL database from UniServer to Ubuntu Server

Mysql – Adding index to large thesql tables

Related Question