List:General Discussion« Previous MessageNext Message »
From:Prabhat Kumar Date:August 11 2010 12:09pm
Subject:Re: info on mysql
View as plain text  
check this,


http://adminlinux.blogspot.com/2009/12/mysql-tips-calculate-database-and-table.html

Thx

On Wed, Aug 11, 2010 at 5:23 PM, PRATIKSHA JAISWAL <
pratikshadjayswal21@stripped> wrote:

> 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.anirudh@stripped>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 <
> > 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
> >>
> >
> >
>



-- 
Best Regards,

Prabhat Kumar
MySQL DBA

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

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