Author Topic: Forum Database Upgrade  (Read 21947 times)

0 Members and 1 Guest are viewing this topic.

Offline EEVblogTopic starter

  • Administrator
  • *****
  • Posts: 37661
  • Country: au
    • EEVblog
Forum Database Upgrade
« on: March 24, 2017, 02:43:33 am »
gnif has upgraded the forum database with a new version fo MySQL that will support InnoDB
Please report any issues with performance, old posts etc.

Thanks
 

Offline Nusa

  • Super Contributor
  • ***
  • Posts: 2416
  • Country: us
Re: Forum Database Upgrade
« Reply #1 on: March 24, 2017, 03:03:26 am »
Forum was nearly useless (nonresponsive when trying to view a post) a few hours ago, and I gave up. Seems fine now, however.
 

Offline EEVblogTopic starter

  • Administrator
  • *****
  • Posts: 37661
  • Country: au
    • EEVblog
Re: Forum Database Upgrade
« Reply #2 on: March 24, 2017, 04:04:10 am »
Forum was nearly useless (nonresponsive when trying to view a post) a few hours ago, and I gave up. Seems fine now, however.

Thanks.
Anyone else notice an improvement?
 

Offline TheSteve

  • Supporter
  • ****
  • Posts: 3742
  • Country: ca
  • Living the Dream
Re: Forum Database Upgrade
« Reply #3 on: March 24, 2017, 04:07:10 am »
Speed seems fine so far, although I need to retrain my brain as to where the categories are. Moving the EEVblog specific stuff to the top totally makes sense. I feel bad for test equipment(my favorite area) being so close to the bottom though.
VE7FM
 

Offline gnif

  • Administrator
  • *****
  • Posts: 1672
  • Country: au
Re: Forum Database Upgrade
« Reply #4 on: March 24, 2017, 04:27:01 am »
The re-sort was not intentional, it seems the ordering has been messed up in the upgrade, which is strange.

Forum was nearly useless (nonresponsive when trying to view a post) a few hours ago, and I gave up. Seems fine now, however.

Sorry about that, the database was busy converting some tables we prior couldn't as the version of MySQL we were on did not support full text searches, which SMF uses quite extensively. There may be more periods like this in the coming few days as I identify other areas that could use improvement, I have already had to add several indexes to tables that SMF should have.

The goal here is to prevent the 502 errors from occurring that people have been reporting.
 
The following users thanked this post: TheSteve

Offline BravoV

  • Super Contributor
  • ***
  • Posts: 7547
  • Country: 00
  • +++ ATH1
Re: Forum Database Upgrade
« Reply #5 on: March 24, 2017, 04:41:31 am »
The old problem on corrupted attachment still exist, try examples below, look for user's post with photo attachments, and try to view them by clicking them.

What interesting is, at some photo posts, even though they have photo thumbs, but once clicked, it wont pop out the full size photo.

Examples :

-> https://www.eevblog.com/forum/chat/salvaging-old-electronics-parts-and-save-the-world/
-> https://www.eevblog.com/forum/beginners/how-to-better-wire-the-bnc-connector-and-ghz-issue/
 
The following users thanked this post: Jacon

Offline gnif

  • Administrator
  • *****
  • Posts: 1672
  • Country: au
Re: Forum Database Upgrade
« Reply #6 on: March 24, 2017, 05:13:27 am »
I pulled the images in question directly from the server and verified the files on disk are corrupt, these posts are quite old and the issue that caused this may already have been corrected. If you see it on any new posts please be sure to PM me directly, this is the kind of think I want to know about ASAP before too much is corrupted.
 

Offline BravoV

  • Super Contributor
  • ***
  • Posts: 7547
  • Country: 00
  • +++ ATH1
Re: Forum Database Upgrade
« Reply #7 on: March 24, 2017, 05:22:44 am »
I pulled the images in question directly from the server and verified the files on disk are corrupt, these posts are quite old and the issue that caused this may already have been corrected. If you see it on any new posts please be sure to PM me directly, this is the kind of think I want to know about ASAP before too much is corrupted.

Ok, noted.

Btw, just curious if you could run at background "crawling" for low impact, to check or verify attachment especially picture's files integrity, say like thru their unique header to see how much is the damaged, at least the last date when it was occurred, just an idea.

Online JPortici

  • Super Contributor
  • ***
  • Posts: 3452
  • Country: it
Re: Forum Database Upgrade
« Reply #8 on: March 24, 2017, 05:38:35 am »
did you move eevblog section 'up'?
 

Offline Monkeh

  • Super Contributor
  • ***
  • Posts: 7990
  • Country: gb
Re: Forum Database Upgrade
« Reply #9 on: March 24, 2017, 05:46:35 am »
So the forum's been running on MyISAM the whole time?

Boy, does that explain a few things.
 

