# LOAD DATA locks out ALL innoDB tables. **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [July 31, 2008, 1:25pm UTC](https://forums.percona.com/t/load-data-locks-out-all-innodb-tables/860 "2008-07-31T13:25:49Z")\ **Posts on this page:** 1\ **Page:** 1

**Author:** ![wwwwizard](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/wwwwizard/32/786_2.png) [@wwwwizard](https://forums.percona.com/u/wwwwizard)\ **Post date:** [July 31, 2008, 1:25pm UTC](https://forums.percona.com/t/load-data-locks-out-all-innodb-tables/860/1 "2008-07-31T13:25:49Z")

I’m attempting to load a 3GB file, 850K rows, into an _unused_ InnoDB table using LOAD DATA INFILE (local). When I do this, all _other_ InnoDB tables in the db freeze up, and hundreds of update queries start waiting, and timing out after table\_lock\_wait\_timeout (50). I’m thinking some kind of disk contention, perhaps size of tmp or something. This may be associated… not sure… I have set innodb\_buffer\_pool\_size to 512M, and in fact it shows as innodb\_buffer\_pool\_size 536870912 in the “system variables” pane of mysql administrator. However! On the “status variables” pane of mysql administrator, which shows the real values I have the following: innodb\_buffer\_pool\_size 32768 innodb\_buffer\_free\_buffers 0 innodb\_buffer\_pages\_created 170169 innodb\_buffer\_pages\_read 6298 innodb\_buffer\_pages\_written 170169 This tells me that mysql needs more buffer space, but for some reason is not growing the pool size to the size that it is allowed to grow. Why would this be, and is it related to my problem of not being able to import a large file into an innodb table? Thanks in advance. any help appreciated! SHOW VARIABLES auto\_increment\_increment = 1 auto\_increment\_offset = 1 automatic\_sp\_privileges = ON back\_log = 50 basedir = /usr/ bdb\_cache\_size = 8388600 bdb\_home = /var/lib/mysql/ bdb\_log\_buffer\_size = 131072 bdb\_logdir = bdb\_max\_lock = 10000 bdb\_shared\_data = OFF bdb\_tmpdir = /tmp/ binlog\_cache\_size = 32768 bulk\_insert\_buffer\_size = 8388608 character\_set\_client = utf8 character\_set\_connection = utf8 character\_set\_database = latin1 character\_set\_filesystem = binary character\_set\_results = utf8 character\_set\_server = latin1 character\_set\_system = utf8 character\_sets\_dir = /usr/share/mysql/charsets/ collation\_connection = utf8\_general\_ci collation\_database = latin1\_swedish\_ci collation\_server = latin1\_swedish\_ci completion\_type = 0 concurrent\_insert = 1 connect\_timeout = 5 datadir = /var/lib/mysql/ date\_format = %Y-%m-%d datetime\_format = %Y-%m-%d %H:%i:%s default\_week\_format = 0 delay\_key\_write = ON delayed\_insert\_limit = 100 delayed\_insert\_timeout = 300 delayed\_queue\_size = 1000 div\_precision\_increment = 4 engine\_condition\_pushdown = OFF expire\_logs\_days = 0 flush = OFF flush\_time = 0 ft\_boolean\_syntax = + -\>\<()~\*:“”&| ft\_max\_word\_len = 84 ft\_min\_word\_len = 4 ft\_query\_expansion\_limit = 20 ft\_stopword\_file = (built-in) group\_concat\_max\_len = 1024 have\_archive = NO have\_bdb = YES have\_blackhole\_engine = NO have\_compress = YES have\_crypt = YES have\_csv = NO have\_example\_engine = NO have\_federated\_engine = NO have\_geometry = YES have\_innodb = YES have\_isam = NO have\_ndbcluster = NO have\_openssl = DISABLED have\_query\_cache = YES have\_raid = NO have\_rtree\_keys = YES have\_symlink = YES init\_connect = init\_file = init\_slave = innodb\_additional\_mem\_pool\_size = 20971520 innodb\_autoextend\_increment = 1000 innodb\_buffer\_pool\_awe\_mem\_mb = 0 innodb\_buffer\_pool\_size = 536870912 innodb\_checksums = ON innodb\_commit\_concurrency = 0 innodb\_concurrency\_tickets = 500 innodb\_data\_file\_path = ibdata1:3000M:autoextend innodb\_data\_home\_dir = innodb\_doublewrite = ON innodb\_fast\_shutdown = 1 innodb\_file\_io\_threads = 4 innodb\_file\_per\_table = OFF innodb\_flush\_log\_at\_trx\_commit = 1 innodb\_flush\_method = innodb\_force\_recovery = 0 innodb\_lock\_wait\_timeout = 50 innodb\_locks\_unsafe\_for\_binlog = OFF innodb\_log\_arch\_dir = innodb\_log\_archive = OFF innodb\_log\_buffer\_size = 8388608 innodb\_log\_file\_size = 5242880 innodb\_log\_files\_in\_group = 2 innodb\_log\_group\_home\_dir = ./ innodb\_max\_dirty\_pages\_pct = 90 innodb\_max\_purge\_lag = 0 innodb\_mirrored\_log\_groups = 1 innodb\_open\_files = 300 innodb\_support\_xa = ON innodb\_sync\_spin\_loops = 20 innodb\_table\_locks = ON innodb\_thread\_concurrency = 8 innodb\_thread\_sleep\_delay = 10000 interactive\_timeout = 28800 join\_buffer\_size = 10481664 key\_buffer\_size = 268435456 key\_cache\_age\_threshold = 300 key\_cache\_block\_size = 1024 key\_cache\_division\_limit = 100 language = /usr/share/mysql/english/ large\_files\_support = ON large\_page\_size = 0 large\_pages = OFF license = GPL local\_infile = ON locked\_in\_memory = OFF log = OFF log\_bin = ON log\_bin\_trust\_function\_creators = OFF log\_error = log\_slave\_updates = OFF log\_slow\_queries = OFF log\_warnings = 1 long\_query\_time = 10 low\_priority\_updates = OFF lower\_case\_file\_system = OFF lower\_case\_table\_names = 0 max\_allowed\_packet = 16000000 max\_binlog\_cache\_size = 4294967295 max\_binlog\_size = 1073741824 max\_connect\_errors = 10 max\_connections = 5000 max\_delayed\_threads = 20 max\_error\_count = 64 max\_heap\_table\_size = 16777216 max\_insert\_delayed\_threads = 20 max\_join\_size = 4294967295 max\_length\_for\_sort\_data = 1024 max\_prepared\_stmt\_count = 16382 max\_relay\_log\_size = 0 max\_seeks\_for\_key = 4294967295 max\_sort\_length = 1024 max\_sp\_recursion\_depth = 0 max\_tmp\_tables = 32 max\_user\_connections = 0 max\_write\_lock\_count = 4294967295 multi\_range\_count = 256 myisam\_data\_pointer\_size = 6 myisam\_max\_sort\_file\_size = 2147483647 myisam\_recover\_options = OFF myisam\_repair\_threads = 1 myisam\_sort\_buffer\_size = 67108864 myisam\_stats\_method = nulls\_unequal net\_buffer\_length = 16384 net\_read\_timeout = 30 net\_retry\_count = 10 net\_write\_timeout = 60 new = OFF old\_passwords = OFF open\_files\_limit = 25010 optimizer\_prune\_level = 1 optimizer\_search\_depth = 62 pid\_file = /var/run/mysqld/mysqld.pid prepared\_stmt\_count = 0 port = 3306 preload\_buffer\_size = 32768 protocol\_version = 10 query\_alloc\_block\_size = 8192 query\_cache\_limit = 1048576 query\_cache\_min\_res\_unit = 4096 query\_cache\_size = 16777216 query\_cache\_type = ON query\_cache\_wlock\_invalidate = OFF query\_prealloc\_size = 8192 range\_alloc\_block\_size = 2048 read\_buffer\_size = 1998848 read\_only = OFF read\_rnd\_buffer\_size = 4190208 relay\_log\_purge = ON relay\_log\_space\_limit = 0 rpl\_recovery\_rank = 0 secure\_auth = OFF server\_id = 1 skip\_external\_locking = ON skip\_networking = OFF skip\_show\_database = OFF slave\_compressed\_protocol = OFF slave\_load\_tmpdir = /tmp/ slave\_net\_timeout = 3600 slave\_skip\_errors = OFF slave\_transaction\_retries = 10 slow\_launch\_time = 2 socket = /var/lib/mysql/mysql.sock sort\_buffer\_size = 256000000 sql\_mode = sql\_notes = ON sql\_warnings = ON storage\_engine = MyISAM sync\_binlog = 0 sync\_frm = ON system\_time\_zone = CDT table\_cache = 256 table\_lock\_wait\_timeout = 50 table\_type = MyISAM thread\_cache\_size = 8 thread\_stack = 196608 time\_format = %H:%i:%s time\_zone = SYSTEM timed\_mutexes = OFF tmp\_table\_size = 33554432 tmpdir = transaction\_alloc\_block\_size = 8192 transaction\_prealloc\_size = 4096 tx\_isolation = REPEATABLE-READ updatable\_views\_with\_limit = YES version = 5.0.22-log version\_bdb = Sleepycat Software: Berkeley DB 4.1.24: (May 25 = 2006) version\_comment = Source distribution version\_compile\_machine = i686 version\_compile\_os = redhat-linux-gnu wait\_timeout = 28800 SHOW STATUS Aborted\_clients = 7051 Aborted\_connects = 962 Binlog\_cache\_disk\_use = 18 Binlog\_cache\_use = 428667 Bytes\_received = 187 Bytes\_sent = 259 Com\_admin\_commands = 0 Com\_alter\_db = 0 Com\_alter\_table = 0 Com\_analyze = 0 Com\_backup\_table = 0 Com\_begin = 0 Com\_change\_db = 1 Com\_change\_master = 0 Com\_check = 0 Com\_checksum = 0 Com\_commit = 0 Com\_create\_db = 0 Com\_create\_function = 0 Com\_create\_index = 0 Com\_create\_table = 0 Com\_dealloc\_sql = 0 Com\_delete = 0 Com\_delete\_multi = 0 Com\_do = 0 Com\_drop\_db = 0 Com\_drop\_function = 0 Com\_drop\_index = 0 Com\_drop\_table = 0 Com\_drop\_user = 0 Com\_execute\_sql = 0 Com\_flush = 0 Com\_grant = 0 Com\_ha\_close = 0 Com\_ha\_open = 0 Com\_ha\_read = 0 Com\_help = 0 Com\_insert = 0 Com\_insert\_select = 0 Com\_kill = 0 Com\_load = 0 Com\_load\_master\_data = 0 Com\_load\_master\_table = 0 Com\_lock\_tables = 0 Com\_optimize = 0 Com\_preload\_keys = 0 Com\_prepare\_sql = 0 Com\_purge = 0 Com\_purge\_before\_date = 0 Com\_rename\_table = 0 Com\_repair = 0 Com\_replace = 0 Com\_replace\_select = 0 Com\_reset = 0 Com\_restore\_table = 0 Com\_revoke = 0 Com\_revoke\_all = 0 Com\_rollback = 0 Com\_savepoint = 0 Com\_select = 2 Com\_set\_option = 2 Com\_show\_binlog\_events = 0 Com\_show\_binlogs = 0 Com\_show\_charsets = 0 Com\_show\_collations = 0 Com\_show\_column\_types = 0 Com\_show\_create\_db = 0 Com\_show\_create\_table = 0 Com\_show\_databases = 0 Com\_show\_errors = 0 Com\_show\_fields = 0 Com\_show\_grants = 0 Com\_show\_innodb\_status = 0 Com\_show\_keys = 0 Com\_show\_logs = 0 Com\_show\_master\_status = 0 Com\_show\_ndb\_status = 0 Com\_show\_new\_master = 0 Com\_show\_open\_tables = 0 Com\_show\_privileges = 0 Com\_show\_processlist = 0 Com\_show\_slave\_hosts = 0 Com\_show\_slave\_status = 0 Com\_show\_status = 1 Com\_show\_storage\_engines = 0 Com\_show\_tables = 0 Com\_show\_triggers = 0 Com\_show\_variables = 0 Com\_show\_warnings = 0 Com\_slave\_start = 0 Com\_slave\_stop = 0 Com\_stmt\_close = 0 Com\_stmt\_execute = 0 Com\_stmt\_fetch = 0 Com\_stmt\_prepare = 0 Com\_stmt\_reset = 0 Com\_stmt\_send\_long\_data = 0 Com\_truncate = 0 Com\_unlock\_tables = 0 Com\_update = 0 Com\_update\_multi = 0 Com\_xa\_commit = 0 Com\_xa\_end = 0 Com\_xa\_prepare = 0 Com\_xa\_recover = 0 Com\_xa\_rollback = 0 Com\_xa\_start = 0 Compression = OFF Connections = 5525 Created\_tmp\_disk\_tables = 0 Created\_tmp\_files = 8 Created\_tmp\_tables = 1 Delayed\_errors = 0 Delayed\_insert\_threads = 0 Delayed\_writes = 0 Flush\_commands = 2 Handler\_commit = 0 Handler\_delete = 0 Handler\_discover = 0 Handler\_prepare = 0 Handler\_read\_first = 0 Handler\_read\_key = 0 Handler\_read\_next = 0 Handler\_read\_prev = 0 Handler\_read\_rnd = 0 Handler\_read\_rnd\_next = 0 Handler\_rollback = 0 Handler\_savepoint = 0 Handler\_savepoint\_rollback = 0 Handler\_update = 0 Handler\_write = 130 Innodb\_buffer\_pool\_pages\_data = 32734 Innodb\_buffer\_pool\_pages\_dirty = 42 Innodb\_buffer\_pool\_pages\_flushed = 344512 Innodb\_buffer\_pool\_pages\_free = 1 Innodb\_buffer\_pool\_pages\_latched = 0 Innodb\_buffer\_pool\_pages\_misc = 33 Innodb\_buffer\_pool\_pages\_total = 32768 Innodb\_buffer\_pool\_read\_ahead\_rnd = 87 Innodb\_buffer\_pool\_read\_ahead\_seq = 26 Innodb\_buffer\_pool\_read\_requests = 67692652 Innodb\_buffer\_pool\_reads = 2644 Innodb\_buffer\_pool\_wait\_free = 0 Innodb\_buffer\_pool\_write\_requests = 11645744 Innodb\_data\_fsyncs = 793874 Innodb\_data\_pending\_fsyncs = 1 Innodb\_data\_pending\_reads = 0 Innodb\_data\_pending\_writes = 0 Innodb\_data\_read = 105140224 Innodb\_data\_reads = 3525 Innodb\_data\_writes = 894304 Innodb\_data\_written = 1643344384 Innodb\_dblwr\_pages\_written = 344512 Innodb\_dblwr\_writes = 9346 Innodb\_log\_waits = 0 Innodb\_log\_write\_requests = 6950991 Innodb\_log\_writes = 770955 Innodb\_os\_log\_fsyncs = 778708 Innodb\_os\_log\_pending\_fsyncs = 1 Innodb\_os\_log\_pending\_writes = 0 Innodb\_os\_log\_written = 3236460544 Innodb\_page\_size = 16384 Innodb\_pages\_created = 160179 Innodb\_pages\_read = 6284 Innodb\_pages\_written = 344512 Innodb\_row\_lock\_current\_waits = 1 Innodb\_row\_lock\_time = 60876042 Innodb\_row\_lock\_time\_avg = 335 Innodb\_row\_lock\_time\_max = 5194 Innodb\_row\_lock\_waits = 181202 Innodb\_rows\_deleted = 467013 Innodb\_rows\_inserted = 1386569 Innodb\_rows\_read = 55444397 Innodb\_rows\_updated = 488241 Key\_blocks\_not\_flushed = 0 Key\_blocks\_unused = 206174 Key\_blocks\_used = 25786 Key\_read\_requests = 5003135 Key\_reads = 82701 Key\_write\_requests = 494454 Key\_writes = 120223 Last\_query\_cost = 0.000000 Max\_used\_connections = 896 Not\_flushed\_delayed\_rows = 0 Open\_files = 88 Open\_streams = 0 Open\_tables = 209 Opened\_tables = 0 Qcache\_free\_blocks = 90 Qcache\_free\_memory = 16089184 Qcache\_hits = 27542 Qcache\_inserts = 73232 Qcache\_lowmem\_prunes = 0 Qcache\_not\_cached = 79181 Qcache\_queries\_in\_cache = 267 Qcache\_total\_blocks = 645 Questions = 1349114 Rpl\_status = NULL Select\_full\_join = 0 Select\_full\_range\_join = 0 Select\_range = 0 Select\_range\_check = 0 Select\_scan = 1 Slave\_open\_temp\_tables = 0 Slave\_retried\_transactions = 0 Slave\_running = OFF Slow\_launch\_threads = 0 Slow\_queries = 0 Sort\_merge\_passes = 0 Sort\_range = 0 Sort\_rows = 0 Sort\_scan = 0 Ssl\_accept\_renegotiates = 0 Ssl\_accepts = 0 Ssl\_callback\_cache\_hits = 0 Ssl\_cipher = Ssl\_cipher\_list = Ssl\_client\_connects = 0 Ssl\_connect\_renegotiates = 0 Ssl\_ctx\_verify\_depth = 0 Ssl\_ctx\_verify\_mode = 0 Ssl\_default\_timeout = 0 Ssl\_finished\_accepts = 0 Ssl\_finished\_connects = 0 Ssl\_session\_cache\_hits = 0 Ssl\_session\_cache\_misses = 0 Ssl\_session\_cache\_mode = NONE Ssl\_session\_cache\_overflows = 0 Ssl\_session\_cache\_size = 0 Ssl\_session\_cache\_timeouts = 0 Ssl\_sessions\_reused = 0 Ssl\_used\_session\_cache\_entries = 0 Ssl\_verify\_depth = 0 Ssl\_verify\_mode = 0 Ssl\_version = Table\_locks\_immediate = 601047 Table\_locks\_waited = 21465 Tc\_log\_max\_pages\_used = 0 Tc\_log\_page\_size = 0 Tc\_log\_page\_waits = 1 Threads\_cached = 6 Threads\_connected = 356 Threads\_created = 3414 Threads\_running = 6 Uptime = 56966