Performance issues on certain queries (and some semi-related questions)

[RobertMfromLI]

Hello all,


I am having some interesting performance issues only with certain queries. First, here's the system details:


RUNNING:

   
• Software:
  - MySQL v5.3.4
  - PHP v5.2.14
  - Apache v2.2.17
  
  - SMF v2.0rc4
  - ModernDark64rc4 Theme (ie: version for SMF v2.0rc4)
  - Ad Management for v2.0rc4
  - No real customizations

   
• Server running with:
  - 4GB ECC Registered RAM (8GB split and mirrored)
  - Dual 2.4GHz Intel Pentium 4 Xeon DP CPUs
  - 147GB HDD space via two 147GB Ultra320 15K SCSI drives (ie: mirrored setup)
  - RAID support via IBM ServeRAID 6m with 128MB cache
  
• Forum Statistics:
  - Approx 10,000 members
  - Approx 114,000 posts
  - Approx 7,800 topics
  - Approx half a million pageviews a month
  - Avg page creation times range between .1 seconds to .8 seconds (except as noted below)

.





The interesting performance issues and problems:

   
• Virtually everything runs blazingly fast except:
  - Viewing "All Unread" if there are none
  - Marking all posts as read (this one I am sure is related to the one above, see notes):
    - If marking all posts as read returns to forum home screen, it processes blazingly fast
    - If marking all... returns to the "View unread topics" page, it takes 2-5 seconds
  
• The forums have been generating a lot of table errors of late:
  - Database Error: Incorrect key file for table '.\iftcomma_stnvforum\smf_messages.MYI'; try to repair it
  
  - Or problems with smf_sessions
  
  - Everything has been taken offline, and ALL of the tables have been repaired using extended repair, and then optimized.
     - No change in this. This, btw, was something I had been noticing on our previous host (before we started hosting the forums ourselves), on a little less than a daily basis (20-25 times a month).
  .
• MSNBot (an unlabeled or oddly labeled (via useragent) version) seems to create a LOT of errors (such as):
  - (WEB ADDRESS)/index.php?action=dlattach;attach=747;type=avatar
     8: Undefined index: session_var
     File: (FORUM HOME)/Sources/ManageSearchEngines.php
     Line: 563
  
  - (WEB ADDRESS)/index.php?action=dlattach;topic=9938.0;attach=4773
     2: array_reverse() [<a href='function.array-reverse'>function.array-reverse</a>]: The argument should be an array
     File: (FORUM HOME)/Sources/Subs.php
     Line: 3491
  
  - And so on... I've currently banned  5 Class B address ranges that MSNBot uses, largely due to (besides the above, which is commonplace and ONLY for MSNBot) them ignoring (yet again) the robots.txt file, and repeatedly downloading the same file ten of times or hundreds of times a day (depending on their mood, apparently), sometimes with as many as 200 bots on the server doing requests simultaneously. Some trivial single-digit (2%-4%?) amount of our traffic comes from them (of the whole 100% search engine referred traffic).
  
  Q: Is there some sort of security issue buried someplace in there that Microsoft is trying to exploit (knowingly or through stupidity)? Others have already noticed MSNBot sending functions as part of requests to Wordpress - functions it should not know about because they should never be exposed to the outside world.

And finally, any tips on tweaking MySQL for the type of load we have, would be greatly appreciated. I've found a lot of conflicting information out there. Figure, we've got 750MB to 1GB (after all caches in place for stuff like Apache, disk, and other programs).

Best,
Robert

[Joshua Dickerson]

Have you looked at the sticky about how to optimize SMF? Start there. Your hardware is very good and should meet your needs with ease. Consider using nginx or similar web server as you'll notice a considerable stability in resource usage.

Your second one appears to be an issue relating to themes. Possibly due to your custom theme but I'm not sure of that.

I've notified the devs to look in to this.

[RobertMfromLI]

I've looked over them and made changes for the max key seeks value, played with the different cache values and so on (combination of what's on this site and elsewhere).

