I'm not a DB expert. I remember having a chat with "The Learning Newbie" about the different settings one could play around with in the my.cnf which is basically the httpd.conf of MySQL. He told me he was more of Windows DB expert, and has never really had the chance to play around with MySQL.

A few days ago, MySQL really started acting up - the top command showed the mysql daemon (mysqld) in the 90-113% (constantly). This was causing the server load to increase at an average of 6-10%. Then I looked at the my.cnf file, and noticed it had only two small entries. I decided to find something that best suited my server, and quickly replaced it. After the update (and a mysql restart) CPU usage has since only had an average of 2-4%. However, at times, it still hits 6%, and that seems to be a very unstable point for my server.

Here is what I have in the mysql.cnf. Maybe someone could take a look at it - and suggest some possible ways of improving it. Also, I'd like to know why. I'm not one for doing things blindly.

[client]
port=3306
socket=/var/lib/mysql/mysql.sock

[mysqld]
port=3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=14400
connect_timeout=10
thread_cache_size=128
key_buffer=150M
join_buffer=1M
max_allowed_packet=16M
table_cache=1500
record_buffer=1M
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
max_connect_errors=10
thread_concurrency=4
myisam_sort_buffer_size=64M
log-bin
server-id=1

[mysql.server]
user=mysql

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/vps-mi-01.bldhosting.net.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

I'm also in the process of ordering a new Core 2 Quad 2.66Ghz CPU along with 2GB RAM (to which everything will be migrated). Maybe you can suggest improvements based on that spec.

I noticed you aren't logging slow mysql queries. It's an important step in finding out what is it that's causing mysql to spike up CPU. Add the following entries in your my.cnf and restart mysql

set-variable=long_query_time=1

log-slow-queries=/var/log/mysql/log-slow-queries.log

Pay close attention to long-running queries. Run explain on those and / or find the most offending SQLs.

Regards

I've done the following - however I think the setting is log_slow_queries=/var/log/mysql/log-slow-queries.log

These naming rules can be a bit confusing, can't they? I had to look this up to confirm. The following syntax should work:

log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 2

One thing that catches people out (well it caught me out). Is that you need to have already created the log file (you can do this with the touch command on *nix machines) and also set the permissions so the db users can write to the file.
Otherwise the slow query will default to [systemname]-slow.log in the data dictionary (you can find the location of your data directory by calling up mysql and running the following commandSHOW VARIABLES LIKE 'datadir'; )

I've done that. Now, what am I to gather from this file? It now has over 78 000 lines.

Look for queries that take a lot of time/repeatedly occur. Then I'm afraid it's a question of acting like a dba (or getting someone who is) to optimize the queries (often by better use of indexes and/or rewriting subquery statements as joins). If you do a search on youtube for "Jay Pipes Mysql optimization" there's an excellent presentation on doing this. Unfortunately it last nearly a hour.

I've posted this in the Server Administration forum as well. I haven't received much of a response - so I thought I'd try my luck in this forum.

I'm not a DB expert. I remember having a chat with "The Learning Newbie" about the different settings one could play around with in the my.cnf which is basically the httpd.conf of MySQL. He told me he was more of Windows DB expert, and has never really had the chance to play around with MySQL.

A few days ago, MySQL really started acting up - the top command showed the mysql daemon (mysqld) in the 90-113% (constantly). This was causing the server load to increase at an average of 6-10 (6/2 x 100 = 300%!). Then I looked at the my.cnf file, and noticed it had only two small entries. I decided to find something that best suited my server, and quickly replaced it. After the update (and a mysql restart) CPU usage has since only had an average of 2-4. However, at times, it still hits 6, and that seems to be a very unstable point for my server.

Here is what I have in the mysql.cnf. Maybe someone could take a look at it - and suggest some possible ways of improving it. Also, I'd like to know why. I'm not one for doing things blindly.

[client]
port=3306
socket=/var/lib/mysql/mysql.sock

[mysqld]
port=3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=14400
connect_timeout=10
thread_cache_size=128
key_buffer=150M
join_buffer=1M
max_allowed_packet=16M
table_cache=1500
record_buffer=1M
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
max_connect_errors=10
thread_concurrency=4
myisam_sort_buffer_size=64M
log-bin
server-id=1

