I have MySQL 5.0 installed on a Windows 2008 machine (3GB RAM). My server crashes on a regular basis (almost once a day) always with this only error:
Changed limits: max_open_files: 2048 max_connections: 800 table_cache: 619
I did not use the heavy InnoDB .ini file, although I am rethinking that I should have? I am worried that big configuration changes will make my current sites stop working. What should I do?
Here is my current ini settings:
default-character-set=latin1
default-storage-engine=INNODB
max_connections=800
query_cache_size=84M
table_cache=1520
tmp_table_size=30M
thread_cache_size=38
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=30M
key_buffer_size=129M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=6M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=3M
innodb_buffer_pool_size=250M
innodb_log_file_size=50M
innodb_thread_concurrency=10
Here is some extra information from phpMyAdmin:
Server: MYSERVER (localhost via TCP/IP)
Server version: 5.0.90-community-nt
Protocol version: 10
MySQL charset: UTF-8 Unicode (utf8)
Microsoft-IIS/7.0
MySQL client version: 5.0.90
PHP extension: mysqli
From my research, it seems to me that this error is saying that the OS hard coded limits keeps getting hit and that I should use the innoDB heavy .ini file. However, I do not know what the implications will be for my sites using MySQL. Below is the heavy innoDB configurations I am thinking of replacing it with, can anyone tell me what this will mean for my sites with existing databases? They are all InnoDB and even all their tables are InnoDB. Am I on the right track?
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
back_log = 50
max_connections = 100
max_connect_errors = 10
table_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default_table_type = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
log_slow_queries
long_query_time = 2
log_long_format
server-id = 1
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
skip-federated
skip-bdb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
I don't think MySQL should ever kill your operating system, even if it's misbehaving. What you describing is not normal for a healthy server. In the worst case, the MySQL instance should die, not the whole server.
You should investigate for possible hardware problems, such as insufficient cooling or bad RAM chips. So you should rule those out first.
If you agree that this might indeed be a hardware problem, here is what you could do:
Good luck! - Yves
It should improve response and performance for all sites using an innodb database. They will not stop working because of these changes. I do suggestion doing the tunning during off peak hours. The config list is a good start, but ultimately you will have to fine tune it to meet your needs best. I made use of the page of phpmyadmin to turn the server.
I'd change the innodb_buffer_pool_size = 2G to 1GB since you only have 3GB of ram and you need enough for the system to run
Be careful changing the INI file.
The new one you show doesn't include some important settings like the default character set; are you showing only the changed portion?
Also, changing the open-files-limit to 8192 seems strange if you are getting warnings when the OS is crashing with open files being 2048.