# gradually slow insertion **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [February 4, 2010, 9:31pm UTC](https://forums.percona.com/t/gradually-slow-insertion/1342 "2010-02-04T21:31:46Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![r0ash](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/r0ash/32/823_2.png) [@r0ash](https://forums.percona.com/u/r0ash)\ **Post date:** [February 4, 2010, 9:31pm UTC](https://forums.percona.com/t/gradually-slow-insertion/1342/1 "2010-02-04T21:31:46Z")
Hi there, **Background:** We have large flat files span around 60GB and are inserting into database. We are experiencing incremental performance downgrade during insertion. - We have 174 (million) records and expecting another 50 (million) to be inserted - We have splitted main table into 1000+ tables on the basis of first-two-characters of entity-name e.g. entity\_aa, entity\_ab … entity\_zz - During each insertion, there are three queries ran (a) range based search to another table,(b) checking if record is already inserted or not (c) insert into detail (entity\_briefs) table - We added entity\_briefs to handle frequent search queries, but realized that, upon insertion into database, it slow down gradually no matter if we ALTER TABLE entity (or entity\_briefs) DISABLE (or ENABLE) KEY. - The machine has 4 CPUs, Gigs of disk-space, 2GB RAM. Operating system is Linux CentOS (5.4) 32bit CREATE TABLE `entity_briefs` ( `entity_brief_id` bigint(11) NOT NULL auto\_increment, `entity_id` bigint(11) default NULL, `entity_table_prefix` char(2) default NULL, `string_1` varchar(255) default NULL, `string_2` varchar(255) default NULL, `zip` varchar(25) default NULL, `phone` bigint(11) default NULL, PRIMARY KEY (`entity_brief_id`), KEY `idx_entity_id` (`entity_id`), KEY `idx_entity_table_prefix` (`entity_table_prefix`), KEY `idx_zip` (`zip`), KEY `idx_string_1` (`string_1`), KEY `idx_string_2` (`string_2`), KEY `idx_phone` (`phone`) ); mysqltuner.pl output: > > MySQLTuner 1.1.1 - Major Hayden \<[major@mhtx.net](mailto:major@mhtx.net)\> > > Bug reports, feature requests, and downloads at [http://mysqltuner.com/](http://mysqltuner.com/) > > Run with ‘–help’ for additional options and output filtering > > Please enter your MySQL administrative login: xxxxx > > Please enter your MySQL administrative password:xxxxx -------- General Statistics -------------------------------------------------- [–] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.0.85-community [OK] Operating on 32-bit architecture with less than 2GB RAM -------- Storage Engine Statistics ------------------------------------------- [–] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [–] Data in MyISAM tables: 101M (Tables: 1344) [!!] InnoDB is enabled but isn’t being used [!!] Total fragmented tables: 1 -------- Security Recommendations ------------------------------------------- ERROR 1142 (42000) at line 1: SELECT command denied to user ‘xxxx’@‘localhost’ for table ‘user’ [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [–] Up for: 5d 15h 53m 55s (2M q [4.395 qps], 9K conn, TX: 1B, RX: 425M) [–] Reads / Writes: 51% / 49% [–] Total buffers: 34.0M global + 2.7M per thread (500 max threads) [OK] Maximum possible memory usage: 1.3G (67% of installed RAM) [OK] Slow queries: 0% (9/2M) [OK] Highest usage of available connections: 1% (5/500) [!!] Key buffer size / total MyISAM indexes: 8.0M/105.3M [!!] Key buffer hit rate: 94.1% (72M cached / 4M reads) [!!] Query cache is disabled [OK] Temporary tables created on disk: 7% (101 on disk / 1K total) [!!] Thread cache is disabled [!!] Table cache hit rate: 0% (64 open / 277K opened) [OK] Open file limit used: 0% (127/18K) [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks) [!!] Connections aborted: 38% -------- Recommendations ----------------------------------------------------- General recommendations: Add skip-innodb to MySQL configuration to disable InnoDB Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Set thread\_cache\_size to 4 as a starting value Increase table\_cache gradually to avoid file descriptor limits Your applications are not closing MySQL connections properly Variables to adjust: key\_buffer\_size (\> 105.3M) query\_cache\_size (\>= 8M) thread\_cache\_size (start at 4) table\_cache (\> 64) Requirement: What can be done for faster insertions? ---
**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\ **Post date:** [February 5, 2010, 2:48pm UTC](https://forums.percona.com/t/gradually-slow-insertion/1342/2 "2010-02-05T14:48:57Z")
When the working set is bigger than memory, B-Tree indexes slow down. That’s all there is to it. You need to ensure the working set of your indexes fit in RAM. From what I see your key\_buffer\_size might be 8MB, which is WAY too small. Beyond that: use partitioning, or split your table up, or get more memory, or faster disks, etc. “Tuning” will not be effective. The only other thing I see that you could do is create the table with indexes disabled and then enable them afterwards, so they are built by sorting instead of by inserting. This will be much faster. You have a lot of indexes.