Re: Can I dissable transactions?

[Michael Stassen]

Ben Clewett wrote:
Jeff,
Thanks for your idea.  Deleting data x rows at a time would certainly 
help, if AUTOCOMMIT=TRUE.  But I have no idea how :)  I have tried:

DELETE FROM very_large_table WHERE delete_flag=1 LIMIT 10;
But the LIMIT is not understood (4.1.8).
What do you mean?  That appears to be valid syntax.  Do you get an error or 
unexpected results?  (You might also want to try a smaller limit).

Unfortunately my 'delete_flag' is not key.  Therefore repeated attempts 
at deleting small amounts of data is very slow.
Right, no index on delete_flag means a table scan.  The LIMIT N should cause 
mysql to stop as soon as it finds (and deletes) the Nth matching row, 
though.  The trick is to set N low enough to get the speed you need. 
Unfortunately, each run will probably take longer than the one before, as 
there will be farther to go to find the first match.

I expect delete_flag is either 0 or 1.  What percent of rows have 
delete_flag = 1?  If small enough, an index on delete_flag would be used for 
this statement to speed things up.  Of course, that's an extra index 
probably not used for anything else.

If you haven't read them already, you may find these pages from the manual 
helpful:



The fastest way seem to be to dump the data, edit the file, and 
re-insert the data.

But you have given my my solution:
If I cannot disable transactions, I'll have to work with one of the keys 
and iterate through that key bit bit.  So thanks, I'll go off and give 
it a go...

Ben.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can I dissable transactions?

[Ben Clewett]

Jeff,
Thanks for your idea.  Deleting data x rows at a time would certainly 
help, if AUTOCOMMIT=TRUE.  But I have no idea how :)  I have tried:

DELETE FROM very_large_table WHERE delete_flag=1 LIMIT 10;
But the LIMIT is not understood (4.1.8).
Unfortunately my 'delete_flag' is not key.  Therefore repeated attempts 
at deleting small amounts of data is very slow.

The fastest way seem to be to dump the data, edit the file, and 
re-insert the data.

But you have given my my solution:
If I cannot disable transactions, I'll have to work with one of the keys 
and iterate through that key bit bit.  So thanks, I'll go off and give 
it a go...

Ben.
Jeff Smelser wrote:
On Monday 14 February 2005 03:52 am, Ben Clewett wrote:
I am having a lot of problems deleting a large amount of data.  Say 20GB
from a 40GB table.  I seem to get failure quite a lot (due NOT to mysql,
but bad hardware), then MySQL roles back the transaction, which takes as
many hours and starting the transaction.  I also get this a lot:

There is a feature of DB2 that can do this.. Its really not always all its 
cracked up to be..

In this case, it would happily delete, if something goes wrong, your table is 
now marked bad.. The other 20million rows are now gone.. Is that what you 
want?

What you need to do, is set up a simple script to delete 20,000 rows a time, 
and commit, just keep doing it till its done.. This way you could do 20,000 
rows, wait a bit, do it again. or whatever. If it fails, you only rollback 
what it was doing during the transaction and you wont have to start all over.

Jeff
--
Ben Clewett
Road Tech Computer System Ltd
[EMAIL PROTECTED]
http://www.roadrunner.uk.com
+44(0)1923 46
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can I dissable transactions?

[Jeff Smelser]

On Monday 14 February 2005 03:52 am, Ben Clewett wrote:
> I am having a lot of problems deleting a large amount of data.  Say 20GB
> from a 40GB table.  I seem to get failure quite a lot (due NOT to mysql,
> but bad hardware), then MySQL roles back the transaction, which takes as
> many hours and starting the transaction.  I also get this a lot:

There is a feature of DB2 that can do this.. Its really not always all its 
cracked up to be..

In this case, it would happily delete, if something goes wrong, your table is 
now marked bad.. The other 20million rows are now gone.. Is that what you 
want?

What you need to do, is set up a simple script to delete 20,000 rows a time, 
and commit, just keep doing it till its done.. This way you could do 20,000 
rows, wait a bit, do it again. or whatever. If it fails, you only rollback 
what it was doing during the transaction and you wont have to start all over.

Jeff


pgpd6v4TIoxmG.pgp
Description: PGP signature

Can I dissable transactions?

[Ben Clewett]

Dear MySQL,
I am having a lot of problems deleting a large amount of data.  Say 20GB 
from a 40GB table.  I seem to get failure quite a lot (due NOT to mysql, 
but bad hardware), then MySQL roles back the transaction, which takes as 
many hours and starting the transaction.  I also get this a lot:

"Lock wait timeout exceeded; Try restarting transaction"
With InnoDB is there a way of completely disabling transactions on a 
session.  So I can delete data without rollback and on bad termination, 
can restart and continue deleting where I left off?

Many thanks for an answer to this problem,
Ben Clewett.

--
Ben Clewett
Road Tech Computer System Ltd
[EMAIL PROTECTED]
http://www.roadrunner.uk.com
+44(0)1923 46
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]