You are either not logged in or not registered with our community. Click here to register.
 
December 03, 2016, 07:37:39 AM

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length

Click here if you are having problems.
Default Wide Screen Beige Lilac Rainbow Black & Blue October Send us your theme!

Hark!  The Herald!
Holiday Issue 2016

Wiki Blogs Dicebot

Author Topic: Server Slowdowns  (Read 4578 times)

0 Members and 1 Guest are viewing this topic.

Offline MargueriteTopic starter

  • Lifestyle Domme, Mistress, Queen of Sin/Debauchery, Eden's Stalker, Domina McSpanks
  • Lady
  • Carnite
  • *
  • Join Date: Jul 2008
  • Location: In A House Made Of Collected Hearts
  • Gender: Female
  • "Now, ask me what I'm going to do to you."
  • My Role Play Preferences
  • View My Rolls
  • Referrals: 2
Server Slowdowns
« on: May 26, 2011, 08:22:50 PM »
The past few days the Elliquiy site has slowed down to where I either have to click several times or had to wait a few minutes for it to slow. Other people have mentioned it and wondered if it had to do with just my internet but considering others have the problem, are the servers getting overloaded?

Offline Saerrael

  • ~~~Seraph ; In love~~~ Do you like what you see? Let me entertain ya ítill you scream.
  • Champion
  • Carnite
  • *
  • Join Date: Aug 2009
  • Location: You don't know how you took it You just know what you got Oh Lordy you've been stealing from the thieves And you got caught
  • Gender: Male
  • Hold me, thrill me, kiss me, kill me.
  • My Role Play Preferences
  • View My Rolls
  • Referrals: 6
Re: Server Slowdowns
« Reply #1 on: May 26, 2011, 08:25:03 PM »
I timed out yesterday (Firefox, old version) and thought it was me. Hearing this from others, though, led me to believe it's not just me.

Offline Jagerin

  • They/Them Pronouns || Alpha Raptor || Fandom Monarch || Estrous Twinkle Particle || Kho's Murdoc || Nic's Scully
  • Champion
  • Carnite
  • *
  • Join Date: Nov 2008
  • Location: A melancholy town where we never smile.
  • Black roses and Hail Mary's can't bring you back.
  • My Role Play Preferences
  • View My Rolls
  • Referrals: 20
Re: Server Slowdowns
« Reply #2 on: May 26, 2011, 08:25:24 PM »
Glad to hear it isn't just me.

Offline Wistful Dream

  • ✧ ~ ✦ ~ Bright Star ~ ✦ ~ ✧ ☾ A Dragon's Mariposa ☽ ~ * ~ A Captain's Princess ~ * ~ An Imp's Vixen
  • Dame
  • Carnite
  • *
  • Join Date: May 2008
  • Location: The Beast's Castle
  • Gender: Female
  • I know my value.
  • My Role Play Preferences
  • View My Rolls
  • Referrals: 3
Re: Server Slowdowns
« Reply #3 on: May 26, 2011, 09:10:49 PM »
Its been happening to me for a few days now, I just figured it was my browser not liking the site.

Offline Star Safyre

  • Mrs. Fyre or, if you're slightly more daring, O Captain my Captain.
  • Dame
  • Addict
  • *
  • Join Date: Aug 2010
  • Location: Beyond the flesh
  • He who is fixed to a star does not change his mind
  • My Role Play Preferences
  • View My Rolls
  • Referrals: 0
Re: Server Slowdowns
« Reply #4 on: May 26, 2011, 09:14:22 PM »
The mister and I noticed the site failing to load for about five minutes.  Downforeveryone said it was down, but came back online just as quickly.

Online Vekseid

Re: Server Slowdowns
« Reply #5 on: May 26, 2011, 10:00:33 PM »
I know there were network problems this morning, which caused some of it. Let's see.

