mysql err log的一个菜鸟级问题

xihuowo

其中里面有大量的 /usr/sbin/mysqld: Sort aborted
请问各位大鸟，这是啥子意思，是啥子原因造成的，不胜感激！

070905  9:36:39 [Warning] 'user' entry 'root@mainDBServer' ignored in --skip-name-resolve mode.
070905  9:36:39 [Warning] 'user' entry '@mainDBServer' ignored in --skip-name-resolve mode.
070905  9:36:39 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.24a-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-5.0.24-r1
070905 11:07:56 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1479_0.MYI'; try to repair it
070905 11:07:57 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1479_13.MYI'; try to repair it
070905 11:08:00 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1479_1.MYI'; try to repair it
070905 11:08:00 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1479_8.MYI'; try to repair it
070905 11:08:32 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1479_5.MYI'; try to repair it
070905 11:08:32 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1479_0.MYI'; try to repair it
070905 11:08:33 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1479_1.MYI'; try to repair it
070905 11:08:33 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1479_2.MYI'; try to repair it
070905 11:08:43 [ERROR] /usr/sbin/mysqld: Table '' is marked as crashed and should be repaired
070905 11:08:43 [ERROR] /usr/sbin/mysqld: Sort aborted
070905 15:39:28 [Note] /usr/sbin/mysqld: Normal shutdown

070905 15:39:28 [ERROR] /usr/sbin/mysqld: Sort aborted
070905 15:39:28 [ERROR] /usr/sbin/mysqld: Sort aborted
070905 15:39:28 [ERROR] /usr/sbin/mysqld: Sort aborted
070905 15:39:28 [ERROR] /usr/sbin/mysqld: Sort aborted
070905 15:39:28 [ERROR] /usr/sbin/mysqld: Sort aborted
070905 15:39:28 [ERROR] /usr/sbin/mysqld: Sort aborted
070905 15:39:28 [ERROR] /usr/sbin/mysqld: Sort aborted
070905 15:39:28 [ERROR] /usr/sbin/mysqld: Sort aborted
070905 15:39:28 [ERROR] /usr/sbin/mysqld: Sort aborted
070905 15:39:28 [ERROR] /usr/sbin/mysqld: Sort aborted
070905 15:39:30 [Note] /usr/sbin/mysqld: Shutdown complete

070905 15:39:31 [Warning] 'user' entry 'root@mainDBServer' ignored in --skip-name-resolve mode.
070905 15:39:31 [Warning] 'user' entry '@mainDBServer' ignored in --skip-name-resolve mode.
070905 15:39:31 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.24a-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-5.0.24-r1
070906  9:35:42 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:35:42 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:36:06 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:36:06 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:36:06 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:36:13 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:36:13 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:37:30 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:38:16 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:40:51 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:41:12 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:44:47 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:44:49 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:44:51 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:45:44 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:45:56 [ERROR] /usr/sbin/mysqld: Sort aborted
070906  9:45:58 [ERROR] /usr/sbin/mysqld: Sort aborted

hyran

请问 这个问题 最后怎么解决的?

我刚刚也遇到了类似的 问题，请高手帮忙啊 !

------------------------------
080616  1:19:46 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.51a-community-log'  socket: '/usr/local/mysql/mysql.sock'  port: 3306  MySQL Community Edition (GPL)
080616 15:38:02 [Note] /usr/sbin/mysqld: Normal shutdown

080616 15:38:02 [Warning] Aborted connection 827541 to db: 'bbs' user: 'mysql' host: '210.74.189.230' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 827515 to db: 'bbs' user: 'mysql' host: '210.74.189.230' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 827624 to db: 'pblog' user: 'mysql' host: '192.168.168.224' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 827492 to db: 'bbs' user: 'mysql' host: '210.74.189.230' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 763068 to db: 'bbs' user: 'mysql' host: '210.74.189.230' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 791202 to db: 'bbs' user: 'mysql' host: '210.74.189.230' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 814082 to db: 'bbs' user: 'mysql' host: '210.74.189.230' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 809727 to db: 'bbs' user: 'mysql' host: '210.74.189.230' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 796401 to db: 'bbs' user: 'mysql' host: '210.74.189.230' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 824267 to db: 'bbs' user: 'mysql' host: '210.74.189.230' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 824411 to db: 'bbs' user: 'mysql' host: '210.74.189.230' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 823743 to db: 'bbs' user: 'mysql' host: '192.168.168.229' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 825319 to db: 'bbs' user: 'mysql' host: '210.74.189.230' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 827299 to db: 'pblog' user: 'mysql' host: '192.168.168.224' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 827036 to db: 'bbs' user: 'mysql' host: '210.74.189.230' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 827300 to db: 'bbs' user: 'mysql' host: '210.74.189.230' (Got timeout reading communication packets)
080616 15:38:02 [Warning] Aborted connection 827329 to db: 'bbs' user: 'mysql' host: '210.74.189.230' (Got timeout reading communication packets)
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
080616 15:38:02 [ERROR] /usr/sbin/mysqld: Sort aborted
----------------------------------------

