…MySQL */ reclength=max(file->min_record_length(table_options),reclength); if (info_length+(ulong) create_fields.elements*FCOMP+288+ n_length+int_length+com_length…MySQL table. So what’s the maximum number of columns in a MySQL table…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
… data in index size for given MySQL Instance SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),’M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),’G') data…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row in…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
…– system files, binary log, FRM files, MySQL MyISAM system tables etc. Starting MySQL 5.5 MySQL uses asynchronous IO which should …format: Compact Rows: 278987416 Avg_row_length: 75 Data_length: 20975714304 Max_data_length: 0 Index_length: 9047113728 Data_free: 6291456 Auto_increment: NULL Create…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… row table which would look as following in terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row *************************** Name: sbtest Engine: InnoDB Version: 10 Row_format: Compact Rows: 10000060 Avg_row_length: 224 Data_length…
Post: How much space does empty Innodb table take ?
December 16, 2008 By 7 Comments
…_table mode is interesting question on its own. As we populate table we will see Free space will remain at zero as Data_length is small: mysql> show table status like “test… Row_format: Compact Rows: 1069 Avg_row_length: 199 Data_length: 212992 Max_data_length: 0 Index_length: 360448 Data_free: 0 Auto_increment: NULL Create_time…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…table status like “%bool%” \G *************************** 1. row *************************** Name: bbool Engine: InnoDB Version: 10 Row_format: Compact Rows: 2097405 Avg_row_length: 37 Data_length…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… tables. mysql> show table status like ‘fact’\G *************************** 1. row *************************** Name: fact Engine: MyISAM Rows: 147045493 Avg_row_length: 117 Data_length: 17217646764 Index_length: 11993816064 mysql> show table status like ‘dim1′\G *************************** 1. row *************************** Name: dim1 Engine: MyISAM Rows: 453193 Avg_row_length: 122 Data_length…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 27 Comments
…length / data_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10; +————————————-+——–+——–+——–+————+———+ | concat(table_schema,’.',table_name) | rows | data…
Post: Getting History of Table Sizes in MySQL
December 8, 2010 By 13 Comments
… * * * mysql -u root -e “INSERT INTO stats.tables SELECT DATE(NOW()),TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,DATA_FREE,AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES” Though if you’re looking to keep it completely inside MySQL…