Code: [Select]
# User@Host: elliquiy[elliquiy] @ localhost []
# Query_time: 15.036542  Lock_time: 0.000075 Rows_sent: 0  Rows_examined: 0
SET timestamp=1306462096;
SELECT id_topic
                                FROM smf_messages
                                WHERE id_topic IN (87599, 89380, 81556, 84635, 79298, 87709, 76976, 88174, 88446, 88129, 70677, 87435, 80090, 86789, 87217, 79429, 79595, 87097, 84389, 84270, 86786, 75143, 84843, 85483, 84628, 81193, 82838, 80432, 86133, 84869, 85722, 86205, 86130, 81987, 85352, 62413, 76924, 35835, 74025, 85017, 80179, 85157, 83611, 84859, 84118, 83971, 83429, 81857, 81950, 81951)
                                        AND id_member = -redacted-
                                GROUP BY id_topic
                                                        ORDER BY null
LIMIT 50;

This is the little icon that shows up when you have a post in a thread. I can disable this. This is occurring a few times a day.

Code: [Select]
# User@Host: elliquiy[elliquiy] @ localhost []
# Query_time: 14.899739  Lock_time: 0.000038 Rows_sent: 1  Rows_examined: 1
SET timestamp=1306440773;
SELECT data
                FROM `elliquiy_smf`.smf_sessions
                WHERE session_id = '-redacted-'
                LIMIT 1;

There's no reasonable explanation for this, except that Elliquiy is running low on free RAM and this poor soul got unlucky : / There are a disturbing number of these entries and they shouldn't be happening, at all. This is occurring several times a day. I can convert this to a MEMORY table, however, which should eliminate these.

However, members will effectively lose their sessions when the server restarts or mysql restarts.

Code: [Select]
# Time: 110523 23:16:09
# User@Host: elliquiy[elliquiy] @ localhost []
# Query_time: 198.798036  Lock_time: 0.000040 Rows_sent: 10  Rows_examined: 1835419
use elliquiy_smf;
SET timestamp=1306210569;
SELECT m.id_msg
                                FROM smf_messages AS m
                                        INNER JOIN smf_boards AS b ON (b.id_board = m.id_board)
                                WHERE (FIND_IN_SET(-1, b.member_groups) != 0)
                                        AND b.id_board != 4

                                        AND m.approved = 1
                                ORDER BY m.id_msg DESC
                                LIMIT 0, 10;

This only happens a few times a day, but when it does it basically is the end of that person's page load. There's nothing I can do about this on any trivial level as it involves the FIND_IN_SET operation, which needs a schema redesign to fix.

Code: [Select]
# Query_time: 12.956855  Lock_time: 0.049332 Rows_sent: 8  Rows_examined: 31
use elliquiy_smf;
SET timestamp=1306459081;
SELECT
                        IFNULL(lo.log_time, 0) AS is_online, IFNULL(a.id_attach, 0) AS id_attach, a.filename, a.attachment_type,
                        mem.signature, mem.personal_text, mem.location, mem.gender, mem.avatar, mem.id_member, mem.member_name,
                        mem.real_name, mem.email_address, mem.hide_email, mem.date_registered, mem.website_title, mem.website_url,
                        mem.birthdate, mem.member_ip, mem.member_ip2, mem.icq, mem.aim, mem.yim, mem.msn, mem.posts, mem.last_login,
                        mem.karma_good, mem.id_post_group, mem.karma_bad, mem.lngfile, mem.id_group, mem.time_offset, mem.show_online,
                        mem.buddy_list, mg.online_color AS member_group_color, IFNULL(mg.group_name, '') AS member_group,
                        pg.online_color AS post_group_color, IFNULL(pg.group_name, '') AS post_group, mem.is_activated, mem.warning,
                        CASE WHEN mem.id_group = 0 OR mg.stars = '' THEN pg.stars ELSE mg.stars END AS stars,
                        mem.usertitle
                        FROM smf_members AS mem
                        LEFT JOIN smf_log_online AS lo ON (lo.id_member = mem.id_member)
                        LEFT JOIN smf_attachments AS a ON (a.id_member = mem.id_member)
                        LEFT JOIN smf_membergroups AS pg ON (pg.id_group = mem.id_post_group)
                        LEFT JOIN smf_membergroups AS mg ON (mg.id_group = mem.id_group)
                        WHERE mem.id_member IN (-redacted-, -redacted-, -redacted-, -redacted-, -redacted-, -redacted-);

