SoylentNews is people
SoylentNews
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.