List:General Discussion« Previous MessageNext Message »
From:Anirudh Sundar Date:August 10 2010 4:50am
Subject:Re: info on mysql
View as plain text  
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 <
pratikshadjayswal21@stripped> 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
>

Thread
info on mysqlPRATIKSHA JAISWAL9 Aug
  • RE: info on mysqlGavin Towey9 Aug
  • Re: info on mysqlAnirudh Sundar10 Aug
    • Re: info on mysqlPRATIKSHA JAISWAL11 Aug
      • Re: info on mysqlPrabhat Kumar11 Aug
Re: info on mysqlPrabhat Kumar11 Aug