14 replies to this topic

[AlNipper49]

In addition to the few outages we had the other day we may see a few others.  It isn't anything server-threatening, the issue stems from a backup that went crazy and copied files everywhere.  We're in the process of cleaning up the files and making sure that the backup stops doing crappy things.  If anyone knows anything about mysql master/slave replication lemme know via pm

 

 

[SumnerH]

Trying to get to the admin CP is getting me this:

mySQL query error: ALTER TABLE fu3kupgrade_history ADD upgrade_app varchar(32) NOT NULL default 'core'

SQL error: Duplicate column name 'upgrade_app'
SQL error code: 1060
Date: Wednesday 20th February 2013 09:04:34 PM


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Date: Wed, 20 Feb 2013 21:04:34 +0000
Error: 1060 - Duplicate column name 'upgrade_app'
IP Address: 173.10.186.37 - /admin/upgrade/index.php?_acpRedirect=1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mySQL query error: ALTER TABLE fu3kupgrade_history ADD upgrade_app varchar(32) NOT NULL default 'core'

This looks like someone's in the middle of doing a botched upgrade. Nothing in the normal code should be attempting to add columns.

[SumnerH]

Okay, someone removed mysql's temp directory (which was /var/tmp2).

We had it pointed at /var/tmp2 in order to be able to survive larger indexing operations. In particular, we needed it for the initial creation of the full-text index that stabilized search:
http://sonsofsamhorn...back/?p=3735034

I'm okay with pointing it back at /var/tmp (though note: that's where /usr/tmp points as well), but I'm wondering why it moved.

If Nip or one of us just forgot about that move that's no biggie, but if the smart hands over at the host are messing around with our configuration (in particular, just randomly removing symlinks/directories!) without understanding why it's set the way it is, that could get problematic.

[SumnerH]

Oh, yeah: the site should be fully fixed. Lemme know if you see anything busted.

[AlNipper49]

/var/tmp2 was where a few big files were so I'm guessing the tech tried to delete the atypical directory rather than just the file(s).  I guess we get what we pay for

 

So backup now, whatchoo think .......

[SumnerH]

Backup? Like I said, the only reliable way I've seen actually work in practice for large mysql dbs is LVM snapshots.



That leads me into my secret master plan: we're not running LVM at the moment, which requires formatting things from bare-bones. The easiest way to do that is to throw in a new drive. If we're doing that, the live database is only about 8GB, so we don't need high capacity (figure that 80GB would be plenty with room to grow and snapshot/backup support). Which means a fast SSD is an option without getting too expensive. And since we're using LVM anyway, we could stripe 2 of them very easily, for better performance. Profit.

[AlNipper49]

the problem is that it's not our server, we lease that mofo.   And as sketchy as the techs can be, it helps me a lot in that it's 24/7 and frankly I don't need to be woken up at 2am because you jerks can't talk about Mike Napoli

 

I do have a VPS running mysql which is why I was hoping just to sync there using master/slave and running the backups of that off of that and then using something like scp or whatever to get the files sync'd and backed up.  Running a mysqldump here freezes the place like a motherfucker.

[SumnerH]

the problem is that it's not our server, we lease that mofo.   And as sketchy as the techs can be, it helps me a lot in that it's 24/7 and frankly I don't need to be woken up at 2am because you jerks can't talk about Mike Napoli
 
I do have a VPS running mysql which is why I was hoping just to sync there using master/slave and running the backups of that off of that and then using something like scp or whatever to get the files sync'd and backed up.  Running a mysqldump here freezes the place like a motherfucker.

Great, dash my hopes.

Nominally a master/slave mysql with mysqldumps from the slave should work. I've never seen it accomplished stably on a high-traffic site, though, (which doesn't mean it's not possible).

The next best thing would be late-night mysqlhotcopy to another local (to the live server) copy of the database, then mysqldump from that to wherever. It'll still hang things for a bit, but not for as long as a straight mysqldump from the live server does.

[SumnerH]

Some board features we might consider toggling to improve performance:

1. The list of active users at the bottom of the front page
I think you could still get to that info by clicking http://sonsofsamhorn...sort_order=desc but it wouldn't happen on every view
2. The list of "who's browsing this topic/forum" at the bottom of a topic view (how many users/member names/etc)
3. There's a setting so that the "how many people have viewed a post" only updates every 3 hours instead of in real time--this saves a massive table lock. Unless people really use the up-to-the-minute count, that's likely to be the biggest speed gain of the 3.
4. The debug line at the bottom (Time Now: Feb 20 2013 07:28 PM Last Visit: Today, 10:58 AM Exec. Time: 0.1055 Load: -- Queries: 28 queries Gzip: GZIP Enabled)

Thoughts?

These are all simple on/off switches that can be flipped on the fly, so if people really complain they can be reversed no problem.

[SumnerH]

Some board features we might consider toggling to improve performance:

1. The list of active users at the bottom of the front page
I think you could still get to that info by clicking http://sonsofsamhorn...sort_order=desc but it wouldn't happen on every view
2. The list of "who's browsing this topic/forum" at the bottom of a topic view (how many users/member names/etc)
3. There's a setting so that the "how many people have viewed a post" only updates every 3 hours instead of in real time--this saves a massive table lock. Unless people really use the up-to-the-minute count, that's likely to be the biggest speed gain of the 3.
4. The debug line at the bottom (Time Now: Feb 20 2013 07:28 PM Last Visit: Today, 10:58 AM Exec. Time: 0.1055 Load: -- Queries: 28 queries Gzip: GZIP Enabled)

Thoughts?

These are all simple on/off switches that can be flipped on the fly, so if people really complain they can be reversed no problem.

Screw it, I flipped them all. Yell if that makes you angry.

[MentalDisabldLst]

I do like those features, though I agree they're not critical.  What was average page load time with and without them?

[SumnerH]

I do like those features, though I agree they're not critical.  What was average page load time with and without them?

 

It's about a 5-9% savings on viewing a thread, and about a 25% savings on viewing a board.

 

DB-wise, there were 28 queries on the thread view page before, this shaves 2 of them (read-only); most important is the insert it saves on every page and topic view.

 

How much do you like those features, in what order?

[Eck'sSneakyCheese]

I find myself missing #2, especially in the draft threads. The other 3 I can do without.

[SumnerH]

I suspect that's the most missed one, and it's also the smallest impact on performance if I had to guess. Turning it back on to see.

[Eck'sSneakyCheese]

Thank you. That's a lot more helpful.