300,000+ Forum Loading Slow and High Server Load

[LaurieC]

Seems that our server load is spiking at times and causing members to get errors:

Error 500 '[url' Unknown Host.   Try reloading the Web page.
Retype the address in your browser.
Check your computer's Internet connection.

Website not responding

    * Start with a good server. If you are using shared hosting, check load averages. Make your host give you the information that you need. You also want to know MySQL, Apache, PHP, and kernel version. Along with how many accounts are on the server and other usage statistics. This is KEY for shared hosting. If you are using dedicated, shop around and go with what is best.

Managed Dedicated Rochen Host

    * Ensure your operating system is up-to-date and optimized. Windows has shown to not be as good in terms of performance for Apache or MySQL

Server is running CentOS 4.3

    * Use Apache 2 and optimize it. Still doing my own research on different MPMs so I can't really suggest which one would be best for each situation. Although, they are different ones for different server usage. Do some research

Server is running Apache 1.3.34

    * Use a later version of MySQL. There are perfomance enhancements with every new version.

Up to date

    * Get PHP 5! Its been stable for quite some time now. Run the php.ini-recommended (just erase the '-recommended'). I recommend turning on display_errors and turning off error_logging. Unless you want to stick with the errors going straight to logs.

4.4.1

    * If you're using SMF 1.0.x, make sure the "Check avatar size every time it's displayed" setting is off.  This setting was removed in 1.1.

1.1 RC2 patched

    * Do you have eAccelerator or another optimizer installed?
   
APC installed Level 1 caching

    * Do you have a lot of posts?  If so, have you converted any of your tables to InnoDB?

yabb_attachments
yabb_collapsed_categories
yabb_log_actions
yabb_log_boards
yabb_log_errors
yabb_log_karma
yabb_log_mark_read
yabb_log_online
yabb_log_search_messages
yabb_log_search_results
yabb_log_search_subjects
yabb_log_search_topics
yabb_log_topics
yabb_members
yabb_pm_recipients
yabb_sessions
yabb_settings
yabb_topics
yabb_log_floodcontrol HEAP

    * Have MySQL's settings been tweaked to optimize memory usage and general efficiency?

I have changed the above to InnoDB. Dissabled persistent connection, Dissabled automatic optimization.

    * A link to your forum.
   
http://www.ultimatebass.com/bass-fishing-forum/
   
    * A link to your status.php - You can download it from README: Checklist for performance problems

http://www.ultimatebass.com/bass-fishing-forum/status.php
Note: I have tried to access this and all I get is a white page. Not sure what to do to get the info necessary.

    * a link to your phpinfo.php -
   
http://www.ultimatebass.com/phpinfo.php

    * Is this a dedicated server or shared host. If it is a dedicated server, what spec is the server and what else runs on the server? If it is a shared host post a link to the hosts site the package you are on

Dedicated:

Processors     1
Model    Intel(R) Pentium(R) 4 CPU 3.20GHz
CPU Speed    3.21 GHz
Cache Size    2048 KB
System Bogomips    6419.86
PCI Devices    00:02.5 IDE interface: Silicon Integrated Systems [SiS] 5513 [IDE]
00:04.0 Ethernet controller: Silicon Integrated Systems [SiS] SiS900 PCI Fast Ethernet
00:05.0 RAID bus controller: Silicon Integrated Systems [SiS] RAID bus controller 180 SATA/PATA [SiS]
01:00.0 VGA compatible controller: Silicon Integrated Systems [SiS] 661/741/760/761 PCI/AGP VGA Display Adapter
IDE Devices    none
SCSI Devices    ATA ST3120827AS (Direct-Access)
ATA ST3120827AS (Direct-Access)

CPG
Joomla
CPLinks
Ardvark Top Sites

    * The Number of posts your forum has.
   
311,089

    * The Average number of users you have online during peak time and over what time period that is over, e.g 400 users over 15 mins.

75 - 100 users over 15 mins.

    * A list of mods which you have installed
   
Member Group Color Legend - installed 5/29/06
   
    * Do you still have performance issues if you use the default theme?
   
yes

Any help or ideas are much appreciated.

[Ben_S]

Unfortunatly your status.php isn't loading for some reason, can you try reuploading it.

[LaurieC]

Ben, I uploaded it again but still nothing. I even downloaded it again from the thread here in this forum. Any hints as to what might be wrong and why it isn't loading?

[LaurieC]

