The resurrection of atari-wiki.com

[chronicthehedgehog]

Wow, 7M external links.

That's a lot of chopped pork and ham

Good work

[exxos]

Wow, 7M external links.

That's a lot of chopped pork and ham

Yes, did I mention the database is pretty bad ?

[troed]

Absolutely awesome work Chris! This is such a valuable resource.

[exxos]

Absolutely awesome work Chris! This is such a valuable resource.

[exxos]

That completed quicker than I thought.

5.5million revision tables scanned.

Capture.PNG (34.95 KiB) Viewed 1009 times

Next to make sure the "found" count matches the number of posts in the text database, then delete the invalid entries in the revision database.

[exxos]

ChatGPT made me chuckle with the code it wrote... Use with caution yeah right!

Capture.PNG (10.37 KiB) Viewed 1002 times

Let the carnage commence...

EDIT:

Looks like it's likely to take several hours to clean up the revisions database. One thing I have noticed is that some of the wiki actual pages are not in the text database at all. There was a discrepancy of several thousand indexes. I won't know until it's finished cleaning up the revision table. Though things like the wiki about page currently don't seem to be linked in the text table at all. So they must be in some other part of the database

[exxos]

Does anyone know why deleting entries is incredibly slow ? It seems to be taking like 20 seconds to delete a row. I can only assume because there are literally millions of entries in the database, but I did not really have this slowdown with the text database. I thought deleting 50 at once was slow

It's been going three hours now and the file size in my debug log is only 1,337,251. The size of the "read only" log is 231,919,539 and did not take many minutes to complete. So according to my mathematical guesswork, it is going to take approximately 520 hours to clean up the table

[troed]

What's the database engine? MySQL? If so there must be something wrong with the way the deletion is done

[exxos]

What's the database engine? MySQL? If so there must be something wrong with the way the deletion is done

Yeah mySQL.

It only does this.

Code: Select all

$deleteSql = "DELETE FROM revision WHERE rev_text_id = $revTextId";
             $conn->query($deleteSql);

Nothing different than I was doing before with the text database. But the revision database is going to take 22 days at this rate

SQL is also maxing out one of the CPU cores. It did that while working on the text database, but it varied a lot. Now it's just flatlined 100%. I did wonder if running the script twice would be faster. But I doubt it, SQL seems to "lock up" if you try and access the database multiple times at once.

The only time I've really seen this behaviour is on the forum when the SQL database was crashing. But I'm not seeing anything in the logs indicating that what I can tell. I have seen it when the disk is full it runs incredibly slow but there is 80% free...

[stephen_usher]

MySQL does seem highly inefficient when it comes to deleting or many table operations.