I'd like to explain why we had about 24 hours of downtime and what exactly happened.
Since the upgrade to MyBB 1.8x a few things broke in the database. One of them was the mybb_posts table index for the 'message' column which is basically the posts. Some of you recall that we were offline for 2 days for that fiasco. Partially because some of the queries literally took 12-15 hours to complete. The main issue is the size of the mybb_posts table itself which is stored as InnoDB but was indeed over 30Gb. To run a query like an "ALTER TABLE mybb_posts" is the worst resource hog. Not only does it take many hours to run but HF has to be shutdown to do it. The table is locked for the query. The ALTER TABLE in Innodb uses a TMP table and copies all the data there before committing the change. So you can imagine on 30Gb table how long that can take.
So the message index was missing after the upgrade. This mainly effected the search. You should have notice that searching posts wasn't getting results because of resources I was forced to disable message searching and all searches were based only on thread titles. InnoDB does support FULLTEXT searching which is great but the index does have to be created. Years ago InnoDB did not support FULLTEXT and so I used a custom plugin for SPHINX SEARCH and that worked extremely well up until we switched from 1.6x to 1.4x and then it started to falter. Eventually the SPHINX SEARCH was breaking down mainly due to MyBB updates and incompatibilities. I didn't mind because I knew that InnoDB was now supporting FULLTEXT.
Some time ago we moved our database to a new server with more resources and I added the FULLTEXT while we were 1.6x. All was right with the world and truthfully HF ran very well on 1.6x for a very long time. But, we started to fall behind on features, some of which are in MyBB 1.8x and having HF on the current branch was starting to look like a requirement more than an option.
One day I just said, "FUCK IT, LET's BREAK THIS SHIT" and so I did. I knew that going to 1.8x would break many things and it most certainly did. Luckily I am capable enough to fix just about anything but it takes time and energy to system administrate for this site. I also of course have to offer support and run the sites day to day operations. So things do take time for me to get things done.
After the upgrade I did my best to fix important stuff and we're actually pretty close to having HF not only fixed but improved. One real serious issue during the upgrade was that the posts table didn't create that index and only until now do I know why. I will explain in a minute. Without the index search in posts was horrendous and I was forced to disable it because a query took 60 seconds and just a handful of people searching would explode the resources. This made search unbearable and broke things like thread searching (that search box on the bottom of showthread). I was not pleased but I knew the fix. Unfortunately the fix would require me to take the site offline.
A few weeks ago I tried to fix the index but had continued problems running the query and after 3-4 hours I realized I needed a different approach. I waited a few days and tried something else and that also didn't work as planned. We had minimal site downtime but this was getting annoying.
I created a new plan of action. Yesterday I enacted that.
I began by making backups and closing the site down. My plan was to do a table copy. I exported the structure, then created table mybb_posts2 and ran this:
INSERT INTO `mybb_posts2` SELECT * FROM `mybb_posts`;
Awesome, it was working. Then...oh....then it went bad. The partition which stores the MySQL data actually got full. Anyone that knows about unix systems is that you don't want to fill a partition. So now I'm like "OH WTF". I had taken the site offline to run this query and for 3 hours it ran. Luckily HF wasn't broken but my 2nd attempt had once again FAILED miserably. I was really unhappy about this and I wasn't going to stop this time so I went with my next option to fix the DB.
My last attempt was to do a data only table dump. I'd TRUNCATE the table, add the index, and import the dump. Sound easy doesn't it?
Knowing my partition was full I had to move the data somewhere else on the server with more space. Unfortunately it's a slower SATA drive and read/write IO is very important on things like this. But I knew what I was doing and I was confident this would work. I spent more time getting backups and preparing and by about 6pm my time yesterday I turned HF off and began the process once again to build the index by doing a full table import. At about 2pm it was still running and about 70% done (running process shows percentage while working). I went to bed figuring around 7-8am it would be done. During a data import the index is automatically built and I should have been able to just turn HF on again.
Oh it never goes exactly as planned. When I turned HF on the new posts weren't posting. Error logs showed that the pid column was not set as AUTO_INCREMENT. And for HF to function properly that does need to be there. It is not an index per se but it's close. I looked at my MySQL STRUCTURE export which I imported for the table and I saw the code to set the pid column AUTO_INCREMENT but dammit...no good. I went into the CLI (shell) for MySQL and ran the query I had.
ALTER TABLE `mybb_posts` MODIFY `pid` `pid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
Oh that looked just fine to me but guess what! Wrong sytax. The MODIFY should have been CHANGE....ugh....so that's what went wrong and I suspect it's where it went wrong with the mybb_posts message column too. Remember earlier I said I would explain later? Now is that later. One simple word. An error unnoticed that caused havoc across the HF world.
Well, it was 8am and I had few choices. I was committed to getting this done and we were almost there.
So I ran this:
ALTER TABLE `mybb_posts` CHANGE `pid` `pid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
And the waiting began again. That query alone took 9 hours and 26 minutes to run. Much of the day I was in XMPP with many members just updating them on the percentage and twiddling my thumbs. I did do other work like setup a VPS for the Wiki site, it's moving shortly. But it was a gruesome wait because we started to come to 24 hours offline for this and that sucks. I originally estimated 2-4 hours of downtime but I was far off.
Eventually around 6pm the query finished. And from there it was a breeze. Everything was working great! As expect the search was fixed, the database was running smooth and HF was online once again. I did move the MySQL data folder back to the SSD as there was a noticeable speed difference from the SATA drive. I'd say 1-2 seconds was added for each page load as well as slower searches. I took backups, undid some my.cnf changes, and made sure that this time HF came online it stayed online.
Search is vastly improved now and we even have a minimum 3 characters again. I have it set for 500 results which should be fine. More adjustment might be made as we go forward but overall everything is normal again and better than ever.
I can't say it went perfect. But it did get done.
I am going to look into doing backups and recovery using different methods such as xtrabackup by Percona. That can hopefully improve situations like this. I'm a little bit worried about the MySQL data partition because it has about 15Gb space left. Enough space for couple years of posts but not any space to do anything like we just did. I can certainly manage it though and I plan to move our database again sometime this year to a server with more RAM. The database total is now 30Gb but the indexes are another 25-30Gb by themselves.
The work done in the past 24 hours was some of the most extensive work that needed to get done on HF which required downtime. We should be good after this for the minor changes HF will have and additional fixes. With Xerotic and F8's help I will be able to get a lot more done in the coming months.
Expect more out of HF in the coming days and thank you for reading and waiting patiently.
Since the upgrade to MyBB 1.8x a few things broke in the database. One of them was the mybb_posts table index for the 'message' column which is basically the posts. Some of you recall that we were offline for 2 days for that fiasco. Partially because some of the queries literally took 12-15 hours to complete. The main issue is the size of the mybb_posts table itself which is stored as InnoDB but was indeed over 30Gb. To run a query like an "ALTER TABLE mybb_posts" is the worst resource hog. Not only does it take many hours to run but HF has to be shutdown to do it. The table is locked for the query. The ALTER TABLE in Innodb uses a TMP table and copies all the data there before committing the change. So you can imagine on 30Gb table how long that can take.
So the message index was missing after the upgrade. This mainly effected the search. You should have notice that searching posts wasn't getting results because of resources I was forced to disable message searching and all searches were based only on thread titles. InnoDB does support FULLTEXT searching which is great but the index does have to be created. Years ago InnoDB did not support FULLTEXT and so I used a custom plugin for SPHINX SEARCH and that worked extremely well up until we switched from 1.6x to 1.4x and then it started to falter. Eventually the SPHINX SEARCH was breaking down mainly due to MyBB updates and incompatibilities. I didn't mind because I knew that InnoDB was now supporting FULLTEXT.
Some time ago we moved our database to a new server with more resources and I added the FULLTEXT while we were 1.6x. All was right with the world and truthfully HF ran very well on 1.6x for a very long time. But, we started to fall behind on features, some of which are in MyBB 1.8x and having HF on the current branch was starting to look like a requirement more than an option.
One day I just said, "FUCK IT, LET's BREAK THIS SHIT" and so I did. I knew that going to 1.8x would break many things and it most certainly did. Luckily I am capable enough to fix just about anything but it takes time and energy to system administrate for this site. I also of course have to offer support and run the sites day to day operations. So things do take time for me to get things done.
After the upgrade I did my best to fix important stuff and we're actually pretty close to having HF not only fixed but improved. One real serious issue during the upgrade was that the posts table didn't create that index and only until now do I know why. I will explain in a minute. Without the index search in posts was horrendous and I was forced to disable it because a query took 60 seconds and just a handful of people searching would explode the resources. This made search unbearable and broke things like thread searching (that search box on the bottom of showthread). I was not pleased but I knew the fix. Unfortunately the fix would require me to take the site offline.
A few weeks ago I tried to fix the index but had continued problems running the query and after 3-4 hours I realized I needed a different approach. I waited a few days and tried something else and that also didn't work as planned. We had minimal site downtime but this was getting annoying.
I created a new plan of action. Yesterday I enacted that.
I began by making backups and closing the site down. My plan was to do a table copy. I exported the structure, then created table mybb_posts2 and ran this:
INSERT INTO `mybb_posts2` SELECT * FROM `mybb_posts`;
Awesome, it was working. Then...oh....then it went bad. The partition which stores the MySQL data actually got full. Anyone that knows about unix systems is that you don't want to fill a partition. So now I'm like "OH WTF". I had taken the site offline to run this query and for 3 hours it ran. Luckily HF wasn't broken but my 2nd attempt had once again FAILED miserably. I was really unhappy about this and I wasn't going to stop this time so I went with my next option to fix the DB.
My last attempt was to do a data only table dump. I'd TRUNCATE the table, add the index, and import the dump. Sound easy doesn't it?
Knowing my partition was full I had to move the data somewhere else on the server with more space. Unfortunately it's a slower SATA drive and read/write IO is very important on things like this. But I knew what I was doing and I was confident this would work. I spent more time getting backups and preparing and by about 6pm my time yesterday I turned HF off and began the process once again to build the index by doing a full table import. At about 2pm it was still running and about 70% done (running process shows percentage while working). I went to bed figuring around 7-8am it would be done. During a data import the index is automatically built and I should have been able to just turn HF on again.
Oh it never goes exactly as planned. When I turned HF on the new posts weren't posting. Error logs showed that the pid column was not set as AUTO_INCREMENT. And for HF to function properly that does need to be there. It is not an index per se but it's close. I looked at my MySQL STRUCTURE export which I imported for the table and I saw the code to set the pid column AUTO_INCREMENT but dammit...no good. I went into the CLI (shell) for MySQL and ran the query I had.
ALTER TABLE `mybb_posts` MODIFY `pid` `pid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
Oh that looked just fine to me but guess what! Wrong sytax. The MODIFY should have been CHANGE....ugh....so that's what went wrong and I suspect it's where it went wrong with the mybb_posts message column too. Remember earlier I said I would explain later? Now is that later. One simple word. An error unnoticed that caused havoc across the HF world.
Well, it was 8am and I had few choices. I was committed to getting this done and we were almost there.
So I ran this:
ALTER TABLE `mybb_posts` CHANGE `pid` `pid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
And the waiting began again. That query alone took 9 hours and 26 minutes to run. Much of the day I was in XMPP with many members just updating them on the percentage and twiddling my thumbs. I did do other work like setup a VPS for the Wiki site, it's moving shortly. But it was a gruesome wait because we started to come to 24 hours offline for this and that sucks. I originally estimated 2-4 hours of downtime but I was far off.
Eventually around 6pm the query finished. And from there it was a breeze. Everything was working great! As expect the search was fixed, the database was running smooth and HF was online once again. I did move the MySQL data folder back to the SSD as there was a noticeable speed difference from the SATA drive. I'd say 1-2 seconds was added for each page load as well as slower searches. I took backups, undid some my.cnf changes, and made sure that this time HF came online it stayed online.
Search is vastly improved now and we even have a minimum 3 characters again. I have it set for 500 results which should be fine. More adjustment might be made as we go forward but overall everything is normal again and better than ever.
I can't say it went perfect. But it did get done.
I am going to look into doing backups and recovery using different methods such as xtrabackup by Percona. That can hopefully improve situations like this. I'm a little bit worried about the MySQL data partition because it has about 15Gb space left. Enough space for couple years of posts but not any space to do anything like we just did. I can certainly manage it though and I plan to move our database again sometime this year to a server with more RAM. The database total is now 30Gb but the indexes are another 25-30Gb by themselves.
The work done in the past 24 hours was some of the most extensive work that needed to get done on HF which required downtime. We should be good after this for the minor changes HF will have and additional fixes. With Xerotic and F8's help I will be able to get a lot more done in the coming months.
Expect more out of HF in the coming days and thank you for reading and waiting patiently.
![[Image: 2.gif]](https://i.ibb.co/wpwb99m/2.gif)
![[Image: WDngpYJ.png]](https://i.imgur.com/WDngpYJ.png)
![[BLANK]](https://hackforums.net/uploads/avatars/avatar_3498571.jpg?dateline=1545319363){kind=avatar}
