MySQL slow query log results

[Simetrical]

simetrical@loki [~]# sudo mysqldumpslow -s c -t 50
simetrical, please enter your password: 

Reading mysql slow query log from /var/lib/mysql/loki-slow.log
Count: 32269  Time=41.26s (1331560s)  Lock=542.18s (17495474s)  Rows=0.3 (10253), 4users@localhost
  SELECT *
  FROM session
  WHERE userid = N
  AND host = 'S'
  AND idhash = 'S'
  LIMIT N

Count: 30707  Time=40.04s (1229403s)  Lock=679.19s (20855800s)  Rows=0.6 (18478), totalwar_vb[totalwar_vb]@localhost
  SELECT *
  FROM session
  WHERE sessionhash = 'S'
  AND lastactivity > N
  AND idhash = 'S'

Count: 5498  Time=27.58s (151645s)  Lock=0.69s (3773s)  Rows=41.6 (228545), totalwar_vb[totalwar_vb]@localhost
  SELECT a.*, au.*, post.userid, post.postid 
  FROM post AS post
  INNER JOIN award_user AS au ON (au.userid=post.userid)
  INNER JOIN award AS a ON (a.award_id=au.award_id)
  WHERE N=N 
  
  
  GROUP BY au.issue_id
  ORDER BY au.issue_time DESC

Count: 2575  Time=1185.42s (3052467s)  Lock=75.15s (193508s)  Rows=1.3 (3360), totalwar_vb[totalwar_vb]@localhost
  select t.*,p.pagetext
  from thread t
  left join post p on(p.postid=t.firstpostid)
  where t.forumid = N
  order by dateline desc
  limit N,N

Count: 2533  Time=836.18s (2118046s)  Lock=30.00s (75987s)  Rows=4.0 (10235), totalwar_vb[totalwar_vb]@localhost
  SELECT post.postid
  FROM post AS post
  WHERE post.threadid = N
  AND post.visible = N
  ORDER BY post.dateline 
  LIMIT N, N

Count: 2210  Time=39.92s (88230s)  Lock=519.82s (1148795s)  Rows=251.4 (555620), totalwar_vb[totalwar_vb]@localhost
  SELECT
  user.username, (user.options & N) AS invisible, user.usergroupid,
  session.userid, session.inforum, session.lastactivity,
  IF(displaygroupid=N, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid
  FROM session AS session
  LEFT JOIN user AS user ON(user.userid = session.userid)
  WHERE session.lastactivity > N
  ORDER BY username ASC

Count: 1530  Time=1093.28s (1672714s)  Lock=12.76s (19526s)  Rows=0.1 (134), totalwar_vb[totalwar_vb]@localhost
  SELECT post.*,
  IF(visible = N, N, N) AS isdeleted,
  editlog.userid AS edit_userid, editlog.dateline AS edit_dateline, editlog.reason AS edit_reason
  FROM post AS post
  LEFT JOIN editlog AS editlog ON (editlog.postid = post.postid)
  WHERE post.postid = N

Count: 1065  Time=37.62s (40067s)  Lock=558.92s (595251s)  Rows=0.0 (0), totalwar_vb[totalwar_vb]@localhost
  INSERT IGNORE INTO session
  (sessionhash, userid, host, idhash, lastactivity, location, styleid, languageid, loggedin, inforum, inthread, incalendar, badlocation, useragent, bypass, profileupdate)
  VALUES
  ('S', N, 'S', 'S', N, 'S', N, N, N, N, N, N, N, 'S', N, N)

Count: 864  Time=907.02s (783667s)  Lock=7.27s (6279s)  Rows=0.1 (115), totalwar_vb[totalwar_vb]@localhost
  SELECT MIN(postid) AS postid
  FROM post
  WHERE threadid = N
  AND visible = N
  AND dateline > N
  LIMIT N

Count: 618  Time=1156.46s (714693s)  Lock=0.70s (432s)  Rows=0.0 (0), totalwar_vb[totalwar_vb]@localhost
  SELECT  post.pagetext AS preview,
  thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
  thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
  hiddencount, deletedcount
  FROM thread AS thread
  LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
  WHERE thread.threadid IN (N) 
  ORDER BY sticky DESC, lastpost DESC

Count: 503  Time=35.52s (17869s)  Lock=467.40s (235102s)  Rows=1.0 (503), totalwar_vb[totalwar_vb]@localhost
  SELECT IF(visible = N, N, N) AS isdeleted,
  threadread.readtime AS threadread, forumread.readtime AS forumread,
  thread.*
  FROM thread AS thread
  LEFT JOIN threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = N)
  LEFT JOIN forumread AS forumread ON (forumread.forumid = thread.forumid AND forumread.userid = N)
  WHERE thread.threadid = N

Count: 470  Time=16.54s (7776s)  Lock=61.22s (28772s)  Rows=0.0 (0), totalwar_vb[totalwar_vb]@localhost
  REPLACE INTO datastore
  (title, data, unserialize)
  VALUES
  ('S', 'S', N)

Count: 431  Time=33.88s (14603s)  Lock=586.80s (252909s)  Rows=0.0 (0), 2users@localhost
  UPDATE session
  SET lastactivity = N, location = 'S', inforum = N, inthread = N, incalendar = N, badlocation = N
  WHERE sessionhash = 'S'

Count: 413  Time=890.02s (367580s)  Lock=32.44s (13396s)  Rows=0.0 (0), totalwar_vb[totalwar_vb]@localhost
  SELECT  post.pagetext AS preview,
  thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
  thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
  hiddencount, deletedcount
  , threadread.readtime AS threadread
  FROM thread AS thread
  LEFT JOIN threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = N)
  LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
  WHERE thread.threadid IN (N) 
  ORDER BY sticky DESC, lastpost DESC

