473,569 Members | 2,692 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Showing disk usage of innodb tables?

Gh!
How can I see how much disk space is being occupied by an InnoDB table?

Looking into /var/lib/mysql doesn't seem to work anymore for InnoDB tables.

Please tell also if there is a way to see how much space is occupied by
a particular row or column of a table.

Thanks
Sep 28 '05 #1
1 5134
Gh! wrote:
How can I see how much disk space is being occupied by an InnoDB table?

Looking into /var/lib/mysql doesn't seem to work anymore for InnoDB tables.
The default configuration is for all InnoDB tables to share one
tablespace, which may consist of multiple files, but each table is
spread throughout the tablespace in a unpredictable manner.

Read this page in the docs:

http://dev.mysql.com/doc/mysql/en/in...ile-space.html

But you can also initialize the InnoDB tablespace to store each table in
a separate file.

Read this page in the docs:
http://dev.mysql.com/doc/mysql/en/mu...blespaces.html

This still doesn't tell you how much space is occupied by a given table,
because typically the files do not shrink, even if you delete a lot of
records. The space inside in marked as free -- i.e., available for
storing record versions created by future inserts or updates.

You can get a report of the free space in a tablespace with the SHOW
TABLE STATUS command (or mysqlshow --status databasename). If you use a
shared tablespace (the default), the report is for the whole shared
tablespace. If you use separate files for each InnoDB table, the report
is for the free space in the corresponding file.

Read this page in the docs:
http://dev.mysql.com/doc/mysql/en/sh...le-status.html
Please tell also if there is a way to see how much space is occupied by
a particular row or column of a table.


I don't know of a good way to get this information. You could create
another table and copy an individual row or column into it, and then get
space statistics on that table, but it might not match the space usage
of the original table precisely, because of differences in InnoDB file
fragmentation, etc.

Regards,
Bill K.
Sep 28 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
15157
by: Mark Depenbrock | last post by:
--Apple-Mail-2--68472726 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed Can not make mysql connection - error log: 030708 08:53:48 mysqld started
0
2480
by: Asif Iqbal | last post by:
Hi I am running MySQL 4.0.13 with my-medium.cnf (attached) on a Solaris 8, 4x450 Mhz, 4gb RAM. All my tables are InnoDB. Currently my ibdata1 is 1.8 gb. I have about 1 gig space left on the same partition. Also it is excruciatingly slow now. How do prevent the ibdata1 from filling up the disk partition ? How do I increase the speed on my...
0
1975
by: Rob Baxter | last post by:
I have a linux server which is hosting several very large (~20GB) databases. In order to save some disk space I dropped an index on one of the larger (InnoDB) tables because it is no longer needed. It took about 30 minutes for MySQL to drop the index, during which time I saw the free disk space decrease. I didn't think anything of it while the...
0
710
by: Lazy70 | last post by:
Hi to everyone, Ihave a hard problem to resolve. In my Mysql database there is sometihng that doesn't work. With MySQLFront I have all my InnoDB tables with this comment: "Can't open file: 'certificatetype.InnnoDB'. (errno: 1)"
3
949
by: Andy Tran | last post by:
I built a system using mysql innodb to archive SMS messages but the innodb databases are not keeping up with the number of SMS messages coming in. I'm looking for performance of 200 msgs/sec where 1 msg is 1 database row. I'm running on Red Linux: 2.4.20-8bigmem #1 SMP Thu Mar 13 17:32:29 EST 2003 i686 i686 i386 GNU/Linux The machine...
2
2908
by: Jan | last post by:
I am using Apache2triad 1.2 as local development environment. It is working fine, but I cannot create InnoDB tables with the phpMyAdmin 2.5.6 included in this package. According to the my.ini file (see below) some lines in this file must be commented or uncommented to use innoDB. If I do this, the mySQL server does not start any more. ...
0
1304
by: Mike Chirico | last post by:
Hopefully the following will be useful or interesting: TIP 29: An example of using referential integrity with InnoDB tables. Referential Integrity with InnoDB tables. STEP 1 (First create the tables as InnoDB) CREATE TABLE agents (
3
11388
by: Reuben Pearse | last post by:
Hi all, I've just converted the tables in a big database (approx 27 million records) from MyISAM to InnoDB. When I was using MyISAM I backed up the database by stopping MySQL and then copying the MYI, MYD, frm files in the database directory, and restored the database in the same way. Is there a similar way to backup and restore InnoDB...
4
2935
by: Good Man | last post by:
Hi there I have a database with about 20 or so tables, maybe a few thousand rows in each. I am starting to do more complex things with my insertions etc, and I want to start to use transactions, so I imagine I should change some table types in the database from MyISAM to InnoDB. I know that InnoDB tables are a completely different beast...
0
7612
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7924
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7673
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
5219
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3653
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3640
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2113
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1213
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
937
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.