menge-Check total space yang digunakan database MySQL

2 cara untuk mengecek total space yang digunakan database, yang pertama dengan command linux du -h cara ini sebelumnya anda harus mengetahui lokasi / path direktori data mysql dengan query SHOW GLOBAL VARIABLES LIKE ‘datadir’;

kedua dengan cara query SHOW TABLE STATUS pada MySQL versi 4.xx kebawah dan untuk MySQL versi 5.xx dengan men-JOIN table information_schema.schemata dan information_schema.tables
berikut query lengkapnya untuk MySQL versi 5.xx keatas:
SELECT s.schema_name,
CONCAT(
IFNULL( ROUND(
(SUM(t.data_length) + SUM(t.index_length)) /
1024/1024, 2), 0.00), 'Mb') total_size,
CONCAT(
IFNULL( ROUND(
( (SUM(t.data_length) + SUM(t.index_length) ) -
SUM(t.data_free)
) /1024/1024, 2), 0.00), 'Mb') data_used,
CONCAT(
IFNULL( ROUND(
SUM(data_free) / 1024/1024, 2), 0.00), 'Mb') data_free,
IFNULL( ROUND(
( ( (SUM(t.data_length) + SUM(t.index_length) ) -
SUM(t.data_free)) /
( (SUM(t.data_length) + SUM(t.index_length) ) ) * 100),
2), 0) pct_used,
COUNT(table_name) total_tables
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t
ON s.schema_name = t.table_schema
WHERE s.schema_name != 'information_schema'
GROUP BY s.schema_name
ORDER BY pct_used DESC \G

okeh bro… langsung aja kita ke lab yeh…

mysql> SHOW GLOBAL VARIABLES LIKE 'datadir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /databck1/data_mysql/ |
+---------------+-----------------------+
1 row in set (0.02 sec)

mysql> \q
Bye
bash-2.05# cd /databck1/data_mysql/
bash-2.05# du -h
 198K   ./mysql
  10K   ./smaf
 285M   ./hrngw
 131K   ./inplugin
   1K   ./test
  39K   ./mysql_db_tsel
 5.4M   ./radius/radius
 5.4M   ./radius
  20G   .
bash-2.05#

mysql> SELECT s.schema_name,
    -> CONCAT(
    ->    IFNULL( ROUND(
    ->       (SUM(t.data_length) + SUM(t.index_length) )  /
    ->       1024/1024, 2), 0.00), 'Mb') total_size,
    -> CONCAT(
    ->    IFNULL( ROUND(
    ->       ( (SUM(t.data_length)+SUM(t.index_length)) -
    ->          SUM(t.data_free)) / 1024/1024, 2), 0.00), 'Mb') data_used,
    -> CONCAT(
    ->    IFNULL( ROUND(
    ->       SUM(data_free) / 1024/1024, 2), 0.00), 'Mb') data_free,
    ->    IFNULL( ROUND(
    ->       ( ( (SUM(t.data_length) + SUM(t.index_length) ) -
    ->             SUM(t.data_free) ) /
    ->         ( (SUM(t.data_length) + SUM(t.index_length)) ) *100),
    ->       2), 0) pct_used,
    -> COUNT(table_name) total_tables
    -> FROM information_schema.schemata s
    -> LEFT JOIN information_schema.tables t
    -> ON s.schema_name = t.table_schema
    -> WHERE s.schema_name != 'information_schema'
    -> GROUP BY s.schema_name
    -> ORDER BY pct_used DESC \G
*************************** 1. row ***************************
 schema_name: hrngw
  total_size: 903.20Mb
   data_used: 903.20Mb
   data_free: 0.00Mb
    pct_used: 100.00
total_tables: 4
*************************** 2. row ***************************
 schema_name: inplugin
  total_size: 16488.60Mb
   data_used: 16488.60Mb
   data_free: 0.00Mb
    pct_used: 100.00
total_tables: 13
*************************** 3. row ***************************
 schema_name: mysql_db_tsel
  total_size: 0.02Mb
   data_used: 0.02Mb
   data_free: 0.00Mb
    pct_used: 100.00
total_tables: 2
*************************** 4. row ***************************
 schema_name: smaf
  total_size: 0.02Mb
   data_used: 0.02Mb
   data_free: 0.00Mb
    pct_used: 100.00
total_tables: 1
*************************** 5. row ***************************
 schema_name: mysql
  total_size: 0.03Mb
   data_used: 0.03Mb
   data_free: 0.00Mb
    pct_used: 98.44
total_tables: 18
*************************** 6. row ***************************
 schema_name: radius
  total_size: 0.00Mb
   data_used: 0.00Mb
   data_free: 0.00Mb
    pct_used: 0.00
total_tables: 0
*************************** 7. row ***************************
 schema_name: test
  total_size: 0.00Mb
   data_used: 0.00Mb
   data_free: 0.00Mb
    pct_used: 0.00
total_tables: 0
7 rows in set (7.01 sec)

mysql>\q
Bye

Referensi:
https://kb.mysql.com/view.php?id=6551
semoga bermanfaat…

Advertisements

About davidand

Hi, welcome to my blogs. My name is David Andriansyah. I am currently working as an independent Cognos Business Intelligence consultant, and I also as Freelance Web Developer. View all posts by davidand

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: