Hi,
Thanks all for your help.
>>
---Database & Table wise Size in MB---
SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table',
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2),"
Mb") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = "dbname";
>>---Database & Table wise Size in GB---
SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table',
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024 /
1024),3)," Gb") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA =
"dbname";
Result shows the EMPTY SET.
On Tue, Aug 10, 2010 at 10:20 AM, Anirudh Sundar
<sundar.anir...@gmail.com>wrote:
> Hello Pratiksha,
>
> To get the uptime of the MYSQL instance :-
>
> mysql>\s
>
> as given above just give the above command " \s "
>
> For Total number of users connected to server :-
>
> mysql>show global status like '%user%';
>
> ---Database & Table wise Size in MB---
> SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table',
> CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2),"
> Mb") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = "dbname";
>
> ---Database & Table wise Size in GB---
> SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table',
> CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024 /
> 1024),3)," Gb") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA =
> "dbname";
>
> ****Data size, index size & no. of tables, engine type*********
> SELECT s.schema_name,t.engine,
> CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),"Mb") as
> Data_size, CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),"Mb")
> as Index_size,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 not
> in("mysql","information_schema","test") GROUP BY s.schema_name,t.engine
> order by Data_size DESC;
>
> For top slow queries :-
>
> mysqldumpslow -s c -a -t 5 mysqlslow.log > top10_slow-count_envr.log
>
> For Engine Info of a table and other details :-
>
> Show table status like 'tablename';
>
> Cheers,
> Anirudh Sundar
>
> On Mon, Aug 9, 2010 at 9:31 PM, PRATIKSHA JAISWAL <
> pratikshadjayswa...@gmail.com> wrote:
>
>> Hi All,
>>
>> How can i get following information from database or is there any query
>> for
>> the same.
>>
>> (1) mysql server uptime
>> (2) Total number of users connected to server
>> (3) Data file information / where it is located through mysql prompt /
>> size
>> of data file
>> (4) each Database size
>> (5) Database I/O information
>> (6) Invalid object in database
>> (7) Database performance statistics queries
>> (8) Top 5 queries taking more time for executions.
>> (9) Engine information.
>>
>>
>> --
>> Thanks
>> Pratiksha
>>
>
>
