[masterross]

Originally Posted by liwo

That means that there are some indexes missing for a table that is used in a join. I had a quick look at the tables and think I found the missing indexes.

So please do a backup of your database and run the following MySQL querys (adding a table prefix as necessary):

ALTER TABLE `award_user` ADD INDEX `award_id` ( `award_id` )

ALTER TABLE `award_user` ADD INDEX `userid` ( `userid` )

These two statements create the probably missing indexes.

OPTIMIZE TABLE `award_user`

This statement actually fills the newly created indexes with the necessary data.

After doing this, check if the problem persists. If I guessed right it doesn't.

Thanks bro!
Now it's much better!
But some indexes are still missing:

[--] Up for: 6h 1m 4s (2M q [112.642 qps], 106K conn, TX: 3B, RX: 552M)
[--] Reads / Writes: 82% / 18%
[--] Total buffers: 602.0M global + 5.7M per thread (250 max threads)
[OK] Maximum possible memory usage: 2.0G (49% of installed RAM)
[OK] Slow queries: 0% (0/2M)
[OK] Highest usage of available connections: 9% (24/250)
[OK] Key buffer size / total MyISAM indexes: 64.0M/175.9M
[OK] Key buffer hit rate: 100.0% (118M cached / 46K reads)
[OK] Query cache efficiency: 66.6% (1M cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (103 temp sorts / 140K sorts)
[!!] Joins performed without indexes: 132
[OK] Temporary tables created on disk: 7% (3K on disk / 49K total)
[OK] Thread cache hit rate: 99% (24 created / 106K connections)
[OK] Table cache hit rate: 84% (347 open / 413 opened)
[OK] Open file limit used: 5% (539/10K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)