October 21, 2014

New Forum Categories: Help Wanted, For Hire

I’ve just added two categories to our forum, so you can post your job listings if you’re looking for someone to help you, and you can post your qualifications if you are available for hire.

About Baron Schwartz

Baron is the lead author of High Performance MySQL.
He is a former Percona employee.

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

Speak Your Mind

*