mysql config wordpress high loads

My query

./mysqlslap --user=root  --concurrency=50 --iterations=1 --pass=toor -vv --create-schema=db --query="SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)  WHERE 1=1  AND wp_term_taxonomy.taxonomy = 'category'  AND wp_term_taxonomy.term_id IN ('1')  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 35, 6"

Some results

   Average number of seconds to run all queries: 68.904 seconds
    Minimum number of seconds to run all queries: 68.904 seconds
    Maximum number of seconds to run all queries: 68.904 seconds
    Number of clients running queries: 50
    Average number of queries per client: 1

Loads 32+

My machine

• 4GB
• E6550 @ 2.33GHz (2 core)
• Single sata disk
• Debian Lenny + Apache + PHP + MySQL

my sql config can be read at pastebin.ca/1934946 ; how can I adjust this? Thanks

From serverfault Nick

• • Install w3tc or wp-super-cache
  • cut down on the number of posts/tags (remember that wordpress stores revisions of posts in the wp_posts table, so, if someone likes to leave themselves logged in and autosave runs every 5 minutes, you can get quite a few excess revisions in there)
  • replace wordpress
  • get a faster disk

  While I disagree with some of the tunables in the config based on gut feelings, http://blog.mysqltuner.com/ contains a script that you can run that gives you input on your config settings. You'll want to have mysql running for 48+ hours for it to give the best recommendations.

  • table_definition_cache
  • thread_concurrency (On a quadcore, I usually run this at 6)
  • join_buffer_size (you have it listed as join_buffer which may be incorrect, look at show status/show variables to make sure your config values are taking effect as you expect)

  If you aren't running email on the same partition as your database, consider mounting the filesystem with noatime,nodiratime. Check other tunable settings on your filesystem.

  From karmawhore