yueliangdao0608

1、Adjust parameters about memory.
2、Adjust your sql statement.

wildlily980

sort aborted排序放弃的意思.
查查你的排序语句吧.可能是超时了.另外先repair table table_name

yueliangdao0608

还是调整SQL语句是根本。我以前也碰到过。

hyran

我改了几个参数,在观察看一看。

针对解决办法如下：
操作时间：23:48 2008-6-16
@@@@@@@@@@@@@@@@@@@@@@
max_allowed_packet = 32M
sort_buffer_size = 8M
join_buffer_size = 32M


sort_buffer_size = 8M，加到64M
max_allowed_packet = 32M，加到64M
join_buffer_size = 32M，加到64M

hyran

调了两天 还是会有这样的错误。
080619  9:37:35 [Warning] Aborted connection 406648 to db: 'bbs123' user: 'webmysql' host: '192.168.10.230' (Got timeout

reading communication packets)
080619  9:38:24 [Warning] Aborted connection 407756 to db: 'bbs123' user: 'webmysql' host: '192.168.10.230' (Got timeout

reading communication packets)
080619  9:39:04 [Warning] Aborted connection 408777 to db: 'bbs123' user: 'webmysql' host: '192.168.10.230' (Got timeout

reading communication packets)
080619  9:42:14 [Warning] Aborted connection 414206 to db: 'bbs123' user: 'webmysql' host: '192.168.10.230' (Got timeout

reading communication packets)
080619  9:44:07 [Warning] Aborted connection 417813 to db: 'bbs123' user: 'webmysql' host: '192.168.10.230' (Got timeout

reading communication packets)
080619  9:44:50 [Warning] Aborted connection 419634 to db: 'bbs123' user: 'webmysql' host: '192.168.10.230' (Got timeout

reading communication packets)
080619  9:44:56 [Warning] Aborted connection 419892 to db: 'bbs123' user: 'webmysql' host: '192.168.10.230' (Got timeout

reading communication packets)
080619  9:45:13 [Warning] Aborted connection 420538 to db: 'bbs123' user: 'webmysql' host: '192.168.10.230' (Got timeout

reading communication packets)
而且负载经常比较高经常跑过6，7，在高的时候show processlist 连接数并不多 都是低于30。
数据库版本是：
Server version: 5.0.51a 平台 AS4
my.cnf
[root@localhost ~]# cat /etc/my.cnf|grep -v "#"
[client]
port            = 3306
socket          = /usr/local/mysql/mysql.sock

[mysqld]
port            = 3306
socket          = /usr/local/mysql/mysql.sock
datadir         = /usr/local/mysql
interactive_timeout=300
wait_timeout=300
lower_case_table_names = 1

back_log = 500
skip-name-resolve

max_connections = 2000
max_connect_errors = 50
table_cache = 2048
max_allowed_packet = 64M
binlog_cache_size = 8M
max_heap_table_size = 128M
tmp_table_size = 256M
sort_buffer_size = 16M
join_buffer_size = 32M
thread_cache_size = 128
thread_concurrency = 8
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 64M
ft_min_word_len = 4
thread_stack = 192K
transaction_isolation = REPEATABLE-READ

log             = /var/lib/mysql/log/log
log-slow-queries= /var/lib/mysql/log/bbs-slow.log
long_query_time = 5
log-queries-not-using-indexes
log_long_format

log-bin         = /var/lib/mysql/binlog/bbs-bin123
server-id       = 230
binlog-do-db = bbs123

binlog-ignore-db = mysql
relay-log = /var/lib/mysql/binlog/bbs-relay-bin123
log-warnings
skip-slave-start

key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

skip-bdb

innodb_additional_mem_pool_size = 100M
innodb_buffer_pool_size = 1G
innodb_data_file_path = ibdata1:1000M;ibdata2:1000M:autoextend
innodb_data_home_dir = /usr/local/mysql/
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 200M
innodb_log_files_in_group = 2
innodb_log_group_home_dir = /usr/local/mysql/
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 8M
write_buffer = 8M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open_files_limit = 40960
[root@localhost ~]#

yueliangdao0608

How large is your memory?

yueliangdao0608

http://bbs.chinaunix.net/thread-643686-1-1.html

hyran

一共 6G内存 ！

Got timeout reading communication packets 看来 真的应该 让开发人员调整SQL语句了