DB Maint - DONE!
- FishPants
- Server WhOOre
- Posts: 4661
- Joined: Fri Oct 15, 2004 1:38 pm
- Location: Canada
DB Maint - DONE!
Tonight I am going to upgrade the database tables to Innodb barracuda format, which requires a dump of the DB, dropping every table, recreating every table and then import of the data. This is likely going to take a long time, and the board will be down during this period.
I will update the HTML page that I used last time so you don't just get the SQL errors when going to the web page, but the benefit here is multifold:
- Large tables (in our case posts and search wordlists) are significantly smaller
- Complex queries come back significantly faster (i.e. searching)
- Less disk io required for the enhanced performance
I will make a full backup prior to the system maintenance of course, so worst case I am up half the night and restore to where we were when I brought it offline.
I will update the HTML page that I used last time so you don't just get the SQL errors when going to the web page, but the benefit here is multifold:
- Large tables (in our case posts and search wordlists) are significantly smaller
- Complex queries come back significantly faster (i.e. searching)
- Less disk io required for the enhanced performance
I will make a full backup prior to the system maintenance of course, so worst case I am up half the night and restore to where we were when I brought it offline.
Last edited by FishPants on Mon Aug 22, 2011 11:32 am, edited 3 times in total.
No.
- Odin
- Posts: 20732
- Joined: Tue Oct 12, 2004 11:29 pm
- Location: Syracuse, NY
Re: Database change tonight at 2000hrs eastern (or thereabou
Thanks FishPants! You rock!
My Blog: Virtual Vellum
- KKBlue
- Posts: 3972
- Joined: Fri Nov 19, 2010 10:07 am
- Location: Connecticut
Re: Database change tonight at 2000hrs eastern (or thereabou
Thanks for the improvements. I do use the Amazon link to help pay my respects for your efforts. I know that just because you are good at something (job) doesn't mean that you enjoy doing it, so it's an honest thanks coming your way.
"Why do people say grow some balls? Balls are weak and sensitive. If you wanna be tough, grow a vagina. Those things can take a pounding!" - Betty White
- FishPants
- Server WhOOre
- Posts: 4661
- Joined: Fri Oct 15, 2004 1:38 pm
- Location: Canada
Re: Database change tonight at 2000hrs eastern (or thereabou
Ok so that search wordlist took forever to restore.. I had to call it a night at midnight and let it continue (no idea what time it finished).
The good news:
1. Innodb upgraded to support Barracuda.
2. All tables dropped and recreated as single files per table (prep and necessary for Barracuda format).
3. Restored DB
4. Slept.
5. This morning I ran an ALTER TABLE on a couple small tables to test barracuda, it worked.
I know, I know "So now what FishPants?!?! What other kind of awesome database stuff is coming?! DON'T LEAVE US HANGING!!"
So with #5 working, I can convert on the fly -- that means the board won't be going back offline to do this. The bad news is that when it hits large tables (Search wordlist, and posts table specifically) you won't be able to post/edit etc. This will result in the session sitting there waiting and waiting; if enough people are doing this at the time (50-100), or pressing refresh, the mysql connection limit will be reached and you will get an error that all connections are in use.
So, what say you OO -- What do you guys prefer? Do you want the board to show a nice "Sorry the board is offline for now" for say 5-6 hours, or would you rather take a gamble and let it lock tables as it needs -- possibly resulting in Mysql errors?
Need to know before noon eastern, as I'd like to kick off the process in a screen session and go back to my day job. Post your 2 cents here.
The good news:
1. Innodb upgraded to support Barracuda.
2. All tables dropped and recreated as single files per table (prep and necessary for Barracuda format).
3. Restored DB
4. Slept.
5. This morning I ran an ALTER TABLE on a couple small tables to test barracuda, it worked.
I know, I know "So now what FishPants?!?! What other kind of awesome database stuff is coming?! DON'T LEAVE US HANGING!!"
So with #5 working, I can convert on the fly -- that means the board won't be going back offline to do this. The bad news is that when it hits large tables (Search wordlist, and posts table specifically) you won't be able to post/edit etc. This will result in the session sitting there waiting and waiting; if enough people are doing this at the time (50-100), or pressing refresh, the mysql connection limit will be reached and you will get an error that all connections are in use.
So, what say you OO -- What do you guys prefer? Do you want the board to show a nice "Sorry the board is offline for now" for say 5-6 hours, or would you rather take a gamble and let it lock tables as it needs -- possibly resulting in Mysql errors?
Need to know before noon eastern, as I'd like to kick off the process in a screen session and go back to my day job. Post your 2 cents here.
No.
- cheeba
- Posts: 8727
- Joined: Tue Jan 04, 2005 3:32 am
Re: Need your input - Database Conversion (Read my last upda
Um, not to sound too stupid or anything here, but is it possible to implement something so that we get neither problem?
As for which I'd prefer, I guess it'd be mysql errors. Something concrete is always better than hanging and waiting.
Oh and thanks for all the hard work!
As for which I'd prefer, I guess it'd be mysql errors. Something concrete is always better than hanging and waiting.
Oh and thanks for all the hard work!
- Smoove_B
- Posts: 54642
- Joined: Wed Oct 13, 2004 12:58 am
- Location: Kaer Morhen
Re: Need your input - Database Conversion (Read my last upda
In the history of this site, has there ever been a situation where 50+ people are trying to search for something at the same time? I'm not a DBA, but I would think based on how the site is being used, this would be a rare (if not impossible) scenario.
EDIT: Wait, it can also occur if 50+ people are hitting F5? I'd think that might be more likely, though I don't really know how many people are on at any given time during peak hours.
EDIT: Wait, it can also occur if 50+ people are hitting F5? I'd think that might be more likely, though I don't really know how many people are on at any given time during peak hours.
Maybe next year, maybe no go
- FishPants
- Server WhOOre
- Posts: 4661
- Joined: Fri Oct 15, 2004 1:38 pm
- Location: Canada
Re: Need your input - Database Conversion (Read my last upda
Every connection to the site can be a mysql connection, so in normal activity the pipe is open, data requested, data delivered, pipe closes. When I start locking tables, that pipe stays open until it gets the response -- thus the rub.Smoove_B wrote:In the history of this site, has there ever been a situation where 50+ people are trying to search for something at the same time? I'm not a DBA, but I would think based on how the site is being used, this would be a rare (if not impossible) scenario.
EDIT: Wait, it can also occur if 50+ people are hitting F5? I'd think that might be more likely, though I don't really know how many people are on at any given time during peak hours.
I did some testing and I'm not sure that I want to convert the larger tables live:
Code: Select all
mysql> ALTER TABLE phpbb3_topics_track KEY_BLOCK_SIZE=4;
Query OK, 1200531 rows affected (3 min 37.81 sec)
Records: 1200531 Duplicates: 0 Warnings: 0
Keep chiming in folks, if I do the conversion live, I will make sure to update this post previously before I start on tables that will lock the board up (Search words and posts).
No.
- FishPants
- Server WhOOre
- Posts: 4661
- Joined: Fri Oct 15, 2004 1:38 pm
- Location: Canada
Re: Need your input - Database Conversion (Read my last upda
I'm going to live convert the posts table. If it's doing 5500 rows per second, that should take somewhere between 5-7 minutes. If it holds true to that time frame, then I will kick off the search wordlist table after that.
No.
- stessier
- Posts: 29835
- Joined: Tue Dec 21, 2004 12:30 pm
- Location: SC
Re: Need your input - Database Conversion (Read my last upda
So I had a little issue. I think I tried posting during the update and I got 7 posts. I know I hit the button twice. Not sure where the other 5 came from.
I require a reminder as to why raining arcane destruction is not an appropriate response to all of life's indignities. - Vaarsuvius
Global Steam Wishmaslist Tracking
Global Steam Wishmaslist Tracking
Running__ | __2014: 1300.55 miles__ | __2015: 2036.13 miles__ | __2016: 1012.75 miles__ | __2017: 1105.82 miles__ | __2018: 1318.91 miles | __2019: 2000.00 miles |
- Lassr
- Posts: 16873
- Joined: Wed Oct 13, 2004 10:51 am
- Location: Rocket City (AL)
- Contact:
Re: Need your input - Database Conversion (Read my last upda
Wow 7 posts. I only had a double post, and it took 10 minutes to make that double post, I feel insignificant.stessier wrote:So I had a little issue. I think I tried posting during the update and I got 7 posts. I know I hit the button twice. Not sure where the other 5 came from.
Seems to be working better right now.
The only reason people get lost in thought is because it's unfamiliar territory.
Black Lives Matter
Black Lives Matter
- FishPants
- Server WhOOre
- Posts: 4661
- Joined: Fri Oct 15, 2004 1:38 pm
- Location: Canada
Re: Need your input - Database Conversion (Read my last upda
Back from lunch, the posts table is now converted but it took 35 minutes instead of the 5 (based on smaller tables).
Based on that change, it could take up to 18 hours to do the search wordlist if I convert it live. It would be easier to shut down the website, dump the table to a file, drop the table, make a new table with the new options and read in the file (i.e. rebuild it). Time for that is probably 2 hours.
Not sure how my afternoon is, but I may start that either this aft or later this eve.
Based on that change, it could take up to 18 hours to do the search wordlist if I convert it live. It would be easier to shut down the website, dump the table to a file, drop the table, make a new table with the new options and read in the file (i.e. rebuild it). Time for that is probably 2 hours.
Not sure how my afternoon is, but I may start that either this aft or later this eve.
No.
- cheeba
- Posts: 8727
- Joined: Tue Jan 04, 2005 3:32 am
Re: Need your input - Database Conversion (Read my last upda
If you open a socket for me I can implement the backslash protocol.
- Zaxxon
- Forum Moderator
- Posts: 28127
- Joined: Wed Oct 13, 2004 12:11 am
- Location: Surrounded by Mountains
Re: Need your input - Database Conversion (Read my last upda
I don't have an opinion either way on options, but thanks for the work, FP.
Also, I think it's awesome that FP asked which inconvenience we'd prefer, and the first response was 'DUDE MAKE IT SO WE HAVE NO INCONVENIENCE.'
Also, I think it's awesome that FP asked which inconvenience we'd prefer, and the first response was 'DUDE MAKE IT SO WE HAVE NO INCONVENIENCE.'
- naednek
- Posts: 10871
- Joined: Tue Oct 19, 2004 9:23 pm
Re: Need your input - Database Conversion (Read my last upda
We just got to tell Meal to lay off the search while it's happeningSmoove_B wrote:In the history of this site, has there ever been a situation where 50+ people are trying to search for something at the same time? I'm not a DBA, but I would think based on how the site is being used, this would be a rare (if not impossible) scenario.
EDIT: Wait, it can also occur if 50+ people are hitting F5? I'd think that might be more likely, though I don't really know how many people are on at any given time during peak hours.
hepcat - "I agree with Naednek"
- The Meal
- Posts: 27992
- Joined: Tue Oct 12, 2004 10:33 pm
- Location: 2005 Stanley Cup Champion
Re: Need your input - Database Conversion (Read my last upda
"Better to talk to people than communicate via tweet." — Elontra
- naednek
- Posts: 10871
- Joined: Tue Oct 19, 2004 9:23 pm
Re: Need your input - Database Conversion (Read my last upda
guess my joke didn't register. You have a great history on searching old threads. I'm not mocking, sometimes you find stuff that happened years ago and it's amazing how you can remember things. Fed is good at that too.The Meal wrote:
hepcat - "I agree with Naednek"
- FishPants
- Server WhOOre
- Posts: 4661
- Joined: Fri Oct 15, 2004 1:38 pm
- Location: Canada
Re: Need your input - Database Conversion (Read my last upda
I'm shooting for tonight.. Just got out of meetings for the day, and want to spend some time with my kids this eve.
Probably start the backup around 1900hrs eastern (which will lock the DB, I will take the board offline) for the table, then around 2000hrs will start the reinsertion process (that's the longest). After that I'm done mucking with it for awhile!
Probably start the backup around 1900hrs eastern (which will lock the DB, I will take the board offline) for the table, then around 2000hrs will start the reinsertion process (that's the longest). After that I'm done mucking with it for awhile!
No.
- The Meal
- Posts: 27992
- Joined: Tue Oct 12, 2004 10:33 pm
- Location: 2005 Stanley Cup Champion
Re: Need your input - Database Conversion (Read my last upda
That wasn't a mock defense question mark, but a confused one. Thanks for setting me straight. (Oh, if only I could've searched for an answer to that one! )naednek wrote:guess my joke didn't register. You have a great history on searching old threads. I'm not mocking, sometimes you find stuff that happened years ago and it's amazing how you can remember things. Fed is good at that too.The Meal wrote:
"Better to talk to people than communicate via tweet." — Elontra
- FishPants
- Server WhOOre
- Posts: 4661
- Joined: Fri Oct 15, 2004 1:38 pm
- Location: Canada
Re: Need your input - Database Conversion (Read my last upda
Well that took a long time. The good news is that should be it for awhile.
*phew* at least I don't have to play the role of DBA over the weekend.
*phew* at least I don't have to play the role of DBA over the weekend.
No.
- Sectoid
- Posts: 3712
- Joined: Thu Aug 04, 2005 9:35 am
- Location: Cydonia, Mars
- Contact:
Re: DB Maint - DONE!
Thanks for the hard work, FP. I only died twice when OO wasn't working this morning. Good thing I had another guy.
(V)(;,,;)(V) - Why not Zoidberg?
Model Mayhem # 641920
Model Mayhem # 641920
- Odin
- Posts: 20732
- Joined: Tue Oct 12, 2004 11:29 pm
- Location: Syracuse, NY
- theohall
- Posts: 11697
- Joined: Thu Oct 14, 2004 10:01 am
- Location: Jacksonville, FL
- Blackhawk
- Posts: 43740
- Joined: Tue Oct 12, 2004 9:48 pm
- Location: Southwest Indiana
Re: DB Maint - DONE!
Nice job.
Maintenance is inevitable, and websites are going to have to be unavailable sometimes. What I really appreciated were the updates.
Maintenance is inevitable, and websites are going to have to be unavailable sometimes. What I really appreciated were the updates.
(˙pǝsɹǝʌǝɹ uǝǝq sɐɥ ʎʇıʌɐɹƃ ʃɐuosɹǝd ʎW)
- Smoove_B
- Posts: 54642
- Joined: Wed Oct 13, 2004 12:58 am
- Location: Kaer Morhen
Re: DB Maint - DONE!
I still have no idea what you were doing, but anything that keeps the site up and running is a good move. Thanks.
Maybe next year, maybe no go
- Anonymous Bosch
- Posts: 10513
- Joined: Thu Oct 14, 2004 6:09 pm
- Location: Northern California [originally from the UK]
Re: DB Maint - DONE!
Thanks for the yeoman's work, your piscine pants are much appreciated!
"There is only one basic human right, the right to do as you damn well please. And with it comes the only basic human duty, the duty to take the consequences." — P. J. O'Rourke
- LawBeefaroni
- Forum Moderator
- Posts: 55346
- Joined: Fri Oct 15, 2004 3:08 pm
- Location: Urbs in Horto, outrageous taxes on everything
Re: DB Maint - DONE!
Pescado pantalones.Anonymous Bosch wrote:Thanks for the yeoman's work, your piscine pants are much appreciated!
Fisch hosen.
Or whatever, thanks FP!
" Hey OP, listen to my advice alright." -Tha General
"No scientific discovery is named after its original discoverer." -Stigler's Law of Eponymy, discovered by Robert K. Merton
MYT
"No scientific discovery is named after its original discoverer." -Stigler's Law of Eponymy, discovered by Robert K. Merton
MYT
- Enough
- Posts: 14688
- Joined: Tue Oct 12, 2004 11:05 pm
- Location: Serendipity
- Contact:
Re: DB Maint - DONE!
I guess I owe you some image post-processing again heh. Thanks for your efforts!
My blog (mostly photos): Fort Ephemera - My Flickr Photostream
“You only get one sunrise and one sunset a day, and you only get so many days on the planet. A good photographer does the math and doesn’t waste either.” ―Galen Rowell
“You only get one sunrise and one sunset a day, and you only get so many days on the planet. A good photographer does the math and doesn’t waste either.” ―Galen Rowell
- PLW
- Posts: 3058
- Joined: Tue Oct 07, 2008 11:39 am
- Location: Clemson
Re: DB Maint - DONE!
I don't know if this is related, or not, but the board as slowed WAY down for me recently. Often hanging for several seconds halfway through loading a thread before completing. I'm running Chrome, Win 7.
UPDATE: It's improved a lot, so I guess it was my end.
UPDATE: It's improved a lot, so I guess it was my end.
Last edited by PLW on Fri Aug 19, 2011 8:26 pm, edited 1 time in total.
- Alefroth
- Posts: 8535
- Joined: Thu Oct 14, 2004 1:56 pm
- Location: Bellingham WA
Re: DB Maint - DONE!
Agreed. That's a very nice touch.Blackhawk wrote:Nice job.
Maintenance is inevitable, and websites are going to have to be unavailable sometimes. What I really appreciated were the updates.
Thanks for all the work, FishPants.
Ale
- gbasden
- Posts: 7668
- Joined: Wed Oct 13, 2004 1:57 am
- Location: Sacramento, CA
Re: DB Maint - DONE!
Thank you, Fishpants!
- MHS
- Posts: 9808
- Joined: Tue Oct 12, 2004 10:21 pm
- Location: Longmont CO
Re: DB Maint - DONE!
Yup. What he said. Thanks!Blackhawk wrote:Nice job.
Maintenance is inevitable, and websites are going to have to be unavailable sometimes. What I really appreciated were the updates.
Black Lives Matter. No human is illegal. Women's rights are human rights. Love is love. Science is real. Kindness is everything.
- dbt1949
- Posts: 25736
- Joined: Wed Oct 13, 2004 12:34 am
- Location: Hogeye Arkansas
Re: DB Maint - DONE!
I was wondering why I felt better.
Ye Olde Farte
Double Ought Forty
aka dbt1949
Double Ought Forty
aka dbt1949
- Rip
- Posts: 26891
- Joined: Tue Oct 12, 2004 9:34 pm
- Location: Cajun Country!
- Contact:
Re: DB Maint - DONE!
I find it awfully fast now. How about slowing it down a tad.
- FishPants
- Server WhOOre
- Posts: 4661
- Joined: Fri Oct 15, 2004 1:38 pm
- Location: Canada
Re: DB Maint - DONE!
Overall look/feel is faster or anything in particular? Basically it compressed the rows, and anything over a 4K chunk was put into an overflow table. Should make the board snappier, and glad to hear that you're seeing that end result!
No.
- silverjon
- Posts: 10781
- Joined: Wed Mar 12, 2008 7:16 pm
- Location: Western Canuckistan
Re: DB Maint - DONE!
Prior to the update, loading the font colours on the post reply page would fill in the coloured squares row by row. Now it loads as a nigh-instantaneous block again, a concrete example of things being faster.
wot?
To be fair, adolescent power fantasy tripe is way easier to write than absurd existential horror, and every community has got to start somewhere... right?
Unless one loses a precious thing, he will never know its true value. A little light finally scratches the darkness; it lets the exhausted one face his shattered dream and realize his path cannot be walked. Can man live happily without embracing his wounded heart?
To be fair, adolescent power fantasy tripe is way easier to write than absurd existential horror, and every community has got to start somewhere... right?
Unless one loses a precious thing, he will never know its true value. A little light finally scratches the darkness; it lets the exhausted one face his shattered dream and realize his path cannot be walked. Can man live happily without embracing his wounded heart?