0

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`(` |

1 Answer 1

1

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.

7
  • thanks for your help! i edit my first post and put the information there you requested. unfortunately, the variable innodb_buffer_pool_size is not set. Jan 5, 2016 at 8:49
  • btw I just checked the size of the BLOBs. The largest one is 8MB, the smallest one is 1.5KB. on average their size is about 164KB. So thats much more than 14KB per row! Jan 5, 2016 at 9:13
  • 1
    Do SHOW VARIABLES LIKE "innodb_buffer_pool_size";.
    – Rick James
    Jan 5, 2016 at 15:54
  • Part or all of the blob will be stored elsewhere. It depends on the ROW_FORMAT of the table.
    – Rick James
    Jan 5, 2016 at 15:57
  • The value of the buffer pool size is 8388608. The create statement of the table in question doesn't specify a row format. Is there a default ROW_FORMAT for InnoDB? I've seen that other tables in this database have ROW_FORMAT=compact Jan 9, 2016 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.