Count: 396  Time=1094.21s (433307s)  Lock=3.88s (1537s)  Rows=0.1 (34), totalwar_vb[totalwar_vb]@localhost
  SELECT thread.title, thread.threadid, thread.forumid, post.postid, post.dateline
  FROM post AS post
  INNER JOIN thread AS thread USING (threadid)
  WHERE post.postid = N
  AND post.visible = N
  AND thread.visible = N

Count: 379  Time=1062.31s (402616s)  Lock=5.73s (2172s)  Rows=2.5 (957), totalwar_vb[totalwar_vb]@localhost
  SELECT  post.pagetext AS preview,
  thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
  thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
  hiddencount, deletedcount
  FROM thread AS thread
  LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
  WHERE thread.threadid IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) 
  ORDER BY sticky DESC, lastpost DESC

Count: 344  Time=973.64s (334931s)  Lock=9.71s (3341s)  Rows=4.0 (1375), totalwar_vb[totalwar_vb]@localhost
  SELECT thread.title, thread.threadid, thread.forumid, post.postid, post.dateline
  FROM post AS post
  INNER JOIN thread AS thread USING (threadid)
  WHERE thread.visible = N
  AND post.userid =  N
  AND post.visible = N
  ORDER BY post.dateline DESC
  LIMIT N

Count: 324  Time=35.46s (11490s)  Lock=403.72s (130804s)  Rows=0.5 (175), totalwar_vb[totalwar_vb]@localhost
  SELECT text, headers, dateline
  FROM externalcache
  WHERE cachehash = 'S' AND
  dateline >= N

Count: 300  Time=17.62s (5285s)  Lock=89.80s (26939s)  Rows=12.4 (3730), 2users@localhost
  SELECT postid
  FROM post AS post
  WHERE threadid = N
  AND visible = N
  ORDER BY dateline 
  LIMIT N, N

Count: 276  Time=72.30s (19954s)  Lock=456.02s (125862s)  Rows=1.0 (276), totalwar_vb[totalwar_vb]@localhost
  SELECT 
  userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
  IF(displaygroupid=N, user.usergroupid, displaygroupid) AS displaygroupid,
  language.phrasegroup_forumdisplay AS phrasegroup_forumdisplay,
  language.phrasegroup_inlinemod AS phrasegroup_inlinemod,
  language.phrasegroup_global AS phrasegroup_global,
  language.options AS lang_options,
  language.languagecode AS lang_code,
  language.charset AS lang_charset,
  language.locale AS lang_locale,
  language.imagesoverride AS lang_imagesoverride,
  language.dateoverride AS lang_dateoverride,
  language.timeoverride AS lang_timeoverride,
  language.registereddateoverride AS lang_registereddateoverride,
  language.calformat1override AS lang_calformat1override,
  language.calformat2override AS lang_calformat2override,
  language.logdateoverride AS lang_logdateoverride,
  language.decimalsep AS lang_decimalsep,
  language.thousandsep AS lang_thousandsep
  FROM user AS user
  LEFT JOIN userfield AS userfield ON (user.userid = userfield.userid)
  LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.userid) LEFT JOIN language AS language ON (language.languageid = IF(user.languageid = N, N, user.languageid)) 
  WHERE user.userid = N

Count: 255  Time=22.83s (5822s)  Lock=0.00s (0s)  Rows=0.1 (20), 2users@localhost
  # administrator command: Quit

Count: 240  Time=165.72s (39774s)  Lock=4.83s (1159s)  Rows=435.4 (104500), totalwar_vb[totalwar_vb]@localhost
  SELECT
  DISTINCT thread.threadid
  FROM thread AS thread 
  INNER JOIN post AS post ON(thread.threadid = post.threadid )
  WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) AND post.visible = N
  LIMIT N

Count: 220  Time=22.66s (4985s)  Lock=4.40s (967s)  Rows=497.7 (109500), totalwar_vb[totalwar_vb]@localhost
  SELECT
  DISTINCT thread.threadid
  FROM thread AS thread 
  INNER JOIN post AS post ON(thread.threadid = post.threadid )
  WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) AND post.visible = N
  LIMIT N

Count: 204  Time=58.45s (11924s)  Lock=464.37s (94732s)  Rows=1.0 (202), totalwar_vb[totalwar_vb]@localhost
  SELECT 
  userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
  IF(displaygroupid=N, user.usergroupid, displaygroupid) AS displaygroupid,
  language.phrasegroup_holiday AS phrasegroup_holiday,
  language.phrasegroup_global AS phrasegroup_global,
  language.options AS lang_options,
  language.languagecode AS lang_code,
  language.charset AS lang_charset,
  language.locale AS lang_locale,
  language.imagesoverride AS lang_imagesoverride,
  language.dateoverride AS lang_dateoverride,
  language.timeoverride AS lang_timeoverride,
  language.registereddateoverride AS lang_registereddateoverride,
  language.calformat1override AS lang_calformat1override,
  language.calformat2override AS lang_calformat2override,
  language.logdateoverride AS lang_logdateoverride,
  language.decimalsep AS lang_decimalsep,
  language.thousandsep AS lang_thousandsep
  FROM user AS user
  LEFT JOIN userfield AS userfield ON (user.userid = userfield.userid)
  LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.userid) LEFT JOIN language AS language ON (language.languageid = IF(user.languageid = N, N, user.languageid)) 
  WHERE user.userid = N

Count: 204  Time=881.01s (179726s)  Lock=19.89s (4057s)  Rows=0.7 (142), totalwar_vb[totalwar_vb]@localhost
  SELECT  post.pagetext AS preview,
  thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
  thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
  hiddencount, deletedcount
  , threadread.readtime AS threadread
  FROM thread AS thread
  LEFT JOIN threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = N)
  LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
  WHERE thread.threadid IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) 
  ORDER BY sticky DESC, lastpost DESC