This is calling the postbit (the information to your left) when you view a thread page. This unfortunately is probably just one of those times it has to load from disk. It doesn't happen often.

Code: [Select]
# Time: 110526 15:12:53
# User@Host: elliquiy[elliquiy] @ localhost []
# Query_time: 14.895615  Lock_time: 0.000070 Rows_sent: 0  Rows_examined: 0
use elliquiy_smf;
SET timestamp=1306440773;
SELECT
                                        id,
                                        userID,
                                        userName,
                                        userRole,
                                        channel AS channelID,
                                        UNIX_TIMESTAMP(dateTime) AS timeStamp,
                                        text
                                FROM
                                        `elliquiy_smf`.ajax_chat_messages
                                WHERE
                                        id > '4752445'
                                                AND (
                                                        channel = '0'
                                                        OR
                                                        channel = '1000005583'
                                                )
                                                AND
                                                NOW() < DATE_ADD(dateTime, interval 24 HOUR)
                                                AND NOT (
                                                                                        text LIKE ('/login%')
                                                                                        OR
                                                                                        text LIKE ('/logout%')
                                                                                        OR
                                                                                        text LIKE ('/channelEnter%')
                                                                                        OR
                                                                                        text LIKE ('/channelLeave%')
                                                                                        OR
                                                                                        text LIKE ('/kick%')
                                                                                )
                                ORDER BY
                                        id
                                        DESC
                                LIMIT 20;

For the author bragging about how efficient he made ajax chat, this is a really bad query. This won't break the site, though, just cause blank shoutboxes every now and then. It's easier to fix if it becomes a serious concern.

Code: [Select]
# User@Host: elliquiy[elliquiy] @ localhost []
# Query_time: 12.203108  Lock_time: 0.000048 Rows_sent: 103  Rows_examined: 103
SET timestamp=1306459054;
SELECT variable, value, id_member, id_theme
                        FROM `elliquiy_smf`.smf_themes
                        WHERE id_member IN (-1, 0, -redacted-)
                                AND id_theme IN (5, 1);

Another memory issue, but this can be resolved by the smf dev team by baking default theme data and preferences into static configuration.

And the same thing happens with the settings table:

Code: [Select]
# User@Host: elliquiy[elliquiy] @ localhost []
# Query_time: 11.030103  Lock_time: 0.000064 Rows_sent: 363  Rows_examined: 363
SET timestamp=1306459054;
SELECT variable, value
                        FROM `elliquiy_smf`.smf_settings;

Code: [Select]
# User@Host: elliquiy[elliquiy] @ localhost []
# Query_time: 354.198912  Lock_time: 0.000067 Rows_sent: 25  Rows_examined: 3352759
SET timestamp=1306156008;
SELECT
                                        b.id_board, b.name AS bname, c.id_cat, c.name AS cname, m.id_topic, m.id_msg,
                                        t.id_member_started, t.id_first_msg, t.id_last_msg, m.body, m.smileys_enabled,
                                        m.subject, m.poster_time, m.approved
                                FROM smf_messages AS m
                                        INNER JOIN smf_topics AS t ON (t.id_topic = m.id_topic)
                                        INNER JOIN smf_boards AS b ON (b.id_board = t.id_board)
                                        LEFT JOIN smf_categories AS c ON (c.id_cat = b.id_cat)
                                WHERE m.id_member = -redacted-                                        AND (FIND_IN_SET(9, b.member_groups) != 0 OR FIND_IN_SET(11, b.member_groups) != 0)
                                ORDER BY m.id_msg DESC
                                LIMIT 75, 25;

FIND_IN_SET strikes again. Some poor soul was waiting six minutes for this one.

---

Most of these happen around the same time. A flood of queries can't be completed in a sane period and it just mothballs.

If people are willing to tolerate losing a couple of features I can resolve this a bit, though.

Offline Oreo

