# MySQL crash ("opening tables" status) **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [June 21, 2007, 9:31am UTC](https://forums.percona.com/t/mysql-crash-opening-tables-status/363 "2007-06-21T09:31:45Z")\ **Posts on this page:** 1\ **Page:** 1
**Author:** ![rogeriopereira](https://avatars.discourse-cdn.com/v4/letter/r/6bbea6/32.png) [@rogeriopereira](https://forums.percona.com/u/rogeriopereira)\ **Post date:** [June 21, 2007, 9:31am UTC](https://forums.percona.com/t/mysql-crash-opening-tables-status/363/1 "2007-06-21T09:31:45Z")
Hi, I have a MySQL Server 4.0.26 Community Edition runnning in a Windows 2003 server. The hardware is 2 GB RAM, 120 GB IDE Disk, Pentium IV HT 3.06 Ghz MySQL crashs often in this server. It stops responding for new new threads after a week after its last restart, until it reaches the max connection limits (500). When I see the process list, I find that many threads are stuck in the “Opening tables” status, or “Closing tables” status. I have to restart the server when it happens. This server has around 200 active databases, the average number of simultaneous connections is 100, it runs both MyISAM and InnoDB tables. I have read many articles suggesting that I should increase table\_cache, key\_buffer\_size and other variables in order to get more cache hits. I did it but I still have these crashes. I read also that the file descriptors limit in Windows is 2048, but I don’t know how it relates to the problem. Table\_cache is 1500, but opened\_tables remain huge and open\_tables is low (!). I post here my.cnf file and the show status display in the exact moment the server stops responding for new threads. How should I improve server configuration in order to get this problem definitely solved? ==============my.cnf====================== [mysqld] skip-name-resolve log-slow-queries skip-locking set-variable = max\_connections=1000 set-variable = max\_connect\_errors=10 set-variable = table\_cache=800 set-variable = key\_buffer\_size=650M set-variable = max\_allowed\_packet=1M set-variable = sort\_buffer\_size=3M set-variable = read\_buffer\_size=1M set-variable = read\_rnd\_buffer\_size=2M set-variable = myisam\_sort\_buffer\_size=64M set-variable = query\_cache\_size=128M set-variable = long\_query\_time=3 set-variable = tmp\_table\_size=64M set-variable = thread\_cache\_size=128 set-variable = innodb\_buffer\_pool\_size=550M =============== show status ========================== VALUE VARIABLE\_NAME 21075 Aborted\_clients 11997 Aborted\_connects 1243908889 Bytes\_received 107979292 Bytes\_sent 560239 Com\_admin\_commands 133 Com\_alter\_table 0 Com\_analyze 0 Com\_backup\_table 3467 Com\_begin 2262303 Com\_change\_db 0 Com\_change\_master 0 Com\_check 1463 Com\_commit 24 Com\_create\_db 0 Com\_create\_function 33 Com\_create\_index 11423 Com\_create\_table 451702 Com\_delete 2225 Com\_delete\_multi 0 Com\_drop\_db 0 Com\_drop\_function 0 Com\_drop\_index 289 Com\_drop\_table 24 Com\_flush 24 Com\_grant 0 Com\_ha\_close 0 Com\_ha\_open 0 Com\_ha\_read 769121 Com\_insert 322 Com\_insert\_select 2 Com\_kill 2 Com\_load 0 Com\_load\_master\_data 0 Com\_load\_master\_table 18 Com\_lock\_tables 1368 Com\_optimize 0 Com\_purge 71 Com\_rename\_table 1 Com\_repair 320608 Com\_replace 0 Com\_replace\_select 0 Com\_reset 0 Com\_restore\_table 0 Com\_revoke 155 Com\_rollback 0 Com\_savepoint 3595129 Com\_select 1378901 Com\_set\_option 0 Com\_show\_binlog\_events 101 Com\_show\_binlogs 2633 Com\_show\_create 925 Com\_show\_databases 209715 Com\_show\_fields 222 Com\_show\_grants 180 Com\_show\_innodb\_status 5545 Com\_show\_keys 0 Com\_show\_logs 3 Com\_show\_master\_status 0 Com\_show\_new\_master 0 Com\_show\_open\_tables 14169 Com\_show\_processlist 0 Com\_show\_slave\_hosts 0 Com\_show\_slave\_status 2946 Com\_show\_status 50978 Com\_show\_tables 20121 Com\_show\_variables 0 Com\_slave\_start 0 Com\_slave\_stop 367 Com\_truncate 18 Com\_unlock\_tables 8167201 Com\_update 8 Com\_update\_multi 1434990 Connections 103319 Created\_tmp\_disk\_tables 4926 Created\_tmp\_files 413624 Created\_tmp\_tables 0 Delayed\_errors 0 Delayed\_insert\_threads 432 Delayed\_writes 1 Flush\_commands 668 Handler\_commit 94084 Handler\_delete 745913 Handler\_read\_first 156902358 Handler\_read\_key 345536042 Handler\_read\_next 5981052 Handler\_read\_prev 151094963 Handler\_read\_rnd 2738144011 Handler\_read\_rnd\_next 109246 Handler\_rollback 19945571 Handler\_update 37095543 Handler\_write 631884 Key\_blocks\_used 290843011 Key\_read\_requests 948670 Key\_reads 5296913 Key\_write\_requests 1695036 Key\_writes 266 Max\_used\_connections 0 Not\_flushed\_delayed\_rows 0 Not\_flushed\_key\_blocks 95 Open\_files 0 Open\_streams 73 Open\_tables 205236 Opened\_tables 8636 Qcache\_free\_blocks 45298184 Qcache\_free\_memory 22719701 Qcache\_hits 3289899 Qcache\_inserts 612440 Qcache\_lowmem\_prunes 303981 Qcache\_not\_cached 23491 Qcache\_queries\_in\_cache 60583 Qcache\_total\_blocks 42272899 Questions NULL Rpl\_status 57927 Select\_full\_join 9 Select\_full\_range\_join 115798 Select\_range 32 Select\_range\_check 1379336 Select\_scan 0 Slave\_open\_temp\_tables OFF Slave\_running 0 Slow\_launch\_threads 663 Slow\_queries 785 Sort\_merge\_passes 154019 Sort\_range 151199174 Sort\_rows 786228 Sort\_scan 15130381 Table\_locks\_immediate 167130 Table\_locks\_waited 0 Threads\_cached 267 Threads\_connected 344 Threads\_created 209 Threads\_running 664448 Uptime Thanks in advance! Rogerio