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 < pratikshadjayswa...@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 > <sundar.anir...@gmail.com>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 < > > pratikshadjayswa...@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
