List:General Discussion« Previous MessageNext Message »
From:Prabhat Kumar Date:August 11 2010 4:35pm
Subject:Re: info on mysql
View as plain text  
use Google :)

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

> Hi Prabhat,
>
> Thanks a ton for sharing your blogs with us.
>
> I am learning mysql database. I am very good at PostgreSQL.
>
> Do you have any other material where in i can go for Mysql Certification
> and
> mainly mysql replication set up material etc..
>
>
> --
> Thanks
> Pratiksha
>
>
> On Wed, Aug 11, 2010 at 5:39 PM, Prabhat Kumar <aim.prabhat@stripped>wrote:
>
>> 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
>>
>
>


-- 
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