List:General Discussion« Previous MessageNext Message »
From:ML Date:May 7 2003 5:51pm
Subject:Help for a server that slow down when there are really many files opened
View as plain text  
Hello MySQL people,

I'm looking for a big Help to a problem that I have with my MySQL server. I
use MySQL server since more than two years, so I'm almost secure of what I
do. I have read the mysql help, some books, searched the Net, but nothing
helped me, you are my last chance.

I'm running a Linux server with Redhat v7.3, all seems well configured. The
machine is a P4 2.0Ghz, 1GB DDR Ram, 80GB HD. The main application of this
server is for a forum under PHP+MySQL (vBulletin).

The problem is that the server runs smoothly (really smoothly) for a while
(tipically 1-2 hours, sometime more, sometime less) then it abnormally
becomes very very slow (sometimes the server don't respond anymore) for 2-6
minutes, than the cycle repeats...

The only thing that I found is that the server slow down when there are
thousand of files opened.
Tipically there are 50k of files opened, but when the server "slow down"
there are 500.000 files opened.
With "lsof" I noticed that these files are owned by mysql and are related to
the forum's database.
The dump of lsof (when the server slow down) shows thousand (500k) of line
like these:

mysqld    22638   root  605u   REG        3,2      4104    1151923
/var/lib/mysql/postare_it_2/am_banner.MYD
mysqld    22638   root  606u   REG        3,2   4966204    1135766
/var/lib/mysql/postare_it_1/thread.MYD
mysqld    22638   root  607u   REG        3,2     87381    1135708
/var/lib/mysql/postare_it_1/forum.MYD
mysqld    22638   root  608u   REG        3,2  12706704    1151971
/var/lib/mysql/postare_it_2/smsaccesslog.MYD
mysqld    22638   root  609u   REG        3,2 364025988    1135719
/var/lib/mysql/postare_it_1/post.MYD
mysqld    22638   root  610u   REG        3,2   4966204    1135766
/var/lib/mysql/postare_it_1/thread.MYD
mysqld    22638   root  611u  unix 0xe8e6b040            347131469
/var/lib/mysql/mysql.sock
mysqld    22638   root  612u   REG        3,2 364025988    1135719
/var/lib/mysql/postare_it_1/post.MYD
mysqld    22638   root  613u   REG        3,2    436288    1135701
/var/lib/mysql/postare_it_1/customavatar.MYD
mysqld    22638   root  614u  unix 0xcc8e3a60            346359422
/var/lib/mysql/mysql.sock
mysqld    22638   root  615u   REG        3,2     87381    1135708
/var/lib/mysql/postare_it_1/forum.MYD
mysqld    22638   root  616u   REG        3,2 364025988    1135719
/var/lib/mysql/postare_it_1/post.MYD
mysqld    22638   root  617u   REG        3,2     15564    1151979
/var/lib/mysql/postare_it_2/smsgateway.MYD
mysqld    22638   root  618u   REG        3,2    436288    1135701
/var/lib/mysql/postare_it_1/customavatar.MYD
mysqld    22638   root  619u  unix 0xc81260e0            344858133
/var/lib/mysql/mysql.sock
mysqld    22638   root  620u   REG        3,2   4966204    1135766
/var/lib/mysql/postare_it_1/thread.MYD
mysqld    22638   root  621u   REG        3,2     52844    1151956
/var/lib/mysql/postare_it_2/session.MYD
mysqld    22638   root  622u   REG        3,2     52844    1151956
/var/lib/mysql/postare_it_2/session.MYD
mysqld    22638   root  623u   REG        3,2    436288    1135701
/var/lib/mysql/postare_it_1/customavatar.MYD


Previously the server runs under MySQL v3.23.55, some days ago I upgraded to
MySQL v4.0.12 but the problem still occurs.

This is my /etc/my.cnf file (I give many memory to mysql because the forum
is the core application of the server):

# cat /etc/my.cnf
[mysqld]
port=3306
skip-locking
set-variable = key_buffer_size=256M
set-variable = max_allowed_packet=1M
set-variable = table_cache=1024
set-variable = sort_buffer_size=8M
set-variable = read_buffer_size=16M
set-variable = myisam_sort_buffer_size=128M
set-variable = thread_cache=32
set-variable = thread_concurrency=4
server-id = 1
set-variable = join_buffer=8M
set-variable = max_connections=400
set-variable = max_user_connections=200
set-variable = max_connect_errors=20
skip-innodb
set-variable = wait_timeout=240
log-slow-queries
set-variable = query_cache_size=16M


MySQL server has only two database to work with.
The two databases have several (30-40) small tables and these big tables:

Name, Type, Row_format, Rows, Avg_row_length, Data_length, Max_data_length,
Index_length
attachment, MyISAM, Dynamic, 11499, 31929, 367160912, 4294967295, 71680
post, MyISAM, Dynamic, 1242411, 293, 364372060, 4294967295, 53723136
search, MyISAM, Dynamic, 3130, 4838, 15622280, 4294967295, 101376
searchindex, MyISAM, Fixed, 7165457, 11, 78820027, 47244640255, 105419776
thread, MyISAM, Dynamic, 55907, 88, 4973740, 4294967295, 1642496
user, MyISAM, Dynamic, 24339, 147, 3591552, 4294967295, 802816
userfield, MyISAM, Fixed, 24334, 3505, 85290670, 15053860372479, 200704
word, MyISAM, Fixed, 466011, 55, 25630605, 236223201279, 13420544


I could give any additional info, any help would be really greatly
appreciated.

Best regards.

Thread
Help for a server that slow down when there are really many files openedML7 May
  • Re: Help for a server that slow down when there are really many files openedML7 May