Calculating disk space usage per MySQL DB





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}







23















I am currently using information_schema.TABLES to calculate the total disk space usage grouped by the database name, but it is running terribly slowly. On servers with hundreds of databases, it can take minutes to calculate.



What is the quickest method of calculating disk space usage by database? Should I just be looking at the filesystem? Is there a method for speeding up information_schema?










share|improve this question





























    23















    I am currently using information_schema.TABLES to calculate the total disk space usage grouped by the database name, but it is running terribly slowly. On servers with hundreds of databases, it can take minutes to calculate.



    What is the quickest method of calculating disk space usage by database? Should I just be looking at the filesystem? Is there a method for speeding up information_schema?










    share|improve this question

























      23












      23








      23


      17






      I am currently using information_schema.TABLES to calculate the total disk space usage grouped by the database name, but it is running terribly slowly. On servers with hundreds of databases, it can take minutes to calculate.



      What is the quickest method of calculating disk space usage by database? Should I just be looking at the filesystem? Is there a method for speeding up information_schema?










      share|improve this question














      I am currently using information_schema.TABLES to calculate the total disk space usage grouped by the database name, but it is running terribly slowly. On servers with hundreds of databases, it can take minutes to calculate.



      What is the quickest method of calculating disk space usage by database? Should I just be looking at the filesystem? Is there a method for speeding up information_schema?







      mysql






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Mar 4 '12 at 1:35









      GoldenNewbyGoldenNewby

      240127




      240127






















          5 Answers
          5






          active

          oldest

          votes


















          40














          There are 3 scenarios.




          1. If you are using MyISAM, it is easiest to just look at the filesystem and use du -sh /var/lib/mysql/database.

          2. If you are using InnoDB with innodb_file_per_table set, then you can get an approximate answer using du -sh. It is approximate because there is still some data stored in the ibdata1 file, so you will be a little on the low side. This technique also works with mixed MyISAM/InnoDB (innodb_file_per_table) databases.

          3. If you are using InnoDB without innodb_file_per_table set, then you will need to look at INFORMATION_SCHEMA.


          In any of the cases above, you can run the following query to get the information that you are looking for.



          mysql> select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;
          +--------------------+-----------------+
          | table_schema | MB |
          +--------------------+-----------------+
          | prod | 298025.72448921 |
          | information_schema | 0.00781248 |
          | maatkit | 70.77330779 |
          | mysql | 0.66873168 |
          | test | 4752.31449127 |
          +--------------------+-----------------+
          5 rows in set (0.01 sec)


          If you have a very large number of tables, it can be slow, as you have already discovered.






          share|improve this answer
























          • I saw somewhere else that option 3 doesn't take into account VARCHAR sizes.

            – Joe
            Apr 21 '15 at 10:48



















          1














          For getting info on the name of the table and number of records it has, the below query can be used,



          SELECT * 
          FROM information_schema.TABLES ;


          For getting info on databases on the servers with their respective size, the below query can be used,



          SELECT 
          TABLE_SCHEMA AS `Database`,
          SUM((data_length + index_length) / (1024 * 1024)) AS `Database_Size`
          FROM information_schema.TABLES
          GROUP BY table_schema
          ORDER BY `Database_Size` DESC;





          share|improve this answer































            1














            In order for me to see where disk space is being used up (regardless if it's in a mysql table or not), I use my trusty "du" command. Here's an example of me finding where all the space is being eaten up from.



            $ sudo du -cks /* | sort -rn
            954881224 total
            945218092 /mysql
            5299904 /usr
            1781376 /opt
            1166488 /var
            671628 /home
            343332 /run
            213400 /root
            93476 /lib
            30784 /boot
            20652 /etc
            15940 /bin
            13708 /sbin
            12388 /tmp
            24 /mnt
            16 /lost+found
            4 /srv
            4 /snap
            4 /media
            4 /lib64
            0 /vmlinuz
            0 /sys
            0 /proc
            0 /initrd.img
            0 /dev


            You can see that the majority of the space is being used by this folder.
            /mysql



            That folder holds data tables. In order to see which tables are taking all the space you can proceed like this using the "human" or "-h" option. I like to do disk space management this way because sometimes you cannot even log into mysql because you don't know the password or user.



            $ sudo du -chs /mysql/*
            2.3M /mysql/blacklist
            18M /mysql/clientservices
            2.5G /mysql/data
            4.0K /mysql/doubleverify
            137G /mysql/ias
            4.0K /mysql/IAS
            2.2G /mysql/innodb
            16K /mysql/lost+found
            4.0K /mysql/ml_centroids
            16G /mysql/moat
            4.0K /mysql/test
            4.0K /mysql/tmp
            4.0K /mysql/var
            282G /mysql/verticaAdFees
            4.0K /mysql/verticaViewability
            247G /mysql/Whiteops
            217G /mysql/Whiteops_TLX
            902G total


            You can see that all space is being hogged by a few tables holding many GiG's of data. Hope this helps.






            share|improve this answer































              0














              I would look for the size of the file on your data dictionnary. Its instantaneous and accurate.



              Warning: According to the storage engine, indexes are stored within the main file or in another file don't forget to sum them up if needed.






              share|improve this answer



















              • 2





                Yeah, but where is that?

                – Magne
                Dec 10 '14 at 13:24



















              0














              I know this is old but someone may find this relevant.



              In MySQL I use:



              SELECT concat(table_schema) 'Database Name', concat(round(SUM(data_length/power(1024,3)),2),'G') DATA, concat(round(SUM(index_length/power(1024,3)),2),'G') 'INDEX', concat(round(SUM(data_free/power(1024,3)),2),'G') 'DATA FREE', concat(round(sum(data_free)/(SUM(data_length+index_length))*100,2)) '% FRAGMENTED', concat(round(SUM(data_length+index_length)/power(1024,3),2),'G') TOTAL FROM information_schema.TABLES WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') GROUP BY table_schema;


              Since my DB is InnoDB, this is just an estimate.



              I compare this output to:



              du -sch /location/of_Mysql/* | sort -hr | head -n20


              Hope this helps you





              share








              New contributor




              AJinSD is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
              Check out our Code of Conduct.





















                Your Answer








                StackExchange.ready(function() {
                var channelOptions = {
                tags: "".split(" "),
                id: "182"
                };
                initTagRenderer("".split(" "), "".split(" "), channelOptions);

                StackExchange.using("externalEditor", function() {
                // Have to fire editor after snippets, if snippets enabled
                if (StackExchange.settings.snippets.snippetsEnabled) {
                StackExchange.using("snippets", function() {
                createEditor();
                });
                }
                else {
                createEditor();
                }
                });

                function createEditor() {
                StackExchange.prepareEditor({
                heartbeatType: 'answer',
                autoActivateHeartbeat: false,
                convertImagesToLinks: false,
                noModals: true,
                showLowRepImageUploadWarning: true,
                reputationToPostImages: null,
                bindNavPrevention: true,
                postfix: "",
                imageUploader: {
                brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
                contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
                allowUrls: true
                },
                onDemand: true,
                discardSelector: ".discard-answer"
                ,immediatelyShowMarkdownHelp:true
                });


                }
                });














                draft saved

                draft discarded


















                StackExchange.ready(
                function () {
                StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f14337%2fcalculating-disk-space-usage-per-mysql-db%23new-answer', 'question_page');
                }
                );

                Post as a guest















                Required, but never shown

























                5 Answers
                5






                active

                oldest

                votes








                5 Answers
                5






                active

                oldest

                votes









                active

                oldest

                votes






                active

                oldest

                votes









                40














                There are 3 scenarios.




                1. If you are using MyISAM, it is easiest to just look at the filesystem and use du -sh /var/lib/mysql/database.

                2. If you are using InnoDB with innodb_file_per_table set, then you can get an approximate answer using du -sh. It is approximate because there is still some data stored in the ibdata1 file, so you will be a little on the low side. This technique also works with mixed MyISAM/InnoDB (innodb_file_per_table) databases.

                3. If you are using InnoDB without innodb_file_per_table set, then you will need to look at INFORMATION_SCHEMA.


                In any of the cases above, you can run the following query to get the information that you are looking for.



                mysql> select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;
                +--------------------+-----------------+
                | table_schema | MB |
                +--------------------+-----------------+
                | prod | 298025.72448921 |
                | information_schema | 0.00781248 |
                | maatkit | 70.77330779 |
                | mysql | 0.66873168 |
                | test | 4752.31449127 |
                +--------------------+-----------------+
                5 rows in set (0.01 sec)


                If you have a very large number of tables, it can be slow, as you have already discovered.






                share|improve this answer
























                • I saw somewhere else that option 3 doesn't take into account VARCHAR sizes.

                  – Joe
                  Apr 21 '15 at 10:48
















                40














                There are 3 scenarios.




                1. If you are using MyISAM, it is easiest to just look at the filesystem and use du -sh /var/lib/mysql/database.

                2. If you are using InnoDB with innodb_file_per_table set, then you can get an approximate answer using du -sh. It is approximate because there is still some data stored in the ibdata1 file, so you will be a little on the low side. This technique also works with mixed MyISAM/InnoDB (innodb_file_per_table) databases.

                3. If you are using InnoDB without innodb_file_per_table set, then you will need to look at INFORMATION_SCHEMA.


                In any of the cases above, you can run the following query to get the information that you are looking for.



                mysql> select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;
                +--------------------+-----------------+
                | table_schema | MB |
                +--------------------+-----------------+
                | prod | 298025.72448921 |
                | information_schema | 0.00781248 |
                | maatkit | 70.77330779 |
                | mysql | 0.66873168 |
                | test | 4752.31449127 |
                +--------------------+-----------------+
                5 rows in set (0.01 sec)


                If you have a very large number of tables, it can be slow, as you have already discovered.






                share|improve this answer
























                • I saw somewhere else that option 3 doesn't take into account VARCHAR sizes.

                  – Joe
                  Apr 21 '15 at 10:48














                40












                40








                40







                There are 3 scenarios.




                1. If you are using MyISAM, it is easiest to just look at the filesystem and use du -sh /var/lib/mysql/database.

                2. If you are using InnoDB with innodb_file_per_table set, then you can get an approximate answer using du -sh. It is approximate because there is still some data stored in the ibdata1 file, so you will be a little on the low side. This technique also works with mixed MyISAM/InnoDB (innodb_file_per_table) databases.

                3. If you are using InnoDB without innodb_file_per_table set, then you will need to look at INFORMATION_SCHEMA.


                In any of the cases above, you can run the following query to get the information that you are looking for.



                mysql> select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;
                +--------------------+-----------------+
                | table_schema | MB |
                +--------------------+-----------------+
                | prod | 298025.72448921 |
                | information_schema | 0.00781248 |
                | maatkit | 70.77330779 |
                | mysql | 0.66873168 |
                | test | 4752.31449127 |
                +--------------------+-----------------+
                5 rows in set (0.01 sec)


                If you have a very large number of tables, it can be slow, as you have already discovered.






                share|improve this answer













                There are 3 scenarios.




                1. If you are using MyISAM, it is easiest to just look at the filesystem and use du -sh /var/lib/mysql/database.

                2. If you are using InnoDB with innodb_file_per_table set, then you can get an approximate answer using du -sh. It is approximate because there is still some data stored in the ibdata1 file, so you will be a little on the low side. This technique also works with mixed MyISAM/InnoDB (innodb_file_per_table) databases.

                3. If you are using InnoDB without innodb_file_per_table set, then you will need to look at INFORMATION_SCHEMA.


                In any of the cases above, you can run the following query to get the information that you are looking for.



                mysql> select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;
                +--------------------+-----------------+
                | table_schema | MB |
                +--------------------+-----------------+
                | prod | 298025.72448921 |
                | information_schema | 0.00781248 |
                | maatkit | 70.77330779 |
                | mysql | 0.66873168 |
                | test | 4752.31449127 |
                +--------------------+-----------------+
                5 rows in set (0.01 sec)


                If you have a very large number of tables, it can be slow, as you have already discovered.







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Mar 4 '12 at 21:57









                Aaron BrownAaron Brown

                4,4221823




                4,4221823













                • I saw somewhere else that option 3 doesn't take into account VARCHAR sizes.

                  – Joe
                  Apr 21 '15 at 10:48



















                • I saw somewhere else that option 3 doesn't take into account VARCHAR sizes.

                  – Joe
                  Apr 21 '15 at 10:48

















                I saw somewhere else that option 3 doesn't take into account VARCHAR sizes.

                – Joe
                Apr 21 '15 at 10:48





                I saw somewhere else that option 3 doesn't take into account VARCHAR sizes.

                – Joe
                Apr 21 '15 at 10:48













                1














                For getting info on the name of the table and number of records it has, the below query can be used,



                SELECT * 
                FROM information_schema.TABLES ;


                For getting info on databases on the servers with their respective size, the below query can be used,



                SELECT 
                TABLE_SCHEMA AS `Database`,
                SUM((data_length + index_length) / (1024 * 1024)) AS `Database_Size`
                FROM information_schema.TABLES
                GROUP BY table_schema
                ORDER BY `Database_Size` DESC;





                share|improve this answer




























                  1














                  For getting info on the name of the table and number of records it has, the below query can be used,



                  SELECT * 
                  FROM information_schema.TABLES ;


                  For getting info on databases on the servers with their respective size, the below query can be used,



                  SELECT 
                  TABLE_SCHEMA AS `Database`,
                  SUM((data_length + index_length) / (1024 * 1024)) AS `Database_Size`
                  FROM information_schema.TABLES
                  GROUP BY table_schema
                  ORDER BY `Database_Size` DESC;





                  share|improve this answer


























                    1












                    1








                    1







                    For getting info on the name of the table and number of records it has, the below query can be used,



                    SELECT * 
                    FROM information_schema.TABLES ;


                    For getting info on databases on the servers with their respective size, the below query can be used,



                    SELECT 
                    TABLE_SCHEMA AS `Database`,
                    SUM((data_length + index_length) / (1024 * 1024)) AS `Database_Size`
                    FROM information_schema.TABLES
                    GROUP BY table_schema
                    ORDER BY `Database_Size` DESC;





                    share|improve this answer













                    For getting info on the name of the table and number of records it has, the below query can be used,



                    SELECT * 
                    FROM information_schema.TABLES ;


                    For getting info on databases on the servers with their respective size, the below query can be used,



                    SELECT 
                    TABLE_SCHEMA AS `Database`,
                    SUM((data_length + index_length) / (1024 * 1024)) AS `Database_Size`
                    FROM information_schema.TABLES
                    GROUP BY table_schema
                    ORDER BY `Database_Size` DESC;






                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Nov 16 '15 at 12:46









                    MathewMathew

                    1211




                    1211























                        1














                        In order for me to see where disk space is being used up (regardless if it's in a mysql table or not), I use my trusty "du" command. Here's an example of me finding where all the space is being eaten up from.



                        $ sudo du -cks /* | sort -rn
                        954881224 total
                        945218092 /mysql
                        5299904 /usr
                        1781376 /opt
                        1166488 /var
                        671628 /home
                        343332 /run
                        213400 /root
                        93476 /lib
                        30784 /boot
                        20652 /etc
                        15940 /bin
                        13708 /sbin
                        12388 /tmp
                        24 /mnt
                        16 /lost+found
                        4 /srv
                        4 /snap
                        4 /media
                        4 /lib64
                        0 /vmlinuz
                        0 /sys
                        0 /proc
                        0 /initrd.img
                        0 /dev


                        You can see that the majority of the space is being used by this folder.
                        /mysql



                        That folder holds data tables. In order to see which tables are taking all the space you can proceed like this using the "human" or "-h" option. I like to do disk space management this way because sometimes you cannot even log into mysql because you don't know the password or user.



                        $ sudo du -chs /mysql/*
                        2.3M /mysql/blacklist
                        18M /mysql/clientservices
                        2.5G /mysql/data
                        4.0K /mysql/doubleverify
                        137G /mysql/ias
                        4.0K /mysql/IAS
                        2.2G /mysql/innodb
                        16K /mysql/lost+found
                        4.0K /mysql/ml_centroids
                        16G /mysql/moat
                        4.0K /mysql/test
                        4.0K /mysql/tmp
                        4.0K /mysql/var
                        282G /mysql/verticaAdFees
                        4.0K /mysql/verticaViewability
                        247G /mysql/Whiteops
                        217G /mysql/Whiteops_TLX
                        902G total


                        You can see that all space is being hogged by a few tables holding many GiG's of data. Hope this helps.






                        share|improve this answer




























                          1














                          In order for me to see where disk space is being used up (regardless if it's in a mysql table or not), I use my trusty "du" command. Here's an example of me finding where all the space is being eaten up from.



                          $ sudo du -cks /* | sort -rn
                          954881224 total
                          945218092 /mysql
                          5299904 /usr
                          1781376 /opt
                          1166488 /var
                          671628 /home
                          343332 /run
                          213400 /root
                          93476 /lib
                          30784 /boot
                          20652 /etc
                          15940 /bin
                          13708 /sbin
                          12388 /tmp
                          24 /mnt
                          16 /lost+found
                          4 /srv
                          4 /snap
                          4 /media
                          4 /lib64
                          0 /vmlinuz
                          0 /sys
                          0 /proc
                          0 /initrd.img
                          0 /dev


                          You can see that the majority of the space is being used by this folder.
                          /mysql



                          That folder holds data tables. In order to see which tables are taking all the space you can proceed like this using the "human" or "-h" option. I like to do disk space management this way because sometimes you cannot even log into mysql because you don't know the password or user.



                          $ sudo du -chs /mysql/*
                          2.3M /mysql/blacklist
                          18M /mysql/clientservices
                          2.5G /mysql/data
                          4.0K /mysql/doubleverify
                          137G /mysql/ias
                          4.0K /mysql/IAS
                          2.2G /mysql/innodb
                          16K /mysql/lost+found
                          4.0K /mysql/ml_centroids
                          16G /mysql/moat
                          4.0K /mysql/test
                          4.0K /mysql/tmp
                          4.0K /mysql/var
                          282G /mysql/verticaAdFees
                          4.0K /mysql/verticaViewability
                          247G /mysql/Whiteops
                          217G /mysql/Whiteops_TLX
                          902G total


                          You can see that all space is being hogged by a few tables holding many GiG's of data. Hope this helps.






                          share|improve this answer


























                            1












                            1








                            1







                            In order for me to see where disk space is being used up (regardless if it's in a mysql table or not), I use my trusty "du" command. Here's an example of me finding where all the space is being eaten up from.



                            $ sudo du -cks /* | sort -rn
                            954881224 total
                            945218092 /mysql
                            5299904 /usr
                            1781376 /opt
                            1166488 /var
                            671628 /home
                            343332 /run
                            213400 /root
                            93476 /lib
                            30784 /boot
                            20652 /etc
                            15940 /bin
                            13708 /sbin
                            12388 /tmp
                            24 /mnt
                            16 /lost+found
                            4 /srv
                            4 /snap
                            4 /media
                            4 /lib64
                            0 /vmlinuz
                            0 /sys
                            0 /proc
                            0 /initrd.img
                            0 /dev


                            You can see that the majority of the space is being used by this folder.
                            /mysql



                            That folder holds data tables. In order to see which tables are taking all the space you can proceed like this using the "human" or "-h" option. I like to do disk space management this way because sometimes you cannot even log into mysql because you don't know the password or user.



                            $ sudo du -chs /mysql/*
                            2.3M /mysql/blacklist
                            18M /mysql/clientservices
                            2.5G /mysql/data
                            4.0K /mysql/doubleverify
                            137G /mysql/ias
                            4.0K /mysql/IAS
                            2.2G /mysql/innodb
                            16K /mysql/lost+found
                            4.0K /mysql/ml_centroids
                            16G /mysql/moat
                            4.0K /mysql/test
                            4.0K /mysql/tmp
                            4.0K /mysql/var
                            282G /mysql/verticaAdFees
                            4.0K /mysql/verticaViewability
                            247G /mysql/Whiteops
                            217G /mysql/Whiteops_TLX
                            902G total


                            You can see that all space is being hogged by a few tables holding many GiG's of data. Hope this helps.






                            share|improve this answer













                            In order for me to see where disk space is being used up (regardless if it's in a mysql table or not), I use my trusty "du" command. Here's an example of me finding where all the space is being eaten up from.



                            $ sudo du -cks /* | sort -rn
                            954881224 total
                            945218092 /mysql
                            5299904 /usr
                            1781376 /opt
                            1166488 /var
                            671628 /home
                            343332 /run
                            213400 /root
                            93476 /lib
                            30784 /boot
                            20652 /etc
                            15940 /bin
                            13708 /sbin
                            12388 /tmp
                            24 /mnt
                            16 /lost+found
                            4 /srv
                            4 /snap
                            4 /media
                            4 /lib64
                            0 /vmlinuz
                            0 /sys
                            0 /proc
                            0 /initrd.img
                            0 /dev


                            You can see that the majority of the space is being used by this folder.
                            /mysql



                            That folder holds data tables. In order to see which tables are taking all the space you can proceed like this using the "human" or "-h" option. I like to do disk space management this way because sometimes you cannot even log into mysql because you don't know the password or user.



                            $ sudo du -chs /mysql/*
                            2.3M /mysql/blacklist
                            18M /mysql/clientservices
                            2.5G /mysql/data
                            4.0K /mysql/doubleverify
                            137G /mysql/ias
                            4.0K /mysql/IAS
                            2.2G /mysql/innodb
                            16K /mysql/lost+found
                            4.0K /mysql/ml_centroids
                            16G /mysql/moat
                            4.0K /mysql/test
                            4.0K /mysql/tmp
                            4.0K /mysql/var
                            282G /mysql/verticaAdFees
                            4.0K /mysql/verticaViewability
                            247G /mysql/Whiteops
                            217G /mysql/Whiteops_TLX
                            902G total


                            You can see that all space is being hogged by a few tables holding many GiG's of data. Hope this helps.







                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Jun 4 '18 at 22:58









                            Russell LegoRussell Lego

                            111




                            111























                                0














                                I would look for the size of the file on your data dictionnary. Its instantaneous and accurate.



                                Warning: According to the storage engine, indexes are stored within the main file or in another file don't forget to sum them up if needed.






                                share|improve this answer



















                                • 2





                                  Yeah, but where is that?

                                  – Magne
                                  Dec 10 '14 at 13:24
















                                0














                                I would look for the size of the file on your data dictionnary. Its instantaneous and accurate.



                                Warning: According to the storage engine, indexes are stored within the main file or in another file don't forget to sum them up if needed.






                                share|improve this answer



















                                • 2





                                  Yeah, but where is that?

                                  – Magne
                                  Dec 10 '14 at 13:24














                                0












                                0








                                0







                                I would look for the size of the file on your data dictionnary. Its instantaneous and accurate.



                                Warning: According to the storage engine, indexes are stored within the main file or in another file don't forget to sum them up if needed.






                                share|improve this answer













                                I would look for the size of the file on your data dictionnary. Its instantaneous and accurate.



                                Warning: According to the storage engine, indexes are stored within the main file or in another file don't forget to sum them up if needed.







                                share|improve this answer












                                share|improve this answer



                                share|improve this answer










                                answered Mar 4 '12 at 21:34









                                SpredzySpredzy

                                1,63811724




                                1,63811724








                                • 2





                                  Yeah, but where is that?

                                  – Magne
                                  Dec 10 '14 at 13:24














                                • 2





                                  Yeah, but where is that?

                                  – Magne
                                  Dec 10 '14 at 13:24








                                2




                                2





                                Yeah, but where is that?

                                – Magne
                                Dec 10 '14 at 13:24





                                Yeah, but where is that?

                                – Magne
                                Dec 10 '14 at 13:24











                                0














                                I know this is old but someone may find this relevant.



                                In MySQL I use:



                                SELECT concat(table_schema) 'Database Name', concat(round(SUM(data_length/power(1024,3)),2),'G') DATA, concat(round(SUM(index_length/power(1024,3)),2),'G') 'INDEX', concat(round(SUM(data_free/power(1024,3)),2),'G') 'DATA FREE', concat(round(sum(data_free)/(SUM(data_length+index_length))*100,2)) '% FRAGMENTED', concat(round(SUM(data_length+index_length)/power(1024,3),2),'G') TOTAL FROM information_schema.TABLES WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') GROUP BY table_schema;


                                Since my DB is InnoDB, this is just an estimate.



                                I compare this output to:



                                du -sch /location/of_Mysql/* | sort -hr | head -n20


                                Hope this helps you





                                share








                                New contributor




                                AJinSD is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                Check out our Code of Conduct.

























                                  0














                                  I know this is old but someone may find this relevant.



                                  In MySQL I use:



                                  SELECT concat(table_schema) 'Database Name', concat(round(SUM(data_length/power(1024,3)),2),'G') DATA, concat(round(SUM(index_length/power(1024,3)),2),'G') 'INDEX', concat(round(SUM(data_free/power(1024,3)),2),'G') 'DATA FREE', concat(round(sum(data_free)/(SUM(data_length+index_length))*100,2)) '% FRAGMENTED', concat(round(SUM(data_length+index_length)/power(1024,3),2),'G') TOTAL FROM information_schema.TABLES WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') GROUP BY table_schema;


                                  Since my DB is InnoDB, this is just an estimate.



                                  I compare this output to:



                                  du -sch /location/of_Mysql/* | sort -hr | head -n20


                                  Hope this helps you





                                  share








                                  New contributor




                                  AJinSD is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                  Check out our Code of Conduct.























                                    0












                                    0








                                    0







                                    I know this is old but someone may find this relevant.



                                    In MySQL I use:



                                    SELECT concat(table_schema) 'Database Name', concat(round(SUM(data_length/power(1024,3)),2),'G') DATA, concat(round(SUM(index_length/power(1024,3)),2),'G') 'INDEX', concat(round(SUM(data_free/power(1024,3)),2),'G') 'DATA FREE', concat(round(sum(data_free)/(SUM(data_length+index_length))*100,2)) '% FRAGMENTED', concat(round(SUM(data_length+index_length)/power(1024,3),2),'G') TOTAL FROM information_schema.TABLES WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') GROUP BY table_schema;


                                    Since my DB is InnoDB, this is just an estimate.



                                    I compare this output to:



                                    du -sch /location/of_Mysql/* | sort -hr | head -n20


                                    Hope this helps you





                                    share








                                    New contributor




                                    AJinSD is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.










                                    I know this is old but someone may find this relevant.



                                    In MySQL I use:



                                    SELECT concat(table_schema) 'Database Name', concat(round(SUM(data_length/power(1024,3)),2),'G') DATA, concat(round(SUM(index_length/power(1024,3)),2),'G') 'INDEX', concat(round(SUM(data_free/power(1024,3)),2),'G') 'DATA FREE', concat(round(sum(data_free)/(SUM(data_length+index_length))*100,2)) '% FRAGMENTED', concat(round(SUM(data_length+index_length)/power(1024,3),2),'G') TOTAL FROM information_schema.TABLES WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') GROUP BY table_schema;


                                    Since my DB is InnoDB, this is just an estimate.



                                    I compare this output to:



                                    du -sch /location/of_Mysql/* | sort -hr | head -n20


                                    Hope this helps you






                                    share








                                    New contributor




                                    AJinSD is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.








                                    share


                                    share






                                    New contributor




                                    AJinSD is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.









                                    answered 3 mins ago









                                    AJinSDAJinSD

                                    1




                                    1




                                    New contributor




                                    AJinSD is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.





                                    New contributor





                                    AJinSD is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.






                                    AJinSD is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.






























                                        draft saved

                                        draft discarded




















































                                        Thanks for contributing an answer to Database Administrators Stack Exchange!


                                        • Please be sure to answer the question. Provide details and share your research!

                                        But avoid



                                        • Asking for help, clarification, or responding to other answers.

                                        • Making statements based on opinion; back them up with references or personal experience.


                                        To learn more, see our tips on writing great answers.




                                        draft saved


                                        draft discarded














                                        StackExchange.ready(
                                        function () {
                                        StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f14337%2fcalculating-disk-space-usage-per-mysql-db%23new-answer', 'question_page');
                                        }
                                        );

                                        Post as a guest















                                        Required, but never shown





















































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown

































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown







                                        Popular posts from this blog

                                        Liste der Baudenkmale in Friedland (Mecklenburg)

                                        Single-Malt-Whisky

                                        Czorneboh