Count: 183  Time=1104.65s (202151s)  Lock=71.24s (13037s)  Rows=2.9 (536), totalwar_vb[totalwar_vb]@localhost
  SELECT  post.pagetext AS preview,
  thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
  thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
  hiddencount, deletedcount
  , threadread.readtime AS threadread
  FROM thread AS thread
  LEFT JOIN threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = N)
  LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
  WHERE thread.threadid IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) 
  ORDER BY sticky DESC, lastpost DESC

Count: 170  Time=20.75s (3527s)  Lock=2.49s (424s)  Rows=499.4 (84901), totalwar_vb[totalwar_vb]@localhost
  SELECT
  DISTINCT thread.threadid
  FROM thread AS thread 
  INNER JOIN post AS post ON(thread.threadid = post.threadid )
  WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) AND post.visible = N
  LIMIT N

Count: 168  Time=47.04s (7902s)  Lock=361.02s (60652s)  Rows=1.0 (168), totalwar_vb[totalwar_vb]@localhost
  SELECT 
  userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
  IF(displaygroupid=N, user.usergroupid, displaygroupid) AS displaygroupid,
  language.phrasegroup_posting AS phrasegroup_posting,
  language.phrasegroup_postbit AS phrasegroup_postbit,
  language.phrasegroup_showthread AS phrasegroup_showthread,
  language.phrasegroup_inlinemod AS phrasegroup_inlinemod,
  language.phrasegroup_reputationlevel AS phrasegroup_reputationlevel,
  language.phrasegroup_global AS phrasegroup_global,
  language.options AS lang_options,
  language.languagecode AS lang_code,
  language.charset AS lang_charset,
  language.locale AS lang_locale,
  language.imagesoverride AS lang_imagesoverride,
  language.dateoverride AS lang_dateoverride,
  language.timeoverride AS lang_timeoverride,
  language.registereddateoverride AS lang_registereddateoverride,
  language.calformat1override AS lang_calformat1override,
  language.calformat2override AS lang_calformat2override,
  language.logdateoverride AS lang_logdateoverride,
  language.decimalsep AS lang_decimalsep,
  language.thousandsep AS lang_thousandsep
  FROM user AS user
  LEFT JOIN userfield AS userfield ON (user.userid = userfield.userid)
  LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.userid) LEFT JOIN language AS language ON (language.languageid = IF(user.languageid = N, N, user.languageid)) 
  WHERE user.userid = N

Count: 157  Time=1398.37s (219544s)  Lock=7.97s (1252s)  Rows=2.8 (434), totalwar_vb[totalwar_vb]@localhost
  SELECT
  user.username, reputation.whoadded,
  reputation.postid as postid,
  reputation.reputation, reputation.reason,
  post.threadid as threadid,
  reputation.dateline as dateline,
  thread.title as title
  FROM reputation AS reputation
  LEFT JOIN post AS post ON(reputation.postid = post.postid)
  LEFT JOIN thread AS thread ON(post.threadid = thread.threadid)
  LEFT JOIN user AS user ON(user.userid = reputation.whoadded)
  WHERE reputation.userid = N
  AND thread.visible = N
  AND post.visible = N
  ORDER BY reputation.dateline DESC
  LIMIT N, N

Count: 151  Time=20.80s (3141s)  Lock=0.85s (128s)  Rows=497.5 (75118), totalwar_vb[totalwar_vb]@localhost
  SELECT
  DISTINCT thread.threadid
  FROM thread AS thread 
  INNER JOIN post AS post ON(thread.threadid = post.threadid )
  WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) AND post.visible = N
  LIMIT N

Count: 144  Time=32.13s (4627s)  Lock=603.55s (86911s)  Rows=0.6 (93), totalwar_vb[totalwar_vb]@localhost
  SELECT cron.*
  FROM cron AS cron
  LEFT JOIN product AS product ON (cron.product = product.productid)
  WHERE cron.nextrun <= N AND cron.active = N
  AND (product.productid IS NULL OR product.active = N)
  ORDER BY cron.nextrun
  LIMIT N

Count: 137  Time=33.18s (4545s)  Lock=3.79s (519s)  Rows=154.5 (21173), totalwar_vb[totalwar_vb]@localhost
  SELECT
  DISTINCT thread.threadid
  FROM thread AS thread 
  INNER JOIN post AS post ON(thread.threadid = post.threadid )
  WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) AND thread.forumid IN(N) AND post.visible = N
  LIMIT N

Count: 136  Time=21.24s (2889s)  Lock=0.07s (10s)  Rows=1.2 (162), totalwar_vb[totalwar_vb]@localhost
  SELECT pmtext.pmtextid
  FROM pmtext AS pmtext
  LEFT JOIN pm AS pm USING(pmtextid)
  WHERE pm.pmid IS NULL

Count: 135  Time=1247.32s (168388s)  Lock=7.44s (1004s)  Rows=0.5 (74), totalwar_vb[totalwar_vb]@localhost
  SELECT  post.pagetext AS preview,
  thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
  thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
  hiddencount, deletedcount
  FROM thread AS thread
  LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
  WHERE thread.threadid IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) 
  ORDER BY sticky DESC, lastpost DESC

Count: 131  Time=1521.21s (199279s)  Lock=18.09s (2370s)  Rows=2.8 (361), totalwar_vb[totalwar_vb]@localhost
  SELECT 
  IF(votenum >= N, votenum, N) AS votenum,
  IF(votenum >= N AND votenum > N, votetotal / votenum, N) AS voteavg,
  post.pagetext AS preview,
  thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
  thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
  hiddencount, deletedcount
  FROM thread AS thread
  LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
  WHERE thread.threadid IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) 
  ORDER BY sticky DESC, lastpost DESC

