Gossamer Forum: General: Perl Programming: Table Size

Jul 25, 2002, 1:39 PM

Paul

Veteran (19537 posts)

Jul 25, 2002, 1:39 PM

Post #1 of 9

Shortcut

Table Size

What is the best way to find a mysql table size in perl (and quickest)?

I'm currently counting the bytes in every column I want to total up, is there a better way?

Code:
my $sth = $DB->table('Table')->select( {}, 'Col' ); 
while (my $col = $sth->fetchrow) { 
         $total += length($col); 
}

Jul 25, 2002, 2:01 PM

Aki

Staff / Moderator (429 posts)

Jul 25, 2002, 2:01 PM

Post #2 of 9

Shortcut

Re: [Paul] Table Size In reply to

Depends on what you want. The approximate table size on the filesystem or just for the series of records selected?

Jul 25, 2002, 2:08 PM

Paul

Veteran (19537 posts)

Jul 25, 2002, 2:08 PM

Post #3 of 9

Shortcut

Re: [Aki] Table Size In reply to

Either/or

Jul 25, 2002, 2:14 PM

Aki

Staff / Moderator (429 posts)

Jul 25, 2002, 2:14 PM

Post #4 of 9

Shortcut

Re: [Paul] Table Size In reply to

Two ways I can see:

-s test on the MYD file

Or DESCRIBE Tablename; and then calcuate average size based on each summing column sizes... then multipling that by the number of rows.

There's nothing easy though

Jul 25, 2002, 3:01 PM

Paul

Veteran (19537 posts)

Jul 25, 2002, 3:01 PM

Post #5 of 9

Shortcut

Re: [Aki] Table Size In reply to

Bah

Thanks for the suggestions, I guess I'll have to do the second one or stick with my while loop as locating the MYD file is a bit of a task :(

Jul 25, 2002, 3:07 PM

Aki

Staff / Moderator (429 posts)

Jul 25, 2002, 3:07 PM

Post #6 of 9

Shortcut

Re: [Paul] Table Size In reply to

My mistake, easier than I thought:

Quote:

`SHOW TABLE STATUS'
...................

SHOW TABLE STATUS [FROM db_name] [LIKE wild]

`SHOW TABLE STATUS' (new in Version 3.23) works likes `SHOW STATUS',
but provides a lot of information about each table. You can also get
this list using the `mysqlshow --status db_name' command. The
following columns are returned:

*Column* *Meaning*
`Name' Name of the table.
`Type' Type of table. *Note Table types::.
`Row_format' The row storage format (Fixed, Dynamic, or
Compressed).
`Rows' Number of rows.
`Avg_row_length' Average row length.
`Data_length' Length of the data file.
`Max_data_length' Max length of the data file.
`Index_length' Length of the index file.
`Data_free' Number of allocated but not used bytes.
`Auto_increment' Next autoincrement value.
`Create_time' When the table was created.
`Update_time' When the data file was last updated.
`Check_time' When the table was last checked.
`Create_options' Extra options used with `CREATE TABLE'.
`Comment' The comment used when creating the table (or some
information why MySQL couldn't access the table
information).

`InnoDB' tables will report the free space in the tablespace in the
table comment.

Jul 25, 2002, 3:29 PM

Paul

Veteran (19537 posts)

Jul 25, 2002, 3:29 PM

Post #7 of 9

Shortcut

Re: [Aki] Table Size In reply to

Thanks...this is the output incase anyone is wondering....

Code:
$VAR = [ 
   { 
      'Auto_increment' => '', 
      'Avg_row_length' => '9', 
      'Check_time' => '', 
      'Comment' => '', 
      'Create_options' => '', 
      'Create_time' => '2002-06-23 14:21:30', 
      'Data_free' => '0', 
      'Data_length' => '9', 
      'Index_length' => '2048', 
      'Max_data_length' => '38654705663', 
      'Name' => 'allocation', 
      'Row_format' => 'Fixed', 
      'Rows' => '1', 
      'Type' => 'MyISAM', 
      'Update_time' => '2002-06-23 14:32:18' 
   } 
];

Jul 25, 2002, 4:55 PM

Alex

Administrator (9387 posts)

Jul 25, 2002, 4:55 PM

Post #8 of 9

Shortcut

Re: [Paul] Table Size In reply to

Quote:

Thanks for the suggestions, I guess I'll have to do the second one or stick with my while loop as locating the MYD file is a bit of a task :(

If it's a one time thing, `mysqladmin var | grep datadir` will give it to you (or run SHOW VARIABLES). You probably need to be root to do a size test on the myd file (you should also add the size of the indexes too if it's for a quota).

Cheers,

Alex
--
Gossamer Threads Inc.

Jul 25, 2002, 5:10 PM

Paul

Veteran (19537 posts)

Jul 25, 2002, 5:10 PM

Post #9 of 9

Shortcut

Re: [Alex] Table Size In reply to

I decided to add it to my sql module (using the SHOW TABLE STATUS query)

So now I can do:

my $tabs = $DB->table( string or arrayref of tables )->show;

...and it returns a hashref of hashrefs. I can then do:

$tabs->{table_name}->{Data_length}

Last edited by:

Paul: Jul 25, 2002, 5:11 PM