Description:
We have been using older versions of mysql without any problem (5.0.17)
I updated our test server to 5.0.27
scenario is.. temp table performs data conversion, master table is never deleted/updated, only new records insert, insert takes 30-40 minutes for bulk loads
under old mysql versions we had no table locking
under 5.0.27 the select locks, and after a few minutes I get this error
select * from table limit 1;
ERROR 5 (HY000): Out of memory (Needed 1371552 bytes)
Job running that causes the lock is, load data concurrent infile 'file' ignore into table
did something change? Do we need to switch to innodb (WAY slow in test) to do concurrent inserts & reads? I never delete from the tables, and concurrent was supposed to allow insert/read at the same time as long as there's no data holes, and there are none in my case, a delete statement has never been ran on this table, never will it's all historical data.
from the documentation..
For a |MyISAM| table, you can use concurrent inserts to add rows at the same time that |SELECT| statements are running if there are no deleted rows in middle of the table.
is this no longer the case?
I tried making it do inserts in batch form, ie 250 records at a time, but a 30 min load turned into a 3 hour load using that method, and it locks tables longer than I like.
We have been using older versions of mysql without any problem (5.0.19)
I updated our test server to 5.0.27
senario is.. temp table performs data conversion, master table is never deleted/updated, only new records insert, insert takes 30-40 minutes for bulk loads
under old mysql versions we had no table locking
under 5.0.27 the select locks, and after a few minutes I get this error
select * from table limit 1;
ERROR 5 (HY000): Out of memory (Needed 1371552 bytes)
Job running that causes the lock is, load data concurrent infile 'file' ignore into table
did something change? I never delete from the tables, and concurrent was supposed to allow insert/read at the same time as long as there's no data holes, and there are none in my case, a delete statement has never been ran on this table, never will it's all historical data.
show variables like 'concur%'
concurrent_insert 1
How to repeat:
test table
CREATE TABLE `table_test` (
`column1` int(11) NOT NULL,
`column2` char(10) default NULL,
`column3` char(10) default NULL,
`column4` char(10) default NULL,
`column5` char(10) default NULL,
`column6` char(10) default NULL,
`column7` char(10) default NULL,
`column8` char(10) default NULL,
`column9` char(10) default NULL,
`column10` char(10) default NULL,
PRIMARY KEY (`column1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1;
load 1 million rows of junk
LOAD DATA CONCURRENT INFILE '/temp/table_test.txt' REPLACE INTO TABLE
TABLE_TEST fields terminated by '|' optionally enclosed by '' lines
terminated by '\n';
select while the load is running, with 5.0.17 it returns results, with 5.0.18 and newer it locks until the load is complete
select * from table_test;
5.0.17 reports 0 records, 5.0.27 locks until the insert/replace is done, it does this with all selects, not just the count
If you repeat without trunicating the table, select * from table_test where column1='0'; you get the first record with no lock, under 5.0.19 and greater, it locks until the entire load is complete
Our normal tables take 15-30 minutes to load input files, so the locks run mysql client out of memory when it retries over and over
sample data to load can be downloaded from..
http://216.130.145.190/testdata.zip
Suggested fix:
compare 5.0.17 to 5.0.27 concurrent code
