As you probably have noticed, our site has been a bit sluggish lately.
We are aware of the issue and are developing plans for dealing with it. The primary issue lies in the database structure and contents. On-the-fly joins across multiple tables cause a performance hit which is exacerbated by the number of stories we have posted over the years (yes, it HAS been that long... YAY!). Further, stories which have been "archived" — allowing no further comments or moderation — are still sitting in the in-RAM DB and could be offloaded to disk for long-term access. Once offloaded, there would be much less data in the in-RAM database (queries against empty DBs tend to be pretty quick!) so this should result in improved responsiveness.
A complicating factor is that changing the structure on a live, replicated database would cause most every page load to 500 out. So the database has to be offlined and the code updated. That would likely entail on the order of the better part of a day. Obviously, shorter is better. On the other hand "The longest distance between two points is a short cut." We're aiming to do it right, the first time, and be done with it, rather than doing it quick-and-dirty, which usually ends up being not quick and quite dirty.
So, we ARE aware of the performance issues, are working towards a solution, and don't want to cause any more disruption than absolutely necessary.
We will give notice well in advance of taking any actions.
(Score: 1, Insightful) by Anonymous Coward on Wednesday October 10 2018, @03:55PM (14 children)
If you are having performance problems from joins between tables in an in RAM database (assuming you do mean that all of the joined tables are in RAM) then that says your DB table structure design is very very wrong.
So, yes, this would say you should do it right, take some downtime, and make a wholesale change to a proper DB table layout appropriate for the types of queries that get run for the site.
(Score: 3, Informative) by The Mighty Buzzard on Wednesday October 10 2018, @04:14PM (13 children)
You are not entirely incorrect but I'm just not feeling the desire to completely revamp our DB structure and all the code that accesses it at the moment. The old site got around this by dumping archived stories out to mostly pre-rendered .shtml files on a daily basis. We're disinclined to take that route at the moment.
My rights don't end where your fear begins.
(Score: 3, Insightful) by RS3 on Wednesday October 10 2018, @07:27PM (6 children)
Indexes?
(Score: 2) by The Mighty Buzzard on Wednesday October 10 2018, @10:27PM (5 children)
Got plenty but do need a couple new ones added, yes.
My rights don't end where your fear begins.
(Score: 2) by RS3 on Thursday October 11 2018, @12:24AM (4 children)
I don't remember knowing what database you're using, but if it's MySQL, have you tried phpMyAdmin?
(Score: 2) by The Mighty Buzzard on Thursday October 11 2018, @12:33AM (3 children)
Install PHP on critical servers? Are you mad?
My rights don't end where your fear begins.
(Score: 2) by RS3 on Thursday October 11 2018, @01:01AM
Hopping! I inherited WordPress servers, so... It hasn't hurt me in 10 years. Maybe I gots magic. :)
(Score: 2) by RS3 on Thursday October 11 2018, @01:03AM (1 child)
BTW, you could rsync the db to a cloned but otherwise offline server (2nd Ethernet if you're really paranoid) and run phpMyAdmin on that box.
(Score: 2) by The Mighty Buzzard on Friday October 12 2018, @03:01AM
Or I could just do it from a mysql prompt. It'd be a whole lot quicker than installing and configuring PHP. We really don't want our db servers talking to anything outside our network.
My rights don't end where your fear begins.
(Score: 2) by FatPhil on Wednesday October 10 2018, @09:38PM (1 child)
That will point you in the right direction, at least. You can then describe those queries to find out where additional indexes may be useful. Pretty much everything in a well-designed database that you are only interested in small bits of should be O(log(N)^(1+small)) in the size of the tables, and if we're noticing it slow down, that probably means an O(N) has crept in somewhere, which probably means a lack of an index.
Great minds discuss ideas; average minds discuss events; small minds discuss people; the smallest discuss themselves
(Score: 2) by The Mighty Buzzard on Wednesday October 10 2018, @10:32PM
Not at the moment. Going to fix the calls I already know are causing problems. It's on the agenda though.
My rights don't end where your fear begins.
(Score: 0) by Anonymous Coward on Thursday October 11 2018, @02:30AM (1 child)
Data mining much?
(Score: 2) by The Mighty Buzzard on Friday October 12 2018, @03:05AM
Yep. We're digging for lower case 'e's. They're the most in-demand letter in the English language, so we figure we can get a good price for them.
My rights don't end where your fear begins.
(Score: 0) by Anonymous Coward on Thursday October 11 2018, @02:40AM (1 child)
Different Anon here - That is pretty cool, and one implementation I would use as well.
(Score: 2) by The Mighty Buzzard on Friday October 12 2018, @03:03AM
It worked well enough for the but you lose a whole lot of flexibility doing it that way.
My rights don't end where your fear begins.
(Score: 5, Funny) by nitehawk214 on Wednesday October 10 2018, @04:15PM (4 children)
If you wrote your own database engine, you could call it martydb.
"Don't you ever miss the days when you used to be nostalgic?" -Loiosh
(Score: 2) by DannyB on Wednesday October 10 2018, @05:28PM (2 children)
Or . . . . . you could use Microsoft Access database with VB.
To transfer files: right-click on file, pick Copy. Unplug mouse, plug mouse into other computer. Right-click, paste.
(Score: 2) by The Mighty Buzzard on Wednesday October 10 2018, @05:39PM
Nah, I think I'll just write a clustered backend for MySQL that stores to on-disk XML files.
My rights don't end where your fear begins.
(Score: 1) by nitehawk214 on Thursday October 11 2018, @02:49PM
DannyB sounds kind of like VisualB
"Don't you ever miss the days when you used to be nostalgic?" -Loiosh
(Score: 0) by Anonymous Coward on Wednesday October 10 2018, @06:36PM
MartyDB is Web Scale.
(Score: 2) by goodie on Wednesday October 10 2018, @04:59PM (7 children)
If the archived stuff is sitting in memory, it's an issue indeed. There are a few ways that you could make it faster: daily/monthly job to move archiving data into separate tables, then remove archived data from live tables. You could always have a view that unions both live and archived tables to show the entire db, e.g., when performing searches if you don't have an index available.
But overall, if everything fits in memory, what's the issue? Have you profiled your db and seen what the culprit is? Are you swapping due to low amounts of memory available? Are you flushing too many execution plans because you use plain old SQL vs prepared statements? There are a number of things that could come into play here. anyway I don't mean to pry but it'd be interesting to know what the root cause actually is and how you guys are going to address it.
As far as updating a DB schema live, it should not be an issue, maybe you will have to go read-only for a few minutes, redirect to the slave, then update the slave and put the servers back in RW. I am of course making it sound a lot simpler than it is. Worst case, SN could be unavailable for a bit altogether, it's not like this is a daily thing to run :).
(Score: 2) by The Mighty Buzzard on Wednesday October 10 2018, @05:35PM (6 children)
That's the general idea, yep. With the addition of them being on-disk tables instead of being stuck in memory.
Multiple complex queries on bloody big tables for most every page load. Some very poorly optimized queries. Probably a few configuration embuggerances. A whole bunch of legacy cruft that leaves the site running fine with a small population but starting to cause problems at our current traffic and db-size levels.
It should if you try to insert a row into a table that suddenly has fewer columns or more columns that can't be null but default to null to keep screwed up rows from being inserted. Or if you try to pull data from a missing column. Adding an index or a view we should be able to get away with but those aren't the only changes that need to be made.
Also, we aren't using a master/slave setup. We're using mysql-cluster which is entirely in-memory except for tables you explicitly tell it to keep on disk (and BLOB/TEXT columns). Then the mysql bits are separate processes from the ndb backend bits, which basically means it's not quite so simple.
It's not going to be a terribly difficult thing to fix but it's going to take some coding time, some testing time, and some down time.
My rights don't end where your fear begins.
(Score: 0) by Anonymous Coward on Wednesday October 10 2018, @09:34PM (3 children)
So you're saying no-problemo?
(Score: 2) by The Mighty Buzzard on Wednesday October 10 2018, @10:29PM
More or less. It should be time consuming not difficult.
My rights don't end where your fear begins.
(Score: 0) by Anonymous Coward on Wednesday October 10 2018, @11:31PM (1 child)
"So you're saying no-problemo?"
I said that once. And then I learned. Murphy is always lurking in the background and ready to spring into action.
(Score: 0) by Anonymous Coward on Thursday October 11 2018, @02:36PM
That's why you should say “won't work”. Then the only way it can go wrong is by actually working.
(Score: 2) by goodie on Thursday October 11 2018, @12:56AM (1 child)
That's why I was thinking you could switch to RO during the code rollout and then go back to RW once everything is up. Of course, there is a small period of time where reads might screw up. But then again, if you rename the table and make things go through a view before dropping the view and renaming the table back, users may not notice much.
My 2 cents: I have no knowledge of your setup but it might take a while to build it and it will penalize writes over time.
In any case, keep us posted, it's interesting to some of us :)
(Score: 2) by The Mighty Buzzard on Friday October 12 2018, @03:08AM
Writes are far and away the least of our problems. We could make every write take five seconds and still come off looking fast as hell if our reads are fast, which they were until recently.
My rights don't end where your fear begins.
(Score: 3, Funny) by takyon on Wednesday October 10 2018, @06:02PM
I've brought the site to its knees!
[SIG] 10/28/2017: Soylent Upgrade v14 [soylentnews.org]
(Score: -1) by fakefuck39 on Wednesday October 10 2018, @06:09PM
the solution unpopular with non-professional hippies living on some shitty boat is simple. why don't you just add more ram to your servers? this isn't a real scale-out site, and it doesn't have what any professional would call a real database. your servers are tiny, your footprint is tiny. double the memory in your db vm or whatever you're using and be done with it. boohoo, so it'll cost you an extra $200/year. who gives a shit - that's the price of dinner for 2 people, and you're probably all fat anywise, so skip the dinner and do another night of possum stew. don't pretend this is some real production tier-1 environment. oh, right - you don't have anything better to do, and your time (and life) is worth pretty much nothing.
when you hit a pole at the wendy's drive-through in your beater piece of shit datsun, you go to a junk yard and get a new bumper. you don't fly in a specialist from tokyo. just install softram95 and you're good to go.
(Score: 2) by iWantToKeepAnon on Thursday October 11 2018, @01:58PM
"Happy families are all alike; every unhappy family is unhappy in its own way." -- Anna Karenina by Leo Tolstoy
(Score: 0) by Anonymous Coward on Thursday October 11 2018, @04:59PM
I have not yet noticed sluggishness. And yes I disable js/cross-site loads, so it's not that the rest of the world has gotten slower, faster. It's that SN hasn't yet crossed the 100-300ms detection threshold, for my queries/use cases.
Much love to the soylentils who keep infrastructure going. Y'all do work that allows this community to live. (So.. you've birthed a monster? Cue Dr. Frankenfurter laughter.)
(Score: 0) by Anonymous Coward on Thursday October 11 2018, @10:21PM
Just wanted to plug in a quick sign of appreciation too.
The old place was nice until it wasn't. SN is very nice.