Count: 129  Time=19.53s (2520s)  Lock=0.88s (114s)  Rows=500.0 (64500), totalwar_vb[totalwar_vb]@localhost
  SELECT
  DISTINCT thread.threadid
  FROM thread AS thread 
  INNER JOIN post AS post ON(thread.threadid = post.threadid )
  WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) AND post.visible = N
  LIMIT N

Count: 127  Time=17.50s (2222s)  Lock=21.31s (2706s)  Rows=0.0 (0), totalwar_vb[totalwar_vb]@localhost
  UPDATE post SET
  showsignature = N,
  allowsmilie = N,
  title = 'S',
  pagetext = 'S',
  iconid = N,
  attach = N
  WHERE postid = N

Count: 125  Time=1067.51s (133439s)  Lock=124.22s (15528s)  Rows=6.6 (822), totalwar_vb[totalwar_vb]@localhost
  SELECT  post.pagetext AS preview,
  thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
  thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
  hiddencount, deletedcount
  , threadread.readtime AS threadread
  FROM thread AS thread
  LEFT JOIN threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = N)
  LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
  WHERE thread.threadid IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) 
  ORDER BY sticky DESC, lastpost DESC

Count: 123  Time=433.59s (53331s)  Lock=89.93s (11062s)  Rows=12.3 (1518), totalwar_vb[totalwar_vb]@localhost
  SELECT  post.pagetext AS preview,
  thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
  thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
  hiddencount, deletedcount
  , threadread.readtime AS threadread
  FROM thread AS thread
  LEFT JOIN threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = N)
  LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
  WHERE thread.threadid IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) 
  ORDER BY sticky DESC, lastpost DESC

Count: 119  Time=16.77s (1996s)  Lock=0.01s (1s)  Rows=0.0 (0), totalwar_vb[totalwar_vb]@localhost
  DELETE FROM postparsed
  WHERE dateline < N

Count: 107  Time=18.03s (1929s)  Lock=2.34s (250s)  Rows=190.9 (20427), totalwar_vb[totalwar_vb]@localhost
  SELECT
  DISTINCT thread.threadid
  FROM thread AS thread 
  INNER JOIN post AS post ON(thread.threadid = post.threadid )
  WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) AND thread.forumid IN(N) AND post.visible = N
  LIMIT N

Count: 103  Time=927.86s (95570s)  Lock=27.18s (2800s)  Rows=1.6 (162), totalwar_vb[totalwar_vb]@localhost
  SELECT thread.threadid, thread.title, post.attach,
  thread.postusername, thread.dateline, podcastitem.*,
  forum.forumid,
  post.pagetext AS message, post.allowsmilie, post.postid
  FROM thread AS thread
  INNER JOIN forum AS forum ON(forum.forumid = thread.forumid)
  LEFT JOIN post AS post ON (post.postid = thread.firstpostid)
  LEFT JOIN podcastitem AS podcastitem ON (podcastitem.postid = thread.firstpostid)
  WHERE N=N
  AND thread.forumid IN(N)
  AND thread.visible = N
  AND post.visible = N
  AND open <> N
  AND thread.dateline > N
  ORDER BY thread.dateline DESC
  LIMIT N

Count: 98  Time=1207.29s (118314s)  Lock=4.77s (467s)  Rows=0.2 (24), totalwar_vb[totalwar_vb]@localhost
  SELECT  post.pagetext AS preview,
  thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
  thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
  hiddencount, deletedcount
  FROM thread AS thread
  LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
  WHERE thread.threadid IN (N,N,N,N) 
  ORDER BY sticky DESC, lastpost DESC

Count: 96  Time=170.62s (16380s)  Lock=409.04s (39268s)  Rows=16.9 (1622), totalwar_vb[totalwar_vb]@localhost
  SELECT  post.pagetext AS preview,
  thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
  thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
  hiddencount, deletedcount
  , threadread.readtime AS threadread
  FROM thread AS thread
  LEFT JOIN threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = N)
  LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
  WHERE thread.threadid IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) 
  ORDER BY sticky DESC, lastpost DESC

Count: 91  Time=1186.29s (107952s)  Lock=0.00s (0s)  Rows=0.0 (0), totalwar_vb[totalwar_vb]@localhost
  SELECT thread.threadid, thread.title, post.attach,
  thread.postusername, thread.dateline, podcastitem.*,
  forum.forumid,
  post.pagetext AS message, post.allowsmilie, post.postid
  FROM thread AS thread
  INNER JOIN forum AS forum ON(forum.forumid = thread.forumid)
  LEFT JOIN post AS post ON (post.postid = thread.firstpostid)
  LEFT JOIN podcastitem AS podcastitem ON (podcastitem.postid = thread.firstpostid)
  WHERE N=N
  AND thread.forumid IN(N,,{repeated 259 times}N)
  AND thread.visible = N
  AND post.visible = N
  AND open <> N
  AND thread.dateline > N
  ORDER BY thread.dateline DESC
  LIMIT N

Count: 90  Time=752.62s (67736s)  Lock=5.91s (532s)  Rows=0.2 (16), totalwar_vb[totalwar_vb]@localhost
  SELECT post.userid
  FROM post AS post
  WHERE post.threadid = N
  AND post.visible = N
  ORDER BY dateline DESC
  LIMIT N

Count: 90  Time=21.26s (1913s)  Lock=3.01s (271s)  Rows=496.9 (44717), totalwar_vb[totalwar_vb]@localhost
  SELECT
  DISTINCT thread.threadid
  FROM thread AS thread 
  INNER JOIN post AS post ON(thread.threadid = post.threadid )
  WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) AND post.visible = N
  LIMIT N

