Re: Re: Re: How to find size of my database
Hi Rhino, sorry ,for my unclear reply. the size i meant is in KB or MB.. Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : You still haven't said what you mean by size! Let me put it to you this way: what units would be used in the answer you want? In other words, would the answer look like: 1. 234,000 KB? 2. 2.75 million rows? 3. 321 tables? Or something else altogether? -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 4:33 PM Subject: Re: Re: How to find size of my database Hi Rhino, Thanks for the input. I mean to say the total Database Size ,that is to see my current Database Size.I am using 4.0.20 version Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : It would be easier to help if you specified what you meant by size. Do you mean the amount of space the database is using on your hard drive? Or the number of tables in the database? Or the number of rows in the tables? Or somethng else altogether? You should also mention which version of MySQL you are using; later versions have features and commands not found in earlier versions. Also, the size of a give database may differ from version to version of MySQL. -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 3:53 PM Subject: How to find size of my database Hi, is there any command to find the total size of mysql db. Regards, Shivaji. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006
Re: Re: Re: How to find size of my database
Daniel de Veiga has already answered you on how to determine the size of your database by using the file system and simply looking at the size of the physical files in your database. Another possibility is that you could use the SHOW TABLE STATUS command in MySQL. If you go to your MySQL prompt and select a database, then use the SHOW TABLE STATUS command, like this: set tmp; show table status like '%'; you'll find a column called Data_length which tells you the length of the data file for each table. If you simply add the size of each table in the database together, you should have the size of the whole database. Please note that I'm not sure how accurate my suggestion is; you might find that Daniel's approach gives you a better answer. I'm not sure if the Data_length column considers all the overhead that you might have with a table, such as index sizes, pointer sizes, unusable space, etc. Perhaps someone else can jump in with a better approach; I'm inclined to think it can't be quite as easy as I suggested. Also, even if the Data_length column gives an accurate answer for the size of a table, it is rather tedious to have to execute the SHOW TABLE STATUS command and then manually sum up the various sizes. I don't think you can simply execute an SQL query that does all the work for you, which is very unfortunate. It is entirely possible that there is a MySQL command that gives you the actual size of each database directly, although I didn't find it when I searched the manual. Again, perhaps someone with more administrative experience with MySQL can suggest a better approach. If not, perhaps we need to make a feature request of the MySQL people :-) This would appear to be a very useful command to create if it doesn't already exist! -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 5:12 PM Subject: Re: Re: Re: How to find size of my database Hi Rhino, sorry ,for my unclear reply. the size i meant is in KB or MB.. Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : You still haven't said what you mean by size! Let me put it to you this way: what units would be used in the answer you want? In other words, would the answer look like: 1. 234,000 KB? 2. 2.75 million rows? 3. 321 tables? Or something else altogether? -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 4:33 PM Subject: Re: Re: How to find size of my database Hi Rhino, Thanks for the input. I mean to say the total Database Size ,that is to see my current Database Size.I am using 4.0.20 version Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : It would be easier to help if you specified what you meant by size. Do you mean the amount of space the database is using on your hard drive? Or the number of tables in the database? Or the number of rows in the tables? Or somethng else altogether? You should also mention which version of MySQL you are using; later versions have features and commands not found in earlier versions. Also, the size of a give database may differ from version to version of MySQL. -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 3:53 PM Subject: How to find size of my database Hi, is there any command to find the total size of mysql db. Regards, Shivaji. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Re: Re: How to find size of my database
Thanks Daniel,Rhino for the wounder full explanations given.I will work on finding the size of my databases with answers given by you. Regards, Shivaji. On Wed, 03 May 2006 Rhino wrote : Daniel de Veiga has already answered you on how to determine the size of your database by using the file system and simply looking at the size of the physical files in your database. Another possibility is that you could use the SHOW TABLE STATUS command in MySQL. If you go to your MySQL prompt and select a database, then use the SHOW TABLE STATUS command, like this: set tmp; show table status like '%'; you'll find a column called Data_length which tells you the length of the data file for each table. If you simply add the size of each table in the database together, you should have the size of the whole database. Please note that I'm not sure how accurate my suggestion is; you might find that Daniel's approach gives you a better answer. I'm not sure if the Data_length column considers all the overhead that you might have with a table, such as index sizes, pointer sizes, unusable space, etc. Perhaps someone else can jump in with a better approach; I'm inclined to think it can't be quite as easy as I suggested. Also, even if the Data_length column gives an accurate answer for the size of a table, it is rather tedious to have to execute the SHOW TABLE STATUS command and then manually sum up the various sizes. I don't think you can simply execute an SQL query that does all the work for you, which is very unfortunate. It is entirely possible that there is a MySQL command that gives you the actual size of each database directly, although I didn't find it when I searched the manual. Again, perhaps someone with more administrative experience with MySQL can suggest a better approach. If not, perhaps we need to make a feature request of the MySQL people :-) This would appear to be a very useful command to create if it doesn't already exist! -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 5:12 PM Subject: Re: Re: Re: How to find size of my database Hi Rhino, sorry ,for my unclear reply. the size i meant is in KB or MB.. Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : You still haven't said what you mean by size! Let me put it to you this way: what units would be used in the answer you want? In other words, would the answer look like: 1. 234,000 KB? 2. 2.75 million rows? 3. 321 tables? Or something else altogether? -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 4:33 PM Subject: Re: Re: How to find size of my database Hi Rhino, Thanks for the input. I mean to say the total Database Size ,that is to see my current Database Size.I am using 4.0.20 version Regards, Shivaji On Wed, 03 May 2006 Rhino wrote : It would be easier to help if you specified what you meant by size. Do you mean the amount of space the database is using on your hard drive? Or the number of tables in the database? Or the number of rows in the tables? Or somethng else altogether? You should also mention which version of MySQL you are using; later versions have features and commands not found in earlier versions. Also, the size of a give database may differ from version to version of MySQL. -- Rhino - Original Message - From: Shivaji S [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 02, 2006 3:53 PM Subject: How to find size of my database Hi, is there any command to find the total size of mysql db. Regards, Shivaji. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 01/05/2006
