MySQL Forums :: Performance :: How to improve the insertion speed


Advanced Search

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 2880 Maxime Aoustin 05/20/2009 08:31PM
Re: How to improve the insertion speed 2771 Rick James 05/20/2009 11:58PM
Re: How to improve the insertion speed 1949 Maxime Aoustin 05/21/2009 12:13AM
Re: How to improve the insertion speed 1803 Aftab Khan 05/21/2009 02:24AM
Re: How to improve the insertion speed 1653 Maxime Aoustin 05/24/2009 07:44PM
Re: How to improve the insertion speed 1780 Rick James 05/25/2009 03:08PM
Re: How to improve the insertion speed 1631 Maxime Aoustin 05/25/2009 05:16PM
Re: How to improve the insertion speed 1695 Rick James 05/25/2009 06:30PM
Re: How to improve the insertion speed 1692 Maxime Aoustin 05/25/2009 06:58PM
Re: How to improve the insertion speed 1717 Rick James 05/26/2009 12:45AM


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.