Server Slowdowns

Started by Marguerite, May 26, 2011, 08:22:50 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Marguerite

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?
*R.R*A.A*O.O*Wiki*Bordello*Whip and Apple*
You Keep On Crying, Baby, I'll Bleed You Dry
Mar Is Currently: Taking On Threads
Check My Absence Thread For Updates, Thank You

Saerrael

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.

Jag

Glad to hear it isn't just me.
Ons/Offs // Request Thread (Updated 3/10/24) // Slow to Reply at the Moment

Wistful Dream

Its been happening to me for a few days now, I just figured it was my browser not liking the site.

Star Safyre

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.
My heaven is to be with him always.
|/| O/O's / Plots / tumblr / A/A's |/|
And I am a writer, writer of fictions
I am the heart that you call home
And I've written pages upon pages
Trying to rid you from my bones

Vekseid

I know there were network problems this morning, which caused some of it. Let's see.


# 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.


# 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.


# 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.


# 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.


# 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.


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


# 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;



# 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.

Oreo

I have noticed the lag, too and thought it was just me.

What features would we lose?

She led me to safety in a forest of green, and showed my stale eyes some sights never seen.
She spins magic and moonlight in her meadows and streams, and seeks deep inside me,
and touches my dreams. - Harry Chapin

Saerrael

*stares at all the code and just nods*

What Oreo asked.

Marguerite

I am fine with losing a few things.
*R.R*A.A*O.O*Wiki*Bordello*Whip and Apple*
You Keep On Crying, Baby, I'll Bleed You Dry
Mar Is Currently: Taking On Threads
Check My Absence Thread For Updates, Thank You

Vekseid

Quote from: Oreo 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?

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.

Nadir

Those lambs I would be willing to sacrifice for the benevolence of the E server *nods*

Saerrael


lollipop

Agreed (on losing the little face icon). Most threads I've posted in I can remember or have bookmarked.

Oreo

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.

She led me to safety in a forest of green, and showed my stale eyes some sights never seen.
She spins magic and moonlight in her meadows and streams, and seeks deep inside me,
and touches my dreams. - Harry Chapin

Wistful Dream

I wouldn't mind loosing those two things if it makes the site load more reliably.

Ramster

Whatever it takes to keep the site running with the Shoutbox!
Leave not a piss untaken, nor a Michael unappropriated.
A/As!!!
Knight of the Order of the Pizza



Nulla gratuitas sine anchoa

Jag

Quote from: Eden on May 26, 2011, 10:37:04 PM
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.
Ons/Offs // Request Thread (Updated 3/10/24) // Slow to Reply at the Moment

Vekseid

Apparently we just passed 32 megs for the size of the session table so I'll have to restart the database before fixing that >_>

Starlequin

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.   ::)
You live for the fight when it's all that you've got.

Oreo

Yes, but the pages are loading faster. ;D

She led me to safety in a forest of green, and showed my stale eyes some sights never seen.
She spins magic and moonlight in her meadows and streams, and seeks deep inside me,
and touches my dreams. - Harry Chapin

Wistful Dream

Its much better on my end as well :)

Saerrael

Also noticing more speed.

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

Marguerite

Yes, pages are loading fast, but for how long?

Dun. Dun. Dun!
*R.R*A.A*O.O*Wiki*Bordello*Whip and Apple*
You Keep On Crying, Baby, I'll Bleed You Dry
Mar Is Currently: Taking On Threads
Check My Absence Thread For Updates, Thank You

Starlequin

*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-)
You live for the fight when it's all that you've got.

Vekseid

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.