Server Slowdowns

[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?

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

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

[Vekseid]

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

Code Select Expand


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


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


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


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


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


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


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


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

[Saerrael]

*stares at all the code and just nods*

What Oreo asked.

[Marguerite]

I am fine with losing a few things.

[Vekseid]

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]

Same here.

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

[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!

[Jag]

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.

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

[Oreo]

Yes, but the pages are loading faster. ;D

[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!

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

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

[Chelemar]

FROM `elliquiy_smf`.smf_themes

Is this proof that smirfs control the E.?

[Starlequin]

Oh no, what a smurfy thought!

[Vekseid]

Is this proof that smirfs control the E.?

SMF stands for Simple Machines Forum, which is what we run : )

[Star Safyre]

R.I.P. Mr. You Posted Here.  Your sacrifice will not be forgotten.

/salute

[HockeyGod]

I know that everyone has universally said to take away the smiley face and it's gone, so this may not sway the argument, but I used it EVERY time I'm in E. I use it especially in the introduction board - this allows me to see which thread I've posted an intro question into. It appears that now I have to click in every thread to see if I posted.

So...will it ever come back?

[Vekseid]

I know that everyone has universally said to take away the smiley face and it's gone, so this may not sway the argument, but I used it EVERY time I'm in E. I use it especially in the introduction board - this allows me to see which thread I've posted an intro question into. It appears that now I have to click in every thread to see if I posted.

So...will it ever come back?

It's not like the you-posted-here notification icons are causing the problem, far from it. They're just not helping when it is a problem.

We are basically out of RAM. I've disabled some useless things on the server side and tightened belts on BMR and LR. It just seems incredulous to me that I'd have to go beg for another $1,500 for more server upgrades when we're not actually physically out of memory yet. Just nearly so.

Supposedly, setting swappiness to 0 like I have should mean that applications don't get swapped to disk. But they were getting swapped to disk anyway, even though they could almost all fit in RAM and certainly could now. After tightening things Linux is behaving better, though. Lots of swap in and no swap out.

I'll see if the load spikes are gone and if so I can re-enable the icons for now.

[Vekseid]

Tuned the database a bit, looks like we might be able to use a few hundred megabytes more efficiently.

Still will leave Linux flubbing its lips about what to do with half a gig but still an improvement. If things run well this evening I'll re-enable the icons. May also move Sphinx to the backup/dns/mumble server to free up some more RAM and prevent the reindexing slowdowns.

[Starlequin]

*sigh* There was a time I actually knew what all that meant.  :-(

* Starlequin misses being smart.

[Vekseid]

Linux has a general measurement of server load called, creatively, 'load'. It's a numeric value that tells you how many processes are waiting to run at once. Elliquiy's server can handle a load of up to 4 without issue, because it has four drives and four cpu cores. So it can do four things at once, generally.

Swap is 'virtual ram' - it's disk space being used as memory, only roughly about a million times slower. That isn't an exaggeration, it really is a million times slower. This means when the virtual memory of something that is actively being used gets used, the OS starts swapping it in and out of memory, called thrashing. Which is what Elliquiy was doing the slowdowns, but, it shouldn't have been - there was a gig and a half of RAM left free. In Linux, you control how aggressively the system swaps out applications with the swappiness parameter. 0 means it won't swap until necessary (in theory), keeping applications in RAM, 100 means it will swap everything it can, saving ram for the disk cache.

Since Elliquiy isn't a fileserver, we want to keep applications in memory. And that wasn't happening, even at zero. v. frustrating.

Sphinx is the search engine for Elliquiy and Blue Moon. It makes an index of every single post on the site, and allows some pretty advanced features, though we only take advantage of a few of them.

* Vekseid is happy to explain things, though. : )

[Nadir]

Ooooh, sphinxes!

[Starlequin]

Ah, knowledge nuggets. I have missed thy flavor. Why couldn't we have had a teacher like you during our Comp.Prog.Tech. classes instead of those lifeless drones who sent us to read chapters 4-17 every week?

@Atreyu: No, don't run! You'll never make it! Aiiee!

[Vekseid]

As you can see I've reenabled the icons.

Code Select Expand


root@trancephalon:~# free -m
             total       used       free     shared    buffers     cached
Mem:         12038      11800        237          0        161       1493
-/+ buffers/cache:      10146       1891
Swap:         4102        334       3767


Much nicer looking.

Numbers are in megabytes - e.g. we have twelve gigabytes of ram and ~250 megabytes free.

[Kye]

Huzzah! Thank you Veks! While I don't understand all the numbers I'm thrilled to have the icons back.

[HockeyGod]

Yes, I love the smileys...thanks!

[Star Safyre]

Yay, Mr. You Posted Here lives again!   XD

Boo, the blue star is gone.   :'(

[Vekseid]

Oh shoot. Stupid mod overwrote it. Where was that star again? >_>

[Star Safyre]

* Star Safyre raises her hand!

Oh, you meant the graphic.   :-[

[Marguerite]

Another slow down again?

[Saerrael]

I have the same thing, right now.

[Vekseid]

Another slow down again?

My fault, that one, sorry : /

[Marguerite]

For a second, I thought we used up all of the space again.

-Smiles-

[Vekseid]

For a second, I thought we used up all of the space again.

-Smiles-

Well that is basically what happened. I was doing some maintenance on the server and apparently I can't run the forums and do that at the same time : /

[Marguerite]

Damn and it has been only a week since the last server slowdowns.