{"id":578,"date":"2006-02-15T11:20:37","date_gmt":"2006-02-15T01:20:37","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/2006\/02\/15\/information_schemafiles-querying-disk-usage-from-sql\/"},"modified":"2012-05-30T10:15:39","modified_gmt":"2012-05-30T00:15:39","slug":"information_schemafiles-querying-disk-usage-from-sql","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2006\/02\/15\/information_schemafiles-querying-disk-usage-from-sql\/","title":{"rendered":"INFORMATION_SCHEMA.FILES (querying disk usage from SQL)"},"content":{"rendered":"

In MySQL<\/a> 5.1.6 there’s a new INFORMATION_SCHEMA table.<\/p>\n

Currently, it only has information on files for NDB<\/a> but we’re hoping to change that in a future release (read: I think it would be neat).<\/p>\n

This table is a table generated by the MySQL server listing all the different files that are\/could be used by a storage engine. Three (may) be table to file mappings (or not) depending on the engine.<\/p>\n

Basically, NDB does files like so:<\/p>\n

A table is stored in a tablespace.<\/p>\n

A tablespace has datafiles.<\/p>\n

Datafiles are of a set size.<\/p>\n

Space is allocated in datafiles to tables in a unit called an extent.<\/p>\n

If you don’t have any free extents you cannot have new tables store data on disk.<\/p>\n

If you don’t have any free extents you may still be able to add data to a table as there may be free space in an extent allocated to that table.<\/p>\n

Logs (used for crash recovery) are stored in logfiles.<\/p>\n

logfiles are part of logfile groups.<\/p>\n

A tablespace uses a logfile group for logging.<\/p>\n

Try the following bits of code and running SELECT * from INFORMATION_SCHEMA.FILES<\/code> between each statement.<\/p>\n

CREATE LOGFILE GROUP lg1
\nADD UNDOFILE 'undofile.dat'
\nINITIAL_SIZE 16M
\nUNDO_BUFFER_SIZE = 1M
\nENGINE=NDB;<\/p>\n

ALTER LOGFILE GROUP lg1
\nADD UNDOFILE 'undofile02.dat'
\nINITIAL_SIZE = 4M
\nENGINE=NDB;
\n<\/code><\/p>\n

CREATE TABLESPACE ts1
\nADD DATAFILE 'datafile.dat'
\nUSE LOGFILE GROUP lg1
\nINITIAL_SIZE 12M
\nENGINE NDB;<\/p>\n

ALTER TABLESPACE ts1
\nADD DATAFILE 'datafile02.dat'
\nINITIAL_SIZE = 4M
\nENGINE=NDB;
\n<\/code><\/p>\n

CREATE TABLE t1
\n(pk1 INT NOT NULL PRIMARY KEY, b INT NOT NULL, c INT NOT NULL)
\nTABLESPACE ts1 STORAGE DISK
\nENGINE=NDB;<\/p>\n

SHOW CREATE TABLE t1;<\/p>\n

INSERT INTO t1 VALUES (0, 0, 0);
\nSELECT * FROM t1;
\n<\/code><\/p>\n

DROP TABLE t1;<\/code>
\nALTER TABLESPACE ts1
\nDROP DATAFILE 'datafile.dat'
\nENGINE = NDB;<\/p>\n

ALTER TABLESPACE ts1
\nDROP DATAFILE 'datafile02.dat'
\nENGINE = NDB;<\/p>\n

DROP TABLESPACE ts1
\nENGINE = NDB;<\/p>\n

DROP LOGFILE GROUP lg1
\nENGINE =NDB;<\/code><\/p>\n

For a point of interest, these examples are taken from the ndb_dd_basic test (which can be found in mysql-test\/t\/ndb_dd_basic.test)<\/p>\n","protected":false},"excerpt":{"rendered":"

In MySQL 5.1.6 there’s a new INFORMATION_SCHEMA table. Currently, it only has information on files for NDB but we’re hoping to change that in a future release (read: I think it would be neat). This table is a table generated … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[14],"tags":[],"class_list":["post-578","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-9k","jetpack-related-posts":[{"id":478,"url":"https:\/\/www.flamingspork.com\/blog\/2005\/10\/03\/a-funky-thing-done-last-week\/","url_meta":{"origin":578,"position":0},"title":"a funky thing done last week…","author":"Stewart Smith","date":"2005-10-03","format":false,"excerpt":"still have to talk to people about standards for this sort of thing and all that. But as a first checkin - funkyness++! mysql> select * from INFORMATION_SCHEMA.DATAFILES; select * from INFORMATION_SCHEMA.TABLESPACES; Empty set (0.03 sec) Empty set (0.00 sec) mysql> CREATE TABLESPACE ts1 ADD DATAFILE 'datafile.dat' USE LOGFILE GROUP\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":759,"url":"https:\/\/www.flamingspork.com\/blog\/2006\/11\/13\/disk-allocation-xfs-ndb-disk-data-and-more\/","url_meta":{"origin":578,"position":1},"title":"Disk allocation, XFS, NDB Disk Data and more…","author":"Stewart Smith","date":"2006-11-13","format":false,"excerpt":"I've talked about disk space allocation previously, mainly revolving around XFS (namely because it's what I use, a sensible choice for large file systems and large files and has a nice suite of tools for digging into what's going on).Most people write software that just calls write(2) (or libc things\u2026","rel":"","context":"In "linux-kernel"","block_context":{"text":"linux-kernel","link":"https:\/\/www.flamingspork.com\/blog\/category\/linux-kernel\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1229,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/10\/14\/mysql-cluster-ndb-on-win32-progress\/","url_meta":{"origin":578,"position":2},"title":"MySQL Cluster (NDB) on Win32 progress","author":"Stewart Smith","date":"2008-10-14","format":false,"excerpt":"Many things have been happenning in the land of NDB on Win32 as of late. I've fixed about 700 compiler warnings (some of which were real bugs) leaving about 161 to go on Win32 (VS2003). We're getting a few more warnings on Win64 (some of which look merely semantic, while\u2026","rel":"","context":"In "General"","block_context":{"text":"General","link":"https:\/\/www.flamingspork.com\/blog\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1650,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/05\/27\/pluggable-metadata-stores-or-the-revenge-of-table-discovery\/","url_meta":{"origin":578,"position":3},"title":"Pluggable Metadata stores (or… the revenge of table discovery)","author":"Stewart Smith","date":"2009-05-27","format":false,"excerpt":"Users of the ARCHIVE or NDB storage engines in MySQL may be aware of a MySQL feature known as \"table discovery\". For ARCHIVE, you can copy the archive data file around between servers and it magically works (you don't need to copy the FRM). For MySQL Cluster (NDB) it works\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":511,"url":"https:\/\/www.flamingspork.com\/blog\/2005\/11\/23\/disk-space-allocation-part-1-seeing-whats-happenned\/","url_meta":{"origin":578,"position":4},"title":"disk space allocation (part 1: seeing what’s happenned)","author":"Stewart Smith","date":"2005-11-23","format":false,"excerpt":"(a little while ago I was writing a really long entry on everything possible. I realised that this would be a long read for people and that less people would look at it, so I've split it up). This sprung out of doing work on the NDB disk data tree.\u2026","rel":"","context":"In "linux-kernel"","block_context":{"text":"linux-kernel","link":"https:\/\/www.flamingspork.com\/blog\/category\/linux-kernel\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":515,"url":"https:\/\/www.flamingspork.com\/blog\/2005\/11\/29\/disk-space-allocation-part-4-allocating-an-extent\/","url_meta":{"origin":578,"position":5},"title":"disk space allocation (part 4: allocating an extent)","author":"Stewart Smith","date":"2005-11-29","format":false,"excerpt":"For XFS, in normal operation, an extent is only allocated when data has to be written to disk. This is called delayed allocation. If we are extending a file by 50MB - that space is deducted from the total free space on the filesystem, but no decision on where to\u2026","rel":"","context":"In "linux-kernel"","block_context":{"text":"linux-kernel","link":"https:\/\/www.flamingspork.com\/blog\/category\/linux-kernel\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/578","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/comments?post=578"}],"version-history":[{"count":1,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/578\/revisions"}],"predecessor-version":[{"id":2963,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/578\/revisions\/2963"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=578"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=578"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=578"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}