2 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

The first thing you want to do is ascertain the size of the table from 2 perspectives

  • Operating System
  • INFORMATION_SCHEMA

Please run the following

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB=databasename
TB=tablename
SQL="SELECT data_length+index_length FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB}' AND table_name='${TB}'"
TBLSIZE_OPER=`ls -l /var/lib/mysql/${DB}/${TB}.ibd` 
TBLSIZE_INFO=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
(( TBLSIZE_FRAG = TBLSIZE_OPER - TBLSIZE_INFO ))
(( TBLSIZE_FRAG_GB = TBLSIZE_FRAG / 1073741824 ))
echo ${TBLSIZE_FRAG} ${TBLSIZE_FRAG_GB}

This will tell you how many bytes and gigabytes of wasted space you have

If you feel this is a lot of fragmentation, then simply run the following

USE databasename
ALTER TABLE tablename ENGINE=InnoDB;

This will create a temp table with no fragmewntation and then rename the temp table.

I wrote about this almost a year ago : http://dba.stackexchange.com/questions/110996/how-to-monitor-mysql-percentage-disk-utilization/110997#110997How to monitor mysql percentage disk utilization?

The first thing you want to do is ascertain the size of the table from 2 perspectives

  • Operating System
  • INFORMATION_SCHEMA

Please run the following

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB=databasename
TB=tablename
SQL="SELECT data_length+index_length FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB}' AND table_name='${TB}'"
TBLSIZE_OPER=`ls -l /var/lib/mysql/${DB}/${TB}.ibd` 
TBLSIZE_INFO=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
(( TBLSIZE_FRAG = TBLSIZE_OPER - TBLSIZE_INFO ))
(( TBLSIZE_FRAG_GB = TBLSIZE_FRAG / 1073741824 ))
echo ${TBLSIZE_FRAG} ${TBLSIZE_FRAG_GB}

This will tell you how many bytes and gigabytes of wasted space you have

If you feel this is a lot of fragmentation, then simply run the following

USE databasename
ALTER TABLE tablename ENGINE=InnoDB;

This will create a temp table with no fragmewntation and then rename the temp table.

I wrote about this almost a year ago : http://dba.stackexchange.com/questions/110996/how-to-monitor-mysql-percentage-disk-utilization/110997#110997

The first thing you want to do is ascertain the size of the table from 2 perspectives

  • Operating System
  • INFORMATION_SCHEMA

Please run the following

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB=databasename
TB=tablename
SQL="SELECT data_length+index_length FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB}' AND table_name='${TB}'"
TBLSIZE_OPER=`ls -l /var/lib/mysql/${DB}/${TB}.ibd` 
TBLSIZE_INFO=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
(( TBLSIZE_FRAG = TBLSIZE_OPER - TBLSIZE_INFO ))
(( TBLSIZE_FRAG_GB = TBLSIZE_FRAG / 1073741824 ))
echo ${TBLSIZE_FRAG} ${TBLSIZE_FRAG_GB}

This will tell you how many bytes and gigabytes of wasted space you have

If you feel this is a lot of fragmentation, then simply run the following

USE databasename
ALTER TABLE tablename ENGINE=InnoDB;

This will create a temp table with no fragmewntation and then rename the temp table.

I wrote about this almost a year ago : How to monitor mysql percentage disk utilization?

1
source | link

The first thing you want to do is ascertain the size of the table from 2 perspectives

  • Operating System
  • INFORMATION_SCHEMA

Please run the following

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB=databasename
TB=tablename
SQL="SELECT data_length+index_length FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB}' AND table_name='${TB}'"
TBLSIZE_OPER=`ls -l /var/lib/mysql/${DB}/${TB}.ibd` 
TBLSIZE_INFO=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
(( TBLSIZE_FRAG = TBLSIZE_OPER - TBLSIZE_INFO ))
(( TBLSIZE_FRAG_GB = TBLSIZE_FRAG / 1073741824 ))
echo ${TBLSIZE_FRAG} ${TBLSIZE_FRAG_GB}

This will tell you how many bytes and gigabytes of wasted space you have

If you feel this is a lot of fragmentation, then simply run the following

USE databasename
ALTER TABLE tablename ENGINE=InnoDB;

This will create a temp table with no fragmewntation and then rename the temp table.

I wrote about this almost a year ago : http://dba.stackexchange.com/questions/110996/how-to-monitor-mysql-percentage-disk-utilization/110997#110997