Hi all,
I have a problem with configuring my.ini file. I am trying to assign all the memory available on the machine to mysql. Right now I used MySQL Instant Config Wizard to create ini file which is shown below and the database is Innodb. With this config, MySQL is very stable but it is only using about 1.3GB of memory. Widnows/other process is using 1GB which leaves about 1.7GB is still not used.
The problem is when I increase innodb_buffer_pool_size, mysql returns following error and halts as soon as I run any query or command.
Error Code : 2006
MySQL server has gone away
I also increased max_allowed_packet because this may cause this error, but didn’t work for me. Dose anyone have ideas how I can increase mysql memory use?
Server OS: windows server 2003 R2 Standard Edition SP2
RAM: 4.00GB (believe or not this 32bit windows recognizes all 4GB)
CPU: Intel Xeon E5320
MySQL: 5.0.45-community-nt
This is my.ini
[client]
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir=“C:/Program Files/MySQL/MySQL Server 5.0/”
datadir=“C:/Program Files/MySQL/MySQL Server 5.0/Data/”
default-character-set=utf8
default-storage-engine=INNODB
sql-mode=" STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTI ON "
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=185M
thread_cache_size=8
key_buffer_size=32M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_data_home_dir=“D:/MySQL InnoDB Datafiles/”
innodb_additional_mem_pool_size=25M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=12M
innodb_buffer_pool_size=1172M
innodb_log_file_size=586M
This is MySQL server’s status
Variable_name Value
Aborted_clients 2
Aborted_connects 0
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 138
Bytes_sent 336
Com_admin_commands 0
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_call_procedure 0
Com_change_db 0
Com_change_master 0
Com_check 0
Com_checksum 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_create_user 0
Com_dealloc_sql 0
Com_delete 0
Com_delete_multi 0
Com_do 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_drop_user 0
Com_execute_sql 0
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 0
Com_insert_select 0
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_preload_keys 0
Com_prepare_sql 0
Com_purge 0
Com_purge_before_date 0
Com_rename_table 0
Com_repair 0
Com_replace 0
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_revoke_all 0
Com_rollback 0
Com_savepoint 0
Com_select 0
Com_set_option 2
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_charsets 0
Com_show_collations 0
Com_show_column_types 0
Com_show_create_db 0
Com_show_create_table 0
Com_show_databases 1
Com_show_errors 0
Com_show_fields 0
Com_show_grants 0
Com_show_innodb_status 0
Com_show_keys 0
Com_show_logs 0
Com_show_master_status 0
Com_show_ndb_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_privileges 0
Com_show_processlist 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 1
Com_show_storage_engines 0
Com_show_tables 0
Com_show_triggers 0
Com_show_variables 0
Com_show_warnings 0
Com_slave_start 0
Com_slave_stop 0
Com_stmt_close 0
Com_stmt_execute 0
Com_stmt_fetch 0
Com_stmt_prepare 0
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 0
Com_unlock_tables 0
Com_update 0
Com_update_multi 0
Com_xa_commit 0
Com_xa_end 0
Com_xa_prepare 0
Com_xa_recover 0
Com_xa_rollback 0
Com_xa_start 0
Compression OFF
Connections 6
Created_tmp_disk_tables 0
Created_tmp_files 5
Created_tmp_tables 2
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 0
Handler_discover 0
Handler_prepare 0
Handler_read_first 0
Handler_read_key 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 12
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 143
Innodb_buffer_pool_pages_data 90
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 4
Innodb_buffer_pool_pages_free 74918
Innodb_buffer_pool_pages_latched 0
Innodb_buffer_pool_pages_misc 0
Innodb_buffer_pool_pages_total 75008
Innodb_buffer_pool_read_ahead_rnd 1
Innodb_buffer_pool_read_ahead_seq 0
Innodb_buffer_pool_read_requests 27607
Innodb_buffer_pool_reads 39
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 4
Innodb_data_fsyncs 19
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 3657728
Innodb_data_reads 100
Innodb_data_writes 19
Innodb_data_written 136704
Innodb_dblwr_pages_written 4
Innodb_dblwr_writes 4
Innodb_log_waits 0
Innodb_log_write_requests 0
Innodb_log_writes 5
Innodb_os_log_fsyncs 11
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 2560
Innodb_page_size 16384
Innodb_pages_created 0
Innodb_pages_read 90
Innodb_pages_written 4
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 0
Innodb_row_lock_time_avg 0
Innodb_row_lock_time_max 0
Innodb_row_lock_waits 0
Innodb_rows_deleted 0
Innodb_rows_inserted 0
Innodb_rows_read 10
Innodb_rows_updated 0
Key_blocks_not_flushed 0
Key_blocks_unused 28694
Key_blocks_used 0
Key_read_requests 0
Key_reads 0
Key_write_requests 0
Key_writes 0
Last_query_cost 10.499000
Max_used_connections 3
Not_flushed_delayed_rows 0
Open_files 0
Open_streams 0
Open_tables 1
Opened_tables 0
Prepared_stmt_count 0
Qcache_free_blocks 0
Qcache_free_memory 0
Qcache_hits 0
Qcache_inserts 0
Qcache_lowmem_prunes 0
Qcache_not_cached 0
Qcache_queries_in_cache 0
Qcache_total_blocks 0
Questions 2451
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 0
Select_range_check 0
Select_scan 2
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 0
Sort_rows 0
Sort_scan 0
Ssl_accept_renegotiates 0
Ssl_accepts 0
Ssl_callback_cache_hits 0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects 0
Ssl_connect_renegotiates 0
Ssl_ctx_verify_depth 0
Ssl_ctx_verify_mode 0
Ssl_default_timeout 0
Ssl_finished_accepts 0
Ssl_finished_connects 0
Ssl_session_cache_hits 0
Ssl_session_cache_misses 0
Ssl_session_cache_mode NONE
Ssl_session_cache_overflows 0
Ssl_session_cache_size 0
Ssl_session_cache_timeouts 0
Ssl_sessions_reused 0
Ssl_used_session_cache_entries 0
Ssl_verify_depth 0
Ssl_verify_mode 0
Ssl_version
Table_locks_immediate 1534
Table_locks_waited 0
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 1
Threads_connected 2
Threads_created 3
Threads_running 1
Uptime 16477
Uptime_since_flush_status 16477