Mysql – thesql query taking differently long

innodbMySQL

I need some enlightenment on a MySQL query issue:

I recently found out that a select query is taking differently long depending on how large ibdata1 is (at least that's how it seems to me).

To be specific: When I empty the whole table that is being fetched and import only a few entries, the fetch time is how it's supposed to be (0.03 sec). After importing more and more data, the query becomes more and more slow. What's strange is the fact that after deleting entries from the table, it still is running slowly. After deleting ibdata1 and importing all entries, the query suddenly works fine again (0.03 sec).

It probably helps to know that the table is quite big, but not in regards of entries (only 14k), but storage size (all in all the table size is about 2.3 GB because there is a BLOB field). I guess this has something to do with it, since it lets ibdata1 grow pretty fast.

All tables are InnoDB. Any suggestions on why this happens and how to prevent it? I don't want to delete ibdata1 everytime I need to reimport all entries of this table (which sometimes happens because I'm working in a DEV environment).

Here are some more infos. I changed some names (table, field) to not give away too much:

my.cnf:

[client]
port            = 3306
socket          = /home/user/mysql/mysqld.sock


[mysqld]
port            = 3306
#bind-address   = 0.0.0.0
socket          = /home/user/mysql/mysqld.sock
datadir         = /home/user/mysql/data
log-error       = /home/user/mysql/logs/mysqld.err
#skip-locking
key_buffer = 16M
max_allowed_packet = 16M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
lower_case_table_names=1

show create table:

 CREATE TABLE `table_name` (
  `ID_0` varchar(255) NOT NULL,
  `VERSION` bigint(20) NOT NULL,
  `PROTOCOL` longblob,
  `START_TIME` datetime NOT NULL,
  `END_TIME` datetime default NULL,
  `STATUS` varchar(255) NOT NULL,
  `COMMENT` varchar(255) default NULL,
  `ID_1` varchar(255) NOT NULL,
  `ID_2` varchar(255) NOT NULL,
  `ID_3` varchar(255) default NULL,
  PRIMARY KEY  (`ID_0`),
  KEY `FK635C17D0AE6C617C` (`ID_3`),
  KEY `FK635C17D022EA191B` (`ID_1`),
  KEY `FK635C17D0109EAE92` (`ID_2`),
  CONSTRAINT `FK635C17D0109EAE92` FOREIGN KEY (`ID_2`) REFERENCES `table_2` (`ID_2`),
  CONSTRAINT `FK635C17D022EA191B` FOREIGN KEY (`ID_1`) REFERENCES `table_1` (`ID_1`),
  CONSTRAINT `FK635C17D0AE6C617C` FOREIGN KEY (`ID_3`) REFERENCES `table_3` (`ID_3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

show table status:

| Name                     | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment                                                                          |
+--------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------------------------------------------------------+
| table_name | InnoDB |      10 | Compact    | 110937 |          23361 |  2591604736 |               0 |      2162688 |         0 |           NULL | 2015-12-30 03:18:54 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 91136 kB; (`ID_3`) REFER `table_3`(` |

Best Answer

Don't remove ibdata1. That may be fine for one table, but once you have two tables, you will be in trouble. Also don't remove an .ibd file if you have innodb_file_per_table.

What is the size of innodb_buffer_pool_size? Unless you have a small RAM, it should be set to about 70% of RAM. This is a very important speed setting for InnoDB.

A better way to empty out a table: TRUNCATE TABLE. If you will frequently be reloading this table, the have innodb_file_per_table = ON when you create it.

14KB per row, including a BLOB? That is past one critical threshold. Please show us SHOW CREATE TABLE and SHOW TABLE STATUS. There are some important factors to note before discussing this further.

If you ultimate goal is to completely replace the table periodically, then do it this way:

CREATE TABLE new_tbl   (and use either `LIKE real_tbl` or spell out the schema)
Load the data into new_tbl
RENAME TABLE real_tbl TO old_tbl, new_tbl TO real_tbl;
DROP old_tbl;

The RENAME is atomic and instantaneous. With innodb_file_per_table = ON, you will recoup any free space from the old copy.

Related Question