MySQL Crash

Hi,
MySQL server alway crash after some time.
This the error in the windows EventViewer:
Changed limits: max_open_files: 2048 max_connections: 800 table_cache: 619

My Hardware is 2,5 GB of RAM
Where can I solve the problem?

(I've have 150 web sites hosted in this server... I've also Apache installed
on the same MySQL machine)

This is my my.ini:

port=3306

default-storage-engine=INNODB
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=800
query_cache_size=84M
table_cache=1520
tmp_table_size=30M
thread_cache_size=38

*** MyISAM Specific options


myisam_max_sort_file_size=100G
myisam_max_extra_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 Specific options ***

innodb_additional_mem_pool_size=6M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=3M
innodb_buffer_pool_size=512M
innodb_log_file_size=50M
innodb_thread_concurrency=10

thanks a lot

== Quote from Andrea (noreply@nospam.net)'s article
> Hi,
> MySQL server alway crash after some time.
> This the error in the windows EventViewer:
> Changed limits: max_open_files: 2048 max_connections: 800 table_cache: 619
> My Hardware is 2,5 GB of RAM
> Where can I solve the problem?
> (I've have 150 web sites hosted in this server... I've also Apache installed
> on the same MySQL machine)
> This is my my.ini:
> port=3306
> default-storage-engine=INNODB
> sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
> max_connections=800
> query_cache_size=84M
> table_cache=1520
> tmp_table_size=30M
> thread_cache_size=38
> *** MyISAM Specific options
> myisam_max_sort_file_size=100G
> myisam_max_extra_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 Specific options ***
> innodb_additional_mem_pool_size=6M
> innodb_flush_log_at_trx_commit=1
> innodb_log_buffer_size=3M
> innodb_buffer_pool_size=512M
> innodb_log_file_size=50M
> innodb_thread_concurrency=10
> thanks a lot


looks like you may have an innodb heavy server. here's what mysql has for heavy
innodb server:

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
long_query_time = 2
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
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
max_allowed_packet = 16M
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
open-files-limit = 8192
--
POST BY: lark with PHP News Reader

Hi,
no I've have a lot of MyISAM Tables.
All my Db are MAMBO.
I've only one inno tables for some db.
How can I modify my config?


> looks like you may have an innodb heavy server. here's what mysql has for
> heavy
> innodb server:
>
> 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
> long_query_time = 2
> 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
> 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
> max_allowed_packet = 16M
> key_buffer = 512M
> sort_buffer_size = 512M
> read_buffer = 8M
> write_buffer = 8M
> key_buffer = 512M
> sort_buffer_size = 512M
> read_buffer = 8M
> write_buffer = 8M
> open-files-limit = 8192
> --
> POST BY: lark with PHP News Reader

== Quote from Andrea (noreply@nospam.net)'s article
> Hi,
> no I've have a lot of MyISAM Tables.
> All my Db are MAMBO.
> I've only one inno tables for some db.
> How can I modify my config?
> > looks like you may have an innodb heavy server. here's what mysql has for
> > heavy
> > innodb server:
> >
> > 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
> > long_query_time = 2
> > 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
> > 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
> > max_allowed_packet = 16M
> > key_buffer = 512M
> > sort_buffer_size = 512M
> > read_buffer = 8M
> > write_buffer = 8M
> > key_buffer = 512M
> > sort_buffer_size = 512M
> > read_buffer = 8M
> > write_buffer = 8M
> > open-files-limit = 8192
> > --
> > POST BY: lark with PHP News Reader

well, if you have a lot of myisam tables, try something like this:

port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8


log-bin=mysql-bin
--
POST BY: lark with PHP News Reader

a little more that i should have added:
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
--
POST BY: lark with PHP News Reader

lark wrote:
> a little more that i should have added:
> innodb_buffer_pool_size = 16M
> innodb_additional_mem_pool_size = 2M
> # Set .._log_file_size to 25 % of buffer pool size
> innodb_log_file_size = 5M
> innodb_log_buffer_size = 8M
> innodb_flush_log_at_trx_commit = 1
> innodb_lock_wait_timeout = 50
> --
> POST BY: lark with PHP News Reader

one more thing:
to change configuration on windows machine, you need to edit a file
called "my.ini" usually in windows installation directory (c:\windows).