Advertisement

Database Maintenance

This is the main board on The Outhouse, where Outhousers talk about everything. No topics are off limits, and it doesn't have to be about comics. All the topics from the other boards also show up in The Asylum, so you never have to leave1

Hey you! Reader! Want to be a part of the GREATEST COMIC BOOK AND GEEK COMMUNITY on the web?! Logged in users see WAY LESS ADS, so why not register? It's fast and it's easy, like your mom! Sign up today! Membership spots are limited!*

*Membership spots not really limited!

S.F. Jude Terror
User avatar
OMCTO
 
Posts: 75954
Likes: 379 posts
Liked in: 528 posts
Joined: Thu Jun 12, 2008 11:44 pm
Location: Up Your Ass
Title: Webmaster Supreme
Formerly: Dr. Jude Terror


Database Maintenance

Postby S.F. Jude Terror » Mon Feb 11, 2013 2:29 am

In a little bit, I'm going to run an optimization on the posts table on the database. The maintenance page will be up with a live chat while it runs. It shouldn't take more than an hour at most. The regular forum will come back as soon as it finishes automatically.

Trying to get something together that can run automatically at some late hour on a weekend each week to keep the tables running smoothly. This will be the first test run.
Image
I LOVE BLUD BLOOD! - Rob Liefeld

Advertisement

covalesky
User avatar
Expert Post Whore
 
Posts: 5290
Likes: 0 post
Liked in: 1 post
Joined: Fri Sep 03, 2010 11:48 am


Re: Database Maintenance

Postby covalesky » Mon Feb 11, 2013 5:40 am

Don't know if its related or not, but I've been getting the search not allowed atm error throughout the weekend most of the times I try to see the newest posts.

Also getting the Thread does no exists when I click the go to last post icon in the threads list.

sdsichero
User avatar
2k11 Outhouse People's Champion
 
Posts: 69116
Likes: 0 post
Liked in: 393 posts
Joined: Wed Jun 04, 2008 10:25 pm
Location: Secret Base, Honolulu, HI
Title: The Show Starts In 2 Minutes
Formerly: beta-ray


Re: Database Maintenance

Postby sdsichero » Mon Feb 11, 2013 10:38 am

Yeah I can't search either. That's a bummer.

S.F. Jude Terror
User avatar
OMCTO
 
Posts: 75954
Likes: 379 posts
Liked in: 528 posts
Joined: Thu Jun 12, 2008 11:44 pm
Location: Up Your Ass
Title: Webmaster Supreme
Formerly: Dr. Jude Terror


Re: Database Maintenance

Postby S.F. Jude Terror » Mon Feb 11, 2013 10:55 am

Search is disabled when server load is too high. I have a replacement for this coming next week hopefully that will speed everything up. The server is a fucking nightmare right now, mostly because of all the big threads at this point, but in general because there are 2.5 million posts. It's completely overloaded trying to sort through them.
Image
I LOVE BLUD BLOOD! - Rob Liefeld

Victorian Squid
User avatar
YOU WILL NEED A NURSE
 
Posts: 31777
Likes: 0 post
Liked in: 1 post
Joined: Sat Sep 27, 2008 10:04 am
Formerly: Schmormerly


Re: Database Maintenance

Postby Victorian Squid » Mon Feb 11, 2013 10:58 am

Let go of the past and erase it all.

S.F. Jude Terror
User avatar
OMCTO
 
Posts: 75954
Likes: 379 posts
Liked in: 528 posts
Joined: Thu Jun 12, 2008 11:44 pm
Location: Up Your Ass
Title: Webmaster Supreme
Formerly: Dr. Jude Terror


Re: Database Maintenance

Postby S.F. Jude Terror » Mon Feb 11, 2013 11:01 am

I actually have a cool solution for handling. Real quick explanation then I gotta go:


In order to have search enabled, the board keeps an index on post subject, post text, and both together.

This essentially duplicates the post table twice.

Every time a post is made, both indexes must be updated.

Means we're altering 2.5 million rows every time.

I plan to replace search with a custom search engine.

It will separate those indexes, reducing the posts table by 2/3, making posting much faster.

With posting faster, the tables won't be locked when trying to read from it.

Search will also go faster, being its own table.

It's just gonna be some work to get that going.
Image
I LOVE BLUD BLOOD! - Rob Liefeld

S.F. Jude Terror
User avatar
OMCTO
 
Posts: 75954
Likes: 379 posts
Liked in: 528 posts
Joined: Thu Jun 12, 2008 11:44 pm
Location: Up Your Ass
Title: Webmaster Supreme
Formerly: Dr. Jude Terror


Re: Database Maintenance

Postby S.F. Jude Terror » Mon Feb 11, 2013 11:03 am

Another thing I'm seeing is brutal is the jump to new post link on large threads. Looking at a way to disable it.

It requires, instead of a query that loads 50 or so posts, going through 115,000 posts or something (on the twenty-somethings thread for instance) in order to make the pagination.
Image
I LOVE BLUD BLOOD! - Rob Liefeld

Keb
User avatar
<( ' . ' )>
 
Posts: 39601
Likes: 126 posts
Liked in: 194 posts
Joined: Sun Mar 26, 2006 9:14 pm
Location: Right near de mountains, boy!
Title: Wastespacer
Formerly: Cocaine Biceps


Re: Database Maintenance

Postby Keb » Mon Feb 11, 2013 11:25 am

I say we prune the forums!

IvCNuB4
User avatar
Staff Writer
 
Posts: 10528
Likes: 287 posts
Liked in: 188 posts
Joined: Thu Aug 06, 2009 11:51 am
Location: Chicago, IL
Title: Stop messing with my TITLE !


Re: Database Maintenance

Postby IvCNuB4 » Mon Feb 11, 2013 11:29 am

Victorian Squid wrote:Let go of the past and erase it all.


:lol:
Seriously. How much additional space can we also free up if we delete all the fucking endless "Legion of Super Heroes reboot is an abortion" and "it just gets worse" sales doom-n-gloom posts ? :lol:
-
Victorian Squid wrote:In a DC/Marvel cross-over "all your favorite Marvel characters would catch contextually-transmitted diseases and Steph infections."

superfictious
User avatar
Humuhumunukunukuapuaa
 
Posts: 10370
Likes: 78 posts
Liked in: 158 posts
Joined: Tue Jun 03, 2008 2:38 pm
Title: superfictious.wordpress.com


Re: Database Maintenance

Postby superfictious » Mon Feb 11, 2013 11:33 am

IvCNuB4 wrote:
:lol:
Seriously. How much additional space can we also free up if we delete all the fucking endless "Legion of Super Heroes reboot is an abortion" and "it just gets worse" sales doom-n-gloom posts ? :lol:


Are you saying this "maintenance" is a secret ploy to get rid of Nac and Herald? :o

Keb
User avatar
<( ' . ' )>
 
Posts: 39601
Likes: 126 posts
Liked in: 194 posts
Joined: Sun Mar 26, 2006 9:14 pm
Location: Right near de mountains, boy!
Title: Wastespacer
Formerly: Cocaine Biceps


Re: Database Maintenance

Postby Keb » Mon Feb 11, 2013 12:04 pm

Man has a point. Mega threads don't help andthe good ones we can always recreate. A fresh start would be good.

sdsichero
User avatar
2k11 Outhouse People's Champion
 
Posts: 69116
Likes: 0 post
Liked in: 393 posts
Joined: Wed Jun 04, 2008 10:25 pm
Location: Secret Base, Honolulu, HI
Title: The Show Starts In 2 Minutes
Formerly: beta-ray


Re: Database Maintenance

Postby sdsichero » Mon Feb 11, 2013 12:23 pm

Victorian Squid wrote:Let go of the past and erase it all.


Jude is going to do the Superboy punch/Flashpoint??? :shock:

S.F. Jude Terror
User avatar
OMCTO
 
Posts: 75954
Likes: 379 posts
Liked in: 528 posts
Joined: Thu Jun 12, 2008 11:44 pm
Location: Up Your Ass
Title: Webmaster Supreme
Formerly: Dr. Jude Terror


Re: Database Maintenance

Postby S.F. Jude Terror » Mon Feb 11, 2013 12:33 pm

History is important. :P


I have disabled search and I am importing all the posts into a new database table, one with less indexes, which is INNODB, which supports row level locking (making a post won't prevent reading from the table).

This will probably take most of the day. After that, we will begin rebuilding the search engine, and search will return (this could take several days). After that, everything should be a fuck of a lot faster.
Image
I LOVE BLUD BLOOD! - Rob Liefeld

S.F. Jude Terror
User avatar
OMCTO
 
Posts: 75954
Likes: 379 posts
Liked in: 528 posts
Joined: Thu Jun 12, 2008 11:44 pm
Location: Up Your Ass
Title: Webmaster Supreme
Formerly: Dr. Jude Terror


Re: Database Maintenance

Postby S.F. Jude Terror » Mon Feb 11, 2013 1:59 pm

Test...


Posts table is now INNODB. Let's see if the awful slowness goes away now.
Image
I LOVE BLUD BLOOD! - Rob Liefeld

S.F. Jude Terror
User avatar
OMCTO
 
Posts: 75954
Likes: 379 posts
Liked in: 528 posts
Joined: Thu Jun 12, 2008 11:44 pm
Location: Up Your Ass
Title: Webmaster Supreme
Formerly: Dr. Jude Terror


Re: Database Maintenance

Postby S.F. Jude Terror » Mon Feb 11, 2013 2:03 pm

Next I will be rebuilding search.
Image
I LOVE BLUD BLOOD! - Rob Liefeld

leave a comment with facebook

Next

Return to The Asylum



Who is online

Users browsing this forum: No registered users and 45 guests

Advertisement