Lately, I've had a few people ask me "How much data is in our database?". Sure, you can look at the file sizes of MyISAM tables and indexes and get a ballpark figure, but what if you need exact results, or are running InnoDB storage engine? That proves to be more challenging! In playing around with the information_schema, I've put together some queries to help:

Calculate Index Sizes

mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size'
FROM information_schema.TABLES 
WHERE table_schema LIKE 'database'; 

+------------------+
|Total Index Size  |
+------------------+
|1.70 GB           |
+------------------+

1 row in set (1.60 sec) 

Calculate the Total Size of Stored Data

mysql> SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 2), ' GB') AS 'Total Data Size' 
FROM information_schema.TABLES 
WHERE table_schema LIKE 'database';

Lately, I've had a few people ask me "How much data is in our database?". Sure, you can look at the file sizes of MyISAM tables and indexes and get a ballpark figure, but what if you need exact results, or are running InnoDB storage engine? That proves to be more challenging! In playing around with the information_schema, I've put together some queries to help:

Calculate Index Sizes

mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size'
FROM information_schema.TABLES 
WHERE table_schema LIKE 'database'; 

+------------------+
|Total Index Size  |
+------------------+
|1.70 GB           |
+------------------+

1 row in set (1.60 sec) 

Calculate the Total Size of Stored Data

mysql> SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 2), ' GB') AS 'Total Data Size' 
FROM information_schema.TABLES 
WHERE table_schema LIKE 'database';

On the front cover of MySQL Administrator’s Bible is a sentence that reads:

“The book you need to succeed!”

I must say, I do agree. Authored by two very experienced DBAs, Sheeri Cabral and Keith Murphy,
they’ve combined their talents to cover what you really need to know to
succeed. This book is very versatile. If you’re new to MySQL, or
experienced in another database and have to start administrating MySQL,
you need this book. I can honestly say, even if you have years of MySQL
experience, you will learn something new. I did. Divided into four
parts, …

On the front cover of MySQL Administrator’s Bible is a sentence that reads:

“The book you need to succeed!”

I must say, I do agree. Authored by two very experienced DBAs, Sheeri Cabral and Keith Murphy,
they’ve combined their talents to cover what you really need to know to
succeed. This book is very versatile. If you’re new to MySQL, or
experienced in another database and have to start administrating MySQL,
you need this book. I can honestly say, even if you have years of MySQL
experience, you will learn something new. I did. Divided into four
parts, …

What do you do when you're in a situation where you REALLY need your
favorite Maatkit tools, but are not allowed to load software on the
server running MySQL, AND you're not allowed to install software on the
provided workstation, especially a workstation that's running software
from a large corporation in Redmond? Portable Maatkit to the rescue!

DISCLAIMER:
I've only tested a few maatkit tools, namely mk-find and
mk-query-digest, but not completely. Some features and some tools may
not function, such as mk-audit. Use your best judgment. I'm not
responsible if your workstation or server looses all your data, your
USB key bursts into flames, or your significant other leaves you. YMMV,
use at YOUR OWN RISK!

Strawberry Perl
has been out for some time now, and recently, a portable version of
…

What do you do when you're in a situation where you REALLY need your
favorite Maatkit tools, but are not allowed to load software on the
server running MySQL, AND you're not allowed to install software on the
provided workstation, especially a workstation that's running software
from a large corporation in Redmond? Portable Maatkit to the rescue!

DISCLAIMER:
I've only tested a few maatkit tools, namely mk-find and
mk-query-digest, but not completely. Some features and some tools may
not function, such as mk-audit. Use your best judgment. I'm not
responsible if your workstation or server looses all your data, your
USB key bursts into flames, or your significant other leaves you. YMMV,
use at YOUR OWN RISK!

Strawberry Perl
has been out for some time now, and recently, a portable version of
…