mysql database details

[vadi]

Hello Gary / Nagios team,

Thank you Gary Sir,

I got the answer and it's correct:

SELECT s.schema_name,CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(
t.index_length))/1024/1024,2),0.00),"Mb")
total_size,CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length
))-SUM(t.data_free))/1024/1024,2),0.00),"Mb")
data_used,CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb")
data_free,IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(
t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0)
pct_used,COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s
LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schemaWHERE
s.schema_name = "nagios_objects" GROUP BY s.schema_name ORDER BY pct_used
DESC\G

Thanks,
Vadiraj




On Dec 25, 2007 3:07 AM, Gary Every <gevery at gmail.com> wrote:

> check out mysqlshow --status -k <db_name> from the command line, it''l
> give you a bunch of info - the "rows" and "data_length" columns are what
> you're looking for as far as sizes go.
>
> G.~
>
>
>
> On Dec 24, 2007 2:15 PM, vadi <vadi.ksdba at gmail.com> wrote:
>
> > Hi Gary / Nagios Team,
> >
> > Thanks for all your supports.
> >
> > Irrespective of the engine (InnoDB, MyISAM), I am not able to calculate
> > table size and DB size
> >
> > Do you have any perl/bash script to do or please help me how to find
> > this information?
> >
> > Regards,
> > Vadiraj
> >
> >
> > On Dec 23, 2007 9:00 PM, Gary Every <gevery at gmail.com> wrote:
> >
> > > You should be able to get all the information you need thusly:
> > >
> > > In your script grab all the databases:
> > > $sql = "SHOW DATABASES";
> > > // or
> > >
> > > // Count the return rows
> > > // Iterate through them with info from the information_schema.tables
> > > table:
> > >
> > > SELECT * from information_schema WHERE TABLE_SCHEMA = 'db_name';
> > >
> > > There are columns in there that will get you where you want to be :
> > > Specifically the ENGINE column, which tells you what type engine
> > > (innodb,myisam, etc) that table is
> > >
> > > information_schema is a great place to get info about all your tables
> > > - use it
> > >
> > >
> > > You could probably get all the info you need using the following
> > > SINGLE query:
> > >
> > > $sql = "select count(1), table_schema as Database_name, engine as
> > > Engine_Type  from information_schema.tables where engine != 'MEMORY' group
> > > by table_schema,engine";
> > >
> > > You'll get a return like this: (as you'll see the myisam and innodb
> > > table types are split out for you already, as in the ocsweb db)
> > >
> > >
> > > +----------+--------------------+-------------+
> > > | count(1) | DB_Name            | Engine_Type |
> > > +----------+--------------------+-------------+
> > > |       58 | cacti              | MyISAM      |
> > > |        2 | events             | InnoDB      |
> > > |        4 | information_schema | MyISAM      |
> > > |       15 | mysql              | MyISAM      |
> > > |       24 | ocsweb             | InnoDB      |
> > > |       12 | ocsweb             | MyISAM      |
> > > +----------+--------------------+-------------+
> > >
> > >
> > >
> > >
> > >
> > > On Dec 23, 2007 7:10 AM, vadi < vadi.ksdba at gmail.com> wrote:
> > >
> > > > Dear All,
> > > >
> > > > Can any one help me for writing one bash script to find below
> > > > content.
> > > > 1>    To find number of MySql database and there size.
> > > > 2>    To find number of tables in there corresponding databases.
> > > > 3>    To find number of InnoDB and MyISAM tables in each databases.
> > > >
> > > > I want to display this in my nagios tool.
> > > >
> > > > Thanks in advance.
> > > >
> > > > Regards,
> > > > Vadiraj
> > > >
> > > >
> > > > -------------------------------------------------------------------------
> > > > This SF.net email is sponsored by: Microsoft
> > > > Defy all challenges. Microsoft(R) Visual Studio 2005.
> > > > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> > > > _______________________________________________
> > > > Nagios-users mailing list
> > > > Nagios-users at lists.sourceforge.net
> > > > https://lists.sourceforge.net/lists/listinfo/nagios-users
> > > > ::: Please include Nagios version, plugin version (-v) and OS when
> > > > reporting any issue.
> > > > ::: Messages without supporting info will risk being sent to
> > > > /dev/null
> > > >
> > >
> > >
> > >
> > > --
> > > Gary Every
> > > "Pay it Forward!"
> > >
> >
> >
>
>
> --
> Gary Every
> "Pay it Forward!"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://www.monitoring-lists.org/archive/users/attachments/20071225/9434346a/attachment.html>
-------------- next part --------------
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
-------------- next part --------------
_______________________________________________
Nagios-users mailing list
Nagios-users at lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/nagios-users
::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. 
::: Messages without supporting info will risk being sent to /dev/null