DB Maint - DONE!

[FishPants]

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.

[Odin]

Thanks FishPants! You rock!

[KKBlue]

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.

[FishPants]

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.

[cheeba]

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!

[Smoove_B]

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.

[FishPants]

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.

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.

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



That was a smaller table... Basically 5500 rows per second for conversion. On a 55 million row table, that means 10,000 seconds (166 minutes.. I guess that's not too bad). I'd bet its more like 4-8 hours to convert on a large table.

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).

[FishPants]

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.

[stessier]

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.

[Lassr]

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.

Wow 7 posts. I only had a double post, and it took 10 minutes to make that double post, I feel insignificant.

Seems to be working better right now.

[FishPants]

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.

[cheeba]

If you open a socket for me I can implement the backslash protocol.

[Zaxxon]

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.'

[naednek]

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.

We just got to tell Meal to lay off the search while it's happening

[The Meal]

[naednek]

:?:

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.

[FishPants]

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!

[The Meal]

:?:

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.

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! )

[FishPants]

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.

[Sectoid]

Thanks for the hard work, FP. I only died twice when OO wasn't working this morning. Good thing I had another guy.

[Odin]

Thanks, FishPants! It's really appreciated!

[theohall]

Thanks Fish!!!

[Blackhawk]

Nice job.

Maintenance is inevitable, and websites are going to have to be unavailable sometimes. What I really appreciated were the updates.

[Smoove_B]

I still have no idea what you were doing, but anything that keeps the site up and running is a good move. Thanks.

[Anonymous Bosch]

Thanks for the yeoman's work, your piscine pants are much appreciated!

[LawBeefaroni]

Thanks for the yeoman's work, your piscine pants are much appreciated!

Pescado pantalones.

Fisch hosen.

Or whatever, thanks FP!

[Enough]

I guess I owe you some image post-processing again heh. Thanks for your efforts!

[PLW]

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.

[Alefroth]

Nice job.

Maintenance is inevitable, and websites are going to have to be unavailable sometimes. What I really appreciated were the updates.

Agreed. That's a very nice touch.

Thanks for all the work, FishPants.

Ale

[gbasden]

Thank you, Fishpants!

[MHS]

Nice job.

Maintenance is inevitable, and websites are going to have to be unavailable sometimes. What I really appreciated were the updates.

Yup. What he said. Thanks!

[dbt1949]

I was wondering why I felt better.

[Rip]

I find it awfully fast now. How about slowing it down a tad.

[FishPants]

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!

[silverjon]

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.