info on mysql

--0015174bea6ef0546a048d6620d1
Content-Type: text/plain; charset=ISO-8859-1

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

--0015174bea6ef0546a048d6620d1--

RE: info on mysql

For most of your questions, use:

SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;


(7) Database performance statistics queries
(8) Top 5 queries taking more time for executions.
(9) Engine information.

For these, you need to enable the slow query log, gather queries over a giv=
en interval of time, then use either mysql_dump_slow or maatkit's mk-query-=
digest to parse the log.

-----Original Message-----
From: PRATIKSHA JAISWAL [mailto:pratikshadjayswal21@gmail.com]
Sent: Monday, August 09, 2010 9:01 AM
To: mysql@lists.mysql.com
Subject: info on mysql

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

This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: info on mysql

--001636d351e1758cf7048d70dffc
Content-Type: text/plain; charset=ISO-8859-1

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),"M b") 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@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
>

--001636d351e1758cf7048d70dffc--

Re: info on mysql

--000e0cd299dade1cb2048d8ae5c8
Content-Type: text/plain; charset=ISO-8859-1

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
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),"M b") 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@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
>>
>
>

--000e0cd299dade1cb2048d8ae5c8--

Re: info on mysql

--0016364d2727a2d105048d8b1eea
Content-Type: text/plain; charset=ISO-8859-1

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@gmail.com> 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
> 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),"M b") 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@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
> >>
> >
> >
>



--
Best Regards,

Prabhat Kumar
MySQL DBA

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

--0016364d2727a2d105048d8b1eea--

Re: info on mysql

--0016e6de04ed94a3c8048d8ed737
Content-Type: text/plain; charset=ISO-8859-1

use Google :)

On Wed, Aug 11, 2010 at 10:02 PM, PRATIKSHA JAISWAL <
pratikshadjayswal21@gmail.com> 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 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@gmail.com> 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
>>> 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),"M b") 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@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
>>> >>
>>> >
>>> >
>>>
>>
>>
>>
>> --
>> 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

--0016e6de04ed94a3c8048d8ed737--