Count: 87  Time=19.94s (1735s)  Lock=1.30s (113s)  Rows=179.1 (15584), totalwar_vb[totalwar_vb]@localhost
  SELECT
  DISTINCT thread.threadid
  FROM thread AS thread 
  INNER JOIN post AS post ON(thread.threadid = post.threadid )
  WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) AND thread.forumid IN(N) AND post.visible = N
  LIMIT N

Count: 87  Time=19.75s (1718s)  Lock=163.60s (14233s)  Rows=6.8 (589), totalwar_vb[totalwar_vb]@localhost
  SELECT
  post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = N, N, N) AS isdeleted,
  user.*, userfield.*, usertextfield.*,
  icon.title as icontitle, icon.iconpath,
  avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.height AS avheight,
  editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline,
  editlog.reason AS edit_reason,
  postparsed.pagetext_html, postparsed.hasimages,
  sigparsed.signatureparsed, sigparsed.hasimages AS sighasimages,
  sigpic.userid AS sigpic, sigpic.dateline AS sigpicdateline, sigpic.width AS sigpicwidth, sigpic.height AS sigpicheight,
  IF(displaygroupid=N, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid
  , 'S' AS field10
  FROM post AS post
  LEFT JOIN user AS user ON(user.userid = post.userid)
  LEFT JOIN userfield AS userfield ON(userfield.userid = user.userid)
  LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid = user.userid)
  LEFT JOIN icon AS icon ON(icon.iconid = post.iconid)
  LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN customavatar AS customavatar ON(customavatar.userid = user.userid)
  LEFT JOIN editlog AS editlog ON(editlog.postid = post.postid)
  LEFT JOIN postparsed AS postparsed ON(postparsed.postid = post.postid AND postparsed.styleid = N AND postparsed.languageid = N)
  LEFT JOIN sigparsed AS sigparsed ON(sigparsed.userid = user.userid AND sigparsed.styleid = N AND sigparsed.languageid = N)
  LEFT JOIN sigpic AS sigpic ON(sigpic.userid = post.userid)
  WHERE post.postid IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N)
  ORDER BY post.dateline

Count: 84  Time=828.67s (69608s)  Lock=172.48s (14488s)  Rows=3.3 (279), totalwar_vb[totalwar_vb]@localhost
  SELECT 
  IF(votenum >= N, votenum, N) AS votenum,
  IF(votenum >= N AND votenum > N, votetotal / votenum, N) AS voteavg,
  post.pagetext AS preview,
  thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
  thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
  hiddencount, deletedcount
  , threadread.readtime AS threadread
  FROM thread AS thread
  LEFT JOIN threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = N)
  LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
  WHERE thread.threadid IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) 
  ORDER BY sticky DESC, lastpost DESC

simetrical@loki [~]#

Fairly similar to the last run. I'm now convinced I can solve most of these (other than session-table issues . . .) with better indexes. There are a lot of totally unnecessary filesorts and so on. That will be a project for me.

[Simetrical]

Count: 5498 Time=27.58s (151645s) Lock=0.69s (3773s) Rows=41.6 (228545), totalwar_vb[totalwar_vb]@localhost
SELECT a.*, au.*, post.userid, post.postid
FROM post AS post
INNER JOIN award_user AS au ON (au.userid=post.userid)
INNER JOIN award AS a ON (a.award_id=au.award_id)
WHERE N=N


GROUP BY au.issue_id
ORDER BY au.issue_time DESC

Fixed, was a bug in the extension.

Count: 2575 Time=1185.42s (3052467s) Lock=75.15s (193508s) Rows=1.3 (3360), totalwar_vb[totalwar_vb]@localhost
select t.*,p.pagetext
from thread t
left join post p on(p.postid=t.firstpostid)
where t.forumid = N
order by dateline desc
limit N,N

Added an index to thread to fix this.

Count: 2533 Time=836.18s (2118046s) Lock=30.00s (75987s) Rows=4.0 (10235), totalwar_vb[totalwar_vb]@localhost
SELECT post.postid
FROM post AS post
WHERE post.threadid = N
AND post.visible = N
ORDER BY post.dateline
LIMIT N, N

Count: 864 Time=907.02s (783667s) Lock=7.27s (6279s) Rows=0.1 (115), totalwar_vb[totalwar_vb]@localhost
SELECT MIN(postid) AS postid
FROM post
WHERE threadid = N
AND visible = N
AND dateline > N
LIMIT N

Count: 300 Time=17.62s (5285s) Lock=89.80s (26939s) Rows=12.4 (3730), 2users@localhost
SELECT postid
FROM post AS post
WHERE threadid = N
AND visible = N
ORDER BY dateline
LIMIT N, N

Plan to add an index sometime convenient to make this lightning-fast (it will lock the forums for a while, since post is a big table).

Count: 2210 Time=39.92s (88230s) Lock=519.82s (1148795s) Rows=251.4 (555620), totalwar_vb[totalwar_vb]@localhost
SELECT
user.username, (user.options & N) AS invisible, user.usergroupid,
session.userid, session.inforum, session.lastactivity,
IF(displaygroupid=N, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid
FROM session AS session
LEFT JOIN user AS user ON(user.userid = session.userid)
WHERE session.lastactivity > N
ORDER BY username ASC

Need to turn off alphabetization, or maybe write this to sort it on the PHP side.

Count: 1530 Time=1093.28s (1672714s) Lock=12.76s (19526s) Rows=0.1 (134), totalwar_vb[totalwar_vb]@localhost
SELECT post.*,
IF(visible = N, N, N) AS isdeleted,
editlog.userid AS edit_userid, editlog.dateline AS edit_dateline, editlog.reason AS edit_reason
FROM post AS post
LEFT JOIN editlog AS editlog ON (editlog.postid = post.postid)
WHERE post.postid = N

This shouldn't be taking long at all. It puzzles me.

Count: 618 Time=1156.46s (714693s) Lock=0.70s (432s) Rows=0.0 (0), totalwar_vb[totalwar_vb]@localhost
SELECT post.pagetext AS preview,
thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
hiddencount, deletedcount
FROM thread AS thread
LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
WHERE thread.threadid IN (N)
ORDER BY sticky DESC, lastpost DESC

Count: 413 Time=890.02s (367580s) Lock=32.44s (13396s) Rows=0.0 (0), totalwar_vb[totalwar_vb]@localhost
SELECT post.pagetext AS preview,
thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
hiddencount, deletedcount
, threadread.readtime AS threadread
FROM thread AS thread
LEFT JOIN threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = N)
LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
WHERE thread.threadid IN (N)
ORDER BY sticky DESC, lastpost DESC