[mysql.server]
user=mysql

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/vps-mi-01.bldhosting.net.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

I'm also in the process of ordering a new Core 2 Quad 2.66Ghz CPU along with 2GB RAM (to which everything will be migrated). Maybe you can suggest improvements based on that spec.

Also, I've now added logging to slow mysql queries - and now have a very, very large log file.

This site could be useful:
http://hackmysql.com/nontech

Also the following (but it has perhaps too much information if you're new to optimizing sql queries)

http://www.mysqlperformanceblog.com/

I merged the thread from the other forum.

Also, I've now added logging to slow mysql queries - and now have a very, very large log file.

This sounds worrying. You almost instantly have a large slow queries log? There must be a repeated inefficient operation on the database that is hammering performance. Can you look at the tail of the log, and see if there's a query operation that appears to repeat a lot? If so, may be worth printing here.

MySQL Report:

MySQL 5.0.67-community-  uptime 0 17:57:57      Sun Mar 29 19:12:33 2009
 
__ Key _________________________________________________________________
Buffer used   132.89M of 150.00M  %Used:  88.59
  Current     150.00M            %Usage: 100.00
Write hit      40.25%
Read hit       99.41%
 
__ Questions ___________________________________________________________
Total          37.96M   586.9/s
  QC Hits      31.49M   486.9/s  %Total:  82.96
  DMS           5.63M    87.1/s           14.84
  Com_        470.75k     7.3/s            1.24
  COM_QUIT    377.02k     5.8/s            0.99
  -Unknown     12.66k     0.2/s            0.03
Slow 1 s        2.81k     0.0/s            0.01  %DMS:   0.05  Log:  ON
DMS             5.63M    87.1/s           14.84
  SELECT        4.43M    68.6/s           11.68         78.71
  UPDATE        1.10M    17.0/s            2.90         19.57
  INSERT       74.81k     1.2/s            0.20          1.33
  DELETE       22.19k     0.3/s            0.06          0.39
  REPLACE         133     0.0/s            0.00          0.00
Com_          470.75k     7.3/s            1.24
  change_db   400.69k     6.2/s            1.06
  set_option   55.00k     0.9/s            0.14
  admin_comma   4.12k     0.1/s            0.01
 
__ SELECT and Sort _____________________________________________________
Scan            4.12M    63.8/s %SELECT:  92.98
Range          15.81k     0.2/s            0.36
Full join         975     0.0/s            0.02
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan      52.25k     0.8/s
Sort range     15.88k     0.2/s
Sort mrg pass   1.19k     0.0/s
 
__ Query Cache _________________________________________________________
Memory usage   19.11M of  32.00M  %Used:  59.70
Block Fragmnt  16.62%
Hits           31.49M   486.9/s
Inserts         3.99M    61.7/s
Insrt:Prune  898.40:1    61.6/s
Hit:Insert     7.89:1
 
__ Table Locks _________________________________________________________
Waited         74.02k     1.1/s  %Total:   1.30
Immediate       5.64M    87.2/s
 
__ Tables ______________________________________________________________
Open              939 of 1500    %Cache:  62.60
Opened            966     0.0/s
 
__ Connections _________________________________________________________
Max used           22 of  500      %Max:   4.40
Total         377.02k     5.8/s
 
__ Created Temp ________________________________________________________
Disk table     27.02k     0.4/s
Table          36.34k     0.6/s    Size:  32.0M
File            2.37k     0.0/s
 
__ Threads _____________________________________________________________
Running             3 of    6
Cached             16 of  128      %Hit:  99.99
Created            22     0.0/s
Slow                0       0/s
 
__ Aborted _____________________________________________________________
Clients             0       0/s
Connects        7.50k     0.1/s
 
__ Bytes _______________________________________________________________
Sent            2.44G   37.7k/s
Received        1.90G   29.4k/s
 
__ InnoDB Buffer Pool __________________________________________________
Usage               0 of       0  %Used:   0.00
Read hit        0.00%
Pages
  Free              0            %Total:   0.00
  Data              0                      0.00 %Drty:   0.00
  Misc              0                      0.00
  Latched           0                      0.00
Reads               0       0/s
  From file         0       0/s            0.00
  Ahead Rnd         0       0/s
  Ahead Sql         0       0/s
Writes              0       0/s
Flushes             0       0/s
Wait Free           0       0/s
 
__ InnoDB Lock _________________________________________________________
Waits               0       0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms
 
__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads             0       0/s
  Writes            0       0/s
  fsync             0       0/s
  Pending
    Reads           0
    Writes          0
    fsync           0
 
Pages
  Created           0       0/s
  Read              0       0/s
  Written           0       0/s
 
Rows
  Deleted           0       0/s
  Inserted          0       0/s
  Read              0       0/s
  Updated           0       0/s

Top 10 Slow Queries:

Report for slow logs: /var/log/mysql/log-slow-queries.log
10.05k queries total, 1.37k unique
Sorted by 't_sum'
Grand Totals: Time 87.39k s, Lock 15.69k s, Rows sent 1.84M, Rows Examined 8.75G


______________________________________________________________________ 001 ___
Count         : 2.57k  (25.54%)
Time          : 6424 s total, 2.502532 s avg, 2 s to 70 s max  (7.35%)
  95% of Time : 5565 s total, 2.282609 s avg, 2 s to 4 s max
Lock Time (s) : 73 s total, 28.438 ms avg, 0 to 62 s max  (0.47%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 608 avg, 0 to 61.54k max  (84.73%)
Rows examined : 183.79k avg, 1 to 187.80k max  (5.39%)
Database      : watchtvo_forum
Users         : 
    watchtvo_watchtv@localhost  : 100.00% (2567) of query, 90.28% (9073) of all users

Query abstract:
SELECT t.forum_id, t.topic_id, p.post_time FROM phpbb_topics t, phpbb_posts p WHERE p.post_id = t.topic_last_post_id AND p.post_time > N AND t.topic_moved_id = N;

Query sample:
SELECT t.forum_id, t.topic_id, p.post_time 
            FROM phpbb_topics t, phpbb_posts p 
            WHERE p.post_id = t.topic_last_post_id 
                AND p.post_time > 1238123416 
                AND t.topic_moved_id = 0;

______________________________________________________________________ 002 ___
Count         : 54  (0.54%)
Time          : 5689 s total, 105.351852 s avg, 2 s to 279 s max  (6.51%)
  95% of Time : 4862 s total, 95.333333 s avg, 2 s to 273 s max
Lock Time (s) : 4191 s total, 77.611111 s avg, 0 to 220 s max  (26.71%)
  95% of Lock : 3535 s total, 69.313725 s avg, 0 to 217 s max
Rows sent     : 1 avg, 0 to 1 max  (0.00%)
Rows examined : 1 avg, 0 to 2 max  (0.00%)
Database      : ps3sites_ps3top
Users         : 
    ps3sites_ps3user@localhost  : 100.00% (54) of query, 1.25% (126) of all users

Query abstract:
SELECT ip_address, unq_pv FROM ats_ip_log WHERE ip_address = 'S' AND username = 'S';

Query sample:
SELECT ip_address, unq_pv FROM ats_ip_log WHERE ip_address = '88.250.234.70' AND username = 'faske14';

______________________________________________________________________ 003 ___
Count         : 570  (5.67%)
Time          : 4182 s total, 7.336842 s avg, 2 s to 389 s max  (4.79%)
  95% of Time : 2831 s total, 5.232902 s avg, 2 s to 14 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 5 avg, 0 to 5 max  (0.15%)
Rows examined : 33.09k avg, 0 to 117.03k max  (0.22%)
Database      : sofunnyj_sfjdb1
Users         : 
    sofunnyj_dbadmin@localhost  : 100.00% (570) of query, 6.31% (634) of all users

Query abstract:
SELECT * FROM comments WHERE gameid = N ORDER BY commentid DESC LIMIT N;

Query sample:
SELECT * FROM comments WHERE gameid = 65 ORDER BY commentid DESC limit 5;

______________________________________________________________________ 004 ___
Count         : 24  (0.24%)
Time          : 3261 s total, 135.875 s avg, 2 s to 270 s max  (3.73%)
  95% of Time : 2722 s total, 123.727273 s avg, 2 s to 269 s max
Lock Time (s) : 2712 s total, 113 s avg, 0 to 220 s max  (17.28%)
  95% of Lock : 2275 s total, 103.409091 s avg, 0 to 217 s max
Rows sent     : 1 avg, 0 to 1 max  (0.00%)
Rows examined : 1 avg, 0 to 1 max  (0.00%)
Database      : ps3sites_ps3top
Users         : 
    ps3sites_ps3user@localhost  : 100.00% (24) of query, 1.25% (126) of all users

Query abstract:
SELECT * FROM ats_settings;

Query sample:
SELECT * FROM ats_settings;

______________________________________________________________________ 005 ___
Count         : 12  (0.12%)
Time          : 1614 s total, 134.5 s avg, 2 s to 279 s max  (1.85%)
  95% of Time : 1335 s total, 121.363636 s avg, 2 s to 277 s max
Lock Time (s) : 1244 s total, 103.666667 s avg, 0 to 275 s max  (7.93%)
  95% of Lock : 969 s total, 88.090909 s avg, 0 to 219 s max
Rows sent     : 68 avg, 68 to 68 max  (0.04%)
Rows examined : 68 avg, 68 to 68 max  (0.00%)
Database      : watchtvo_forum
Users         : 
    watchtvo_watchtv@localhost  : 100.00% (12) of query, 90.28% (9073) of all users

Query abstract:
SELECT * FROM phpbb_config;

Query sample:
SELECT *
    FROM phpbb_config;

______________________________________________________________________ 006 ___
Count         : 189  (1.88%)
Time          : 875 s total, 4.62963 s avg, 2 s to 347 s max  (1.00%)
  95% of Time : 421 s total, 2.351955 s avg, 2 s to 5 s max
Lock Time (s) : 2 s total, 10.582 ms avg, 0 to 1 s max  (0.01%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 12 avg, 1 to 15 max  (0.12%)
Rows examined : 729 avg, 4 to 4.02k max  (0.00%)
Database      : watchtvo_forum
Users         : 
    watchtvo_watchtv@localhost  : 100.00% (189) of query, 90.28% (9073) of all users

Query abstract:
SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, p.*, pt.post_text, pt.post_subject, pt.bbcode_uid FROM phpbb_posts p, phpbb_users u, phpbb_posts_text pt WHERE p.topic_id = N AND pt.post_id = p.post_id AND u.user_id = p.poster_id ORDER BY p.post_time ASC LIMIT N, N;

Query sample:
SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, p.*,  pt.post_text, pt.post_subject, pt.bbcode_uid
    FROM phpbb_posts p, phpbb_users u, phpbb_posts_text pt
    WHERE p.topic_id = 15356
        AND pt.post_id = p.post_id
        AND u.user_id = p.poster_id
    ORDER BY p.post_time ASC
    LIMIT 360, 15;

______________________________________________________________________ 007 ___
Count         : 16  (0.16%)
Time          : 678 s total, 42.375 s avg, 2 s to 260 s max  (0.78%)
  95% of Time : 418 s total, 27.866667 s avg, 2 s to 232 s max
Lock Time (s) : 368 s total, 23 s avg, 0 to 215 s max  (2.35%)
  95% of Lock : 153 s total, 10.2 s avg, 0 to 93 s max
Rows sent     : 0 avg, 0 to 0 max  (0.00%)
Rows examined : 0 avg, 0 to 0 max  (0.00%)
Database      : ps3sites_ps3top
Users         : 
    ps3sites_ps3user@localhost  : 100.00% (16) of query, 1.25% (126) of all users

Query abstract:
UPDATE ats_stats SET tot_pv_overall = tot_pv_overall + N, tot_pv_0_daily = tot_pv_0_daily + N, tot_pv_0_weekly = tot_pv_0_weekly + N, tot_pv_0_monthly = tot_pv_0_monthly + N WHERE username = 'S';

Query sample:
UPDATE ats_stats SET tot_pv_overall = tot_pv_overall + 1, tot_pv_0_daily = tot_pv_0_daily + 1, tot_pv_0_weekly = tot_pv_0_weekly + 1, tot_pv_0_monthly = tot_pv_0_monthly + 1 WHERE username = 'baharimsin';

______________________________________________________________________ 008 ___
Count         : 70  (0.70%)
Time          : 565 s total, 8.071429 s avg, 2 s to 188 s max  (0.65%)
  95% of Time : 317 s total, 4.80303 s avg, 2 s to 11 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 0 avg, 0 to 0 max  (0.00%)
Rows examined : 1.08M avg, 184.83k to 1.90M max  (0.86%)
Database      : watchtvo_forum
Users         : 
    watchtvo_watchtv@localhost  : 100.00% (70) of query, 90.28% (9073) of all users

Query abstract:
SELECT m.word_id FROM phpbb_search_wordmatch m, phpbb_search_wordlist w WHERE w.word_text IN (S106) AND m.word_id = w.word_id GROUP BY m.word_id HAVING COUNT(m.word_id) > N;

Query sample:
SELECT m.word_id 
                FROM phpbb_search_wordmatch m, phpbb_search_wordlist w 
                WHERE w.word_text IN ('100', '70s', 'age', 'anal', 'andrews', 'anime', 'archieve', 'brazilian', 'british', 'cartoon', 'clip', 'clips', 'comics', 'completely', 'couple', 'couples', 'croft', 'delete', 'disgusting', 'doll', 'downloads', 'erika', 'fanny', 'female', 'free', 'full', 'future', 'gage', 'galery', 'gay', 'girl', 'girly', 'granny', 'gsy', 'hardcore', 'hate', 'head', 'heels', 'hentai', 'high', 'hot', 'hunter', 'illness', 'indian', 'insertion', 'internet', 'jameson', 'jenna', 'joke', 'kathy', 'kinky', 'kreuk', 'kristin', 'latino', 'length', 'lesbian', 'lesbiane', 'love', 'lymara', 'manga', 'masturbation', 'mature', 'mental', 'min', 'minute', 'mirage', 'miss', 'mmf', 'mobile', 'movie', 'movies', 'online', 'pay', 'per', 'peta', 'pics', 'pictures', 'piss', 'porn', 'post', 'previews', 'quicktme', 'rabbit', 'red', 'rip', 'sex', 'sexy', 'site', 'star', 'stars', 'streaming', 'stroies', 'superhero', 'swinger', 'teen', 'teens', 'tiny', 'uder', 'valley', 'video', 'videos', 'virgin', 'wet', 'white', 'wilson', 'younger')  
                    AND m.word_id = w.word_id 
                GROUP BY m.word_id 
                HAVING COUNT(m.word_id) > 44797;

______________________________________________________________________ 009 ___
Count         : 24  (0.24%)
Time          : 561 s total, 23.375 s avg, 3 s to 183 s max  (0.64%)
  95% of Time : 227 s total, 10.318182 s avg, 3 s to 23 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 0 avg, 0 to 0 max  (0.00%)
Rows examined : 2.02M avg, 1.63M to 2.41M max  (0.55%)
Database      : watchtvo_forum
Users         : 
    watchtvo_watchtv@localhost  : 100.00% (24) of query, 90.28% (9073) of all users

Query abstract:
SELECT m.word_id FROM phpbb_search_wordmatch m, phpbb_search_wordlist w WHERE w.word_text IN (S180) AND m.word_id = w.word_id GROUP BY m.word_id HAVING COUNT(m.word_id) > N;

Query sample:
SELECT m.word_id 
                FROM phpbb_search_wordmatch m, phpbb_search_wordlist w 
                WHERE w.word_text IN ('0kf', '0rg3', '2g1', '2rk8', '2xjd', '4zmc', '564h', '586', 'acnd', 'adln', 'adt', 'adult', 'advice', 'akm5', 'amateur', 'amature', 'anime', '***', 'autoplay', 'b3h', 'b9y', 'babe', 'band', 'bb9', 'bikini', 'books', 'boy', 'brothers', 'bs6', 'building', 'celebrities', 'celebs', 'chat', 'chick', 'clarkson', 'clips', 'clubs', 'cnmf', 'cnye', 'com', 'core', 'cqn', 'crack', 'crtg', 'd4z4', 'da6w', 'daily', 'dating', 'deq', 'direct', 'dj81', 'download', 'edqk', 'episode', 'escorts', 'f5y0', 'f8n', 'fat', 'fgrw', 'free', '****', '****ed', '****ing', 'full', 'furniture', 'fwa', 'galleries', 'gay', 'gbg', 'getting', 'ghq', 'girl', 'girls', 'guide', 'gzhn', 'hard', 'heavenly', 'hilton', 'homemade', 'horny', 'hot', 'i', 'idols', 'j0k', 'jg5c', 'kelly', 'kiss', 'lachey', 'ladies', 'lb2', 'leia', 'length', 'lesbian', 'lesbians', 'lezbian', 'live', 'magic', 'male', 'man', 'manhattan', 'married', 'massage', 'mature', 'megan', 'men', 'mesh', 'miami', 'military', 'movie', 'movies', 'muscle', 'naked', 'nchf', 'nick', 'noelia', 'non', 'npk', 'nude', 'nudist', 'older', 'online', 'p8ha', 'pab', 'panties', 'paris', 'patrick', 'personals', 'phone', 'picture', 'pictures', 'plt', 'porn', 'porno', 'pregnant', 'pussey', 'pusy', 'q00n', 'qkzm', 'qs7', 'relaciones', 'rl34', 'rlsf', 'romms', 'sex', 'sexuales', 'single', 'sister', 'smiley', 'song', 'songs', 'sqoa', 'star', 'stars', 'stories', 'straight', 't0p', 't1k', 'tdnb', 'teen', 'teniendo', 'tera', 'theme', 'thongs', 'tiffany', 'vanessa', 'video', 'videos', 'wbh2', 'website', 'woman', 'women', 'xbm', 'xbo', 'xhy', 'xxx', 'y6aw', 'yaxf', 'young', 'youtube', 'ytte')  
                    AND m.word_id = w.word_id 
                GROUP BY m.word_id 
                HAVING COUNT(m.word_id) > 44869;

______________________________________________________________________ 010 ___
Count         : 2  (0.02%)
Time          : 534 s total, 267 s avg, 260 s to 274 s max  (0.61%)
Lock Time (s) : 429 s total, 214.5 s avg, 212 s to 217 s max  (2.73%)
Rows sent     : 43 avg, 43 to 43 max  (0.00%)
Rows examined : 43 avg, 43 to 43 max  (0.00%)
Database      : emo_livehelp
Users         : 
    emo_root@localhost  : 100.00% (2) of query, 0.28% (28) of all users

Query abstract:
SELECT name, value FROM livehelp_settings;

Query sample:
SELECT `name`, `value` FROM livehelp_settings;

That looks interesting. Before then here's a link showing some suggested quick fixes to the my.cnf configuration file:

http://www.bigdbahead.com/?p=103#more-103

Based on the advice in the previous link, I'd be tempted to change the query cache like this:

query_cache_size=128M

-- previously 32M

Edit: This can make a difference as query and result sets can then be stored in memory, and re-used if called again.

A few of the slow queries listed seem to be from the phpbb forum software. It may be worth seeking out their forum and seeing if others have had performance issues? (it may even be that the software being used is an old version and needs upgrading). Some of these queries may be running inefficently because of a lack of an index. If so, the issue should have arisen before with other users of that forum package.

What you can do is perform the following on tables shown in the report:

OPTIMIZE TABLE tablename ;

This should defragment the storage of the database table. For instance you could do this on the following tables in the watchtvo_forum database:
phpbb_posts_text
phpbb_topics
phpbb_posts

The problems with these slow running queries may not be down to my.cnf settings. Mysql can often be very selective (and inefficient) in choosing which index to use on a certain query - as the following link shows:

http://code.openark.org/blog/mysql/7...he-right-index