Heikki Tuuri wrote:
>
> Steve,
>
> on what operating system you are running?
>
linux, intel 2.2.12
> Was the rollback (and the table load) disk bound? Did you configure much
> memory to the InnoDB buffer pool?
>
I would say the rollback was processor bound, the machine was running
with almost no CPU idletime and this event was about the only thing
taking
place. the machine as 192 megs ram and buffer pool=20M
> Was MySQL responsive during the table loading?
>
Yes, very responsive for other MyISAM tables,
select count(*) from the table I was loading was taking about
4 minutes each, but simple select queries looking for data that
had not been commited were much faster.
> Is MySQL responsive if you do a big rollback without cancelling
> (ctrl-c) the client process?
>
If the file ends in ROLLBACK; then it works fine, it rolls back
and mysql is reponsive through the whole process. If the
file ends with an insert and there is never a commit or rollback
then mysql is unresponsive even if the load is not cancelled
(ctrl-c).
I will have to test further but it seems that if I do only 100,000
rows and then the file terminates, then it is okay but for 200,000
rows it can take 20 minutes or more to clean up while mysql is
unresponsive.
> The unresponsiveness may be because MySQL reserves some
> semaphore while it is aborting the client connection. Or it may be
> due to the operating system which lets other threads starve.
>
I'm not familiar with OS level thread control in the process itself,
but the machine (other than mysql) was very responsive.. Unlike when
I try to start mysql with a 1 gig table space to be built, then
the load goes up to about 15 and the machine is very slow to respond
(obviously).
> The above questions help track the reason for the unresponsiveness.
> If the problem is the operating system, I could try adding some
> calls to pthread_yield inside the InnoDB code and that might help.
>
> When InnoDB writes the 1 GB data file full, then MySQL is
> unresponsive because it does the initialization of the database
> with just one thread before creating other, listener threads.
>
> Regards,
>
> Heikki
> http://www.innobase.fi
>
> >There seem to be some operations that cannot be performed on the
> >server whie InnoDB is doing something.
> >
> >For example I inserted about 200,000 rows in to a table from
> >a file (mysql database < file) the file starts with begin; and
> >contains many inserts, I cancelled (ctrl-c) in the middle of the
> >operation which caused InnoDB to rollback (as expected) but
> >the mysql database whas unresponsive for about 10 minutes while
> >innoDB did the rollback. The server would not respond to any
> >processlist requests, connections or anything. This is not so good
> >for the increased read/write concurrency expected from innodb.
> >
> >Also.. If a new table space has been added, it can take a while
> >for innoDB to format 1Gig of tablespace (a long while on a 400mhz
> >pentium). Is there a reason why the whole mysql database must
> >be unresponsive durring this time? Why can't the system only lock
> >out InnoDB format tables if need be and allow MySQL to startup and
> >respond to questions.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
