Description:
Hello!
When reading from a table of the type MyISAM that contains big data
rows (on avg 128MB), the access is *very* slow. This also concerns the
access to columns that are not that big and indexed access.
Below is a list of example commands. Especially note the long duration
of the commands (fetching a small column via the primary key takes
around 4 seconds, and a full table scan takes a few minutes).
It seems to be a real MyISAM problem, since the conversion of the
table to the type InnoDB gives reasonable response times (also see
below).
We are running MySQL 4.0.16 on SuSE Linux 7.1. The DB server is a
Pentium III with 700MHz and has 800MB RAM.
Greetings,
Christian
---------8<--------------8<--------------8<--------------8<----------
mysql> desc contig;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| id | int(11) | | PRI | 0 | |
| contiglist_id | int(11) | YES | MUL | NULL | |
| contigtype_id | int(11) | | | 0 | |
| code | varchar(255) | YES | MUL | NULL | |
| create_time | int(11) | | | 0 | |
| contig_start | int(11) | | | 0 | |
| contig_stop | int(11) | | | 0 | |
| contig_length | int(11) | | | 0 | |
| description | text | YES | | NULL | |
| sequence | longtext | YES | | NULL | |
| quality | double | YES | | NULL | |
| version | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
12 rows in set (0.19 sec)
mysql> select max(contig_length), min(contig_length), avg(contig_length) from contig;
+--------------------+--------------------+--------------------+
| max(contig_length) | min(contig_length) | avg(contig_length) |
+--------------------+--------------------+--------------------+
| 195869683 | 61093376 | 128863053.7000 |
+--------------------+--------------------+--------------------+
1 row in set (1 min 25.82 sec)
mysql> explain select code from contig where id=5;
+--------+-------+---------------+---------+---------+-------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+--------+-------+---------------+---------+---------+-------+------+-------+
| contig | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+--------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (4.51 sec)
mysql> select code from contig where id=5;
+-------------+
| code |
+-------------+
| Mouse:chr06 |
+-------------+
1 row in set (4.96 sec)
mysql> select code from contig where id=6;
+-------------+
| code |
+-------------+
| Mouse:chr07 |
+-------------+
1 row in set (3.99 sec)
mysql> select id, code from contig;
+----+-------------+
| id | code |
+----+-------------+
| 0 | Mouse:chr01 |
| 1 | Mouse:chr02 |
| 2 | Mouse:chr03 |
| 3 | Mouse:chr04 |
| 4 | Mouse:chr05 |
| 5 | Mouse:chr06 |
| 6 | Mouse:chr07 |
| 7 | Mouse:chr08 |
| 8 | Mouse:chr09 |
| 9 | Mouse:chr10 |
| 10 | Mouse:chr11 |
| 11 | Mouse:chr12 |
| 12 | Mouse:chr13 |
| 13 | Mouse:chr14 |
| 14 | Mouse:chr15 |
| 15 | Mouse:chr16 |
| 16 | Mouse:chr17 |
| 17 | Mouse:chr18 |
| 18 | Mouse:chr19 |
| 21 | Mouse:chrX |
+----+-------------+
20 rows in set (1 min 32.04 sec)
mysql> show table status like 'contig';
+------------------------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------------------------------+---------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment |
+------------------------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------------------------------+---------+
| contig | MyISAM | Dynamic | 20 | 128863207 | 2577264140 | 1099511627775 | 7168 | 0 | NULL | 2003-11-20 10:02:22 | 2003-11-20 10:08:08 | NULL | max_rows=100 avg_row_length=134217728 | |
+------------------------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------------------------------+---------+
1 row in set (0.39 sec)
ysql> alter table contig rename contig_myisam;
Query OK, 0 rows affected (0.03 sec)
mysql> create table contig type=innodb select * from contig_myisam;
Query OK, 20 rows affected (15 min 21.41 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql> alter table contig add primary key(id), add index (contiglist_id), add unique(code);
Query OK, 20 rows affected (16 min 23.59 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql> show table status like 'contig%';
+---------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+---------------------------------------+-------------------------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment |
+---------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+---------------------------------------+-------------------------+
| contig | InnoDB | Dynamic | 20 | 129316454 | 2586329088 | NULL | 32768 | 0 | NULL | NULL | NULL | NULL | | InnoDB free: 5737472 kB |
| contig_myisam | MyISAM | Dynamic | 20 | 128863207 | 2577264140 | 1099511627775 | 7168 | 0 | NULL | 2003-11-20 10:02:22 | 2003-11-20 10:08:08 | NULL | max_rows=100 avg_row_length=134217728 | |
+---------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+---------------------------------------+-------------------------+
2 rows in set (0.00 sec)
mysql> explain select code from contig where id=5;
+--------+-------+---------------+---------+---------+-------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+--------+-------+---------------+---------+---------+-------+------+-------+
| contig | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+--------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.23 sec)
mysql> select code from contig where id=5;
+-------------+
| code |
+-------------+
| Mouse:chr06 |
+-------------+
1 row in set (0.00 sec)
mysql> select code from contig where id=9;
+-------------+
| code |
+-------------+
| Mouse:chr10 |
+-------------+
1 row in set (0.00 sec)
mysql> select id, code from contig;
+----+-------------+
| id | code |
+----+-------------+
| 0 | Mouse:chr01 |
| 1 | Mouse:chr02 |
| 2 | Mouse:chr03 |
| 3 | Mouse:chr04 |
| 4 | Mouse:chr05 |
| 5 | Mouse:chr06 |
| 6 | Mouse:chr07 |
| 7 | Mouse:chr08 |
| 8 | Mouse:chr09 |
| 9 | Mouse:chr10 |
| 10 | Mouse:chr11 |
| 11 | Mouse:chr12 |
| 12 | Mouse:chr13 |
| 13 | Mouse:chr14 |
| 14 | Mouse:chr15 |
| 15 | Mouse:chr16 |
| 16 | Mouse:chr17 |
| 17 | Mouse:chr18 |
| 18 | Mouse:chr19 |
| 21 | Mouse:chrX |
+----+-------------+
20 rows in set (0.00 sec)
How to repeat:
create a table with an ID column and a longtext column and fill it
with around 20 rows of ca 128MB each. Then perform some queries
like above. Then try the same with an InnoDB table.
Suggested fix:
???
