0

I have more than 100 dbs and I was trying to query size of all dbs using the sql below

SELECT table_schema                                        "DB Name", 
   Round(Sum(data_length + index_length) / 1024 , 1) "DB Size" 
FROM   information_schema.tables 
GROUP  BY table_schema;

Which gave me following

gpsv3_gpsv2_sscwdo 330784.0
gpsv3_pault     72704.0

When I try to find the size from folder level with command below

 sudo du  /var/lib/mysql | sort -n

I got the follwing

779804  /var/lib/mysql/gpsv3_gpsv2_sscwdo
479016  /var/lib/mysql/gpsv3_pault

I was under impression that that there should few MB difference but it is more than 5 times for gpsv3_pault . is there anything I have misunderstood ?

Thanks

sumit
  • 15,003
  • 12
  • 69
  • 110
  • You should look into those dirs, see if you have binlogs sitting around, check if you have file per table turned on (assuming you're using innodb), etc, etc. There are a bunch of answers on the topics here and probably more on [SF] – pvg Jun 29 '17 at 01:02
  • Choose one of those databases and compare the actual files in the directory to the reported size for each table. See what you find. Also, check for files with names like `#sql-nnnn*.ibd`, which could be temporary files left over from failed `ALTER TABLE` operations. – Michael - sqlbot Jun 29 '17 at 10:14
  • https://stackoverflow.com/questions/5474662/mysql-optimize-all-tables – sumit Jul 25 '17 at 04:08

0 Answers0