MySQL Forums
Forum List  »  Performance

How to improve the insertion speed
Posted by: Maxime Aoustin
Date: May 20, 2009 08:31PM

I have a huge table:
mysql> show create table messa;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| messa| CREATE TABLE `messa` (
  `id` bigint(11) unsigned NOT NULL,
  `login` varchar(20) collate utf8_unicode_ci NOT NULL,
  `parent` varchar(41) collate utf8_unicode_ci default NULL COMMENT 'user/messa_id',
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `content` varchar(255) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `login` (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'messa';
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------+
| 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              |
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------+
| messa | InnoDB |      10 | Compact    | 5037078 |            338 |  1703936000 |               0 |    240123904 |         0 |           NULL | 2009-05-14 20:54:31 | NULL        | NULL       | utf8_unicode_ci |     NULL |                | InnoDB free: 4096 kB | 
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------+
1 row in set (0.09 sec)

This table is counting 5,000,000 rows and it's growing very fast.
At the moment I am inserting around 55 rows per seconds and I'd like at least to double the speed. It's an Innodb table. I was thinking to split the data from this table into several tables and insert into the right table according the ID of the message.

let say that I have 4 tables messages.
table messa_1 deals with the IDs from 1 to 1,000,000
table messa_2 deals with the IDs from 1,000,001 to 2,000,000
...

I was wondering if reducing the number of rows into the table will accelerate the insertion? And how much (if it's to insert 70 rows per second is useless)?
It's quite complex to implement so I wanted to have your point of view.
Also I am sure there is other solutions.

Thanks a lot in advance.

Maxime

Options: ReplyQuote


Subject
Views
Written By
Posted
How to improve the insertion speed
2977
May 20, 2009 08:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.