The resurrection of atari-wiki.com

[exxos]

Just got a reply from ramnode..

. We've been investigating and we may have identified a hardware issue with the node that could be contributing to your poor I/O speed. Being the weekend we might not be able to get eyes on it until the beginning of next week.

If you would like we can try migrating your server to another node, there may be some significant downtime during the migration since your disk is quite large.

[exxos]

Finally some progress with the server. Long story short, they said they don't think they can get the parts any time soon. So they moved me onto a different node on the server. Where the IO problem rather than maxing out at 100% is pretty much bouncing on zero all the time now!

I restored the database just to make sure nothing got corrupted. So its back to myisam now..

Start Count: 5527030
End Count: 5526987
Rows Changed: -43
Likely time to process all rows:
Hours: -2142
Days: -89

It was more like 900 days before!

As converting to innodb was faster last time, ive started the database conversion again. Then see how long it will take..

[exxos]

After conversion to innodb..

. Start Count: 5525707
End Count: 5525578
Rows Changed: -129
Likely time to process all rows:
Hours: -713
Days: -29

So I've gone from 1 delete every 22 seconds to one every 4.5 seconds.. still getting nowhere near like 50 deletes a seconds I had with the other tables

I'm going to run the script twice and attack the table from both ends. Not sure if it will help next.

The IO stats are still bouncing on zero. Though now the CPU is maxed out, well one core anyway. A second script may run on another core but depends where the bottle neck is. Assume the SQL itself.

Edit:

. Start Count: 5525330
End Count: 5525202
Rows Changed: -128
Likely time to process all rows:
Hours: -719
Days: -29

No great surprise Maybe it will slowly speed up as the database gets smaller.

[elterwater]

Would it not be quicker just to export the entries you want to keep to a copy of the table, truncate this one and then re-import the good stuff back?

[exxos]

Finally figured out how to add the index onto the revision database. It is going a few orders of magnitude faster now

Start Count: 5502536
End Count: 5433202
Rows Changed: -69334
Likely time to process all rows:
Hours: -1
Days: 0

I am outputting to a debug log and it is going a lot faster but I don't think it is going to complete in one hour. I still think it is going to take at least a day. Still it is a huge improvement nonetheless!

I am not at home tomorrow anyway so won't be able to do any more work until Thursday at the earliest anyway. Need to get off the PC for a bit and have a rest!

EDIT:
Ah it's slowing down down a bit.

Start Count: 5285635
End Count: 5242531
Rows Changed: -43104
Likely time to process all rows:
Hours: -2
Days: 0

[exxos]

I think I must of messed up the time estimate calculation originally when I change from 1 to 10mins sample somehow.. So the actual times I quoted were probably 10 times less than they were supposed to be So the 29 days would actually have been 290 days

As it has been running for a couple of hours now and clearly the time estimate script is not correct. A manual calculation and I get about 18 hours.

But anyway, now the script is changed again..

Start Count: 4703395
End Count: 4698211
Rows Changed: -5184
Likely time to process all rows:
Hours: -15
Days: 0

So 15 hours to tidy up the revision database. Which is fine because I won't be at home tomorrow anyway. Then hopefully on Thursday I can work on the titles database and clear out all the crap out of that.

[exxos]

It is certainly blasting through them now considering it started around 5525330!

Start Count: 3900006
End Count: 3895636
Rows Changed: -4370
Likely time to process all rows:
Hours: -14
Days: 0

The PAGE database is the next on in the "chain" to tidy up. Though I need to do something about that search index because it is enormous!

Capture.PNG (97.95 KiB) Viewed 130 times

So apparently I can just drop the table and rebuild it anyway...
https://www.mediawiki.org/wiki/Manual:R ... tindex.php

[troed]

Finally figured out how to add the index onto the revision database. It is going a few orders of magnitude faster now

Awesome - yeah it did some like that had to have something to do with it

[exxos]

Revision table now completed Also converted the whole database to innodb.

So next to finish my script to sorting out the PAGE table.

Capture.PNG (138.27 KiB) Viewed 94 times

[exxos]

Right so..

Code: Select all

Page ID 4456 exists in the REVISION table
Page ID 4457 exists in the REVISION table
Page ID 23624 does not exist in the REVISION table
Page ID 3570 exists in the REVISION table
Page ID 3754 exists in the REVISION table
Page ID 3755 exists in the REVISION table
Page ID 3865 exists in the REVISION table
Page ID 3941 exists in the REVISION table
Page ID 4245 exists in the REVISION table

Double checking that ID 23624 is:

1.PNG (15.87 KiB) Viewed 134 times

So its a "bad" one, so all good there.

Checking a matched ID such as 4456:

2.PNG (14.29 KiB) Viewed 134 times

So far so good.....