exxos's Atari, Amiga & retro forum

stephen_usher wrote: 17 Nov 2023 16:38 MySQL does seem highly inefficient when it comes to deleting or many table operations.

I do read on Google others seem to be having similar problems. But I don't really get why it was going like 50 a second before and has dropped to like 1 every 20 seconds now. The only thing which has changed is the table I am working on. It has the usual 5.5million rows, but so did the text database.

I ran some disk i/o stats and basically no disk access at all. Server has 6GB free RAM.

My A.I. friend wrote a script to calculate how long it would take to process all rows in the revision database ...

Start Count: 5525914
End Count: 5525910
Rows Changed: -4
Likely time to process all rows:
Hours: -23024.641666667
Days: -959.36006944444

So this poses somewhat of a small problem :lol: :roll:

I suppose in the future would be awesome when we have proper AI driven robots. I can just say to the AI robot, go solder all these circuit boards up and just leave it to it :lol:

EDIT:

I did a longer text over 10mins and looks better but still way to long.

Start Count: 5525901
End Count: 5525863
Rows Changed: -38
Likely time to process all rows:
Hours: -2423
Days: -100

Is there an index on rev_text_id?

If it's having to do a full table scan each time that would slow it down. Can you generate an execution plan or similar? Might show what's holding it up

I only do SQL Server so not sure about MySQL. I assume it's ballpark similar under the hood

EDIT

Or maybe updating the index(es) is slowing it down??

chronicthehedgehog wrote: 17 Nov 2023 18:30 Is there an index on rev_text_id?

Not sure, this is what the dump between text and revision database. I don't think they have indexes.

If it's having to do a full table scan each time that would slow it down. Can you generate an execution plan or similar? Might show what's holding it up

Possibly but why only slow down during deleting ? And why was it faster running on the text database but not the revision one?

If I run the script without the delete line, it completes within a couple of minutes.

It's really quite baffling.

exxos wrote: 17 Nov 2023 18:35 If I run the script without the delete line, it completes within a couple of minutes.

It's really quite baffling.

It could be the cost of updating the indexes.
Sometimes it's worth disabling them and recreating them afterwards for a big job - but only if you've got the means/scripts to recreate them

chronicthehedgehog wrote: 17 Nov 2023 18:43 It could be the cost of updating the indexes.

Possibly but again why did this not happen with the text database.

Sometimes it's worth disabling them and recreating them for a big job - but only if you've got the means/scripts to recreate them

The only possible solution I can really think is to read all the contents out and dump them in a new database. Aside from the time in writing the script it should only take a few minutes to complete the transaction. Though I'm somewhat cautious of screwing around with databases on that level.

I guess I could just turn the server off and on again :lol: There isn't much of anything else which is simple that I can really do at this point.

I've since realised that the database was actually MYISAM. So I converted the revision table to INNODB instead. Though I started the execution in phpmyadmin, and it logged me out. So i'm suspect if it actually completed before the logout or it continued in the background :shrug: but anyway, the table tests fine according to the check table thingy .

So the new results...

Start Count: 5525785
End Count: 5525699
Rows Changed: -86
Likely time to process all rows:
Hours: -1070
Days: -44

Basically twice as fast now. But still going to take a incredible long time :( I'll try again the optimise and de-frag options to see if that helps any.

The defrag has been running all-night and still going :roll: CPU and RAM are fine but disk io is maxed out. I benchmarked the disk and get over 600MB/sec write and double for reads. But with iotop it's only showing about 10MB/sec and almost no disk activity.

So I've emailed ramnode and asked about the problem. It seems something is amiss somewhere as the server is doing almost nothing and yet has a io bottleneck somehow.

I'm going to guess it's all io_wait. I think @chronicthehedgehog is on the right track regarding indexes. I'm guessing there's some immutable lock on each update.

troed wrote: 18 Nov 2023 15:25 I'm going to guess it's all io_wait. I think @chronicthehedgehog is on the right track regarding indexes. I'm guessing there's some immutable lock on each update.

But how to solve it ? And why the revision tables does it and not the text database. :shrug: