Stories
Slash Boxes
Comments

SoylentNews is people

Meta
posted by martyb on Wednesday October 10 2018, @03:00PM   Printer-friendly

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.


Original Submission

 
This discussion has been archived. No new comments can be posted.
Display Options Threshold/Breakthrough Mark All as Read Mark All as Unread
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
  • (Score: 2) by goodie on Wednesday October 10 2018, @04:59PM (7 children)

    by goodie (1877) on Wednesday October 10 2018, @04:59PM (#747036) Journal

    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

    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 :).

    Starting Score:    1  point
    Karma-Bonus Modifier   +1  

    Total Score:   2  
  • (Score: 2) by The Mighty Buzzard on Wednesday October 10 2018, @05:35PM (6 children)

    by The Mighty Buzzard (18) Subscriber Badge <themightybuzzard@proton.me> on Wednesday October 10 2018, @05:35PM (#747050) Homepage Journal

    ...daily/monthly job to move archiving data into separate tables, then remove archived data from live tables.

    That's the general idea, yep. With the addition of them being on-disk tables instead of being stuck in memory.

    But overall, if everything fits in memory, what's the issue?

    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.

    As far as updating a DB schema live, it should not be an issue...

    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)

      by Anonymous Coward on Wednesday October 10 2018, @09:34PM (#747150)

      As far as updating a DB schema live, it should not be an issue...

      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.

      So you're saying no-problemo?

      • (Score: 2) by The Mighty Buzzard on Wednesday October 10 2018, @10:29PM

        by The Mighty Buzzard (18) Subscriber Badge <themightybuzzard@proton.me> on Wednesday October 10 2018, @10:29PM (#747175) Homepage Journal

        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)

        by Anonymous Coward on Wednesday October 10 2018, @11:31PM (#747201)

        "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

          by Anonymous Coward on Thursday October 11 2018, @02:36PM (#747437)

          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)

      by goodie (1877) on Thursday October 11 2018, @12:56AM (#747223) Journal

      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

      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.

      Adding an index

      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 :)