When I head the word 'Percona Server', the first thing that jumps at me is : "Does the DB Server have multiple CPUs?" These days, that is taken for granted. What is unfortunate is that most people forget to configure MySQL or Percona Server to summon those multiple CPUs.
MySQL 5.1 with InnoDB 5.1 Plugin, MySQL 5.5 and Percona Server have options to increase threading. Here are those options along with other InnoDB features you may wish to tune:
innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. Best round number to set for this is (2 X Number of CPUs) + Number of Disks. As I learned firsthand from the Percona NYC Conference back in May 2011, you should set this to 0 in order to alert InnoDB Storage Engine to find the best number of threads for the environment it is running in.
innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.
innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.
innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).
innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64.
innodb_replication_delay imposes thread delay on a slave is innodb_thread_concurrency is reached.
innodb_read_ahead_threshold allows linear readings of the set number of extents (64 pages [page = 16K]) before switching to asynchronous reading.
Time would escape me if I named more options. You can read about them in MySQL's Documentation.
Switching gears to RAID: Please stick with your RAID10. It is easier to maintain other redundant setups, especially RAID5.. Also, make sure you have the latest firmware for your RAID card. Otherwise, RAID firmware may conflict with the OS kernel resulting either in disk crashes or lower overall throughput.
As for Is ext4 ok or should I create a separate xfs volume for the data?, keep in mind that ext3 places a max size limit of 2TB per file. If you are not dealing with TBs of data, don't sweat using ext3 or ext4.
A separate data volume would increase disk performance since mysqld will not compete with the OS.
You should decide what exactly you need, before deciding those 3 options.
My suggestion is as following:
Install memcache and the necesary php module to use with memcache. Once you install memcache, you will have an IP and PORT to connect to, for SET / GET operations
Decide where exactly in your Wordpress php code you need to inject the code that displays the "featured" stuff
Add a php code similar to:
- CONNECT to memcache
- Check for the existance of a KEY/VALUE pair, named "FEATURED STUFF"
- If there is, GET that pair, and display the pair's value
- If not, run the SELECT query, FETCH the results, build a string that will contain the HTML body of "FEATURED STUFF", PUT the string in memcache as a KEY/VALUE pair.
The code above should not be cached with WP Super Cache. Let memcache do the cacheing for you.
If you need help with the actual implementation, let me know.
Sample PHP Code:
$memcache = new Memcache;
$memcache->connect('localhost', 11211) or die ("Could not connect");
if ($gotten = $memcache->get('featured_stuff')) {
echo 'CACHE HIT'</br>';
echo $gotten;
} else {
# Never mind - fetch it and store for next time!
$gotten = dbfetch($query, ...);
$memcache->set($featured_stuff,$gotten);
echo $gotten;
}
Best Answer
For a Wordpress blog it should be fine to set
query_cache_type = 1. See, the major problems with the query cache are:It invalidates very easily (any update on some table invalidates all queries related to said table)
It has a single mutex on which any incoming or outgoing query must go through.
The query cache was fine in the days where machines had one core, maybe two; it does not fare well with multiple cores and high concurrency, and it does not fare well with write intensive applications.
Your Wordpress blog is not likely to be write intensive: your writes are new posts, updates, comments... How many of these? You may not even counter them on a per-minute basis...
It would actually not be read intensive either. Hundreds of reads per day Thousands? That's nothing.
With regard plugins, that greatly depends on the plugin and on your deployment. Wordpress is written in PHP, and PHP might use such cache mechanisms as file system, memcached or APC. It should be typically "better" caching for the specific purpose it is oriented. That is, it will invalidate cached data based on real changes to relevant data -- not for irrelevant operations on some table. The programmers of the plugin would need to decide which pieces of data are important to cache, which are not, how invalidation works.
Again, in the scale of a blog, this is no big deal, and should not make a significant difference (of course there could always be some crazy plugin).
Bottom line: for your needs, anything goes and it does not matter much.