Count: 379 Time=1062.31s (402616s) Lock=5.73s (2172s) Rows=2.5 (957), totalwar_vb[totalwar_vb]@localhost
SELECT post.pagetext AS preview,
thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
hiddencount, deletedcount
FROM thread AS thread
LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
WHERE thread.threadid IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N)
ORDER BY sticky DESC, lastpost DESC

Count: 204 Time=881.01s (179726s) Lock=19.89s (4057s) Rows=0.7 (142), totalwar_vb[totalwar_vb]@localhost
SELECT post.pagetext AS preview,
thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
hiddencount, deletedcount
, threadread.readtime AS threadread
FROM thread AS thread
LEFT JOIN threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = N)
LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
WHERE thread.threadid IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N)
ORDER BY sticky DESC, lastpost DESC

Count: 183 Time=1104.65s (202151s) Lock=71.24s (13037s) Rows=2.9 (536), totalwar_vb[totalwar_vb]@localhost
SELECT post.pagetext AS preview,
thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
thread.lastpost, thread.lastposter, thread.lastpostid, thread.dateline, IF(views<=replycount, replycount+N, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
hiddencount, deletedcount
, threadread.readtime AS threadread
FROM thread AS thread
LEFT JOIN threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = N)
LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
WHERE thread.threadid IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N)
ORDER BY sticky DESC, lastpost DESC

Not fixable with indexes. This will do a filesort no matter what. It will have to be recoded, if anything.

Count: 503 Time=35.52s (17869s) Lock=467.40s (235102s) Rows=1.0 (503), totalwar_vb[totalwar_vb]@localhost
SELECT IF(visible = N, N, N) AS isdeleted,
threadread.readtime AS threadread, forumread.readtime AS forumread,
thread.*
FROM thread AS thread
LEFT JOIN threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = N)
LEFT JOIN forumread AS forumread ON (forumread.forumid = thread.forumid AND forumread.userid = N)
WHERE thread.threadid = N

Another puzzling one. Maybe too many writes and too many locks?

Count: 470 Time=16.54s (7776s) Lock=61.22s (28772s) Rows=0.0 (0), totalwar_vb[totalwar_vb]@localhost
REPLACE INTO datastore
(title, data, unserialize)
VALUES
('S', 'S', N)

No longer occurring AFAIK.

Count: 396 Time=1094.21s (433307s) Lock=3.88s (1537s) Rows=0.1 (34), totalwar_vb[totalwar_vb]@localhost
SELECT thread.title, thread.threadid, thread.forumid, post.postid, post.dateline
FROM post AS post
INNER JOIN thread AS thread USING (threadid)
WHERE post.postid = N
AND post.visible = N
AND thread.visible = N

Another one that should be ridiculously fast. Maybe these are just showing up because they're common and happened to be run in large numbers when the disk was totally frozen up or something.

Count: 344 Time=973.64s (334931s) Lock=9.71s (3341s) Rows=4.0 (1375), totalwar_vb[totalwar_vb]@localhost
SELECT thread.title, thread.threadid, thread.forumid, post.postid, post.dateline
FROM post AS post
INNER JOIN thread AS thread USING (threadid)
WHERE thread.visible = N
AND post.userid = N
AND post.visible = N
ORDER BY post.dateline DESC
LIMIT N

Could be fixed by extending the index on userid to userid, visible, dateline.

Count: 324 Time=35.46s (11490s) Lock=403.72s (130804s) Rows=0.5 (175), totalwar_vb[totalwar_vb]@localhost
SELECT text, headers, dateline
FROM externalcache
WHERE cachehash = 'S' AND
dateline >= N

Another very fast one. This returns a huge result set, but only if it returns anything, and half the time it got in the log it didn't, so that doesn't mean much.

Count: 276 Time=72.30s (19954s) Lock=456.02s (125862s) Rows=1.0 (276), totalwar_vb[totalwar_vb]@localhost
SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF(displaygroupid=N, user.usergroupid, displaygroupid) AS displaygroupid,
language.phrasegroup_forumdisplay AS phrasegroup_forumdisplay,
language.phrasegroup_inlinemod AS phrasegroup_inlinemod,
language.phrasegroup_global AS phrasegroup_global,
language.options AS lang_options,
language.languagecode AS lang_code,
language.charset AS lang_charset,
language.locale AS lang_locale,
language.imagesoverride AS lang_imagesoverride,
language.dateoverride AS lang_dateoverride,
language.timeoverride AS lang_timeoverride,
language.registereddateoverride AS lang_registereddateoverride,
language.calformat1override AS lang_calformat1override,
language.calformat2override AS lang_calformat2override,
language.logdateoverride AS lang_logdateoverride,
language.decimalsep AS lang_decimalsep,
language.thousandsep AS lang_thousandsep
FROM user AS user
LEFT JOIN userfield AS userfield ON (user.userid = userfield.userid)
LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.userid) LEFT JOIN language AS language ON (language.languageid = IF(user.languageid = N, N, user.languageid))
WHERE user.userid = N

