hi guys,
i have got a problem managing a huge database. the database is around 3Gb and the largest table is 1.8 gb. it is being used by a vbullien forum. The problem i amfacing here is the database suddenly seems to stop growing above 3Gb. old posts are getting deleted. I have checked its a 32 bit linux, so filesystem limit should not be a problem. i have attatched the my.cnf below and the details of table that is probably causing the problem.
show mysql> show table status like 'post' \G
*************************** 1. row ***************************
Name: post
Type: MyISAM
Row_format: Dynamic
Rows: 5270236
Avg_row_length: 360
Data_length: 1900205540
Max_data_length: 281474976710655
Index_length: 225594368
Data_free: 0
Auto_increment: 5395146
Create_time: 2005-12-22 14:42:01
Update_time: 2005-12-24 15:46:38
Check_time: 2005-12-24 15:39:22
Create_options: max_rows=4294967295 avg_row_length=361
Comment:
1 row in set (0.00 sec)
notice the data_free is 0. The server has 16 GB ram quad zeon and runs only mysql. 6 seperate apache servers collect data from this server all running the same site.
any help will be highly appreciated. i am sure LQ has a huge mysql db. did any LQ mainteners face this problem? here's the my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
skip-external-locking
safe-show-database
skip-innodb
max_connections = 1000
key_buffer = 6000M
myisam_sort_buffer_size = 1024M
join_buffer_size = 10M
read_buffer_size = 5M
sort_buffer_size = 10M
table_cache = 7000
thread_cache_size = 2304
wait_timeout = 28800
connect_timeout = 15
tmp_table_size = 2048M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 160000
bulk_insert_buffer_size = 32M
thread_concurrency = 16
query_cache_limit = 50M
query_cache_size = 2048M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
set-variable = long_query_time=15
set-variable = low-priority-updates=1
log-slow-queries = /var/lib/mysql/slow_queries.log
tmpdir = /home/mysqltmp
[mysqld_safe]
err-log=/var/log/mysqld.log
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M
Thanks, absolutely any help appreciated.
