Announcement

Announcement Module
Collapse
No announcement yet.

maximize memory usage for mysql and mysql server has gone away error

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • maximize memory usage for mysql and mysql server has gone away error

    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
Working...
X