Comments

  1. Venkatesh says

    Hi,

    My Current MYSQL Config as below. Could you please provide us suggestions to improve the performance.

    Hardware:

    32 GB – RAM
    12 Core Processor
    1TB – Disk

    My.cnf
    ==========

    freehold:

    [client]
    port=3323
    socket=/tmp/mysqld_mdmp.sock
    character-sets-dir=/home/mysqld/sys/5.5.8/share/mysql/charsets

    [mysqld]
    port=3323
    server-id=1
    socket=/tmp/mysqld_mdmp.sock
    datadir=/home/mysqld/mdmp/data
    basedir=/home/mysqld/sys/5.5.8
    tmpdir=/home/mysqld/mdmp/tmp
    slow_query_log=mdmp_slow_queries.log

    # binary logging and replication
    binlog_cache_size=1M
    binlog_format=MIXED
    log-slave-updates
    log-bin=mdmp_freehold_binlog
    sync_binlog=1
    relay-log=mdmp_freehold_relaylog
    max_relay_log_size=100M
    #read-only

    # limits
    max_connections=1000
    #old-passwords
    #lower_case_table_names=1
    general_log = ON
    max_heap_table_size=128M
    event_scheduler=ON
    query_cache_limit = 10M
    open_files_limit = 8088
    back_log=128
    max_allowed_packet = 50M

    # performance
    sort_buffer_size = 8M
    read_buffer_size = 2M
    query_prealloc_size = 65536
    read_rnd_buffer_size = 8M
    table_open_cache = 1024
    thread_cache_size = 8
    query_cache_size = 128M
    join_buffer_size = 8M
    long_query_time=300
    key_buffer_size = 500M
    concurrent_insert=2
    myisam_sort_buffer_size = 64M

    # innodb
    innodb_additional_mem_pool_size=32M
    innodb_data_home_dir=/home/mysqld/mdmp/data/
    innodb_log_group_home_dir=/home/mysqld/mdmp/data
    #innodb_data_file_path=innodbspace1.dbf:1073741824;innodbspace2.dbf:1073741824:a
    utoextend
    innodb_data_file_path=innodbspace1.dbf:1073741824;innodbspace2.dbf:5158993920;in
    nodbspace3.dbf:17179869184;innodbspace4.dbf:17179869184;innodbspace5.dbf:1717986
    9184;innodbspace6.dbf:17179869184;innodbspace7.dbf:17179869184;innodbspace8.dbf:
    17179869184:autoextend
    # innodb_data_file_path=innodbspace1.dbf:1073741824;innodbspace2.dbf:250G;/mydat
    a/data2/mdmp/innodbspace3.dbf:1073741824:autoextend
    innodb_log_files_in_group=2
    innodb_flush_log_at_trx_commit=1
    innodb_flush_method=O_DIRECT
    innodb_thread_concurrency=16
    innodb_buffer_pool_size = 4500M
    innodb_lock_wait_timeout=60
    innodb_log_buffer_size = 32M
    innodb_log_file_size = 512M
    innodb_open_files=2048

    #Replication-skip
    replicate-ignore-table=mysql.gsd_runs
    replicate-ignore-table=mysql.gsd_monitor

    boonton:

    [client]
    port=3323
    socket=/tmp/mysqld_mdmp.sock
    character-sets-dir=/home/mysqld/sys/5.5.8/share/mysql/charsets

    [mysqld]
    port=3323
    server-id=2
    socket=/tmp/mysqld_mdmp.sock
    datadir=/home/mysqld/mdmp/data
    basedir=/home/mysqld/sys/5.5.8
    tmpdir=/home/mysqld/mdmp/tmp
    slow_query_log=mdmp_slow_queries.log

    # binary logging and replication
    binlog_cache_size=1M
    binlog_format=MIXED
    log-slave-updates
    log-bin=mdmp_boonton_binlog
    sync_binlog=1
    relay-log=mdmp_boonton_relaylog
    max_relay_log_size=100M
    read-only

    # limits
    max_connections=1000
    #old-passwords
    #lower_case_table_names=1
    general_log = ON
    max_heap_table_size=128M
    event_scheduler=ON
    query_cache_limit = 10M
    open_files_limit = 8088
    back_log=128
    max_allowed_packet = 50M

    # performance
    sort_buffer_size = 8M
    read_buffer_size = 2M
    query_prealloc_size = 65536
    read_rnd_buffer_size = 8M
    table_open_cache = 1024
    thread_cache_size = 8
    query_cache_size = 128M
    join_buffer_size = 8M
    long_query_time=300
    key_buffer_size = 500M
    concurrent_insert=2
    myisam_sort_buffer_size = 64M

    # innodb
    innodb_additional_mem_pool_size=32M
    innodb_data_home_dir=/home/mysqld/mdmp/data/
    innodb_log_group_home_dir=/home/mysqld/mdmp/data
    #innodb_data_file_path=innodbspace1.dbf:1073741824;innodbspace2.dbf:1073741824:a
    utoextend
    innodb_data_file_path=innodbspace1.dbf:1073741824;innodbspace2.dbf:4580179968;in
    nodbspace3.dbf:17179869184;innodbspace4.dbf:17179869184;innodbspace5.dbf:1717986
    9184;innodbspace6.dbf:17179869184;innodbspace7.dbf:17179869184;innodbspace8.dbf:
    17179869184:autoextend
    # innodb_data_file_path=innodbspace1.dbf:1073741824;innodbspace2.dbf:250G;/mydat
    a/data2/mdmp/innodbspace3.dbf:1073741824:autoextend
    innodb_log_files_in_group=2
    innodb_flush_log_at_trx_commit=1
    innodb_flush_method=O_DIRECT
    innodb_thread_concurrency=16
    innodb_buffer_pool_size = 4500M
    innodb_lock_wait_timeout=60
    innodb_log_buffer_size = 32M
    innodb_log_file_size = 512M
    innodb_open_files=2048

    #Replication-skip
    replicate-ignore-table=mysql.gsd_runs
    replicate-ignore-table=mysql.gsd_monitor

    ========

    ==> max_heap_table_size=128M

Leave a Reply

Your email address will not be published. Required fields are marked *