MYSQL variables to tweak for queries fetching huge datasets

  • Filter
  • Time
  • Show
Clear All
new posts

  • MYSQL variables to tweak for queries fetching huge datasets

    SHOW varaible o/p
    auto_increment_increment 1
    auto_increment_offset 1
    automatic_sp_privileges ON
    back_log 50
    basedir /usr/
    binlog_cache_size 32768
    bulk_insert_buffer_size 8388608
    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 0
    concurrent_insert 1
    connect_timeout 15
    datadir /var/lib/mysql/
    date_format %Y-%m-%d
    datetime_format %Y-%m-%d %H:%i:%s
    default_week_format 0
    delay_key_write ON
    delayed_insert_limit 100
    delayed_insert_timeout 300
    delayed_queue_size 1000
    div_precision_increment 4
    keep_files_on_create OFF
    engine_condition_pushdown OFF
    expire_logs_days 0
    flush OFF
    flush_time 0
    ft_boolean_syntax + -><()~*:""&|
    ft_max_word_len 84
    ft_min_word_len 4
    ft_query_expansion_limit 20
    ft_stopword_file (built-in)
    group_concat_max_len 1024
    have_archive NO
    have_bdb NO
    have_blackhole_engine NO
    have_compress YES
    have_community_features NO
    have_profiling NO
    have_crypt YES
    have_csv NO
    have_dynamic_loading YES
    have_example_engine NO
    have_federated_engine NO
    have_geometry YES
    have_innodb YES
    have_isam NO
    have_merge_engine YES
    have_ndbcluster NO
    have_openssl NO
    have_ssl NO
    have_query_cache YES
    have_raid NO
    have_rtree_keys YES
    have_symlink YES
    hostname scbcaappz200
    innodb_additional_mem_pool_size 1048576
    innodb_autoextend_increment 8
    innodb_buffer_pool_awe_mem_mb 0
    innodb_buffer_pool_size 8388608
    innodb_checksums ON
    innodb_commit_concurrency 0
    innodb_concurrency_tickets 500
    innodb_data_file_path ibdata1:10M:autoextend
    innodb_adaptive_hash_index ON
    innodb_doublewrite ON
    innodb_fast_shutdown 1
    innodb_file_io_threads 4
    innodb_file_per_table OFF
    innodb_flush_log_at_trx_commit 1
    innodb_force_recovery 0
    innodb_lock_wait_timeout 50
    innodb_locks_unsafe_for_binlog OFF
    innodb_log_archive OFF
    innodb_log_buffer_size 1048576
    innodb_log_file_size 5242880
    innodb_log_files_in_group 2
    innodb_log_group_home_dir ./
    innodb_max_dirty_pages_pct 90
    innodb_max_purge_lag 0
    innodb_mirrored_log_groups 1
    innodb_open_files 300
    innodb_rollback_on_timeout OFF
    innodb_support_xa ON
    innodb_sync_spin_loops 20
    innodb_table_locks ON
    innodb_thread_concurrency 8
    innodb_thread_sleep_delay 10000
    innodb_use_legacy_cardinality_algorithm ON
    interactive_timeout 28800
    join_buffer_size 131072
    key_buffer_size 2147483648
    key_cache_age_threshold 300
    key_cache_block_size 1024
    key_cache_division_limit 100
    language /usr/share/mysql/english/
    large_files_support ON
    large_page_size 0
    large_pages OFF
    lc_time_names en_US
    license GPL
    local_infile ON
    locked_in_memory OFF
    log OFF
    log_bin OFF
    log_bin_trust_function_creators OFF
    log_queries_not_using_indexes OFF
    log_slave_updates OFF
    log_slow_queries OFF
    log_warnings 1
    long_query_time 10
    low_priority_updates OFF
    lower_case_file_system OFF
    lower_case_table_names 0
    max_allowed_packet 1073741824
    max_binlog_cache_size 18446744073709547520
    max_binlog_size 1073741824
    max_connect_errors 10
    max_connections 500
    max_delayed_threads 20
    max_error_count 64
    max_heap_table_size 16777216
    max_insert_delayed_threads 20
    max_join_size 18446744073709551615
    max_length_for_sort_data 1024
    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
    multi_range_count 256
    myisam_data_pointer_size 6
    myisam_max_sort_file_size 9223372036853727232
    myisam_mmap_size 18446744073709551615
    myisam_recover_options OFF
    myisam_repair_threads 1
    myisam_sort_buffer_size 8388608
    myisam_stats_method nulls_unequal
    net_buffer_length 8192
    net_read_timeout 60
    net_retry_count 10
    net_write_timeout 60
    new OFF
    old_passwords OFF
    open_files_limit 2500
    optimizer_prune_level 1
    optimizer_search_depth 62
    pid_file /var/lib/mysql/mysqld.pid
    port 3306
    preload_buffer_size 32768
    protocol_version 10
    query_alloc_block_size 8192
    query_cache_limit 1048576
    query_cache_min_res_unit 4096
    query_cache_size 0
    query_cache_type ON
    query_cache_wlock_invalidate OFF
    query_prealloc_size 8192
    range_alloc_block_size 4096
    read_buffer_size 262144
    read_only OFF
    read_rnd_buffer_size 524288
    relay_log_info_file relay-log.info
    relay_log_purge ON
    relay_log_space_limit 0
    rpl_recovery_rank 0
    secure_auth OFF
    server_id 1
    skip_external_locking ON
    skip_networking OFF
    skip_show_database OFF
    slave_compressed_protocol OFF
    slave_load_tmpdir /var/lib/mysql/.tmp/
    slave_net_timeout 3600
    slave_skip_errors OFF
    slave_transaction_retries 10
    slow_launch_time 2
    socket /var/lib/mysql/mysql.sock
    sort_buffer_size 524288
    sql_big_selects ON
    sql_notes ON
    sql_warnings OFF
    storage_engine MyISAM
    sync_binlog 0
    sync_frm ON
    system_time_zone EDT
    table_cache 64
    table_lock_wait_timeout 50
    table_type MyISAM
    thread_cache_size 0
    thread_stack 524288
    time_format %H:%i:%s
    time_zone SYSTEM
    timed_mutexes OFF
    tmp_table_size 33554432
    tmpdir /var/lib/mysql/.tmp/
    transaction_alloc_block_size 8192
    transaction_prealloc_size 4096
    tx_isolation REPEATABLE-READ
    updatable_views_with_limit YES
    version 5.0.95
    version_comment SUSE MySQL RPM
    version_compile_machine x86_64
    version_compile_os suse-linux-gnu
    wait_timeout 28800

  • #2
    For memory concern, the query result could either be buffered on the client side or on the server side. By default, results are sent to client as they are generated and get buffered on the client side. The C API function for that is mysql_store_result(). If the client doesn't have sufficient memory, you can buffer data on the server instead and fetch it row by row, that is what mysql_use_result() does. In this mode, it is better to have simple processing for each row to avoid tying up the server for too long. If you are using mysql program as the client, use the -q option to turn off caching on the client side. If you using some connector, check the corresponding functions.

    Also use the same max_allowed_packet for the client.