This one's just stupid. It should go in APC.

Count: 255 Time=22.83s (5822s) Lock=0.00s (0s) Rows=0.1 (20), 2users@localhost
# administrator command: Quit

Haha.

Count: 240 Time=165.72s (39774s) Lock=4.83s (1159s) Rows=435.4 (104500), totalwar_vb[totalwar_vb]@localhost
SELECT
DISTINCT thread.threadid
FROM thread AS thread
INNER JOIN post AS post ON(thread.threadid = post.threadid )
WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) AND post.visible = N
LIMIT N

Count: 220 Time=22.66s (4985s) Lock=4.40s (967s) Rows=497.7 (109500), totalwar_vb[totalwar_vb]@localhost
SELECT
DISTINCT thread.threadid
FROM thread AS thread
INNER JOIN post AS post ON(thread.threadid = post.threadid )
WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) AND post.visible = N
LIMIT N

MySQL fulltext search is inevitably a bit slow.

Count: 204 Time=58.45s (11924s) Lock=464.37s (94732s) Rows=1.0 (202), totalwar_vb[totalwar_vb]@localhost
SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF(displaygroupid=N, user.usergroupid, displaygroupid) AS displaygroupid,
language.phrasegroup_holiday AS phrasegroup_holiday,
language.phrasegroup_global AS phrasegroup_global,
language.options AS lang_options,
language.languagecode AS lang_code,
language.charset AS lang_charset,
language.locale AS lang_locale,
language.imagesoverride AS lang_imagesoverride,
language.dateoverride AS lang_dateoverride,
language.timeoverride AS lang_timeoverride,
language.registereddateoverride AS lang_registereddateoverride,
language.calformat1override AS lang_calformat1override,
language.calformat2override AS lang_calformat2override,
language.logdateoverride AS lang_logdateoverride,
language.decimalsep AS lang_decimalsep,
language.thousandsep AS lang_thousandsep
FROM user AS user
LEFT JOIN userfield AS userfield ON (user.userid = userfield.userid)
LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.userid) LEFT JOIN language AS language ON (language.languageid = IF(user.languageid = N, N, user.languageid))
WHERE user.userid = N

This is another one for APC.

Count: 157 Time=1398.37s (219544s) Lock=7.97s (1252s) Rows=2.8 (434), totalwar_vb[totalwar_vb]@localhost
SELECT
user.username, reputation.whoadded,
reputation.postid as postid,
reputation.reputation, reputation.reason,
post.threadid as threadid,
reputation.dateline as dateline,
thread.title as title
FROM reputation AS reputation
LEFT JOIN post AS post ON(reputation.postid = post.postid)
LEFT JOIN thread AS thread ON(post.threadid = thread.threadid)
LEFT JOIN user AS user ON(user.userid = reputation.whoadded)
WHERE reputation.userid = N
AND thread.visible = N
AND post.visible = N
ORDER BY reputation.dateline DESC
LIMIT N, N

Index adjusted to kill filesort.

Count: 144 Time=32.13s (4627s) Lock=603.55s (86911s) Rows=0.6 (93), totalwar_vb[totalwar_vb]@localhost
SELECT cron.*
FROM cron AS cron
LEFT JOIN product AS product ON (cron.product = product.productid)
WHERE cron.nextrun <= N AND cron.active = N
AND (product.productid IS NULL OR product.active = N)
ORDER BY cron.nextrun
LIMIT N

Difficult to index, but the tables are tiny, so I don't really care. It should be fast enough.

Count: 136 Time=21.24s (2889s) Lock=0.07s (10s) Rows=1.2 (162), totalwar_vb[totalwar_vb]@localhost
SELECT pmtext.pmtextid
FROM pmtext AS pmtext
LEFT JOIN pm AS pm USING(pmtextid)
WHERE pm.pmid IS NULL

Table scan of pmtext, and no way I know of to stop it. I think this is one that should be killed entirely, if I remember where it's used correctly.

Count: 127 Time=17.50s (2222s) Lock=21.31s (2706s) Rows=0.0 (0), totalwar_vb[totalwar_vb]@localhost
UPDATE post SET
showsignature = N,
allowsmilie = N,
title = 'S',
pagetext = 'S',
iconid = N,
attach = N
WHERE postid = N

Another one that should be super-fast.

Count: 119 Time=16.77s (1996s) Lock=0.01s (1s) Rows=0.0 (0), totalwar_vb[totalwar_vb]@localhost
DELETE FROM postparsed
WHERE dateline < N

If this is run infrequently enough with small enough N's, it will be deleting most of the table, so it will inevitably be slow. There's an appropriate index it can use if it wants, but for small N it doesn't bother.

Count: 103 Time=927.86s (95570s) Lock=27.18s (2800s) Rows=1.6 (162), totalwar_vb[totalwar_vb]@localhost
SELECT thread.threadid, thread.title, post.attach,
thread.postusername, thread.dateline, podcastitem.*,
forum.forumid,
post.pagetext AS message, post.allowsmilie, post.postid
FROM thread AS thread
INNER JOIN forum AS forum ON(forum.forumid = thread.forumid)
LEFT JOIN post AS post ON (post.postid = thread.firstpostid)
LEFT JOIN podcastitem AS podcastitem ON (podcastitem.postid = thread.firstpostid)
WHERE N=N
AND thread.forumid IN(N)
AND thread.visible = N
AND post.visible = N
AND open <> N
AND thread.dateline > N
ORDER BY thread.dateline DESC
LIMIT N

I already added an index for a previous query that improves this substantially.

Count: 90 Time=752.62s (67736s) Lock=5.91s (532s) Rows=0.2 (16), totalwar_vb[totalwar_vb]@localhost
SELECT post.userid
FROM post AS post
WHERE post.threadid = N
AND post.visible = N
ORDER BY dateline DESC
LIMIT N

