Requesting Input on Mysql + Xcache Tuning

[MGBrose]

Server Hardware

Hey everyone so i'm on my shiny new first dedicated server. We literally just moved over to it last week, and I was wondering based on my current configuration if I could get some tips on optimizing my IPboard Xen slice in terms of mysql + xcache. I'm not a pro, but I have a pretty good understanding of linux.

My main interest is in making mysql even quicker than it currently is, our current execution times for IPboard are generally between 0.05 - 0.09 seconds. I'm Also game for xcache config recommendations as well, if anyone has them!

Personal Dedicated Server:
CPU: Intel Xeon E3-1230 sandybridge (8 cores w/HT)
RAM: 8 GB DDR3 ECC Memory [will be upgraded to 12GB this week]
HD: 1000 GB Hard Drive x2 (in mirroring raid)
BW: 1GBPS
Setup: Debian/XEN paravirt
Location: Florida US (wish I was there)

More specific to our WebServer slice:
OS: Centos Barebones on a Xen top priority Slice
CPU: Access to all 8 cores
RAM: 2.5GB
HD: 50GB

Setup
MySQL: 5.1
Php: 5.3.8
Apache: 2.2
Apache Handler: mod_php (didn't have good luck with fcgi)
Opcode Cacheing: Xcache
CDN: Cloudflare Pro

Whats On it:
IPBoard 3.2.3: Mazda626.net We've been on IPboard since the beginning. I think we've been live since about 2002, so this year will be our 10 year anniversary!
IPBoard.3.2.3 dev site for testing new addons tweaks and changes before we put them live.
Media Wiki (to be added)
Wordpress (a few small blogs)
I own/have full control of all the sites on it, no clients on here.

Current Memory Usage:

Memory Used: 21.73% (567,288 of 2,611,036) [Lots of free memory to utilize more efficiently]
Swap Used: 1.51% (39,556 of 2,621,432) [Don't know why any is being used]


Current MySql Config:

[mysqld]

set-variable = max_connections=250 [will probably drop to 150]

log-slow-queries

safe-show-database

query_cache_size = 32M

query_cache_type=1

query_cache_limit = 1M

tmp_table_size = 36M

max_heap_table_size = 36M

thread_cache_size = 8

table_cache = 512

innodb_buffer_pool_size = 6M

join_buffer_size=4M

Mysql Tuner/Snapshot Output: Snapshot: Mysql Extended Snapshot: http://pastebin.com/BUjQC53U

>>  MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>

>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

>>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.1.56-log

[OK] Operating on 64-bit architecture-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 432M (Tables: 210)

[--] Data in InnoDB tables: 144K (Tables: 9)

[!!] Total fragmented tables: 40

-------- Performance Metrics -------------------------------------------------

[--] Up for: 20h 3m 16s (1M q [17.013 qps], 102K conn, TX: 6B, RX: 275M)

[--] Reads / Writes: 55% / 45%

[--] Total buffers: 90.0M global + 6.6M per thread (250 max threads)

[OK] Maximum possible memory usage: 1.7G (68% of installed RAM)

[OK] Slow queries: 0% (5/1M)

[OK] Highest usage of available connections: 4% (11/250)

[OK] Key buffer size / total MyISAM indexes: 8.0M/335.9M

[OK] Key buffer hit rate: 98.7% (24M cached / 308K reads)

[OK] Query cache efficiency: 37.4% (228K cached / 610K selects)

[!!] Query cache prunes per day: 15564

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 11K sorts)

[!!] Joins performed without indexes: 439

[OK] Temporary tables created on disk: 15% (1K on disk / 12K total)

[OK] Thread cache hit rate: 99% (16 created / 102K connections)

[OK] Table cache hit rate: 66% (438 open / 659 opened)

[OK] Open file limit used: 51% (662/1K)

[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

[OK] InnoDB data size / buffer pool: 144.0K/12.0M-------- Recommendations -----------------------------------------------------

General recommendations:

	Run OPTIMIZE TABLE to defragment tables for better performance

	MySQL started within last 24 hours - recommendations may be inaccurate

	Adjust your join queries to always utilize indexes

Variables to adjust:

	query_cache_size (> 32M)

	join_buffer_size (> 4.0M, or always use indexes with joins)

Current Xcache Config:

extension = "xcache.so"

xcache.cacher="On"

xcache.coredump_directory=""

xcache.count="8"

xcache.coveragedump_directory="/tmp/pcov/"

xcache.coverager="Off"

xcache.gc_interval="3200"

xcache.mmap_path="/dev/zero"

xcache.optimizer="Off"

xcache.readonly_protection="Off"

xcache.size="384M"

xcache.slots="8K"

xcache.test="Off"

xcache.ttl="3200"

xcache.var_count="8"

xcache.var_gc_interval="300"

xcache.var_maxttl="3200"

xcache.var_size="32M"

xcache.var_slots="8K"

xcache.var_ttl="300"

Thanks in advance everyone, we really appreciate it!

[Grumpy]

I find this line
[!!] Joins performed without indexes: 439
more concerning than anything. Do you have custom tables or some addon that's created additional tables? IPB alone should yield nearly zero of these (given your traffic). I'm also assuming these occurrences are somewhat rarer.

Re: Memory usage. I don't think your current indicated traffic really necessitates anymore than what you're using now. That's just all there is to it.
Re: SWAP usage. Either A: your OS just allocated some for some unknown reason during boot and still has it. swap isn't cleared after usage, only cleared when it runs out of space or reboots. or B: you're using 0 and it's an inaccurate reading. swap value is often a guessed or estimated value based on subtraction.

"IPboard are generally between 0.05 - 0.09 seconds"
That's like godly rates... I don't think you really need to do anything.

[MGBrose]

Thats good to hear about our times are pretty fast :D, Also thanks for the input.

The joins I believe are coming from 1 of 2 addons. 1 We use a garage system from devfuse, I think its possibly that or a custom addon we had developed that backsup externally hosted images locally and replaces the external image links with local ones. So I suspect its one of those two, most likely our custom one.

Without any tweaks done here is the mysql tuner result of 3 days uptime now.

Extended mysql status: http://pastebin.com/EYeMnwxH

MysqlTuner output:

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.1.56-log

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 438M (Tables: 210)

[--] Data in InnoDB tables: 144K (Tables: 9)

[!!] Total fragmented tables: 42-------- Performance Metrics -------------------------------------------------

[--] Up for: 2d 21h 15m 1s (4M q [17.557 qps], 357K conn, TX: 22B, RX: 969M)

[--] Reads / Writes: 55% / 45%

[--] Total buffers: 90.0M global + 6.6M per thread (250 max threads)

[OK] Maximum possible memory usage: 1.7G (68% of installed RAM)

[OK] Slow queries: 0% (8/4M)

[OK] Highest usage of available connections: 6% (15/250)

[OK] Key buffer size / total MyISAM indexes: 8.0M/337.8M

[OK] Key buffer hit rate: 98.7% (76M cached / 1M reads)

[OK] Query cache efficiency: 38.6% (856K cached / 2M selects)

[!!] Query cache prunes per day: 25026

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 31K sorts)

[!!] Joins performed without indexes: 1381

[OK] Temporary tables created on disk: 13% (4K on disk / 29K total)

[OK] Thread cache hit rate: 99% (30 created / 357K connections)

[OK] Table cache hit rate: 55% (445 open / 803 opened)

[OK] Open file limit used: 52% (669/1K)

[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)

[OK] InnoDB data size / buffer pool: 144.0K/12.0M

-------- Recommendations -----------------------------------------------------

General recommendations:

	Run OPTIMIZE TABLE to defragment tables for better performance

	Adjust your join queries to always utilize indexes

Variables to adjust:

	query_cache_size (> 32M)

	join_buffer_size (> 4.0M, or always use indexes with joins)

• Based on these results I think I'm going to bump querycache to 64M, I mean why not we have the room for sure.
• I'm gunna drop max connections to 200 (max we reguarly see is 30-40ish
• Ill also bump the joinbuffer to 6M or so.
  

Is my keybuffer low by the way? I see a lot of recommended configs around here running 200-300M +?

[Grumpy]

Be sure to add indexes to the columns that your custom addon uses. That's about the only significant suggestion I can give.

You can always fine tune things, to what is now, but as you get more traffic, then you can fine tune again. :P

[altenerg]

My IPB built-in task to optimize mysql tables never works. :/ You should setup a cron to optimize tables nightly. Use command line

crontab  -e[/CODE]

Here's mine:

2 9 * * * mysqlcheck -o -u yourusername -pyourpassword yourdatabase_name

Key buffer size / total MyISAM indexes: 8.0M/337.8M

How are you guys missing that?! Up your key buffer to 512M. Your entire database tables (tablesTables) should always be able to fit in key buffer. Fix that and then run for 24 to 48 hours and see if it helps solve other issues.

DatainMyISAM:432M(:210

Also...