Table getting to limits

Discussion in 'Moderator Section' started by pgordemer, Jan 29, 2013.

  1. pgordemer

    pgordemer Guest

    We have to address the table size and speed of the "read/unread" table very soon (like in the next 2-3 weeks). The size and resulting speed is becoming a problem. Not because the forums are busy, quite the contrary, because there are 1000's and 1000's of members that have never read a message/and or never returned/or came over from Snitz.

    Its exasperated by the huge amount of boards. So we have 500,000+ topics x 203 boards x 34,000 members in a table to keep track of the read/unread messages. The forum spends 90% of its energy just maintaining and searching that table.

    I know you feel its important to say there are 34,000 members here, but we need to be realistic about this. Adding horsepower/memory is not going to resolve this issue, especially when its just for an "image".

    We can't use system tools to manage this, as they will timeout before they finish, but I have an arsenal of MySQL scripts that can do it.

    I suggest we purge any member with "0" posts that have not logged in for 4 years. I can see no harm here, it can only make the table smaller and more efficient, if these people ever come back they can read as a guest or reregister, but in the real world if they haven't been here in all this time, they aren't coming back.
     
  2. PopUpSteve

    PopUpSteve Administrator

    Messages:
    19,429
    Likes Received:
    2,081
    Joined:
    Dec 22, 2002
    Location:
    Malvern, PA
    Ok, let talk about this the next evening that you have free.

    Silly thought but can't we just mark those same members as having read all post upto the beginning of like last year? Would that not clean up the tables?
     
  3. pgordemer

    pgordemer Guest

    Yes and now, there is a 3rd party script that will do that, but that's a significant amount of computational time to do it and it makes the system unavailable while it does it. Plus in this case because the # of non-active users is so high, it will update the flags, but the size won't change much. Your 2nd choice is to reduce the # of boards.

    Plus again, all this work and computing power just for a display #. I have a real problem putting "lipstick on a pig".
     
  4. pgordemer

    pgordemer Guest

    Along those same lines, one other thing we can do is move the mysimpleads tables out to their own database. This way MySQL can open 2 Databases at once and read in parallel, vs reading all from one database. Did that on 2 other sites and it worked well - plus it made it portable to allow other non forums sites to use the ad server.
     
  5. PopUpSteve

    PopUpSteve Administrator

    Messages:
    19,429
    Likes Received:
    2,081
    Joined:
    Dec 22, 2002
    Location:
    Malvern, PA
    But the pig would then be ready for the prom. [LOL]

    I like the MySimpleAds idea. For one thing, I'm getting read to add some tables to manage the rallies and we can also move the sweepstakes tables that I'm also about to get back to soon. I have been working on my PHP and your pointer have really helped.

    Can't get rid of boards except some of the old rally boards, pre 2012. Perhaps I can convert each board to a PDF.

    As far as the 3rd party script goes, how long do you think it would take to run? Is it something I could do after midnight?
     
  6. PopUpSteve

    PopUpSteve Administrator

    Messages:
    19,429
    Likes Received:
    2,081
    Joined:
    Dec 22, 2002
    Location:
    Malvern, PA
    And this is basically "Mark All As Read", right?
     
  7. pgordemer

    pgordemer Guest

    Mark as read script is bad idea, I know I have done it before on other forums. It LOCKS the database so NO ONE can read or do anything while it runs. Its also a Band-Aid for a few days or weeks, but the processing still happens.

    The answer is to just purge old members, again if they haven't logged in 4 years and have 0 messages, there is no loss in the message base to the forum and save a significant amount or processing.

    You really have to get over having that number display. If it bothers you that much, edit the template for that screen and make your own numbers. No experienced forum user is fooled currently, they all know how many are active, and if you go to members list, it is simple to see when they were registered and how many messages they have left.
     
  8. pgordemer

    pgordemer Guest

    All of the mysa_ tables have been moved from the popupportal_smf database to popupportal_mysimpleads. MySimpleAds config file has been changed to point to the new database.
     
  9. PopUpSteve

    PopUpSteve Administrator

    Messages:
    19,429
    Likes Received:
    2,081
    Joined:
    Dec 22, 2002
    Location:
    Malvern, PA
    No, I was just asking, that is what you are referring to. When a member logs on and goes to the "Recent Unread Topics" and clicks "Mark All Messages As Read", it clears out the table to which you are referring.
     
  10. PopUpSteve

    PopUpSteve Administrator

    Messages:
    19,429
    Likes Received:
    2,081
    Joined:
    Dec 22, 2002
    Location:
    Malvern, PA
    Cool, on the mysa_ tables.
     
  11. pgordemer

    pgordemer Guest

    Indirectly. Every time a new message is created a pointer for EVERY MEMBER is created to it, so a single message creates 35,000 links, with the flag of what have not been read. When you click Mark all as read, then the entries for YOU are removed from that list. This also explains the long delay when you create a new topic.

    Currently the table of messages has 548,000 entries. The table(s) for read flags (remember even a member that is not here has entries) is 4,953,571
     
  12. PopUpSteve

    PopUpSteve Administrator

    Messages:
    19,429
    Likes Received:
    2,081
    Joined:
    Dec 22, 2002
    Location:
    Malvern, PA
    Just curious, any way to flush that table so everyone would should no updated messages and just continue from there? So everyone would show as no new messages next time they login.
     
  13. PopUpSteve

    PopUpSteve Administrator

    Messages:
    19,429
    Likes Received:
    2,081
    Joined:
    Dec 22, 2002
    Location:
    Malvern, PA
    Let me take a day and I will get some numbers on how many members that would affect. I would want to retain the purged info (offline) for contact purposes.
     
  14. PopUpSteve

    PopUpSteve Administrator

    Messages:
    19,429
    Likes Received:
    2,081
    Joined:
    Dec 22, 2002
    Location:
    Malvern, PA
    I ran a quick query and I came up with 5,215 members that would be purged. Current membership (Jan 29, 2013 @ 4:41:00 pm) is 34,349. The new count would be 29,134.

    I can live with that number.

    What I would like to do is the following:
    [list type=decimal]
    [*]Send an email to each of these 5,215 to invite them back.

    [*]Save the 5,215 (less any who may answer the email) to a database which can be used for future mailings.

    [*]Purge the remaining members one (1) week after the email invitation.

    [/list]

    If this fits with what we need to do to clean up the message table, then we can do this every year for now on, always going back five (5) years to purge.
     
  15. pgordemer

    pgordemer Guest

    What every you want to do, but personally, I believe to be a waste. If there was a reason for them to come back, they would have already in 4 years. Sending an email isn't going to do it, chances are they are mostly going to be bounces. Saving that email for a future mailing also doesn't make sense, they didn't answer the first time or don't exist.

    I think if you did that once a year that would work. Do NOT purge them yourself by just running that type of query, there is a lot of stuff in other tables that all has to be reconciled.

    BTW, if you emptied that table, then EVERY message in the system would be shown as unread to EVERY one - first class trip to pissing off the people that really do read daily.
     
  16. PopUpSteve

    PopUpSteve Administrator

    Messages:
    19,429
    Likes Received:
    2,081
    Joined:
    Dec 22, 2002
    Location:
    Malvern, PA
    It's the packrat in me. I still have 5 1/4 copies of dBase somewhere and I think a copy of Microsoft BOB.

    I would not even think of purging these. Not with instructions from you, and a brain from the Wizard.
     
  17. PopUpSteve

    PopUpSteve Administrator

    Messages:
    19,429
    Likes Received:
    2,081
    Joined:
    Dec 22, 2002
    Location:
    Malvern, PA
    Here's the email I will be sending.

     
  18. PopUpSteve

    PopUpSteve Administrator

    Messages:
    19,429
    Likes Received:
    2,081
    Joined:
    Dec 22, 2002
    Location:
    Malvern, PA
    In addition to my weekly pruning of the following boards (remove all topics not posted in for 30 days):
    • The Other Stuff
    • Deals too good not to share!
    • For Sale and Wanted
    • FreeCycle
    I will also start pruning the "* Introduce Yourself" board of topics that have not had activity in 365 days. I will do this on the first of each month.

    I hope this will help, Phil.
     
  19. pgordemer

    pgordemer Guest

    The # of messages is not the problem, the system can easily scale up, its the # of registered members that have never read or wrote a message since those flags for the messages is the problem.

    Again, lots of message, lots of active members reading, no problem.
     
  20. PopUpSteve

    PopUpSteve Administrator

    Messages:
    19,429
    Likes Received:
    2,081
    Joined:
    Dec 22, 2002
    Location:
    Malvern, PA

Share This Page