Mysql 当机问题

Coolriver

SELECT * FROM mdl_config;  这种语句注定了早晚要出事的。可以想办法早点处理吧。如果行数不多，还不入写到配置文件中，用一个数据处理了。

falkon

最新的LOG。
# Query_time: 2  Lock_time: 0  Rows_sent: 1000  Rows_examined: 488537
SELECT * FROM mdl_log WHERE course = '1' ORDER BY time LIMIT 55000,1000;
# Time: 100112 19:58:26
# User@Host: moodle[moodle] @  [10.0.0.1]
# Query_time: 2  Lock_time: 0  Rows_sent: 1000  Rows_examined: 489537
SELECT * FROM mdl_log WHERE course = '1' ORDER BY time LIMIT 56000,1000;
# Time: 100112 19:58:30
# User@Host: moodle[moodle] @  [10.0.0.1]
# Query_time: 2  Lock_time: 0  Rows_sent: 1000  Rows_examined: 490537
SELECT * FROM mdl_log WHERE course = '1' ORDER BY time LIMIT 57000,1000;
# Time: 100112 19:58:34
# User@Host: moodle[moodle] @  [10.0.0.1]
# Query_time: 2  Lock_time: 0  Rows_sent: 1000  Rows_examined: 491537
SELECT * FROM mdl_log WHERE course = '1' ORDER BY time LIMIT 58000,1000;
# Time: 100112 19:58:37
# User@Host: moodle[moodle] @  [10.0.0.1]
# Query_time: 3  Lock_time: 0  Rows_sent: 1000  Rows_examined: 492537
SELECT * FROM mdl_log WHERE course = '1' ORDER BY time LIMIT 59000,1000;
# Time: 100112 19:58:40
# User@Host: moodle[moodle] @  [10.0.0.1]
# Query_time: 3  Lock_time: 0  Rows_sent: 1000  Rows_examined: 493537
SELECT * FROM mdl_log WHERE course = '1' ORDER BY time LIMIT 60000,1000;

[ 本帖最后由 falkon 于 2010-1-13 10:05 编辑 ]

cenalulu

mysqldumpslow -s c -t 5 /path/to/slow-log
把结果贴一下

falkon

Count: 14947  Time=0.00s (67s)  Lock=0.00s (0s)  Rows=418.0 (6247846), moodle[moodle]@[10.0.0.1]
  SELECT * FROM mdl_config

Count: 4681  Time=0.00s (1s)  Lock=0.00s (1s)  Rows=85.0 (397885), moodle[moodle]@[10.0.0.1]
  SELECT name,value FROM mdl_config_plugins WHERE plugin = 'S'

Count: 2133  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=22.0 (46926), moodle[moodle]@[10.0.0.1]
  SELECT * FROM mdl_modules

Count: 1788  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=37.0 (66156), moodle[moodle]@[10.0.0.1]
  SELECT * FROM mdl_block

Count: 1741  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=32.8 (57056), moodle[moodle]@[10.0.0.1]
  SELECT ctx.*
  FROM mdl_context ctx
  WHERE ctx.path LIKE 'S'
  AND ctx.contextlevel IN (N,N)
  UNION
  SELECT ctx.*
  FROM mdl_context ctx
  JOIN mdl_groups  g
  ON (ctx.instanceid=g.id AND ctx.contextlevel=N)
  WHERE g.courseid=N
  UNION
  SELECT ctx.*
  FROM mdl_context ctx
  JOIN mdl_block_pinned  b
  ON (ctx.instanceid=b.blockid AND ctx.contextlevel=N)
  WHERE b.pagetype='S'

cenalulu

原帖由 falkon 于 2010-1-13 10:05 发表
Count: 14947  Time=0.00s (67s)  Lock=0.00s (0s)  Rows=418.0 (6247846), moodle[moodle]@[10.0.0.1]
  SELECT * FROM mdl_config

Count: 4681  Time=0.00s (1s)  Lock=0.00s (1s)  Rows=85.0 (397885),  ...

汗。。。你昨天的log没清除阿？。。。

falkon

原帖由 cenalulu 于 2010-1-13 12:44 发表


汗。。。你昨天的log没清除阿？。。。

忘了。
今天再来一次。

tianma123

从记录来看，应该是索引的问题。Rows_examined: 490537 太大了。。。。

justin033

原帖由 chenyx 于 2010-1-11 10:51 发表
死机的时候查看下什么进程死锁造成cpu100%

先看进程吧

falkon

Reading mysql slow query log from /var/log/slow.log
Count: 437  Time=3.14s (1372s)  Lock=0.00s (0s)  Rows=1000.0 (436982), moodle[moodle]@[10.0.0.1]
  SELECT * FROM mdl_log WHERE course = 'S' ORDER BY time LIMIT N,N

Count: 3  Time=2.00s (6s)  Lock=0.00s (0s)  Rows=0.0 (0), moodle[moodle]@[10.0.0.1]
  SELECT a.*, b.username
  FROM   mdl_groups_members a,
  mdl_user b
  WHERE  groupid in (SELECT     gr.id
  FROM       mdl_user u
  INNER JOIN mdl_groups_members gm ON gm.userid = u.id
  INNER JOIN mdl_groups gr ON gr.id = gm.groupid
  WHERE      u.username = 'S')
  AND    b.id = a.userid
  ORDER BY groupid, userid

Count: 1  Time=3.00s (3s)  Lock=0.00s (0s)  Rows=34475.0 (34475), root[root]@[10.0.0.2]
  SELECT /*!N SQL_NO_CACHE */ * FROM `moodle`.`mdl_stats_user_weekly`

Count: 1  Time=64.00s (64s)  Lock=0.00s (0s)  Rows=553806.0 (553806), root[root]@[10.0.0.2]
  SELECT /*!N SQL_NO_CACHE */ * FROM `moodle`.`mdl_statements`

Count: 1  Time=3.00s (3s)  Lock=0.00s (0s)  Rows=25448.0 (2544, root[root]@[10.0.0.2]
  SELECT /*!N SQL_NO_CACHE */ * FROM `moodle`.`mdl_grade_grades_history`