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.
