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 <
[email protected]> 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
>