Ok, it is up and working. For some reason when I uploaded it using ftp it was corupt. With a little help from Rochen we got a good file on the server.

[Ben_S]

When it's going slow can you have a look at the status.php file to see if there are any queries running that are taking a long time.

That server is more than enough for a forum of your size.

[LaurieC]

Load Averages     13.90 10.60 6.54

August 02, 2006, 01:54:50 PM
Operating System:    CentOS release 4.3 (Final)
Processor:    Intel® Pentium® 4 CPU 3.20GHz (3208.659MHz)
Load averages:    16.34, 11.42, 6.90
Current processes:    158 (149 sleeping, 6 running, 3 zombie)
Processes by CPU:    httpd (60) 2.2%, mysqld (1) 0.7%, (other) (51) 0.5%
Memory usage:    37.209% (378920k / 1018360k)
Swap: 22.514% (461144k / 2048248k)
MySQL processes
Total processes:    13 (1 sleeping, 12 running, 0 locked)

Running processes
State    Time    Query
Sending data    118s    

CREATE TEMPORARY TABLE yabb_topics_posted_in (
   PRIMARY KEY (ID_TOPIC)
)
SELECT ID_TOPIC, ID_BOARD
FROM yabb_messages
WHERE ID_MEMBER = 419
GROUP BY ID_TOPIC

Sending data    88s    

CREATE TEMPORARY TABLE yabb_topics_posted_in (
   PRIMARY KEY (ID_TOPIC)
)
SELECT ID_TOPIC, ID_BOARD
FROM yabb_messages
WHERE ID_MEMBER = 419
GROUP BY ID_TOPIC

   0s    

SELECT
   lo.ID_MEMBER, lo.logTime, mem.realName, mem.memberName, mem.showOnline,
   mg.onlineColor, mg.ID_GROUP, mg.groupName
FROM yabb_log_online AS lo
   LEFT JOIN yabb_members AS mem ON (mem.ID_MEMBER = lo.ID_MEMBER)
   LEFT JOIN yabb_membergroups AS mg ON (mg.ID_GROUP = IF(mem.ID_GROUP = 0, mem.ID_POST_GROUP, mem.ID_GROUP))
WHERE INSTR(lo.url, '%s')

Copying to tmp table    5s    

SELECT labels, is_read, COUNT(ID_PM) AS num
FROM yabb_pm_recipients
WHERE ID_MEMBER = 1
GROUP BY labels, is_read

Sending data    3s    

SELECT pm.ID_PM, pm.ID_MEMBER_FROM
FROM (yabb_personal_messages AS pm, yabb_pm_recipients AS pmr)
WHERE pmr.ID_PM = pm.ID_PM
   AND pmr.ID_MEMBER = 1774
   AND pmr.deleted = 0
   AND FIND_IN_SET('-1', pmr.labels)
ORDER BY pmr.ID_PM ASC
LIMIT 0, 20

Sending data    2s    

SELECT COUNT(t.ID_TOPIC)
FROM yabb_topics AS t
   LEFT JOIN yabb_log_boards AS lb ON (lb.ID_BOARD = 68 AND lb.ID_MEMBER = 2951)
   LEFT JOIN yabb_log_topics AS lt ON (lt.ID_TOPIC = t.ID_TOPIC AND lt.ID_MEMBER = 2951)
WHERE t.ID_BOARD = 68
   AND t.ID_LAST_MSG > IFNULL(lt.ID_MSG, IFNULL(lb.ID_MSG, 0))
   AND t.ID_LAST_MSG > 303758

   2s    

SELECT filename, ID_ATTACH, attachmentType
FROM yabb_attachments
WHERE ID_ATTACH = 916
   AND ID_MEMBER > 0
LIMIT 1

   1s    

SELECT rank_cache, rank_cache_time FROM ats_stats WHERE username = '%s'

   1s    

SELECT data
FROM yabb_sessions
WHERE session_id = '%s'
LIMIT 1

   1s    

SELECT * FROM ats_settings

   0s    

SELECT folder, element, published, params
FROM mos_mambots
WHERE published >= 1
AND access <= 0
AND folder = 'system'
ORDER BY ordering

MySQL Statistics
MySQL 4.1.x
Connections per second:    1.5906
Kilobytes received per second:    3.8276
Kilobytes sent per second:    39.2311
Queries per second:    17.4521
Percentage of slow queries:    0.0003
Opened vs. Open tables:
(table_cache)    1.5825 (should be <= 80)
Table cache usage:
(table_cache)    0.0412 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size)    0.0326 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.2352 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    1.0006 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0.0799 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer)    0 (should be <= 0.001)
Query cache enabled:
(query_cache_type)    1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit)    0.0212 (should be <= 0.1)
Query cache prune rate:
(query_cache_size)    0 (should be <= 0.05)

