On Jan 29, 2008, at 4:37 PM, Baron Schwartz wrote:
It's because your index is bigger than your memory (or at least bigger
than your InnoDB buffer pool). InnoDB can't build indexes by sorting
rows, so building the indexes gets slow.
Hmm, this would be an interesting theory for the main table in
question, but it doesn't seem to explain why the test case has the
same problem. A "show table status" tells me that at no point does
the combined size of the data and indexes exceed 306 MB, though I've
got 512 MB allocated to the buffer pool (and top tells me that no swap
space is being used):
mysql> create table test2 (id int NOT NULL auto_increment, x int NOT
NULL, primary key (id), key (x)) Engine=InnoDB;
Query OK, 0 rows affected (0.10 sec)
mysql> insert into test2 (x) select cast(rand()*10000000 as
unsigned) from big_table where id between 1 and 1000000;
Query OK, 981734 rows affected (25.43 sec)
Records: 981734 Duplicates: 0 Warnings: 0
mysql> show table status like 'test2';
+-------+--------+---------+------------+--------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
| 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 |
+-------+--------+---------+------------+--------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
| test2 | InnoDB | 10 | Compact | 982123 | 29
| 28884992 | 0 | 19447808 | 0 |
981735 | 2008-01-29 17:06:23 | NULL | NULL |
latin1_swedish_ci | NULL | | InnoDB free: 6144 kB |
+-------+--------+---------+------------+--------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
1 row in set (0.08 sec)
mysql> insert into test2 (x) select cast(rand()*10000000 as
unsigned) from big_table where id between 1 and 1000000;
Query OK, 981734 rows affected (36.97 sec)
Records: 981734 Duplicates: 0 Warnings: 0
mysql> show table status like 'test2';
+-------+--------+---------+------------+---------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
| 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 |
+-------+--------+---------+------------+---------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
| test2 | InnoDB | 10 | Compact | 1964237 | 28
| 56180736 | 0 | 39403520 | 0 |
1963469 | 2008-01-29 17:06:23 | NULL | NULL |
latin1_swedish_ci | NULL | | InnoDB free: 4096 kB |
+-------+--------+---------+------------+---------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
1 row in set (0.08 sec)
mysql> insert into test2 (x) select cast(rand()*10000000 as
unsigned) from big_table where id between 1 and 1000000;
Query OK, 981734 rows affected (58.99 sec)
Records: 981734 Duplicates: 0 Warnings: 0
mysql> show table status like 'test2';
+-------+--------+---------+------------+---------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
| 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 |
+-------+--------+---------+------------+---------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
| test2 | InnoDB | 10 | Compact | 2945777 | 28
| 84508672 | 0 | 76169216 | 0 |
2945203 | 2008-01-29 17:06:23 | NULL | NULL |
latin1_swedish_ci | NULL | | InnoDB free: 6144 kB |
+-------+--------+---------+------------+---------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
1 row in set (0.08 sec)
mysql> insert into test2 (x) select cast(rand()*10000000 as
unsigned) from big_table where id between 1 and 1000000;
Query OK, 981734 rows affected (1 min 7.23 sec)
Records: 981734 Duplicates: 0 Warnings: 0
mysql> show table status like 'test2';
+-------+--------+---------+------------+---------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
| 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 |
+-------+--------+---------+------------+---------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
| test2 | InnoDB | 10 | Compact | 3927317 | 28
| 112852992 | 0 | 78266368 | 0 |
3926937 | 2008-01-29 17:06:23 | NULL | NULL |
latin1_swedish_ci | NULL | | InnoDB free: 5120 kB |
+-------+--------+---------+------------+---------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
1 row in set (0.07 sec)
mysql> insert into test2 (x) select cast(rand()*10000000 as
unsigned) from big_table where id between 1 and 1000000;
Query OK, 981734 rows affected (1 min 28.27 sec)
Records: 981734 Duplicates: 0 Warnings: 0
mysql> show table status like 'test2';
+-------+--------+---------+------------+---------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
| 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 |
+-------+--------+---------+------------+---------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
| test2 | InnoDB | 10 | Compact | 4909431 | 28
| 141180928 | 0 | 118112256 | 0 |
4908671 | 2008-01-29 17:06:23 | NULL | NULL |
latin1_swedish_ci | NULL | | InnoDB free: 7168 kB |
+-------+--------+---------+------------+---------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
1 row in set (0.09 sec)
mysql> insert into test2 (x) select cast(rand()*10000000 as
unsigned) from big_table where id between 1 and 1000000;
Query OK, 981734 rows affected (1 min 59.73 sec)
Records: 981734 Duplicates: 0 Warnings: 0
mysql> show table status like 'test2';
+-------+--------+---------+------------+---------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
| 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 |
+-------+--------+---------+------------+---------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
| test2 | InnoDB | 10 | Compact | 5890971 | 28
| 168476672 | 0 | 151797760 | 0 |
5890405 | 2008-01-29 17:06:23 | NULL | NULL |
latin1_swedish_ci | NULL | | InnoDB free: 7168 kB |
+-------+--------+---------+------------+---------+----------------
+-------------+-----------------+--------------+-----------
+----------------+---------------------+-------------+------------
+-------------------+----------+----------------
+----------------------+
1 row in set (0.08 sec)
mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+
1 row in set (0.06 sec)
With regards to your suggestions:
Perhaps you can keep the table as many small tables, or keep older
data in an archive table that's MyISAM and keep only the newest rows
in InnoDB. Or if you're using MySQL 5.1, this might be a candidate
for partitioning.
The first idea would require changes to our application logic in a
number of places; and as for the second idea, we're using MySQL 5.0.45
right now. However, both are interesting and I am investigating
them. I would like to understand the root cause of the problem before
going with any particular solution, and I'm not convinced it can be
due to the buffer pool exhaustion for the reasons I showed above.
In any event, thank you for your thoughts. They're helpful and very
much appreciated! Any idea on this further information?
David
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
