Re: MySQL + NAS(NFS or CIFS) + Huge Table Insert Problem (Long)
Date: September 20, 2005 08:45AM
Ingo Str�wing wrote:
> Though I feel like being neither a performance
> expert nor an InnoDB expert, I'll share my
> thoughts and raise some questions.
> InnoDB is a transactional storage engine. At every
> commit (at least) it needs to flush its log
> synchronical to the disk (fsync(2)). This means
> that it has to wait for acknowledge from the disk
> that the log data are safe. Now the question is:
> How long are your transactions? Do you commit
> after each batch? After each couple of inserts?
> After every insert statement? Do you run with
> autocommit (which means an implicit commit after
> each insert statement)?
I played with a number of things here...
My inserts towards the end (before all got bad) were running at between 500 and 1500 rows/second. I was initially doing single line inserts of ~200k to 1.5M rows an hour, in a batched single update process (our application would output "INSERT into LARGE_TABLE A=0, B=...; next line). When things began to get bad, we converted our insert statements (through a perl preprocessor) into "Insert into LARGE_TABLE values (0,...),(0,...) ; (myisamdump -e syntax) with 10,000 rows per statement. This really had little effect on the issue and I'm wondering if the single huge table over NFS isn't what was getting us.
The perl script is now happily inserting the batched rows in lots of 100k in ~10-15 seconds (to local disk and the much smaller daily tables) - assuming DELAY_KEY_WRITE - haven't timed it without the option though will get the opportunity in the morning. I am terrified of putting anything other than packed MyISAM tables (read-only) on the NFS mount until I figure out what is going wrong with them.
> The above it the default behaviour. It might be
> slightly influenced by the option
> innodb_flush_log_at_trx_commit if durability
> requirements are not too high.
I really only had the one process doing inserts, and even when the inserts would run into the next hour, the script would see the prior running and bow out (more later on this process). I'd get a slight accordion effect during the day (where the data would back up) that would catch up with lighter activity at night. I messed around with some locking option in the InnoDB portion of my.cnf that said I was asking for a performance hit if I set the option (glutton for punishment, I tried - same error, only took longer to get there :) ).
I may try to set up another mountpoint on the filer with a few spare disks and try that option out if I haven't already (notes are at work and I post from home).
> You might want to check your NAS performance with
> a small test program, which operates similar to
> your dd test, but fsync(2)s the disk after every
> block (or every second).
THAT will be a fun test :) Once I feel like the current issues have begun to stabilize, I'll definitely play here. If it dies here, it will confirm the NFS client as the culprit and I can focus there.
I've really been trying to find a way to focus on the cause of the issue because every indication I have so far is that every component of the system (NAS+server+network link) is operating at well below its performance capabilities when I add in MySQL, which would seem to make it the sorta obvious scapegoat, except that it is bored as well. Adding the fsync call to DD just to see what happens - this gives me somewhere to start.
Before this post I sat lost only being able to see:
Thanks for this - you saw something I had been missing :)
> Since MyISAM does not use fsync(2), I wonder what
> it means that "a MyISAM table on localhost it runs
> fine, albeit slower". If using MyISAM instead of
> InnoDB is an option for you (beware the durability
> implications), I would like to ask you to expand
> more about what you tried with it. Since I feel
> more knowlegable with MyISAM, I might be able to
> provide more ideas for its optimization.
I never really had more than one process *writing* to the DB at once. My inputs come from multiple sources, but are batched for hourly inserts by a shell script that does 'mysql -ppass -udbuser -d foo < inputfile.sql && rm inputfile.sql || mv inputfile.sql $FAILED_DIR'.
If the last hourly script has not completed, a new one will die...
if [ -f lock/lock.file ]
echo "Process from $LAST is still running. Quitting."
Sometimes I'd back up for an hour and have more files to process, and I did have periods of a few days where all I did was spool files (while troubleshooting). The scripts would catch up, processing fewer and fewer files with each run, until they were current.
At this point, here's where this behemoth is at:
I selected into daily tables from the monster with a shell script: (basic steps)
-create table LT_2005_mm_dd (...);
-INSERT INTO LT_2005_mm_dd
SELECT * from foo.LARGE_TABLE
WHERE C >= '2005-mm-dd' and C < '2005-mm-dd+1';
-myisampack LT_2005_mm_dd ;
-myisamchk -rq LT_2005_mm_dd;
-date = date+1
That ran for a bit to get me back to where I could see my data...
Now, every day I create (from the insert script) a new identical MyISAM table (LT_2005_09_20) and batch the inserts (in 100K increments) into it hourly. Once that process is stable (the script we were using for insertions was written specifically for the "single table" and making it smart enough to figure out what's going on has been a bit of a bear...), I'll start playing with the whole merge table thing to get us back to properly defined monthly MERGE tables, and hopefully progress to an all-inclusive MERGE of the MERGEs... (if I can pull that off - otherwise, I'll stick to the whole stuck with 127 days thing).
MyISAM has proven very space friendly with PACK_KEYS=1+myisampack. On the 500G local storage I have a while to play before I *need* the NFS mount again.
I'd really like to figure out what's going on with this, but I'm guessing the combination of read-only tables and multiple smaller tables will clear my issues up (for values of "guess" that include wildly crossing my fingers). The dd+fsync approach will be fun just to see if I can figure out where the issue is. I'm pretty happy with the performance of the NetApps filer (blows the doors off of RAID5 local for writes where MySQL is stuck in IOWait for periods that feel like days on end :) ), overall happy with both MySQL and the Linux NFS clients, but would love to know what's hanging me up.
Identifying/fixing it has the potential to help us all (or at least any poor fool like me stuck with NFS as their only large storage option), but I wasn't really sure where to begin. Strace'ing the input isn't really an option and I can't trigger the bug consistantly, just frequently enough to be a showstopper with our previous implementation plan.
Thanks for the post - you gave me somewhere to look that I had been missing.