Skip to content

Instantly share code, notes, and snippets.

@juanrdlo

juanrdlo/my.cnf

Forked from kenelliott/my.cnf
Created October 8, 2020 17:16
Show Gist options
  • Save juanrdlo/bb10e83f995ffa2c43f811b092e997b0 to your computer and use it in GitHub Desktop.
Save juanrdlo/bb10e83f995ffa2c43f811b092e997b0 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Ken Elliott revised this gist Nov 6, 2012. 1 changed file with 380 additions and 0 deletions.
    380 changes: 380 additions & 0 deletions table_info.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,380 @@

    # Query: show table status;
    Rows = 132
    Auto_increment = 232
    Check_time =
    Data_length = 16384
    Row_format = Compact
    Checksum =
    Update_time =
    Engine = InnoDB
    Create_options =
    Comment =
    Index_length = 16384
    Data_free = 0
    Avg_row_length = 124
    Version = 10
    Create_time = 2012-11-06 09:50:08
    Max_data_length = 0
    Name = QT213_agents
    Collation = latin1_swedish_ci

    # Query: explain select count(id) from QT213_ldusage;
    Extra = Using index
    key_len = 4
    ref =
    table = QT213_ldusage
    rows = 19855689
    key = PRIMARY
    select_type = SIMPLE
    possible_keys =
    id = 1
    type = index

    # Query: flush status;
    # Query: select count(id) from QT213_ldusage;
    # Query: show status like 'Handler%';
    Handler_commit = 1
    Handler_delete = 0
    Handler_discover = 0
    Handler_prepare = 0
    Handler_read_first = 1
    Handler_read_key = 1
    Handler_read_last = 0
    Handler_read_next = 19927302
    Handler_read_prev = 0
    Handler_read_rnd = 0
    Handler_read_rnd_next = 0
    Handler_rollback = 0
    Handler_savepoint = 0
    Handler_savepoint_rollback = 0
    Handler_update = 0
    Handler_write = 0

    # Query: show variables;
    auto_increment_increment = 1
    auto_increment_offset = 1
    autocommit = ON
    automatic_sp_privileges = ON
    back_log = 50
    basedir = /usr
    big_tables = OFF
    binlog_cache_size = 65536
    binlog_direct_non_transactional_updates = OFF
    binlog_format = STATEMENT
    binlog_stmt_cache_size = 32768
    bulk_insert_buffer_size = 1073741824
    character_set_client = latin1
    character_set_connection = latin1
    character_set_database = latin1
    character_set_filesystem = binary
    character_set_results = latin1
    character_set_server = latin1
    character_set_system = utf8
    character_sets_dir = /usr/share/mysql/charsets/
    collation_connection = latin1_swedish_ci
    collation_database = latin1_swedish_ci
    collation_server = latin1_swedish_ci
    completion_type = NO_CHAIN
    concurrent_insert = ALWAYS
    connect_timeout = 30
    datadir = /var/lib/mysql/
    date_format = %Y-%m-%d
    datetime_format = %Y-%m-%d %H:%i:%s
    default_storage_engine = InnoDB
    default_week_format = 0
    delay_key_write = ON
    delayed_insert_limit = 100
    delayed_insert_timeout = 300
    delayed_queue_size = 1000
    div_precision_increment = 4
    engine_condition_pushdown = ON
    error_count = 0
    event_scheduler = OFF
    expire_logs_days = 1
    external_user =
    flush = OFF
    flush_time = 0
    foreign_key_checks = ON
    ft_boolean_syntax = + -><()~*:""&|
    ft_max_word_len = 84
    ft_min_word_len = 4
    ft_query_expansion_limit = 20
    ft_stopword_file = (built-in)
    general_log = OFF
    general_log_file = /var/lib/mysql/Xen6-WH-Database.log
    group_concat_max_len = 1024
    have_compress = YES
    have_crypt = YES
    have_csv = YES
    have_dynamic_loading = YES
    have_geometry = YES
    have_innodb = YES
    have_ndbcluster = NO
    have_openssl = DISABLED
    have_partitioning = YES
    have_profiling = YES
    have_query_cache = YES
    have_rtree_keys = YES
    have_ssl = DISABLED
    have_symlink = YES
    hostname = Xen6-WH-Database
    identity = 0
    ignore_builtin_innodb = OFF
    init_connect =
    init_file =
    init_slave =
    innodb_adaptive_flushing = ON
    innodb_adaptive_hash_index = ON
    innodb_additional_mem_pool_size = 4194304
    innodb_autoextend_increment = 8
    innodb_autoinc_lock_mode = 1
    innodb_buffer_pool_instances = 1
    innodb_buffer_pool_size = 12884901888
    innodb_change_buffering = all
    innodb_checksums = ON
    innodb_commit_concurrency = 16
    innodb_concurrency_tickets = 500
    innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend
    innodb_data_home_dir =
    innodb_doublewrite = OFF
    innodb_fast_shutdown = 1
    innodb_file_format = Antelope
    innodb_file_format_check = ON
    innodb_file_format_max = Antelope
    innodb_file_per_table = ON
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method = O_DSYNC
    innodb_force_load_corrupted = OFF
    innodb_force_recovery = 0
    innodb_io_capacity = 200
    innodb_large_prefix = OFF
    innodb_lock_wait_timeout = 300
    innodb_locks_unsafe_for_binlog = OFF
    innodb_log_buffer_size = 134217728
    innodb_log_file_size = 268435456
    innodb_log_files_in_group = 2
    innodb_log_group_home_dir = ./
    innodb_max_dirty_pages_pct = 75
    innodb_max_purge_lag = 0
    innodb_mirrored_log_groups = 1
    innodb_old_blocks_pct = 37
    innodb_old_blocks_time = 0
    innodb_open_files = 300
    innodb_purge_batch_size = 20
    innodb_purge_threads = 0
    innodb_random_read_ahead = OFF
    innodb_read_ahead_threshold = 56
    innodb_read_io_threads = 16
    innodb_replication_delay = 0
    innodb_rollback_on_timeout = OFF
    innodb_rollback_segments = 128
    innodb_spin_wait_delay = 6
    innodb_stats_method = nulls_equal
    innodb_stats_on_metadata = ON
    innodb_stats_sample_pages = 8
    innodb_strict_mode = OFF
    innodb_support_xa = OFF
    innodb_sync_spin_loops = 30
    innodb_table_locks = ON
    innodb_thread_concurrency = 16
    innodb_thread_sleep_delay = 10000
    innodb_use_native_aio = ON
    innodb_use_sys_malloc = ON
    innodb_version = 1.1.8
    innodb_write_io_threads = 16
    insert_id = 0
    interactive_timeout = 28800
    join_buffer_size = 1048576
    keep_files_on_create = OFF
    key_buffer_size = 1048576
    key_cache_age_threshold = 300
    key_cache_block_size = 1024
    key_cache_division_limit = 100
    large_files_support = ON
    large_page_size = 0
    large_pages = OFF
    last_insert_id = 0
    lc_messages = en_US
    lc_messages_dir = /usr/share/mysql/
    lc_time_names = en_US
    license = GPL
    local_infile = ON
    lock_wait_timeout = 31536000
    locked_in_memory = OFF
    log = OFF
    log_bin = OFF
    log_bin_trust_function_creators = OFF
    log_error = /var/lib/mysql/Xen6-WH-Database.err
    log_output = FILE
    log_queries_not_using_indexes = OFF
    log_slave_updates = OFF
    log_slow_queries = OFF
    log_warnings = 1
    long_query_time = 10.000000
    low_priority_updates = OFF
    lower_case_file_system = OFF
    lower_case_table_names = 0
    max_allowed_packet = 1048576
    max_binlog_cache_size = 18446744073709547520
    max_binlog_size = 268435456
    max_binlog_stmt_cache_size = 18446744073709547520
    max_connect_errors = 100
    max_connections = 2000
    max_delayed_threads = 20
    max_error_count = 64
    max_heap_table_size = 1073741824
    max_insert_delayed_threads = 20
    max_join_size = 18446744073709551615
    max_length_for_sort_data = 1024
    max_long_data_size = 1048576
    max_prepared_stmt_count = 16382
    max_relay_log_size = 0
    max_seeks_for_key = 18446744073709551615
    max_sort_length = 1024
    max_sp_recursion_depth = 0
    max_tmp_tables = 32
    max_user_connections = 0
    max_write_lock_count = 18446744073709551615
    metadata_locks_cache_size = 1024
    min_examined_row_limit = 0
    multi_range_count = 256
    myisam_data_pointer_size = 6
    myisam_max_sort_file_size = 268435456
    myisam_mmap_size = 18446744073709551615
    myisam_recover_options = OFF
    myisam_repair_threads = 1
    myisam_sort_buffer_size = 134217728
    myisam_stats_method = nulls_unequal
    myisam_use_mmap = OFF
    net_buffer_length = 16384
    net_read_timeout = 30
    net_retry_count = 10
    net_write_timeout = 60
    new = OFF
    old = OFF
    old_alter_table = OFF
    old_passwords = OFF
    open_files_limit = 10000
    optimizer_prune_level = 1
    optimizer_search_depth = 62
    optimizer_switch = index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
    performance_schema = OFF
    performance_schema_events_waits_history_long_size = 10000
    performance_schema_events_waits_history_size = 10
    performance_schema_max_cond_classes = 80
    performance_schema_max_cond_instances = 1000
    performance_schema_max_file_classes = 50
    performance_schema_max_file_handles = 32768
    performance_schema_max_file_instances = 10000
    performance_schema_max_mutex_classes = 200
    performance_schema_max_mutex_instances = 1000000
    performance_schema_max_rwlock_classes = 30
    performance_schema_max_rwlock_instances = 1000000
    performance_schema_max_table_handles = 100000
    performance_schema_max_table_instances = 50000
    performance_schema_max_thread_classes = 50
    performance_schema_max_thread_instances = 1000
    pid_file = /var/lib/mysql/Xen6-WH-Database.pid
    plugin_dir = /usr/lib64/mysql/plugin/
    port = 3306
    preload_buffer_size = 32768
    profiling = OFF
    profiling_history_size = 15
    protocol_version = 10
    proxy_user =
    pseudo_thread_id = 847
    query_alloc_block_size = 8192
    query_cache_limit = 524288
    query_cache_min_res_unit = 4096
    query_cache_size = 33554432
    query_cache_type = ON
    query_cache_wlock_invalidate = OFF
    query_prealloc_size = 8192
    rand_seed1 = 0
    rand_seed2 = 0
    range_alloc_block_size = 4096
    read_buffer_size = 1048576
    read_only = OFF
    read_rnd_buffer_size = 1048576
    relay_log =
    relay_log_index =
    relay_log_info_file = relay-log.info
    relay_log_purge = ON
    relay_log_recovery = OFF
    relay_log_space_limit = 0
    report_host =
    report_password =
    report_port = 3306
    report_user =
    rpl_recovery_rank = 0
    secure_auth = OFF
    secure_file_priv =
    server_id = 0
    skip_external_locking = ON
    skip_name_resolve = OFF
    skip_networking = OFF
    skip_show_database = OFF
    slave_compressed_protocol = OFF
    slave_exec_mode = STRICT
    slave_load_tmpdir = /tmp
    slave_net_timeout = 3600
    slave_skip_errors = OFF
    slave_transaction_retries = 10
    slave_type_conversions =
    slow_launch_time = 2
    slow_query_log = OFF
    slow_query_log_file = /var/lib/mysql/Xen6-WH-Database-slow.log
    socket = /var/lib/mysql/mysql.sock
    sort_buffer_size = 1048576
    sql_auto_is_null = OFF
    sql_big_selects = ON
    sql_big_tables = OFF
    sql_buffer_result = OFF
    sql_log_bin = ON
    sql_log_off = OFF
    sql_low_priority_updates = OFF
    sql_max_join_size = 18446744073709551615
    sql_mode =
    sql_notes = ON
    sql_quote_show_create = ON
    sql_safe_updates = OFF
    sql_select_limit = 18446744073709551615
    sql_slave_skip_counter = 0
    sql_warnings = OFF
    ssl_ca =
    ssl_capath =
    ssl_cert =
    ssl_cipher =
    ssl_key =
    storage_engine = InnoDB
    stored_program_cache = 256
    sync_binlog = 0
    sync_frm = ON
    sync_master_info = 0
    sync_relay_log = 0
    sync_relay_log_info = 0
    system_time_zone = EST
    table_definition_cache = 400
    table_open_cache = 400
    thread_cache_size = 100
    thread_concurrency = 1
    thread_handling = one-thread-per-connection
    thread_stack = 262144
    time_format = %H:%i:%s
    time_zone = SYSTEM
    timed_mutexes = OFF
    timestamp = 1352220621
    tmp_table_size = 1073741824
    tmpdir = /tmp
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    tx_isolation = REPEATABLE-READ
    unique_checks = ON
    updatable_views_with_limit = YES
    version = 5.5.25a
    version_comment = MySQL Community Server (GPL)
    version_compile_machine = x86_64
    version_compile_os = Linux
    wait_timeout = 28800
    warning_count = 0
  2. Ken Elliott created this gist Nov 6, 2012.
    61 changes: 61 additions & 0 deletions my.cnf
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,61 @@
    [mysqld_safe]
    nice = -15

    [mysqld]
    max_binlog_size = 256M #max size for binlog before rolling
    expire_logs_days = 1 #binlog files older than this will be purged

    ## Per-Thread Buffers * (max_connections) = total per-thread mem usage
    thread_stack = 256K #default: 32bit: 192K, 64bit: 256K
    sort_buffer_size = 1M #default: 2M, larger may cause perf issues
    read_buffer_size = 1M #default: 128K, change in increments of 4K
    read_rnd_buffer_size = 1M #default: 256K
    join_buffer_size = 1M #default: 128K
    binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries

    ## Query Cache
    query_cache_size = 32M #global buffer
    query_cache_limit = 512K #max query result size to put in cache

    ## Connections
    max_connections = 2000 #multiplier for memory usage via per-thread buffers
    max_connect_errors = 100 #default: 10
    concurrent_insert = 2 #default: 1, 2: enable insert for all instances
    connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10

    ## Table and TMP settings
    max_heap_table_size = 1G #recommend same size as tmp_table_size
    bulk_insert_buffer_size = 1G #recommend same size as tmp_table_size
    tmp_table_size = 1G #recommend 1G min

    # set tmpdir to a ramdisk?
    #tmpdir = /data/mysql-tmp0:/data/mysql-tmp1

    ## Thread settings
    thread_concurrency = 0 #recommend 2x CPU cores [0 create as many as needed]
    thread_cache_size = 100 #recommend 5% of max_connections

    ## MyISAM Engine
    key_buffer = 1M #global buffer
    myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes
    myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes

    ## InnoDB IO settings - 5.5.x and greater
    innodb_write_io_threads = 16
    innodb_read_io_threads = 16

    ## InnoDB Plugin Independent Settings
    innodb_file_per_table #enable always
    innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend
    innodb_buffer_pool_size = 12G #global buffer
    innodb_additional_mem_pool_size = 4M #global buffer
    innodb_flush_log_at_trx_commit = 0 #2/0 = perf, 1 = ACID
    innodb_log_file_size = 256M
    innodb_log_buffer_size = 128M #global buffer
    innodb_lock_wait_timeout = 300
    innodb_thread_concurrency = 16 #recommend 2x core quantity
    innodb_commit_concurrency = 16 #recommend 4x num disks
    innodb_flush_method = O_DSYNC #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI
    innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush
    skip-innodb-doublewrite
    sync_binlog = 0
    97 changes: 97 additions & 0 deletions system_info.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,97 @@
    = SYSTEM INFORMATION
    Linux Xen6-WH-Database 2.6.32-262.el6.x86_64 #1 SMP Sun Apr 8 18:38:00 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux

    =CPUINFO
    processor : 0
    vendor_id : GenuineIntel
    cpu family : 6
    model : 45
    model name : Intel(R) Xeon(R) CPU E5-2640 0 @ 2.50GHz
    stepping : 7
    cpu MHz : 2500.159
    cache size : 15360 KB
    fpu : yes
    fpu_exception : yes
    cpuid level : 13
    wp : yes
    flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat clflush mmx fxsr sse sse2 syscall lm rep_good unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 x2apic popcnt aes hypervisor lahf_lm
    bogomips : 5000.31
    clflush size : 64
    cache_alignment : 64
    address sizes : 46 bits physical, 48 bits virtual
    power management:

    = REMOVED IDENTICAL PROCESSORS THERE ARE 16 TOTAL PROCESSORS

    processor : 15
    vendor_id : GenuineIntel
    cpu family : 6
    model : 45
    model name : Intel(R) Xeon(R) CPU E5-2640 0 @ 2.50GHz
    stepping : 7
    cpu MHz : 2500.159
    cache size : 15360 KB
    fpu : yes
    fpu_exception : yes
    cpuid level : 13
    wp : yes
    flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat clflush mmx fxsr sse sse2 syscall lm rep_good unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 x2apic popcnt aes hypervisor lahf_lm
    bogomips : 5000.44
    clflush size : 64
    cache_alignment : 64
    address sizes : 46 bits physical, 48 bits virtual
    power management:

    MEMORYINFO\n
    MemTotal: 16313180 kB
    MemFree: 6733304 kB
    Buffers: 175304 kB
    Cached: 5266544 kB
    SwapCached: 0 kB
    Active: 8468376 kB
    Inactive: 662608 kB
    Active(anon): 3691820 kB
    Inactive(anon): 16 kB
    Active(file): 4776556 kB
    Inactive(file): 662592 kB
    Unevictable: 34760 kB
    Mlocked: 10232 kB
    SwapTotal: 2064376 kB
    SwapFree: 2064376 kB
    Dirty: 64 kB
    Writeback: 0 kB
    AnonPages: 3723912 kB
    Mapped: 23256 kB
    Shmem: 220 kB
    Slab: 202268 kB
    SReclaimable: 167716 kB
    SUnreclaim: 34552 kB
    KernelStack: 4656 kB
    PageTables: 15628 kB
    NFS_Unstable: 0 kB
    Bounce: 0 kB
    WritebackTmp: 0 kB
    CommitLimit: 10220964 kB
    Committed_AS: 14736532 kB
    VmallocTotal: 34359738367 kB
    VmallocUsed: 55296 kB
    VmallocChunk: 34359650952 kB
    HardwareCorrupted: 0 kB
    AnonHugePages: 3633152 kB
    HugePages_Total: 0
    HugePages_Free: 0
    HugePages_Rsvd: 0
    HugePages_Surp: 0
    Hugepagesize: 2048 kB
    DirectMap4k: 6144 kB
    DirectMap2M: 16766976 kB

    =DRIVEINFO
    Filesystem Size Used Avail Use% Mounted on
    /dev/mapper/vg_xen6whdatabase-lv_root
    33G 2.2G 29G 7% /
    tmpfs 7.8G 0 7.8G 0% /dev/shm
    /dev/sda1 485M 33M 427M 8% /boot
    /dev/sdb 296G 6.1G 275G 3% /var/lib/mysql
    192.168.101.15:/var/lib/mysql/DataWareHouse-Outfiles/
    591G 530G 32G 95% /var/lib/mysql/DataWareHouse-Outfiles