Chinaunix

标题: 紧急,mysqld错误 [打印本页]

作者: yxyup 时间: 2008-08-14 09:06
标题: 紧急,mysqld错误
mysqld.log中突然出现以下错误,请问是什么原因造成的? 谢谢!

Number of processes running now: 0
080813 21:54:04  mysqld restarted
080813 21:54:06  InnoDB: Started; log sequence number 0 43655
^G/usr/local/mysql/bin/mysqld: Out of memory (Needed 59381760 bytes)
080813 21:54:07 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run
080813 21:54:07 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
080813 21:54:07 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.26-standard'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Edition - Standard (GPL)

作者: yueliangdao0608 时间: 2008-08-14 09:15
There are two points here.

1、^G/usr/local/mysql/bin/mysqld: Out of memory (Needed 59381760 bytes)
2、080813 21:54:07 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run

Although the last is an warning,you should fix it.

作者: yxyup 时间: 2008-08-14 09:15
参数据配置如下(物理内存 2G)

max_connections       = 500
max_connect_errors    = 100000
key_buffer             = 256M
myisam_sort_buffer_size = 51200000
join_buffer_size       = 32M
read_buffer_size       = 64M
sort_buffer_size       = 32M
table_cache          = 1000
thread_cache_size    = 80
max_join_size          = 512000000
max_binlog_cache_size = 512000000
tmp_table_size       = 102400000
max_heap_table_size    = 102400000
read_rnd_buffer_size = 5242880
bulk_insert_buffer_size = 64M
thread_concurrency    = 2
query_cache_limit    = 4000000
query_cache_size       = 0
query_cache_type       = 1
wait_timeout          = 7200
interactive_timeout    = 7200

作者: yejr 时间: 2008-08-14 09:18
join_buffer_size       = 32M
read_buffer_size       = 64M
sort_buffer_size       = 32M

这是每session都要分配的内存，减小到1M以下再调。

作者: yueliangdao0608 时间: 2008-08-14 09:28
This is my configuration.
join_buffer_size       = 2M
read_buffer_size       = 2M
sort_buffer_size       = 32M

They belong to thread level,so you should decrease these parameters.

作者: 枫影谁用了 时间: 2008-08-14 09:30

原帖由 yejr 于 2008-8-14 09:18 发表
join_buffer_size       = 32M
read_buffer_size       = 64M
sort_buffer_size       = 32M

这是每session都要分配的内存，减小到1M以下再调。

每个session固定分配的内存？是最大可用值吧。

sort_buffer_size = 256M。

我刚看了一下我的配置read的是32.

如果是每个session固定分配，如果我有一百个连接，那就要占用内存1000*256M??应该是动态分配可用值吧。

作者: yueliangdao0608 时间: 2008-08-14 09:32
标题: 回复 #6 枫影谁用了的帖子
如果100个连接同时进行QUERY呢？

作者: 枫影谁用了 时间: 2008-08-14 09:37

原帖由 yueliangdao0608 于 2008-8-14 09:32 发表
如果100个连接同时进行QUERY呢？

如果这样的话倒是。

作者: showsa 时间: 2008-08-14 09:56
试试：

read_buffer_size = 128K
read_rnd_buffer_size = 1M
sort_buffer_size = 2M
join_buffer_size = 2M

其中 read_buffer_size 你可以调整，这个在不同情况下性能差别比较大
并不是越大越好，事实上可能会降低性能

你可以测试32k 64k 128k ... 看哪个值最适合你的系统

祝好运

作者: yxyup 时间: 2008-08-14 10:30
好的,谢谢各位

作者: ruochen 时间: 2008-08-14 11:22
080813 21:54:07 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run
080813 21:54:07 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them

这两个错误还是第一次见

作者: gogo407 时间: 2008-08-14 14:31
标题: 回复 #11 ruochen 的帖子
080813 21:54:07 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run
080813 21:54:07 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
这个可能是连带反映
:em11:
参数不是越大越好（毕竟你的内存是个有限值）
呵呵:em11:

作者: ruochen 时间: 2008-08-14 15:31

原帖由 gogo407 于 2008-8-14 14:31 发表
080813 21:54:07 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run
080813 21:54:07 [Warning] Can't open and lo ...

哦，谢谢你的解释

080813 21:54:06 InnoDB: Started; log sequence number 0 43655
^G/usr/local/mysql/bin/mysqld: Out of memory (Needed 59381760 bytes)

其实第一个错误是有Out of memory 的
解决错误的时候肯定是从第一个开始慢慢向后排除的

作者: yxyup 时间: 2008-08-15 09:14
参数已做调整,现在如下,

[mysqld]
#delay_key_write       = all
max_connections       = 500
max_connect_errors    = 100000
key_buffer             = 256M
myisam_sort_buffer_size = 51200000
join_buffer_size       = 2097152
read_buffer_size       = 4194304
sort_buffer_size       = 4194304
table_cache          = 1000
thread_cache_size    = 80
max_join_size          = 512000000
max_binlog_cache_size = 512000000
tmp_table_size       = 102400000
max_heap_table_size    = 102400000
read_rnd_buffer_size = 5242880
bulk_insert_buffer_size = 64M
thread_concurrency    = 2
query_cache_limit    = 4000000
query_cache_size       = 0
query_cache_type       = 1
wait_timeout          = 7200
interactive_timeout    = 7200
skip-innodb
skip-bdb
[root@5co2 data]#

但问题并没有解决,情况如下

080814 22:00:18  mysqld started
080814 22:00:18 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run
080814 22:00:18 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
080814 22:00:19 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.26-standard'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Edition - Standard (GPL)

Number of processes running now: 0
080814 22:53:47  mysqld restarted
080814 22:53:48 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run
080814 22:53:48 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
080814 22:53:48 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.26-standard'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Edition - Standard (GPL)

Number of processes running now: 0
080814 23:17:23  mysqld restarted
^G/usr/local/mysql/bin/mysqld: Out of memory (Needed 237527040 bytes)
^G/usr/local/mysql/bin/mysqld: Out of memory (Needed 178145280 bytes)
^G/usr/local/mysql/bin/mysqld: Out of memory (Needed 133607424 bytes)
^G/usr/local/mysql/bin/mysqld: Out of memory (Needed 100205568 bytes)
^G/usr/local/mysql/bin/mysqld: Out of memory (Needed 75153408 bytes)
080814 23:17:23 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run
080814 23:17:23 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
080814 23:17:23 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.26-standard'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Edition - Standard (GPL)

[ 本帖最后由 yxyup 于 2008-8-15 09:49 编辑 ]

作者: 枫影谁用了 时间: 2008-08-15 09:25
080814 21:52:12 [ERROR] /usr/local/mysql/bin/mysqld: unknown option '--skip-innode'

这里笔误？应该是INNODB

作者: yxyup 时间: 2008-08-15 09:50

原帖由 枫影谁用了 于 2008-8-15 09:25 发表
080814 21:52:12 [ERROR] /usr/local/mysql/bin/mysqld: unknown option '--skip-innode'

这里笔误？应该是INNODB

这是我写错了,但这个不是问题根源

[ 本帖最后由 yxyup 于 2008-8-15 13:39 编辑 ]

作者: 石展 时间: 2008-08-23 14:42
兄弟,你是直接把mysql4的var拷到mysql5上使用的吧?如果是的话，得先rebuild一下．

作者: gogo407 时间: 2008-08-25 17:47

原帖由石展于 2008-8-23 14:42 发表
兄弟,你是直接把mysql4的var拷到mysql5上使用的吧?如果是的话，得先rebuild一下．

lz 应该不至于这样吧，关注：）

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)