Home » Open Source » MySQL » mysqldump size vs. database size (mysql-server-5.1.66-1.el6_3.x86_64)
mysqldump size vs. database size [message #582456] Thu, 18 April 2013 08:30 Go to next message
oranooob
Messages: 88
Registered: May 2009
Member
HeidiSQL shows that my DB is 7 GB size:

http://i.stack.imgur.com/GJKi3.png

MySQL says the same:
    SELECT table_schema "Data Base Name", SUM(data_length + index_length) / 1024 / 1024 "Data Base Size in MB"
    FROM information_schema.TABLES
    GROUP BY table_schema ;


result:

    "Data Base Name"	"Data Base Size in MB"
    "identityiq"	"7170.04983044"
    "information_schema"	"0.00781250"
    "mysql"	"0.63037777"


but dump output only the half size
    # mysqldump -u root -p identityiq > test.sql
    Enter password:
    # du -sh test.sql
    3.3G    test.sql


but uncompressed backup only 3.3 GB? Why this differnece?
Re: mysqldump size vs. database size [message #582458 is a reply to message #582456] Thu, 18 April 2013 09:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10613
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Indexes.
Re: mysqldump size vs. database size [message #582536 is a reply to message #582458] Fri, 19 April 2013 03:30 Go to previous message
oranooob
Messages: 88
Registered: May 2009
Member
Mahesh Rajendran wrote on Thu, 18 April 2013 09:07
Indexes.


thanks bro. You are right.

with this SQL command you see index size:

SELECT CONCAT(table_schema,'.',table_name) TABLES, CONCAT(ROUND(table_rows/1000000,2),'M') ROWS, CONCAT(ROUND(data_length/(1024*1024*1024),2),'G') data_size, CONCAT(ROUND(index_length/(1024*1024*1024),2),'G') index_size, CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),2),'G') total_size, ROUND(index_length/data_length,2) index_data_ratio
FROM information_schema.TABLES
ORDER BY total_size DESC
LIMIT 20;
Previous Topic: MySQL Cluster
Next Topic: MySQL Cluster Performance
Goto Forum:
  


Current Time: Sat Apr 19 17:57:58 CDT 2014

Total time taken to generate the page: 0.24202 seconds