Offline gnif

  • Administrator
  • *****
  • Posts: 1672
  • Country: au
Re: Forum Database Upgrade
« Reply #10 on: March 24, 2017, 06:01:21 am »
So the forum's been running on MyISAM the whole time?

Boy, does that explain a few things.

No it has not, most of the database was on InnoDB but a few of the tables contained text columns that were preventing them from being moved across to InnoDB due to the lack of full text search. The issue was the background maintenance tasks were performing a join with one of these tables, which would then cause a lock contention and stop general forum use. That lock would cause a massive backlog of traffic and a huge spike in CPU load, etc... It has involved a ton of testing to determine where the fault was before throwing "fixes" at the server.

When you have a website that is as busy as this and no dev/test platform, you can't just to haphazardly upgrading packages, until now the remaining two MyISAM tables have not been causing an issue, but as load has gone up on the website it became a problem.

There are a few things that are planned to improve website performance, the server is in need of some TLC when it comes to new/faster technologies, etc, it has just had to wait until I could ensure I had enough time to perform the changes and deal with any new problems that may arise. The up side is we get a faster website, the down side is there will be outages in order to perform this work.
 
The following users thanked this post: nugglix

Offline Monkeh

  • Super Contributor
  • ***
  • Posts: 7990
  • Country: gb
Re: Forum Database Upgrade
« Reply #11 on: March 24, 2017, 06:04:09 am »
So the forum's been running on MyISAM the whole time?

Boy, does that explain a few things.

No it has not, most of the database was on InnoDB but a few of the tables contained text columns that were preventing them from being moved across to InnoDB due to the lack of full text search.

Ah, well, that's why we have you to give the whole story. Still explains things.

Quote
The issue was the background maintenance tasks were performing a join with one of these tables, which would then cause a lock contention and stop general forum use. That lock would cause a massive backlog of traffic and a huge spike in CPU load, etc... It has involved a ton of testing to determine where the fault was before throwing "fixes" at the server.

When you have a website that is as busy as this and no dev/test platform, you can't just to haphazardly upgrading packages, until now the remaining two MyISAM tables have not been causing an issue, but as load has gone up on the website it became a problem.

Calm, calm, not attacking you, just observing. You're not the only one in this line of business, and when most of the information on things goes through the Dave filter (no offence, Dave), it's hard to get a good picture.

It's great you've found some time to give it the needed love, the ride's been getting a little bumpy.
 
The following users thanked this post: nugglix

Offline gnif

  • Administrator
  • *****
  • Posts: 1672
  • Country: au
Re: Forum Database Upgrade
« Reply #12 on: March 24, 2017, 06:05:44 am »
Calm, calm, not attacking you, just observing. You're not the only one in this line of business, and when most of the information on things goes through the Dave filter (no offence, Dave), it's hard to get a good picture.

Sorry I did not take your comments as an attack, I was just being explicit in detail for those that are not like you and do not understand why there is outages when this website is worked on.
 

Offline EEVblogTopic starter

  • Administrator
  • *****
  • Posts: 37661
  • Country: au
    • EEVblog
Re: Forum Database Upgrade
« Reply #13 on: March 24, 2017, 06:24:08 am »
The re-sort was not intentional, it seems the ordering has been messed up in the upgrade, which is strange.

Yeah, no idea why that happened!  :-//
 

Offline TheSteve

  • Supporter
  • ****
  • Posts: 3742
  • Country: ca
  • Living the Dream
Re: Forum Database Upgrade
« Reply #14 on: March 24, 2017, 06:29:35 am »
It seems to have switched to alphabetic order:

EEVblog
Electronics
General
Products
VE7FM
 

Offline DimitriP

  • Super Contributor
  • ***
  • Posts: 1288
  • Country: us
  • "Best practices" are best not practiced.© Dimitri
Re: Forum Database Upgrade
« Reply #15 on: March 24, 2017, 06:31:55 am »
Quote
the ride's been getting a little bumpy.

Bumpy? Where ?

Anytime I'm here during the last year and a half  I rarely noticed anything wrong or bumpy (other than the very rare, occasional and not too often 502 bad gateway error)

   If three 100  Ohm resistors are connected in parallel, and in series with a 200 Ohm resistor, how many resistors do you have? 
 

Offline gnif

  • Administrator
  • *****
  • Posts: 1672
  • Country: au
Re: Forum Database Upgrade
« Reply #16 on: March 24, 2017, 06:44:16 am »
When you have a website that is as busy as this and no dev/test platform, you can't just to haphazardly upgrading packages,

Perhaps it is time to remedy that situation. Life is so much less stressed if you don't have to reassemble the pieces in a hurry when things go wrong. Much easier to back out to the previous state and try again later.

