Steve,
your observations suggest that it is MySQL which is holding some
semaphore while it is aborting a connection (which in your case
required a big rollback). The operating system seems to be innocent.
On Windows NT I have seen that disk operations will freeze the
computer almost totally. Linux is better I think.
I do not see this as a big problem, if the unresponsiveness is only
when MySQL aborts a connection. Especially, Monty's TODO list is
overflowing, and better not add yet another item there.
Your buffer pool of 20 MB is small. When the tables grow your
operation can become disk bound and really slow down. During big
table imports you might want to allocate a much bigger buffer pool.
Regards,
Heikki
At 05:09 PM 5/14/01 -0600, you wrote:
>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
