Steve,
At 09:48 AM 5/15/01 -0600, you wrote:
>Heikki Tuuri wrote:
>>
>> 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.
>>
>
>If the large data file simply dones't end in COMMIT; then mysql
>should not abort the connection it closes gracefully no? Then rollback
>should be run after timeout? If the thread doesn't exit with
>(ctrl-c) should the ROLLBACK; be graceful?
ending a connection without a commit or a rollback from the user is
really abnormal termination. The user should decide whether his
work is committed or rolled back. Now the default is that if
autocommit=0 and the user end the connection his work is rolled back.
But that is not what the user normally wants.
Regards,
Heikki
>> 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