The issue is cost both financially and time, in an ideal world it would be setup very differently, most of my paying clients have fully redundant setups which makes this much easier, but they come with a pretty hefty price. Many people would be quite amazed at the time involved in setting this up, there are so many options, no one 'best' option, it depends on the requirements and available hardware. For example one of my larger clients (who allows me to be public about my involvement with them :)) have one of the largest phpBB3 forums on the internet, the forum hosting alone involves 6 servers, for redundancy and performance.
« Last Edit: March 24, 2017, 06:47:13 am by gnif »
 
The following users thanked this post: EEVblog

Offline Richard Crowley

  • Super Contributor
  • ***
  • Posts: 4317
  • Country: us
  • KJ7YLK
Re: Forum Database Upgrade
« Reply #17 on: March 24, 2017, 06:46:53 am »
Wow, I have never seen a DB related problem, delay or error on this website.  I wish other forums I frequent were as snappy as EEVblog.  GearSlutz in particular has always been painfully slow.  The only errors I see here are related to availability (mostly error messages from Cloudflare.)
 

Offline gnif

  • Administrator
  • *****
  • Posts: 1672
  • Country: au
Re: Forum Database Upgrade
« Reply #18 on: March 24, 2017, 06:48:36 am »
Wow, I have never seen a DB related problem, delay or error on this website.  I wish other forums I frequent were as snappy as EEVblog.  GearSlutz in particular has always been painfully slow.  The only errors I see here are related to availability (mostly error messages from Cloudflare.)

Depends on time of day, to be honest this morning at 4:00AM (ADST) was the first time I had ever seen the 502 error personally, which was a lucky break as I was able to inspect the cause while it was occurring.
 

Offline Monkeh

  • Super Contributor
  • ***
  • Posts: 7990
  • Country: gb
Re: Forum Database Upgrade
« Reply #19 on: March 24, 2017, 06:57:35 am »
Wow, I have never seen a DB related problem, delay or error on this website.  I wish other forums I frequent were as snappy as EEVblog.  GearSlutz in particular has always been painfully slow.  The only errors I see here are related to availability (mostly error messages from Cloudflare.)

Depends on time of day, to be honest this morning at 4:00AM (ADST) was the first time I had ever seen the 502 error personally, which was a lucky break as I was able to inspect the cause while it was occurring.

The down(under)sides. You miss what must be much of the peak traffic down there in the future.
 

Offline Brumby

  • Supporter
  • ****
  • Posts: 12288
  • Country: au
Re: Forum Database Upgrade
« Reply #20 on: March 24, 2017, 07:14:26 am »
The issue is cost both financially and time, in an ideal world it would be setup very differently, most of my paying clients have fully redundant setups which makes this much easier, but they come with a pretty hefty price. Many people would be quite amazed at the time involved in setting this up, there are so many options, no one 'best' option, it depends on the requirements and available hardware.

There are some of us that do understand.

I have no problems with outages - and I appreciate everything that is done behind the scenes.
 
The following users thanked this post: gnif

Offline gnif

  • Administrator
  • *****
  • Posts: 1672
  • Country: au
Re: Forum Database Upgrade
« Reply #21 on: March 24, 2017, 07:15:14 am »
You would think that since we are in the future we could stop these things before you people in the past see them  :palm:
 
The following users thanked this post: tooki

Offline Brumby

  • Supporter
  • ****
  • Posts: 12288
  • Country: au
Re: Forum Database Upgrade
« Reply #22 on: March 24, 2017, 07:17:33 am »
I do love a dreamer....
 

Offline gnif

  • Administrator
  • *****
  • Posts: 1672
  • Country: au
Re: Forum Database Upgrade
« Reply #23 on: March 24, 2017, 07:40:18 am »
It appears the ordering issue is a bug in SMF, it relies on the order of the records being returned from the database being the order they are stored, with the later version of MySQL the records are returned by the order of the first index (the primary key) by default. This bug was already fixed for the boards themselves, but not for the forum categories.

I am hacking in a fix now and will submit a bug report to SMF to get this fixed.
 
The following users thanked this post: EEVblog

Offline EEVblogTopic starter

  • Administrator
  • *****
  • Posts: 37661
  • Country: au
    • EEVblog
Re: Forum Database Upgrade
« Reply #24 on: March 24, 2017, 07:44:10 am »
The issue is cost both financially and time, in an ideal world it would be setup very differently, most of my paying clients have fully redundant setups which makes this much easier, but they come with a pretty hefty price. Many people would be quite amazed at the time involved in setting this up, there are so many options, no one 'best' option, it depends on the requirements and available hardware. For example one of my larger clients (who allows me to be public about my involvement with them :)) have one of the largest phpBB3 forums on the internet, the forum hosting alone involves 6 servers, for redundancy and performance.

 :o
*hiding my company credit card*
 
The following users thanked this post: jancumps, tooki


Share me

Digg  Facebook  SlashDot  Delicious  Technorati  Twitter  Google  Yahoo
Smf