Hello Gary / Nagios team,<br><br>Thank you Gary Sir,<br><br>I got the answer and it's correct:<br><br>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_schema
WHERE s.schema_name = "nagios_objects" GROUP BY s.schema_name ORDER BY pct_used DESC\G<br><br>Thanks,<br>Vadiraj<br><br><br><br><br><div class="gmail_quote">On Dec 25, 2007 3:07 AM, Gary Every <<a href="mailto:gevery@gmail.com">
gevery@gmail.com</a>> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">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.
<br><font color="#888888">
<br>G.~</font><div><div></div><div class="Wj3C7c"><br><br><br><div class="gmail_quote">On Dec 24, 2007 2:15 PM, vadi <<a href="mailto:vadi.ksdba@gmail.com" target="_blank">vadi.ksdba@gmail.com</a>> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hi Gary / Nagios Team,<br>
<p>Thanks for all your supports.</p><p>Irrespective of the engine (InnoDB, MyISAM), I am not able to calculate table size and DB size<br><br>Do you have any perl/bash script to do or please help me how to find this information?
<br></p><p>Regards,<br>Vadiraj</p><p><br></p>
<div class="gmail_quote">On Dec 23, 2007 9:00 PM, Gary Every <<a href="mailto:gevery@gmail.com" target="_blank">gevery@gmail.com</a>> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
You should be able to get all the information you need thusly:<br><br>In your script grab all the databases:<br>$sql = "SHOW DATABASES";<br>// or<br><br>// Count the return rows<br>// Iterate through them with info from the information_schema.tables table:
<br><br>SELECT * from information_schema WHERE TABLE_SCHEMA = 'db_name';<br><br>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
<br><br>information_schema is a great place to get info about all your tables - use it<br><br><br>You could probably get all the info you need using the following SINGLE query:<br><br>$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";
<br><br>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)<br><br><br>+----------+--------------------+-------------+<br>| count(1) | DB_Name | Engine_Type |
<br>+----------+--------------------+-------------+<br>| 58 | cacti | MyISAM | <br>| 2 | events | InnoDB | <br>| 4 | information_schema | MyISAM | <br>| 15 | mysql | MyISAM |
<br>| 24 | ocsweb | InnoDB | <br>| 12 | ocsweb | MyISAM | <br>+----------+--------------------+-------------+<br><br><br><br><br><br><div class="gmail_quote"><div><div></div>
<div>On Dec 23, 2007 7:10 AM, vadi <
<a href="mailto:vadi.ksdba@gmail.com" target="_blank">vadi.ksdba@gmail.com</a>> wrote:<br></div></div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div><div></div><div>Dear All,<br><br>
Can any one help me for writing one bash script to find below content.<br>1> To find number of MySql database and there size.<br>2> To find number of tables in there corresponding databases.<br>
3> To find number of InnoDB and MyISAM tables in each databases.<br><br>I want to display this in my nagios tool.<br><br>Thanks in advance.<br><br>Regards,<br>Vadiraj<br>
<br></div></div>-------------------------------------------------------------------------<br>This SF.net email is sponsored by: Microsoft<br>Defy all challenges. Microsoft(R) Visual Studio 2005.<br><a href="http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/" target="_blank">
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/</a><br>_______________________________________________<br>Nagios-users mailing list<br><a href="mailto:Nagios-users@lists.sourceforge.net" target="_blank">Nagios-users@lists.sourceforge.net
</a><br><a href="https://lists.sourceforge.net/lists/listinfo/nagios-users" target="_blank">https://lists.sourceforge.net/lists/listinfo/nagios-users</a><br>::: Please include Nagios version, plugin version (-v) and OS when reporting any issue.
<br>::: Messages without supporting info will risk being sent to /dev/null<br></blockquote></div><font color="#888888"><br><br clear="all"><br>-- <br>Gary Every<br>"Pay it Forward!"<br>
</font></blockquote></div><br>
</blockquote></div><br><br clear="all"><br>-- <br>Gary Every<br>"Pay it Forward!"<br>
</div></div></blockquote></div><br>