MySQL社区

标题: mysql数据库停留在opening table是生么原因造成的？ [打印本页]

作者: zhp1131 时间: 2011-11-2 18:21
标题: mysql数据库停留在opening table是生么原因造成的？
請指點
CUP P4 3.0 超線程雙核，內存4G 裝有3個mysql數據庫服務，其中一個數據庫配置如下：
[mysqld]
basedir=D:/MySQL
#bind-address=172.16.5.44
datadir=D:/MySQL/data
#language=D:/MySQL/share/your language directory
default-character-set=latin1
default-storage-engine=INNODB
#slow query log
#tmpdir
#port=3306

#log-bin=mysql-bin
# lsc 10-30
#log_slow_queries=5
#log_slow=log=D:\Log\Slow.log

skip-locking
skip-name-resolve

back_log=50
#(Unknown optionconnect_timeout=10
key_buffer_size=128M
thread_stack = 256K
thread_cache_size=48
thread_cache=48

join_buffer_size=8M
sort_buffer_size=2M
max_connections=100
wait_timeout=120
interactive_timeout=120
#max_connect_errors=1000
long_query_time=1
max_heap_table_size=98M
tmp_table_size=256M

max_allowed_packet=1M
net_buffer_length=16K
thread_concurrency=4

#*** MyISAM Specific options
myisam_max_sort_file_size=100M
myisam_max_extra_sort_file_size=100M
myisam_sort_buffer_size=64M
myisam_repair_threads = 1
read_buffer_size = 256K
read_rnd_buffer_size=2M
sort_buffer_size=2M

open_files_limit=512

bulk_insert_buffer_size = 138M

query_cache_type=2
query_cache_size=16M
query_cache_limit=128k

table_cache=256

#*** INNODB Specific options ***
innodb_additional_mem_pool_size=6M
innodb_buffer_pool_size=512M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=3M
innodb_log_file_size=100M
innodb_thread_concurrency=8
innodb_lock_wait_timeout = 120
innodb_file_io_threads=10
innodb_open_files=800

innodb_max_dirty_pages_pct = 90

innodb_file_io_threads=10

server-id=3
master-host=172.16.250.250
master-user=backup
master-password=1234

#Flush tables to disk between SQL commands.
flush
#INSERT/DELETE/UPDATE has lower priority than selects.
low-priority-updates
#Lock mysqld in memory.(=Don't swap.)
memlock

[WinMySQLadmin]
Server=D:/MySQL/bin/mysqld-nt.exe
user=ellbl
password=szsunlbl

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

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

[mysqldump]
quick
set-variable = max_allowed_packet=16M
SQL_QUOTE_SHOW_CREATE=0

[mysql]
#no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192

作者: kider 时间: 2011-11-3 09:57
什么情况下停留？

作者: lyq999999 时间: 2011-11-3 18:48
同问。我也遇到同样问题

作者: zhp1131 时间: 2011-11-4 18:25
大约1小时左右有一两个或多个WEB（apache+php+mysql）用户使用时，用户使用show指令就会卡，不一会儿就停留在opening tables状态，用户无法连接数据库，线程很快就被站满。

欢迎光临 MySQL社区 (http://www.mysqlpub.com/)