Re: Server Slowdowns
« Reply #6 on: May 26, 2011, 10:09:01 PM »
I have noticed the lag, too and thought it was just me.

What features would we lose?

Offline Saerrael

  • ~~~Seraph ; In love~~~ Do you like what you see? Let me entertain ya ítill you scream.
  • Champion
  • Carnite
  • *
  • Join Date: Aug 2009
  • Location: You don't know how you took it You just know what you got Oh Lordy you've been stealing from the thieves And you got caught
  • Gender: Male
  • Hold me, thrill me, kiss me, kill me.
  • My Role Play Preferences
  • View My Rolls
  • Referrals: 6
Re: Server Slowdowns
« Reply #7 on: May 26, 2011, 10:12:16 PM »
*stares at all the code and just nods*

What Oreo asked.

Offline MargueriteTopic starter

  • Lifestyle Domme, Mistress, Queen of Sin/Debauchery, Eden's Stalker, Domina McSpanks
  • Lady
  • Carnite
  • *
  • Join Date: Jul 2008
  • Location: In A House Made Of Collected Hearts
  • Gender: Female
  • "Now, ask me what I'm going to do to you."
  • My Role Play Preferences
  • View My Rolls
  • Referrals: 2
Re: Server Slowdowns
« Reply #8 on: May 26, 2011, 10:18:19 PM »
I am fine with losing a few things.

Online Vekseid

Re: Server Slowdowns
« Reply #9 on: May 26, 2011, 10:19:24 PM »
I have noticed the lag, too and thought it was just me.

What features would we lose?

The two culprits I can fix easily are
- The little face-like icon that shows you what threads you've posted in when you view a board, and
- The ability to stay logged in after the database restarts (sortof). This doesn't happen often, but people really complain when it does happen when I did this optimization before.

Offline Dim Hon

  • The Horrible, Lovely Thing || The Sorcerer's Familiar || Not This || Sinners' Nadir || Desire of the Endless || More Dark In Me Than Bones || St. Fucking
  • Oracle
  • Carnite
  • *
  • Join Date: Dec 2008
  • Location: ne invoces expellere non possis
  • The Aforementioned Darkness
  • My Role Play Preferences
  • View My Rolls
  • Referrals: 19
Re: Server Slowdowns
« Reply #10 on: May 26, 2011, 10:37:04 PM »
Those lambs I would be willing to sacrifice for the benevolence of the E server *nods*

Offline Saerrael

  • ~~~Seraph ; In love~~~ Do you like what you see? Let me entertain ya ítill you scream.
  • Champion
  • Carnite
  • *
  • Join Date: Aug 2009
  • Location: You don't know how you took it You just know what you got Oh Lordy you've been stealing from the thieves And you got caught
  • Gender: Male
  • Hold me, thrill me, kiss me, kill me.
  • My Role Play Preferences
  • View My Rolls
  • Referrals: 6
Re: Server Slowdowns
« Reply #11 on: May 26, 2011, 10:42:09 PM »
Same here.

Offline lollipop

Re: Server Slowdowns
« Reply #12 on: May 27, 2011, 01:28:09 AM »
Agreed (on losing the little face icon). Most threads I've posted in I can remember or have bookmarked.

Offline Oreo

Re: Server Slowdowns
« Reply #13 on: May 27, 2011, 01:47:12 AM »
Yeah, the little face can go. How often does the data base restart? Is that the once a month cleanup? If so that wouldn't be too much of a hardship.

Offline Wistful Dream

  • ✧ ~ ✦ ~ Bright Star ~ ✦ ~ ✧ ☾ A Dragon's Mariposa ☽ ~ * ~ A Captain's Princess ~ * ~ An Imp's Vixen
  • Dame
  • Carnite
  • *
  • Join Date: May 2008
  • Location: The Beast's Castle
  • Gender: Female
  • I know my value.
  • My Role Play Preferences
  • View My Rolls
  • Referrals: 3
Re: Server Slowdowns
« Reply #14 on: May 27, 2011, 02:14:52 AM »
I wouldn't mind loosing those two things if it makes the site load more reliably.

Offline Ramster

