0

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

The load average peaks above 32.

The specs of the test machine are:

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

The MySQL config can be read over here. Does anyone have advise on how I can adjust then to improve performance? Thanks.

Coops
  • 6,055
  • 1
  • 34
  • 54

1 Answers1

0
  • 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.

karmawhore
  • 3,865
  • 18
  • 9