<div id="readability-page-1" class="page"><article id="mainContent">
                <div>
                  <p><a href="/m/category/%EA%B0%9C%EB%B0%9C%28%ED%95%A9%EB%8B%88%EB%8B%A4%29/DB"><span>개발(합니다)/DB</span></a></p><h3>[MySql] DB 용량 확인하는 방법</h3>
                </div>
                <p><cite>otrodevym</cite>
                  <span> 2021. 11. 5. 00:00</span>
                </p>
                                <div>
                                <p data-ke-size="size16">서버를 이용하다보면 서버의 용량을 많이 사용하는 DB에 사용량과 여유량을 확인 해야 합니다.</p>
<p data-ke-size="size16">정기적으로 데이터를 백업해주고 여유공간을 확보해주저야지 서비스가 중단되는 이슈를 막을 수 있습니다.</p>
<p data-ke-size="size16">DB에서 사용하고 있는 용량, 데이터베이스별, 테이블별을 포스팅합니다.</p>



<h2 data-ke-size="size26">전체 용량 확인</h2>
<pre id="code_1635988861348" data-ke-language="sql" data-ke-type="codeblock"><code>SELECT SUM(data_length+index_length)/1024/1024 used_MB, SUM(data_free)/1024/1024 free_MB FROM information_schema.tables;</code></pre>
<figure data-origin-width="408" data-origin-height="166" data-ke-mobilestyle="widthOrigin">
    <span data-url="https://k.kakaocdn.net/dn/bL6zkd/btrjQKdnNVV/UQIVfwcZn3HOcGb8A0kkEK/img.png" data-lightbox="lightbox" data-alt="">
        <img src="https://k.kakaocdn.net/dn/bL6zkd/btrjQKdnNVV/UQIVfwcZn3HOcGb8A0kkEK/img.png" onerror="this.onerror=null; this.src='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png'; this.srcset='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png';" data-origin-width="408" data-origin-height="166" data-ke-mobilestyle="widthOrigin">
    </span>
    
</figure>
<h2 data-ke-size="size26">데이터베이스별 용량 확인</h2>
<pre id="code_1635988870164" data-ke-language="sql" data-ke-type="codeblock"><code>SELECT table_schema "Database Name",
SUM(data_length + index_length) / 1024 / 1024 "Size(MB)"
FROM information_schema.TABLES
GROUP BY table_schema;</code></pre>

<figure data-origin-width="471" data-origin-height="306" data-ke-mobilestyle="widthOrigin">
    <span data-url="https://k.kakaocdn.net/dn/bwA2Lk/btrjLkmz7My/wigMpFpXKSUMav4kCwRQm1/img.png" data-lightbox="lightbox" data-alt="">
        <img src="https://k.kakaocdn.net/dn/bwA2Lk/btrjLkmz7My/wigMpFpXKSUMav4kCwRQm1/img.png" onerror="this.onerror=null; this.src='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png'; this.srcset='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png';" data-origin-width="471" data-origin-height="306" data-ke-mobilestyle="widthOrigin">
    </span>
    
</figure>
<h2 data-ke-size="size26">테이블별 용량 확인</h2>
<pre id="code_1635988876117" data-ke-language="sql" data-ke-type="codeblock"><code>SELECT
concat(table_schema,'.',table_name),
concat(round(data_length/(1024*1024*1024),2),'G') DATA,
concat(round(index_length/(1024*1024*1024),2),'G') idx,
concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(index_length/data_length,2) idxfrac
FROM information_schema.TABLES
WHERE table_rows is not null;</code></pre>

<figure data-origin-width="873" data-origin-height="258" data-ke-mobilestyle="widthOrigin">
    <span data-url="https://k.kakaocdn.net/dn/Q0sFb/btrjRjs5lfr/05bMXnOe3NNzxk07dADXq0/img.png" data-lightbox="lightbox" data-alt="">
        <img src="https://k.kakaocdn.net/dn/Q0sFb/btrjRjs5lfr/05bMXnOe3NNzxk07dADXq0/img.png" onerror="this.onerror=null; this.src='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png'; this.srcset='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png';" data-origin-width="873" data-origin-height="258" data-ke-mobilestyle="widthOrigin">
    </span>
    
</figure>
            
            
                </div>
                

              </article></div>

미니 블로그 : 메모하는 습관

otrodevym

서버를 이용하다보면 서버의 용량을 많이 사용하는 DB에 사용량과 여유량을 확인 해야 합니다.
정기적으로 데이터를 백업해주고 여유공간을 확보해주저야지 서비스가 중단되는 이슈를 막을 수 있습니다.
DB에서 사용하고 있는 용량, 데이터베이스별, 테이블별을 포스팅합니다.
 
 
 
전체 용량 확인
SELECT SUM(data_length+index_length)/1024/1024 used_MB, SUM(data_free)/1024/1024 free_MB FROM information_schema.tables;
데이터베이스별 용량 확인
SELECT table_schema "Database Name",
SUM(data_length + index_length) / 1024 / 1024 "Size(MB)"
FROM information_schema.TABLES
GROUP BY table_schema;
 
테이블별 용량 확인
SELECT
concat(table_schema,'.',table_name),
concat(round(data_length/(1024*1024*1024),2),'G') DATA,
concat(round(index_length/(1024*1024*1024),2),'G') idx,
concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(index_length/data_length,2) idxfrac
FROM information_schema.TABLES
WHERE table_rows is not null;

[MySql] DB 용량 확인하는 방법

댓글