Re: Server Slowdowns
« Reply #15 on: May 27, 2011, 05:35:05 AM »
Whatever it takes to keep the site running with the Shoutbox!

Offline Jagerin

  • They/Them Pronouns || Alpha Raptor || Fandom Monarch || Estrous Twinkle Particle || Kho's Murdoc || Nic's Scully
  • Champion
  • Carnite
  • *
  • Join Date: Nov 2008
  • Location: A melancholy town where we never smile.
  • Black roses and Hail Mary's can't bring you back.
  • My Role Play Preferences
  • View My Rolls
  • Referrals: 20
Re: Server Slowdowns
« Reply #16 on: May 27, 2011, 08:13:56 AM »
Those lambs I would be willing to sacrifice for the benevolence of the E server *nods*

I've got the ritual stone knife. *nods*

Whatever needs to go to keep the site running needs to go.

Online Vekseid

Re: Server Slowdowns
« Reply #17 on: May 27, 2011, 11:53:26 AM »
Apparently we just passed 32 megs for the size of the session table so I'll have to restart the database before fixing that >_>

Offline Starlequin

Re: Server Slowdowns
« Reply #18 on: May 27, 2011, 07:31:45 PM »
Ahaaa! *points index finger in random upward direction* I've been wondering where little Mr. You Posted Here had vanished off to. I was about to file a missing persons report. Poor little fellow. I hope he finds a new position soon; this is no time to  be unemployed. He will be missed.   ::)

Offline Oreo

Re: Server Slowdowns
« Reply #19 on: May 27, 2011, 07:47:54 PM »
Yes, but the pages are loading faster. ;D

Offline Wistful Dream

  • ✧ ~ ✦ ~ Bright Star ~ ✦ ~ ✧ ☾ A Dragon's Mariposa ☽ ~ * ~ A Captain's Princess ~ * ~ An Imp's Vixen
  • Dame
  • Carnite
  • *
  • Join Date: May 2008
  • Location: The Beast's Castle
  • Gender: Female
  • I know my value.
  • My Role Play Preferences
  • View My Rolls
  • Referrals: 3
Re: Server Slowdowns
« Reply #20 on: May 27, 2011, 07:49:50 PM »
Its much better on my end as well :)

Offline Saerrael

  • ~~~Seraph ; In love~~~ Do you like what you see? Let me entertain ya ítill you scream.
  • Champion
  • Carnite
  • *
  • Join Date: Aug 2009
  • Location: You don't know how you took it You just know what you got Oh Lordy you've been stealing from the thieves And you got caught
  • Gender: Male
  • Hold me, thrill me, kiss me, kill me.
  • My Role Play Preferences
  • View My Rolls
  • Referrals: 6
Re: Server Slowdowns
« Reply #21 on: May 27, 2011, 07:55:19 PM »
Also noticing more speed.

Of course, I was thinking it was me, again >.>'

Offline MargueriteTopic starter

  • Lifestyle Domme, Mistress, Queen of Sin/Debauchery, Eden's Stalker, Domina McSpanks
  • Lady
  • Carnite
  • *
  • Join Date: Jul 2008
  • Location: In A House Made Of Collected Hearts
  • Gender: Female
  • "Now, ask me what I'm going to do to you."
  • My Role Play Preferences
  • View My Rolls
  • Referrals: 2
Re: Server Slowdowns
« Reply #22 on: May 27, 2011, 07:58:31 PM »
Yes, pages are loading fast, but for how long?

Dun. Dun. Dun!

Offline Starlequin

Re: Server Slowdowns
« Reply #23 on: May 27, 2011, 08:00:57 PM »
*shrugs* I surf from a smartphone; I have little to no concept of this speed you speak of. Anything that takes less than ten minutes is blazin'.  8-)

Online Vekseid

Re: Server Slowdowns
« Reply #24 on: May 27, 2011, 10:45:24 PM »
I've set swappiness to 10 and that might be sufficient. Being here during the lag helps >_>

The server was -very- aggressively swapping despite having nearly a gig and a half free. I'll see if that solves things for now.