This will be covered well enough by the index I mentioned above I'll be adding to post.

Count: 87 Time=19.75s (1718s) Lock=163.60s (14233s) Rows=6.8 (589), totalwar_vb[totalwar_vb]@localhost
SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = N, N, N) AS isdeleted,
user.*, userfield.*, usertextfield.*,
icon.title as icontitle, icon.iconpath,
avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.height AS avheight,
editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline,
editlog.reason AS edit_reason,
postparsed.pagetext_html, postparsed.hasimages,
sigparsed.signatureparsed, sigparsed.hasimages AS sighasimages,
sigpic.userid AS sigpic, sigpic.dateline AS sigpicdateline, sigpic.width AS sigpicwidth, sigpic.height AS sigpicheight,
IF(displaygroupid=N, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid
, 'S' AS field10
FROM post AS post
LEFT JOIN user AS user ON(user.userid = post.userid)
LEFT JOIN userfield AS userfield ON(userfield.userid = user.userid)
LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid = user.userid)
LEFT JOIN icon AS icon ON(icon.iconid = post.iconid)
LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN customavatar AS customavatar ON(customavatar.userid = user.userid)
LEFT JOIN editlog AS editlog ON(editlog.postid = post.postid)
LEFT JOIN postparsed AS postparsed ON(postparsed.postid = post.postid AND postparsed.styleid = N AND postparsed.languageid = N)
LEFT JOIN sigparsed AS sigparsed ON(sigparsed.userid = user.userid AND sigparsed.styleid = N AND sigparsed.languageid = N)
LEFT JOIN sigpic AS sigpic ON(sigpic.userid = post.userid)
WHERE post.postid IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N)
ORDER BY post.dateline

Impossible to avoid a filesort here too. Recoding will be necessary to optimize this.


So that's all of the top 50, aside from some repetitive ones, and aside from the whole session issue. I wonder if session would benefit from being InnoDB with covering indexes to make SELECTs be from memory? That would still potentially slow down UPDATEs, but the concurrency seems to be too high for table-level locking to be effective.

[Simetrical]

Heh, fast disk and memory can be pretty awesome sometimes. I added that index I wanted to post, in about 4.5 minutes. That should eliminate one of the top problems here. I've reduced the long_query_time to one second; let's see what crops up now.

[Simetrical]

I just found the boards locked for like 40 seconds, so I'm looking at this again. I'm no longer convinced mysqldumpslow is terribly useful at this point: the biggest problem seems to be locking, and the locked queries are more numerous than the actually problematic ones, i.e., the ones that take out a lock for a long time in the first place. mysqldumpslow says it subtracts the lock time from the query time, though, so I'm not sure why there would be any issue.

Anyway, I've made three configuration changes:

# 20080127 Simetrical: Upped from 8M to 128M.  This is suicidal if more than
# a few threads try using it at once, but filesort performance is a big prob-
# lem.
sort_buffer_size = 128M
# 20080127 Simetrical: Upped from 2500 to 5000.  Open_tables tended to hover at
# just below 2500, so that was insufficient.
table_cache = 5000
thread_cache_size = 512
# 20080127 Simetrical: Upped from 256M to 1G.  Again, suicidal if it's too com-
# mon, but we're getting a temp table on disk every 40 seconds.
tmp_table_size = 1G

I also hacked the enormous post-fetching query in showpost.php from sorting by dateline (which means filesort) to sorting by postid (which means no filesort). This one:

SELECT
	post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS isdeleted,
	user.*, userfield.*, usertextfield.*,
	icon.title as icontitle, icon.iconpath,
	avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.height AS avheight,
	deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason,
	editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline,
	editlog.reason AS edit_reason,
	postparsed.pagetext_html, postparsed.hasimages,
	sigparsed.signatureparsed, sigparsed.hasimages AS sighasimages,
	sigpic.userid AS sigpic, sigpic.dateline AS sigpicdateline, sigpic.width AS sigpicwidth, sigpic.height AS sigpicheight,
	IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid
	
	
FROM post AS post
LEFT JOIN user AS user ON(user.userid = post.userid)
LEFT JOIN userfield AS userfield ON(userfield.userid = user.userid)
LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid = user.userid)
LEFT JOIN icon AS icon ON(icon.iconid = post.iconid)
LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN customavatar AS customavatar ON(customavatar.userid = user.userid)
	LEFT JOIN deletionlog AS deletionlog ON(post.postid = deletionlog.primaryid AND deletionlog.type = 'post')
LEFT JOIN editlog AS editlog ON(editlog.postid = post.postid)
LEFT JOIN postparsed AS postparsed ON(postparsed.postid = post.postid AND postparsed.styleid = 16 AND postparsed.languageid = 1)
LEFT JOIN sigparsed AS sigparsed ON(sigparsed.userid = user.userid AND sigparsed.styleid = 16 AND sigparsed.languageid = 1)
LEFT JOIN sigpic AS sigpic ON(sigpic.userid = post.userid)
	
WHERE post.postid IN (0,2033262,2035921,2154338)
ORDER BY post.dateline -- Now post.postid

Maybe that will help. We seem to have 2 GB of RAM free, not even used for OS disk cache . . .

[Simetrical]

Boards seem to be pretty slow right now. CPU usage is high, almost all user time and system time, and a considerable amount is MySQL. Might be worth looking at this again. Unfortunately, I discovered that due to file ownership issues (mostly because of my gratuitously fiddling with the default locations of files), MySQL hasn't been keeping a slow query log since February. I've restarted it and it's working again, so maybe I'll have something to report, but I doubt it will be all that useful without the microslow patch.

Anyway, troubleshooting PHP might end up being more productive, since it's still the bulk of the CPU use by far.