[Ben_S]

Any idea what it was in case someone else has problems?

[LaurieC]

Sorry, I just edited that post as we had a spike and I didn't want to double post.

I think it was something to do with Joomla. We have it updated to current version 1.0.10 and that seemed to fix the issue last night. Brad said something to do with the .htaccess file.

[Ben_S]

What search method are you using, Admin > Search.

Can you also check that the tabels were changed to InnoDB? You can see with phpMyAdmin.

Actually topics_posted_in, this is the show posts if a user that seems to be causing it. 1.1 RC3 should reasolve this problem. Hopefully it will be available in the not too distant future.

[LaurieC]

Space used by forum messages in the database: 172,816 KB
Space used to index messages in the database: 277,383 KB

Search index:
Why create a search index?
      No index
      Fulltext index
Index: cannot be created because the max message length is above 65,535 or table type is not MyISAM
      Custom index (Checked)
Index: already created [remove custom index]
Size: 219,399 KB
Force the use of a search index:  (Checked)
Match whole words only:  (Checked)


As for the table, the ones listed in the first post were taken directly from phpMyAdmin so those are in fact InnoDB. I followed the instructions in one of the stickied topics here when our site hit 90,000 posts.

and it is at it again...

August 02, 2006, 02:51:10 PM
Operating System:    CentOS release 4.3 (Final)
Processor:    Intel® Pentium® 4 CPU 3.20GHz (3208.659MHz)
Load averages:    14.17, 5.53, 3.27
Current processes:    156 (150 sleeping, 3 running, 3 zombie)
Processes by CPU:    httpd (59) 2.0%, mysqld (1) 0.7%, (other) (50) 0.9%
Memory usage:    38.467% (391736k / 1018360k)
Swap: 21.012% (430388k / 2048248k)
MySQL processes
Total processes:    7 (2 sleeping, 4 running, 1 locked)

Running processes
State    Time    Query
Copying to tmp table    4s    

SELECT hour, SUM(views) AS sum_views
FROM ultimate_adserver.phpads_adstats
WHERE day <= 20060726
AND day >= 20060726
GROUP BY hour

Copying to tmp table    3s    

SELECT a.id, a.title, a.sectionid, a.catid
FROM mos_content AS a
LEFT JOIN mos_content_frontpage AS f ON f.content_id = a.id
INNER JOIN mos_categories AS cc ON cc.id = a.catid
INNER JOIN mos_sections AS s ON s.id = a.sectionid
WHERE ( a.state = 1 AND a.sectionid > 0 )
AND ( a.publish_up = '%s' OR a.publish_up <= '%s' )
AND ( a.publish_down = '%s' OR a.publish_down >= '%s' )
AND a.access <= 0 AND cc.access <= 0 AND s.access <= 0
AND s.published = 1
AND cc.published = 1
ORDER BY a.created DESC
LIMIT 5

Sorting result    0s    

SELECT a.id, a.introtext, a.fulltext , a.images, a.attribs, a.title, a.state
FROM mos_content AS a
INNER JOIN mos_categories AS cc ON cc.id = a.catid
INNER JOIN mos_sections AS s ON s.id = a.sectionid
WHERE a.state = 1
AND a.access <= 0 AND cc.access <= 0 AND s.access <= 0
AND (a.publish_up = '%s' OR a.publish_up <= '%s' )
AND (a.publish_down = '%s' OR a.publish_down >= '%s' )
AND a.catid = 50
AND cc.published = 1
AND s.published = 1
ORDER BY a.ordering

MySQL Statistics
MySQL 4.1.x
Connections per second:    1.6103
Kilobytes received per second:    3.9431
Kilobytes sent per second:    38.6279
Queries per second:    17.553
Percentage of slow queries:    0.0004
Opened vs. Open tables:
(table_cache)    1.6325 (should be <= 80)
Table cache usage:
(table_cache)    0.0414 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size)    0.0306 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.2267 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    1.0006 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0.081 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer)    0 (should be <= 0.001)
Query cache enabled:
(query_cache_type)    1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit)    0.0221 (should be <= 0.1)
Query cache prune rate:
(query_cache_size)    0 (should be <= 0.05)