MyBB Community Forums

Hi all,

I have found out about this problem for some time now. But haven't had the time to find out what may cause the problem when the page loads. CPU usage is quite high.

[~]# php -v
PHP 5.3.1 (cli) (built: Feb 18 2010 01:01:30)
Copyright © 1997-2009 The PHP Group
Zend Engine v2.3.0, Copyright © 1998-2009 Zend Technologies
with the ionCube PHP Loader v3.3.10, Copyright © 2002-2009, by ionCube Ltd.
[~]# mysql -V
mysql Ver 14.12 Distrib 5.0.89, for pc-linux-gnu (i686) using readline 5.1
[~]#

Attached is the query log.

Count         : 8.20k  (12.67%)
Time          : 269969 s total, 32.935098 s avg, 6 s to 1433 s max  (29.09%)
  95% of Time : 199685 s total, 25.64338 s avg, 6 s to 80 s max
Lock Time (s) : 400 s total, 48.798 ms avg, 0 to 60 s max  (3.18%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 51 avg, 0 to 51 max  (0.03%)
Rows examined : 39.53k avg, 0 to 61.07k max  (3.06%)
Database      : carlife_mybb
Users         : 
	carlife@localhost  : 100.00% (8197) of query, 52.32% (33846) of all users

Query abstract:
SELECT tid, COUNT(pid) AS total_posts, MAX(pid) AS last_pid FROM mybb_posts WHERE tid IN (N51) GROUP BY tid;

Query sample:
SELECT tid, COUNT(pid) as total_posts, MAX(pid) as last_pid FROM mybb_posts WHERE tid IN (14986,14192,14935,15333,14223,2990,13245,9614,15340,15044,13565,15300,15242,13197,15207,15108,1478,14092,13991,15227,13771,14724,6032,14803,14175,12938,15283,15158,13785,14819,11804,13854,15210,14853,13939,14673,15315,15243,15312,10271,13923,14340,14413,13536,10307,14668,13720,14503,15293,14239,13629) GROUP BY tid;


Count         : 5.88k  (9.09%)
Time          : 95177 s total, 16.183812 s avg, 6 s to 732 s max  (10.25%)
  95% of Time : 74798 s total, 13.390261 s avg, 6 s to 40 s max
Lock Time (s) : 52 s total, 8.842 ms avg, 0 to 8 s max  (0.41%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 50 avg, 0 to 50 max  (0.02%)
Rows examined : 11.86k avg, 0 to 32.40k max  (0.66%)
Database      : carlife_mybb
Users         : 
	carlife@localhost  : 100.00% (5881) of query, 52.32% (33846) of all users

Query abstract:
SELECT tid, COUNT(pid) AS total_posts, MAX(pid) AS last_pid FROM mybb_posts WHERE tid IN (N50) GROUP BY tid;

Query sample:
SELECT tid, COUNT(pid) as total_posts, MAX(pid) as last_pid FROM mybb_posts WHERE tid IN (15344,15301,14984,12537,14715,15306,15296,13911,2987,15322,14746,15318,15272,14445,15274,8657,14973,14683,15211,2139,1477,15269,15331,13633,15101,12651,15064,14725,15115,13752,14183,13396,6183,14689,14716,14690,15230,13033,9534,10710,15257,15156,15320,15266,15224,15319,15295,15219,14505,15279) GROUP BY tid;

Count         : 5.53k  (8.54%)
Time          : 54873 s total, 9.929967 s avg, 6 s to 216 s max  (5.91%)
  95% of Time : 44122 s total, 8.405792 s avg, 6 s to 17 s max
Lock Time (s) : 129 s total, 23.344 ms avg, 0 to 52 s max  (1.02%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 13 avg, 1 to 34 max  (0.01%)
Rows examined : 6.00k avg, 2 to 17.45k max  (0.31%)
Database      : carlife_mybb
Users         : 
	carlife@localhost  : 100.00% (5526) of query, 52.32% (33846) of all users

Query abstract:
SELECT p.pid FROM mybb_posts p WHERE p.tid='S' AND p.visible='S' ORDER BY p.dateline LIMIT N, N;

Query sample:
SELECT p.pid FROM mybb_posts p WHERE p.tid='4165' AND p.visible='1' ORDER BY p.dateline LIMIT 1230, 15;

Count         : 3.13k  (4.84%)
Time          : 40705 s total, 13.004792 s avg, 6 s to 56 s max  (4.39%)
  95% of Time : 35353 s total, 11.891356 s avg, 6 s to 28 s max
Lock Time (s) : 19 s total, 6.07 ms avg, 0 to 8 s max  (0.15%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 19 avg, 19 to 19 max  (0.00%)
Rows examined : 9.06k avg, 1.35k to 21.78k max  (0.27%)
Database      : carlife_mybb
Users         : 
	carlife@localhost  : 100.00% (3130) of query, 52.32% (33846) of all users

Query abstract:
SELECT tid, COUNT(pid) AS total_posts, MAX(pid) AS last_pid FROM mybb_posts WHERE tid IN (N19) GROUP BY tid;

Query sample:
SELECT tid, COUNT(pid) as total_posts, MAX(pid) as last_pid FROM mybb_posts WHERE tid IN (9513,13211,11302,3687,13213,4888,15162,14541,5851,15199,14915,15146,14798,15233,14912,15168,15345,8004,12942) GROUP BY tid;

Count         : 3.23k  (5.00%)
Time          : 40000 s total, 12.376238 s avg, 6 s to 147 s max  (4.31%)
  95% of Time : 34639 s total, 11.283062 s avg, 6 s to 25 s max
Lock Time (s) : 45 s total, 13.923 ms avg, 0 to 7 s max  (0.36%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 52 avg, 52 to 52 max  (0.01%)
Rows examined : 7.26k avg, 842 to 9.68k max  (0.22%)
Database      : carlife_mybb
Users         : 
	carlife@localhost  : 100.00% (3232) of query, 52.32% (33846) of all users

Query abstract:
SELECT tid, COUNT(pid) AS total_posts, MAX(pid) AS last_pid FROM mybb_posts WHERE tid IN (N52) GROUP BY tid;

Query sample:
SELECT tid, COUNT(pid) as total_posts, MAX(pid) as last_pid FROM mybb_posts WHERE tid IN (15142,15176,2644,2603,14149,14976,9388,12590,13212,15316,14912,13977,15250,15024,15111,14232,15254,15261,15059,15234,15164,15223,14902,14991,15087,14575,15019,14585,15205,13150,15169,14836,15028,14567,15137,15123,14928,14975,13204,15070,14950,14920,14734,10363,14979,14961,12372,14876,14799,14937,14846,13887) GROUP BY tid;

Count         : 574  (0.89%)
Time          : 17235 s total, 30.026132 s avg, 6 s to 356 s max  (1.86%)
  95% of Time : 10037 s total, 18.416514 s avg, 6 s to 178 s max
Lock Time (s) : 25 s total, 43.554 ms avg, 0 to 6 s max  (0.20%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 20 avg, 0 to 20 max  (0.00%)
Rows examined : 8.76k avg, 0 to 19.85k max  (0.05%)
Database      : carlife_mybb
Users         : 
	carlife@localhost  : 100.00% (574) of query, 52.32% (33846) of all users

Query abstract:
SELECT tid, COUNT(pid) AS total_posts, MAX(pid) AS last_pid FROM mybb_posts WHERE tid IN (N20) GROUP BY tid;

Query sample:
SELECT tid, COUNT(pid) as total_posts, MAX(pid) as last_pid FROM mybb_posts WHERE tid IN (9513,13211,11302,15075,13727,4803,12138,15160,5851,15199,14915,15146,15764,15233,15732,15009,15693,9590,12942,15722) GROUP BY tid;

Count         : 1.14k  (1.77%)
Time          : 13816 s total, 12.076923 s avg, 6 s to 191 s max  (1.49%)
  95% of Time : 10160 s total, 9.355433 s avg, 6 s to 23 s max
Lock Time (s) : 60 s total, 52.448 ms avg, 0 to 8 s max  (0.48%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 1 avg, 0 to 1 max  (0.00%)
Rows examined : 3.81k avg, 0 to 8.73k max  (0.04%)
Database      : carlife_mybb
Users         : 
	carlife@localhost  : 100.00% (1144) of query, 52.32% (33846) of all users

Query abstract:
SELECT message FROM mybb_posts WHERE tid = 'S' ORDER BY pid ASC LIMIT N;

Query sample:
SELECT message FROM mybb_posts WHERE tid = '4805' ORDER BY pid ASC LIMIT 1;

Any idea what might cause it??

Thanks.

Are you on a dedicated or VPS server? How do you know that the high CPU isn't caused by someone else on your shared server or by overcrowding of the server?

Hi TimB,

No, i am not on a dedicated server, but I do have root access. When i was testing it out, i shutdown temporary all the other databases. I have left the forum running for about 1 hours, i noticed that every time there the mysql does a query, the CPU usage will increase.

It does not happened before this, it started when i was doing the last 2 upgrades.

Thanks.

What version did you upgrade from? Was it part of the 1.4 series? Also, are you running any plugins like the shoutbox plugin? I don't know a whole lot about server administration so I'm just trying to think of some of the usual causes.

It looks like full table scans for me.
if you have a sql console type:

explain SELECT tid, COUNT(pid) as total_posts, MAX(pid) as last_pid FROM mybb_posts WHERE tid IN (15142,15176,2644,2603,14149,14976,9388,12590,13212,15316,14912,13977,15250,1502 ​ 4,15111,14232,15254,15261,15059,15234,15164,15223,14902,14991,15087,14575,15019, ​ 14585,15205,13150,15169,14836,15028,14567,15137,15123,14928,14975,13204,15070,14 ​950,14920,14734,10363,14979,14961,12372,14876,14799,14937,14846,13887) GROUP BY tid;

Get rid of that "tags" plugin for one.

Then read this: http://www.vbum.com/2010/01/mybb-and-inc...rformance/

considered it solved.

I have found out that the problem was with the tags plugins. Anyway labrocca i have found out that the culprit was the tags plugin as what you have suggested.

What i have did was i create and index table for the tags, and now the speed is ok.

Thank you all.

is this the reason my server has crashed to?

My server has crashed now 2 times this week with in 48 hours, and Yes i am using the TAGS plug in myself .. ???

If so can you explain what you guys did to bring down the CPU, thank you!

Cool features aren't always wise to install if they're going to create problems with load. Part of my philosophy with my plugins is to streamline them as much as possible. Some authors aren't concerned about that.


Technoman...try to use phpmyadmin to create "indexes" on some of the columns.

labrocca bro

I only know how to do 1 thing in phpmyadmin, and that's to export my database .sql file

how to make index for some of the columns is the same like how to make super for my wife ...

i do not know how