Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
What is the error? That first line you quoted isn't an error message. So what is in the event logs? Also, when you say "server", do you mean the OS, as indicated by the title, or the MySQL service? –  John Gardeniers Apr 19 '10 at 22:21
    
You are right, the first line I quoted was actually a "warning" from the event logs. As soon as that message gets logged, the server crashes and reboots. There are no errors besides this warning. –  TruMan1 Apr 19 '10 at 23:11
    
What about the Windows even logs? Nothing there at all? –  John Gardeniers Apr 20 '10 at 1:14
add comment

3 Answers

up vote 2 down vote accepted

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:

  • improve cooling. Maybe open the server case and leave it running this way to prove the theory.
  • burn a memcheck live CD and do a quick RAM check. This requires a reboot, but I reckon your server is giving you daily opportunities, right? ;-)

Good luck! - Yves

share|improve this answer
add comment

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

share|improve this answer
    
Did you end up using the "socket = /tmp/mysql.sock" setting? I do not know what that is, but it is not in my current settings. –  TruMan1 Apr 19 '10 at 22:11
    
The socket functions are pretty clearly documented, as well as all of the other settings you are thinking of changing. It's best to know as much as you can about the systems you are managing, so would really recommend putting in some study time before throwing a bunch of settings changes you don't understand onto a production server. Also, what Yves said above: the entire server OS rebooting is not normal behaviour, and I would look to things outside of MySQL for the cause. One thing he didn't mention is to check your OS's open file limit. If MySQL using them all up, the OS might go belly up. –  ryandenki Aug 22 '11 at 1:54
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.