Get the script from here https://dba.stackexchange.com/a/114870
cat>/tmp/row_size.pl <<\EOF
#!/usr/bin/perl
use strict;
$| = 1;
my %DataType = (
"TINYINT"=>1, "SMALLINT"=>2, "MEDIUMINT"=>3, "INT"=>4, "INTEGER"=>4, "BIGINT"=>8,
"FLOAT"=>'$M<=24?4:8', "DOUBLE"=>8,
"DECIMAL"=>'int(($M-$D)/9)*4+int(((($M-$D)%9)+1)/2)+int($D/9)*4+int((($D%9)+1)/2)',
"NUMERIC"=>'int(($M-$D)/9)*4+int(((($M-$D)%9)+1)/2)+int($D/9)*4+int((($D%9)+1)/2)',
"BIT"=>'($M+7)>>3',
"DATE"=>3, "TIME"=>3, "DATETIME"=>8, "TIMESTAMP"=>4, "YEAR"=>1,
"BINARY"=>'$M',"CHAR"=>'$M*$CL',
"VARBINARY"=>'$M+($M>255?2:1)', "VARCHAR"=>'$M*$CL+($M>255?2:1)',
"ENUM"=>'$M>255?2:1', "SET"=>'($M+7)>>3',
"TINYBLOB"=>9, "TINYTEXT"=>9,
"BLOB"=>10, "TEXT"=>10,
"MEDIUMBLOB"=>11, "MEDIUMTEXT"=>11,
"LONGBLOB"=>12, "LONGTEXT"=>12
);
my %DataTypeMin = (
"VARBINARY"=>'($M>255?2:1)', "VARCHAR"=>'($M>255?2:1)'
);
my ($D, $M, $S, $C, $L, $dt, $dp ,$bc, $CL);
my $fieldCount = 0;
my $byteCount = 0;
my $byteCountMin = 0;
my @fields = ();
my $fieldName;
my $tableName;
my $defaultDbCL = 1;
my $defaultTableCL = 1;
my %charsetMaxLen;
my %collationMaxLen;
open (CHARSETS, "mysql -B --skip-column-names information_schema -e 'select CHARACTER_SET_NAME,MAXLEN from CHARACTER_SETS;' |");
%charsetMaxLen = map ( ( /^(\w+)/ => /(\d+)$/ ), <CHARSETS>);
close CHARSETS;
open (COLLATIONS, "mysql -B --skip-column-names information_schema -e 'select COLLATION_NAME,MAXLEN from CHARACTER_SETS INNER JOIN COLLATIONS USING(CHARACTER_SET_NAME);' |");
%collationMaxLen = map ( ( /^(\w+)/ => /(\d+)$/ ), <COLLATIONS>);
close COLLATIONS;
open (TABLEINFO, "mysqldump -d --compact ".join(" ",@ARGV)." |");
while (<TABLEINFO>) {
chomp;
if ( ($S,$C) = /create database.*?`([^`]+)`.*default\scharacter\sset\s+(\w+)/i ) {
$defaultDbCL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : 1;
print "Database: $S".($C?" DEFAULT":"").($C?" CHARSET $C":"")." (bytes per char: $defaultDbCL)\n\n";
next;
}
if ( /^create table\s+`([^`]+)`.*/i ) {
$tableName = $1;
@fields = ();
next;
}
if ( $tableName && (($C,$L) = /^\)(?:.*?default\scharset=(\w+))?(?:.*?collate=(\w+))?/i) ) {
$defaultTableCL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : (exists $collationMaxLen{$L} ? $collationMaxLen{$L} : $defaultDbCL);
print "Table: $tableName".($C||$L?" DEFAULT":"").($C?" CHARSET $C":"").($L?" COLLATION $L":"")." (bytes per char: $defaultTableCL)\n";
$tableName = "";
$fieldCount = 0;
$byteCount = 0;
$byteCountMin = 0;
while ($_ = shift @fields) {
if ( ($fieldName,$dt,$dp,$M,$D,$S,$C,$L) = /\s\s`([^`]+)`\s+([a-z]+)(\((\d+)(?:,(\d+))?\)|\((.*)\))?(?:.*?character\sset\s+(\w+))?(?:.*?collate\s+(\w+))?/i ) {
$dt = uc $dt;
if (exists $DataType{$dt}) {
if (length $S) {
$M = ($S =~ s/(\'.*?\'(?!\')(?=,|$))/$1/g);
$dp = "($M : $S)"
}
$D = 0 if !$D;
$CL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : (exists $collationMaxLen{$L} ? $collationMaxLen{$L} : $defaultTableCL);
$bc = eval($DataType{$dt});
$byteCount += $bc;
$byteCountMin += exists $DataTypeMin{$dt} ? $DataTypeMin{$dt} : $bc;
} else {
$bc = "??";
}
$fieldName.="\t" if length($fieldName) < 8;
print "bytes:\t".$bc."\t$fieldName\t$dt$dp".($C?" $C":"").($L?" COLL $L":"")."\n";
++$fieldCount;
}
}
print "total:\t$byteCount".($byteCountMin!=$byteCount?"\tleast: $byteCountMin":"\t\t")."\tcolumns: $fieldCount\n\n";
next;
}
push @fields, $_;
}
close TABLEINFO;
EOF
Then paste output:
cd /tmp
perl row_size.pl -h $host -P $port -u librenms -p$password librenms devices
Mine is:
$ perl row_size.pl -h $host -P $port -u librenms -p$password librenms devices
Table: devices DEFAULT CHARSET utf8 COLLATION utf8_unicode_ci (bytes per char: 3)
bytes: 4 device_id INT(10)
bytes: 385 hostname VARCHAR(128) COLL utf8_unicode_ci
bytes: 385 sysName VARCHAR(128) COLL utf8_unicode_ci
bytes: 17 ip VARBINARY(16)
bytes: 766 community VARCHAR(255) COLL utf8_unicode_ci
bytes: 1 authlevel ENUM(3 : 'noAuthNoPriv','authNoPriv','authPriv') COLL utf8_unicode_ci
bytes: 193 authname VARCHAR(64) COLL utf8_unicode_ci
bytes: 193 authpass VARCHAR(64) COLL utf8_unicode_ci
bytes: 1 authalgo ENUM(2 : 'MD5','SHA') COLL utf8_unicode_ci
bytes: 193 cryptopass VARCHAR(64) COLL utf8_unicode_ci
bytes: 1 cryptoalgo ENUM(3 : 'AES','DES','') COLL utf8_unicode_ci
bytes: 13 snmpver VARCHAR(4) COLL utf8_unicode_ci
bytes: 2 port SMALLINT(5)
bytes: 49 transport VARCHAR(16) COLL utf8_unicode_ci
bytes: 4 timeout INT(11)
bytes: 4 retries INT(11)
bytes: 1 snmp_disable TINYINT(1)
bytes: 4 bgpLocalAs INT(10)
bytes: 385 sysObjectID VARCHAR(128) COLL utf8_unicode_ci
bytes: 10 sysDescr TEXT COLL utf8_unicode_ci
bytes: 10 sysContact TEXT COLL utf8_unicode_ci
bytes: 10 version TEXT COLL utf8_unicode_ci
bytes: 10 hardware TEXT COLL utf8_unicode_ci
bytes: 10 features TEXT COLL utf8_unicode_ci
bytes: 4 location_id INT(10)
bytes: 97 os VARCHAR(32) COLL utf8_unicode_ci
bytes: 1 status TINYINT(1)
bytes: 151 status_reason VARCHAR(50) COLL utf8_unicode_ci
bytes: 1 ignore TINYINT(1)
bytes: 1 disabled TINYINT(1)
bytes: 8 uptime BIGINT(20)
bytes: 4 agent_uptime INT(10)
bytes: 4 last_polled TIMESTAMP
bytes: 4 last_poll_attempted TIMESTAMP
bytes: 8 last_polled_timetaken DOUBLE(5,2)
bytes: 8 last_discovered_timetaken DOUBLE(5,2)
bytes: 4 last_discovered TIMESTAMP
bytes: 4 last_ping TIMESTAMP
bytes: 8 last_ping_timetaken DOUBLE(8,2)
bytes: 10 purpose TEXT COLL utf8_unicode_ci
bytes: 61 type VARCHAR(20) COLL utf8_unicode_ci
bytes: 10 serial TEXT COLL utf8_unicode_ci
bytes: 766 icon VARCHAR(255) COLL utf8_unicode_ci
bytes: 4 poller_group INT(11)
bytes: 1 override_sysLocation TINYINT(1)
bytes: 10 notes TEXT COLL utf8_unicode_ci
bytes: 4 port_association_mode INT(11)
bytes: 4 max_depth INT(11)
bytes: 1 disable_notify TINYINT(1)
total: 3829 least: 175 columns: 49