The key corruption issue seems to be the biggie I can't resolve. Id been hoping upgrading MySQL a few months ago would fix that (there were some bugs in the earlier v5 releases that could cause such) but alas, no such luck.

Moving attachments and avatars next.

Certain Linux stuff I cannot (yet) run on this box. The underlying OS is OS/2 Warp Server for eBusiness (2002 release with updates from 2008 & 2009). The AMP Stack is comprised entirely of Linux ports (including the "support modules" such as GBM, etc).

I'll re-dig thru the tips to see if I missed something, and any pointers would also be appreciated.

Best,
Rob

[RobertMfromLI]

My apologies for not posting that I'd already read that (and elsewhere) and already did many of the changes. I know you guys get a lot of people who don't bother to read that stuff.

Rundown of what I've done (or why I havent done certain ones):




1: Move your uploaded avatars directory.
- DONE (but just today)


2: Disable hostname lookups.
- DONE (ages ago, no noticeable difference)


3: Enable (or disable) gzip compression.
- Switched from enabled to disabled about 4 months ago.
Note: If your webserver compresses the type of data you use to serve your webpages... (YES)

4: Tune topics, members, and posts per page.
- Tuned about 4 months ago. Increased performance somewhat (pageloads at 75% time on medium size topics, 40-50% time on large size topics (ie: size topics=#posts in topic)


5: If you are going to enable search, and are not or cannot use Sphinx, use a Large Custom Index.
- DONE April 2010


NOTE: not using UTF-8. I dont see how much of a difference that may make performance wise, but there is UTF-8 character data in the posts, so I suspect I need to fix this. For some reason, our previous host (BlueHost/HostMonster) rebuilt the database without it set as UTF-8 charset.


6: Don't use post moderation on an active forum.
- We dont





7: Disable the recent posts feature on the board index.
- Ages ago

8: Run the mark read for inactive users script periodically. The log_topics, log_boards, and log_mark_read tables
- NOT done until RIGHT NOW (still processing)




9: Do you really need that many boards?
N/A - we don't have a ton of boards, and we need the ones we have.


10: Do you really need the calendar?
- Sadly

11: Ben has a post about disabling features according to server load here.
- Not quite willing to try the v1.1 recommendations on v2.0rc4 due to some changes I've noticed in the database since earlier rc's.



12a: Consider using database-driven sessions
- Since day three (Apr 2010)



12b: and offloading all attachments to Mediawiki.
- Last time I checked, I couldn't find a recommended version for v2.0rc4 that was reported as stable.
- We've had very few attachments until just recently (last couple months) and the issues we've had predate that.



Addition #1 (March 5th): Prune zero-post members
- CANT: some of our lurkers are our contributors or crew.

Addition #2 (May 3rd): Perform only necessary tasks, and spread them out (SMF 2.0 only)
- Done 4 months ago


Addition #3 (July 1st): Cap the length of your threads
- Not really applicable to the type of traffic or prolonged interest we generate. With #4 tweaked, even pages from the largest thread we have (which isn't that large at 2600 posts) are now instantaneous.


Addition #4 (November 1st): Don't go crazy on on-line time
- We haven't



Addition #5 (November 1st): If you really want, disable tracking daily page views
- Well, we kinda do need this. At least till we do some custom reports via Google Analytics (but it's currently having problems with our subdomain and keeps claiming all traffic is from www.domain instead of forums.domain (as well as other issues in recording page info)).


So, that pretty much covers it. As you can see, I already went through all of that.     

[Joshua Dickerson]

That is only the basic improvements. Moving to InnoDB and Memory engines is a significant one. You already run current LAMP versions. The text to varchar is another HUGE one although I think those have all been fixed in 2.0 and you probably won't be affected by it. I can only assume you're using caching in some way and Sphinx.

A status.php would help a lot.

[RobertMfromLI]

That is only the basic improvements. Moving to InnoDB and Memory engines is a significant one. You already run current LAMP versions. The text to varchar is another HUGE one although I think those have all been fixed in 2.0 and you probably won't be affected by it. I can only assume you're using caching in some way and Sphinx.

A status.php would help a lot.

We did other tweaks, from links to other pages found here and via a Google search. I'll post a status.php report as soon as the log unread stuff finishes.


Thanks very much,
Robert

[RobertMfromLI]

Status.php results attached as an html file (page save).


Some of the MySQL settings (indicated in the file) have been changed to default or different settings than we previously had.


Best,
Robert


NOTE: Of course, some of the performance values are skewed because I am clearing unread messages still. (I didnt end up waiting)

[Joshua Dickerson]

It would be easier and better if you just provide the URL

[RobertMfromLI]

It would be easier and better if you just provide the URL

http://forums.startreknewvoyages.com/stts.php

[Ensiferous]

Enable the slow query log for MySQL and see what you're actually dealing with. Making changes in the dark is fun and all, but it's rarely useful.

[RobertMfromLI]

Enable the slow query log for MySQL and see what you're actually dealing with. Making changes in the dark is fun and all, but it's rarely useful.

Percentage of slow queries: 0.0001

No seeming problems there. I've tweaked a bunch of the settings, not so much via making changes in the dark, but by updating the configuration files based off (a) status.php and (b) taking advantage of the better hardware that the forums are on now.

I've also done some more digging, and I suspect the crashes may be indeed related to msnbot after all. As an example, I've found (in the error logs) 783 requests by 5 bots in only TWO seconds. There's a lot more like that in the logs (of which, in 35.5 hours, 3330 errors were generated by msnbot/bingbot).

Quick stats on the few I have looked at so far:

331 in a two second period
782 in a two second period
1181 in a four second period

It's like a bunch of attack spurts all at once. They get confused when they get something that they don't like (probably an SMF forum error due to a broken bit of PHP in the theme I am using), and then go crazy.

Anyway, I KNOW I did not have the server daemons tuned to handle 150-390 requests a second. I figured no more than 100 when it was originally tuned. Something that I have since rectified (kinda a moot point since msnbot is blocked in the firewall via five rules for five Class B ranges).

I think that leaves me watching over things for a while, and seeing what I can do to fix the forum theme.

Best,
Robert

[RobertMfromLI]

Currently the stats that status.php is showing are:




Connections per second:    0.2052

Kilobytes received per second:    1.0382

Kilobytes sent per second:    9.8871

Queries per second:    4.5492

Percentage of slow queries:    0

Opened vs. Open tables:
(table_cache)    15.1556 (should be <= 80)

Key buffer read hit rate:
(key_buffer_size)    0.0305 (should be <= 0.01)

Key buffer write hit rate:
(key_buffer_size)    0.2104 (should be <= 0.5)

Thread cache hit rate:
(thread_cache_size)    138.8824 (should be >= 30 )

Thread cache usage:
(thread_cache_size)    [/color]0.1667 (should be >= 0.7 and <= 0.9)

Temporary table disk usage:
(tmp_table_size)    0.4498 (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.4086 (should be <= 0.5)

Query cache prune rate:
(query_cache_size)    0 (should be <= 0.05)



The key buffer is quite large - it simply doesn't seem to be efficiently using it. In that, I am not sure what else I can do.


Thread cache usage is the other interesting one. I suspect that number is low simply because not many threads are being used concurrently.

[RobertMfromLI]

For anyone interest, with 2GB assigned to OS/2's High Memory Arena (of 4GB, with the rest assigned to shared memory and system memory), below is my my.cnf with the irrelevant statements (such as directories) removed. On *nix, your memory pool will be set up differently obviously (OS/2 handles memory a bit differently), so with a system with 4GB of RAM, you will undoubtedly have a different amount of memory you can allocate to AMP.

Code Select Expand


[client]
compress
user=root
[mysqld]
skip-innodb #deactivate innodb
read_buffer_size=4M
read_rnd_buffer_size=4M
myisam_sort_buffer_size=128M
max_heap_table_size=160MB
tmp_table_size=384M
max_seeks_for_key=100
key_cache_block_size=2048
key_buffer_size=512M
table_cache=288
sort_buffer_size=8M
thread_cache_size=96
thread_concurrency=4
skip_name_resolve
query_cache_size=128M
query_cache_limit=8M
myisam_recover=BACKUP,FORCE



If anyone has any further suggestions, they'd be appreciated. So far, MySQL is only using about 300MB of RAM at peak periods, which is currently a lot less than it can use (not much else running on the system other than AMP).


The biggest slowdowns I have right now are as follows:

   
• Marking all messages read
  I don't see any way of speeding this up from looking at the code that gets executed (other than clearing all of those tables or running the mark read script that marks messages read for absent forum members - which I am doing, but will probably take another week or two to complete).
• Doing a forum search (this is the one I am interested in improving if possible)
  I switched from the standard fulltext index to a large one. Somehow this slowed down searches tremendously. The new large index is roughly 132MB (about twice the size of the original).

Any suggestions would be greatly appreciated.

[Joshua Dickerson]

What user variable cache are you using and what level do you have caching at for SMF?

[RobertMfromLI]

What user variable cache are you using and what level do you have caching at for SMF?

I have no additional caching (specific to SMF or MySQL) enabled, and no caching mods installed. Nor did I previously. I've increased the cache values considerably since the time I was running with a standard fulltext index (at which time, the exact same search queries would take roughly 2-3 seconds, as opposed to 15). I've increased max_heap_table_size to 256MB (last night) which improved search performance a little (roughly 8-9 seconds for the same query). I checked for real memory exhaustion (ie: "Am I forcing swapping?") and 70% of the memory pool is untouched by AMP. And of what is being "allocated" for MySQL, much seems unused, so I am suspecting those cache values are fine.


I would consider enabling one of the caching options in SMF, but would really like a recommendation as to which one is best. If they are reliant on a separate caching module, I'd have to compile/port it (hence the preference for a recommendation before I simply start porting and testing the various packages).


By "user variable caching" I am not 100% sure what you mean.






Best,
Robert

[RobertMfromLI]

The biggest slowdowns I have right now are as follows:

   
• Doing a forum search (this is the one I am interested in improving if possible)
  
  I switched from the standard fulltext index to a large one. Somehow this slowed down searches tremendously. The new large index is roughly 132MB (about twice the size of the original).

Any suggestions would be greatly appreciated.

Just a clarification on what "slow" is. Whatever setup BlueHost had (the one we couldn't change) would cause searches to take upwards of 45 seconds (on a dedicated box)... straight up to and over the timeout directives at times.


When we took over hosting, and implemented a proper fulltext index and did our initial MySQL (etc) configuration, we cut that down to an acceptable 2-3 seconds. After switching to a large index, searches WERE taking about 15 seconds. We've done some further tweaking to take into account the larger index and reduced that to 7-9 seconds.


While that isn't horrendous, it's still roughly three times the length of time it used to take with a standard fulltext index.


Best,
Rob

[RobertMfromLI]

ZendCache:


Here's what phpinfo reports is our current Zend configuration:

Zend Extension[/color]220060519

[/color]Debug Build[/color]no

[/color]Thread Safety[/color]enabled

[/color]Zend Memory Manager[/color]enabled

[/size]I believe I have to get sqlite fixed (for SMP on Warp Server) in order to enable ZendCache?[/size][/font]

[Joshua Dickerson]

ZendCache = suck
APC or memcached = awesome

Use Sphinx for searching.

[Something like that]

That is only the basic improvements. Moving to InnoDB and Memory engines is a significant one.

This.

[RobertMfromLI]

That is only the basic improvements. Moving to InnoDB and Memory engines is a significant one.

This.

...is sadly not (yet) applicable for solving my remaining issues: all of which center around smf_messages.


- Table corruption (smf_messages)
- Slow search (smf_messages)


Still waiting on an answer on memchached. If that goes ok, then if I can manage to switch to Sphinx, THEN, finally I can move smf_messages to memcached.