PHP processes keep overwhelming my server, eating up the CPU and making it lock up :(

[hoppipolla]

I'll try to catch up soon (it's all very heavy for me lol) but also here is my my.cnf:

http://pastebin.com/q2unrNZF

and some of my slow queries log:

http://pastebin.com/AsFJseyb


Some are really long I know... sometimes it might be due to a maxed out CPU though I dunno o.O

[hoppipolla]

OK erm... in response to the posts...

Seiji - I suspect that the variety and complexity of MyBB searches may be a bit too much for working out indexing? Wouldn't it take aaaages? >.<

Doug S - With st (Steal Time) and stolen CPU... I think there might be something there! I'm asking my host...

tgalati4 - With the modifications to my.cnf... I may make them! Do you have high hopes for them? I don't really like to make changes unless I really think they might make a difference!

EDIT -- Although I see you were also mentioning the st CPU like Doug S... that is VERY interesting isn't it...

Thanks a lot again guys!

[SeijiSensei]

Seiji - I suspect that the variety and complexity of MyBB searches may be a bit too much for working out indexing? Wouldn't it take aaaages? >.<

Not necessarily.

First, let me suggest you set up the application on another machine for testing purposes. It's hard to play around on a production machine without possibly compromising the application for actual users.

Since you have so many contending processes I'd have to guess that any queries that are causing the problem must be fairly common ones. Searches would be at the top of my list. Walk yourself through the application and try everything that normal users would do. Do some activities take longer than expected? If so, look in the PHP code to find the queries those activities employ, then run them yourself from the command line using the mysql client. Look at the criteria in their WHERE clauses and make sure that those criteria are well-indexed.

A lot of bulletin-board applications rely on simple SQL queries to handle searching. That often leads to deteriorating performance as the size of the database increases. I use SphinxSearch to do full-text queries in situations like yours as it is exceedingly fast. Sphinx is designed for applications like forums and catalogs where all the information is stored in an SQL database. In one case, where someone was using Zen Cart to handle a 50,000+ catalog of items, full-text searches could take up to a minute. After I replaced the standard SQL-based method with Sphinx, those searches were nearly instantaneous.

[hoppipolla]

One issue is that even after I disabled all searches by removing the search.php page, the server still died in the same way.

Only by closing the forum completely (and just leaving up the shoutbox, chat room and gallery) can I keep the server up

Even users coming online can sometimes result in a very slow query, even though only one row has to be examined. Look:

http://pastebin.com/DNh9ahFv

[SeijiSensei]

UPDATE mybb_users SET lastactive='1365606064', timeonline=timeonline+105 WHERE uid='12504';
# Time: 130410 16:01:22
# User@Host: sonichen[sonichen] @ localhost []
# Query_time: 15.888670 Lock_time: 0.000030 Rows_sent: 0 Rows_examined: 1

Does that mean it took 15 seconds (!) just to update a single field? Use the mysql command-line client to show the indexes on the "users" table. Is uid the primary key? If not, is it indexed?

This could be because the server itself is overburdened, or it could be bad database design. If you take down the webserver and just run the same query from the command line with mysql, does it still take a long time?

It's also weird that uid is encased in quotes. Isn't it a numerical field?

[hoppipolla]

Ah it's not stolen time ._.

Look...

http://i.imgur.com/gASMyQM.jpg (my site on low CPU)

http://i.imgur.com/RmT9JcW.jpg (my site when it all locks up)


And sometimes it can take minutes to log a user on (to finish the query)... it's weird.

[Doug S]

Ah it's not stolen time ._.

Your new pie chart links do not prove that your VM was not suffering from "stolen" time.
Your much much earlier link to your screen capture of your "top" command does prove (I think) that your VM was, at that time, suffering from an extreme percentage of "stolen" time.
The new pie chart links might suggest that your VM might be limited to 23.51% (perhaps 25%) of the host overall CPU capactity. The host hypervisor does not have to actually use the time, if it steals it. However, that is likely a substantial amount of CPU time available to your VM.
By the way, for my example from my computer of post #20, the host load averages were very low:

doug@s15:~/temp$ uptime
15:28:28 up 3 days,  1:41,  5 users,  load average: 0.00, 0.06, 0.26

[hoppipolla]

I really think it's PHP processes getting "locked up". The more I allow it to start, the more they stack up and the longer it takes for them to clear ._.

[hoppipolla]

For the record erm, I managed to put a fast and I think fully working server together by installing Ubuntu 12.04, then ZPanel, then memcache, APC and Varnish, and then switching Apache for Lighttpd. I lost the ZPanel interface as it relies on Apache AFAIK, but I got a working and very fast server out of the deal I think.

Whatever change had to be made to stop the crashes I think was made by the ZPanel staff in the background (for their default ZPanel install).

So erm... yeah, result, I think