1

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

3
  • 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? Apr 19, 2010 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, 2010 at 23:11
  • What about the Windows even logs? Nothing there at all? Apr 20, 2010 at 1:14

3 Answers 3

2

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

0

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

2
  • 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, 2010 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, 2011 at 1:54
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .