Bad performance on a large table

MySQL & MariaDB Other MySQL® Questions
1

Hello!

I’m having some issues with a MySQL server and I’m hoping someone here might be able to shed some light on them.

I’ve got a MyISAM table with about 8 million records. The MYI file is 2.6G and the MYI file is 1.8G. The machine has 4G of RAM, and it’s got dual XEON processors and a RAID 5 hard disk array. When I run a query such as “create table tmp select id, amt from tbl”, the system becomes overloaded for at least a minute, causing severe performance issues with concurrent db access in other apps. When I need to modify the table or run a more advanced query, the times are 30 minutes+, and the system is pretty much unusable during that time.

Can you spot any glaring problems in any of the settings below?

free -m:

total used free shared buffers cachedMem: 4042 3831 210 0 6 3538-/+ buffers/cache: 287 3755Swap: 8191 0 8191

show variables:

auto_increment_increment 1auto_increment_offset 1automatic_sp_privileges ONback_log 50basedir /usr/bdb_cache_size 8388600bdb_home /srv/mysql/bdb_log_buffer_size 524288bdb_logdir bdb_max_lock 10000bdb_shared_data OFFbdb_tmpdir /tmp/binlog_cache_size 32768bulk_insert_buffer_size 8388608character_set_client latin1character_set_connection latin1character_set_database latin1character_set_filesystem binarycharacter_set_results latin1character_set_server latin1character_set_system utf8character_sets_dir /usr/share/mysql/charsets/collation_connection latin1_swedish_cicollation_database latin1_swedish_cicollation_server latin1_swedish_cicompletion_type 0concurrent_insert 1connect_timeout 5datadir /srv/mysql/date_format %Y-%m-%ddatetime_format %Y-%m-%d %H:%i:%sdefault_week_format 0delay_key_write ONdelayed_insert_limit 100delayed_insert_timeout 300delayed_queue_size 1000div_precision_increment 4engine_condition_pushdown OFFexpire_logs_days 0flush OFFflush_time 0ft_boolean_syntax + -><()~*:“”&|ft_max_word_len 84ft_min_word_len 4ft_query_expansion_limit 20ft_stopword_file (built-in)group_concat_max_len 4096have_archive NOhave_bdb YEShave_blackhole_engine NOhave_compress YEShave_crypt YEShave_csv NOhave_dynamic_loading YEShave_example_engine NOhave_federated_engine NOhave_geometry YEShave_innodb YEShave_isam NOhave_merge_engine YEShave_ndbcluster NOhave_openssl DISABLEDhave_query_cache YEShave_raid NOhave_rtree_keys YEShave_symlink YESinit_connect init_file init_slave innodb_additional_mem_pool_size 1048576innodb_autoextend_increment 8innodb_buffer_pool_awe_mem_mb 0innodb_buffer_pool_size 8388608innodb_checksums ONinnodb_commit_concurrency 0innodb_concurrency_tickets 500innodb_data_file_path ibdata1:10M:autoextendinnodb_data_home_dir innodb_doublewrite ONinnodb_fast_shutdown 1innodb_file_io_threads 4innodb_file_per_table OFFinnodb_flush_log_at_trx_commit 1innodb_flush_method innodb_force_recovery 0innodb_lock_wait_timeout 50innodb_locks_unsafe_for_binlog OFFinnodb_log_arch_dir innodb_log_archive OFFinnodb_log_buffer_size 1048576innodb_log_file_size 5242880innodb_log_files_in_group 2innodb_log_group_home_dir ./innodb_max_dirty_pages_pct 90innodb_max_purge_lag 0innodb_mirrored_log_groups 1innodb_open_files 300innodb_support_xa ONinnodb_sync_spin_loops 20innodb_table_locks ONinnodb_thread_concurrency 8innodb_thread_sleep_delay 10000interactive_timeout 28800join_buffer_size 131072key_buffer_size 1073741824key_cache_age_threshold 300key_cache_block_size 1024key_cache_division_limit 100language /usr/share/mysql/english/large_files_support ONlarge_page_size 0large_pages OFFlc_time_names en_USlicense GPLlocal_infile ONlocked_in_memory OFFlog OFFlog_bin OFFlog_bin_trust_function_creators OFFlog_error log_queries_not_using_indexes OFFlog_slave_updates OFFlog_slow_queries ONlog_warnings 1long_query_time 1low_priority_updates OFFlower_case_file_system OFFlower_case_table_names 0max_allowed_packet 16776192max_binlog_cache_size 4294967295max_binlog_size 1073741824max_connect_errors 10max_connections 100max_delayed_threads 20max_error_count 64max_heap_table_size 536869888max_insert_delayed_threads 20max_join_size 4294967295max_length_for_sort_data 1024max_prepared_stmt_count 16382max_relay_log_size 0max_seeks_for_key 4294967295max_sort_length 1024max_sp_recursion_depth 0max_tmp_tables 32max_user_connections 0max_write_lock_count 4294967295multi_range_count 256myisam_data_pointer_size 6myisam_max_sort_file_size 2147483647myisam_recover_options OFFmyisam_repair_threads 1myisam_sort_buffer_size 8388608myisam_stats_method nulls_unequalnet_buffer_length 16384net_read_timeout 30net_retry_count 10net_write_timeout 60new OFFold_passwords OFFopen_files_limit 2158optimizer_prune_level 1optimizer_search_depth 62pid_file /var/run/mysqld/mysqld.pidport 3306preload_buffer_size 32768prepared_stmt_count 0protocol_version 10query_alloc_block_size 8192query_cache_limit 1048576query_cache_min_res_unit 4096query_cache_size 0query_cache_type ONquery_cache_wlock_invalidate OFFquery_prealloc_size 8192range_alloc_block_size 2048read_buffer_size 131072read_only OFFread_rnd_buffer_size 262144relay_log_purge ONrelay_log_space_limit 0rpl_recovery_rank 0secure_auth OFFserver_id 0skip_external_locking ONskip_networking OFFskip_show_database OFFslave_compressed_protocol OFFslave_load_tmpdir /tmp/slave_net_timeout 3600slave_skip_errors OFFslave_transaction_retries 10slow_launch_time 2socket /var/lib/mysql/mysql.socksort_buffer_size 2097144sql_big_selects ONsql_mode sql_notes ONsql_warnings OFFssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine MyISAMsync_binlog 0sync_frm ONsystem_time_zone EDTtable_cache 1024table_lock_wait_timeout 50table_type MyISAMthread_cache_size 0thread_stack 196608time_format %H:%i:%stime_zone SYSTEMtimed_mutexes OFFtmp_table_size 536870912tmpdir /tmp/transaction_alloc_block_size 8192transaction_prealloc_size 4096tx_isolation REPEATABLE-READupdatable_views_with_limit YESversion 5.0.27-logversion_bdb Sleepycat Software: Berkeley DB 4.1.24: (October 21, 2006)version_comment Source distributionversion_compile_machine i686version_compile_os redhat-linux-gnuwait_timeout 28800

show status:

Aborted_clients 10Aborted_connects 0Binlog_cache_disk_use 0Binlog_cache_use 0Bytes_received 631Bytes_sent 204777375Com_admin_commands 0Com_alter_db 0Com_alter_table 0Com_analyze 0Com_backup_table 0Com_begin 0Com_change_db 0Com_change_master 0Com_check 0Com_checksum 0Com_commit 0Com_create_db 0Com_create_function 0Com_create_index 0Com_create_table 1Com_dealloc_sql 0Com_delete 0Com_delete_multi 0Com_do 0Com_drop_db 0Com_drop_function 0Com_drop_index 0Com_drop_table 1Com_drop_user 0Com_execute_sql 0Com_flush 0Com_grant 0Com_ha_close 0Com_ha_open 0Com_ha_read 0Com_help 0Com_insert 0Com_insert_select 0Com_kill 1Com_load 0Com_load_master_data 0Com_load_master_table 0Com_lock_tables 0Com_optimize 0Com_preload_keys 0Com_prepare_sql 0Com_purge 0Com_purge_before_date 0Com_rename_table 0Com_repair 0Com_replace 0Com_replace_select 0Com_reset 0Com_restore_table 0Com_revoke 0Com_revoke_all 0Com_rollback 0Com_savepoint 0Com_select 1Com_set_option 0Com_show_binlog_events 0Com_show_binlogs 0Com_show_charsets 0Com_show_collations 0Com_show_column_types 0Com_show_create_db 0Com_show_create_table 0Com_show_databases 0Com_show_errors 0Com_show_fields 0Com_show_grants 0Com_show_innodb_status 0Com_show_keys 0Com_show_logs 0Com_show_master_status 0Com_show_ndb_status 0Com_show_new_master 0Com_show_open_tables 0Com_show_privileges 0Com_show_processlist 0Com_show_slave_hosts 0Com_show_slave_status 0Com_show_status 1Com_show_storage_engines 0Com_show_tables 0Com_show_triggers 0Com_show_variables 1Com_show_warnings 0Com_slave_start 0Com_slave_stop 0Com_stmt_close 0Com_stmt_execute 0Com_stmt_fetch 0Com_stmt_prepare 0Com_stmt_reset 0Com_stmt_send_long_data 0Com_truncate 0Com_unlock_tables 0Com_update 0Com_update_multi 0Com_xa_commit 0Com_xa_end 0Com_xa_prepare 0Com_xa_recover 0Com_xa_rollback 0Com_xa_start 0Compression OFFConnections 113Created_tmp_disk_tables 0Created_tmp_files 11Created_tmp_tables 2Delayed_errors 0Delayed_insert_threads 0Delayed_writes 0Flush_commands 1Handler_commit 0Handler_delete 0Handler_discover 0Handler_prepare 0Handler_read_first 0Handler_read_key 0Handler_read_next 0Handler_read_prev 0Handler_read_rnd 0Handler_read_rnd_next 8314748Handler_rollback 0Handler_savepoint 0Handler_savepoint_rollback 0Handler_update 0Handler_write 358Innodb_buffer_pool_pages_data 20Innodb_buffer_pool_pages_dirty 0Innodb_buffer_pool_pages_flushed 0Innodb_buffer_pool_pages_free 492Innodb_buffer_pool_pages_latched 0Innodb_buffer_pool_pages_misc 0Innodb_buffer_pool_pages_total 512Innodb_buffer_pool_read_ahead_rnd 1Innodb_buffer_pool_read_ahead_seq 0Innodb_buffer_pool_read_requests 269Innodb_buffer_pool_reads 13Innodb_buffer_pool_wait_free 0Innodb_buffer_pool_write_requests 0Innodb_data_fsyncs 3Innodb_data_pending_fsyncs 0Innodb_data_pending_reads 0Innodb_data_pending_writes 0Innodb_data_read 2510848Innodb_data_reads 26Innodb_data_writes 3Innodb_data_written 1536Innodb_dblwr_pages_written 0Innodb_dblwr_writes 0Innodb_log_waits 0Innodb_log_write_requests 0Innodb_log_writes 1Innodb_os_log_fsyncs 3Innodb_os_log_pending_fsyncs 0Innodb_os_log_pending_writes 0Innodb_os_log_written 512Innodb_page_size 16384Innodb_pages_created 0Innodb_pages_read 20Innodb_pages_written 0Innodb_row_lock_current_waits 0Innodb_row_lock_time 0Innodb_row_lock_time_avg 0Innodb_row_lock_time_max 0Innodb_row_lock_waits 0Innodb_rows_deleted 0Innodb_rows_inserted 0Innodb_rows_read 0Innodb_rows_updated 0Key_blocks_not_flushed 0Key_blocks_unused 926842Key_blocks_used 999Key_read_requests 212218Key_reads 1011Key_write_requests 994Key_writes 527Last_query_cost 10.499000Max_used_connections 5Not_flushed_delayed_rows 0Open_files 106Open_streams 0Open_tables 53Opened_tables 1Qcache_free_blocks 0Qcache_free_memory 0Qcache_hits 0Qcache_inserts 0Qcache_lowmem_prunes 0Qcache_not_cached 0Qcache_queries_in_cache 0Qcache_total_blocks 0Questions 5754Rpl_status NULLSelect_full_join 0Select_full_range_join 0Select_range 0Select_range_check 0Select_scan 3Slave_open_temp_tables 0Slave_retried_transactions 0Slave_running OFFSlow_launch_threads 0Slow_queries 1Sort_merge_passes 0Sort_range 0Sort_rows 0Sort_scan 0Ssl_accept_renegotiates 0Ssl_accepts 0Ssl_callback_cache_hits 0Ssl_cipher Ssl_cipher_list Ssl_client_connects 0Ssl_connect_renegotiates 0Ssl_ctx_verify_depth 0Ssl_ctx_verify_mode 0Ssl_default_timeout 0Ssl_finished_accepts 0Ssl_finished_connects 0Ssl_session_cache_hits 0Ssl_session_cache_misses 0Ssl_session_cache_mode NONESsl_session_cache_overflows 0Ssl_session_cache_size 0Ssl_session_cache_timeouts 0Ssl_sessions_reused 0Ssl_used_session_cache_entries 0Ssl_verify_depth 0Ssl_verify_mode 0Ssl_version Table_locks_immediate 3137Table_locks_waited 0Tc_log_max_pages_used 0Tc_log_page_size 0Tc_log_page_waits 0Threads_cached 0Threads_connected 2Threads_created 112Threads_running 1Uptime 1407

Thanks. Any help will be greatly appreciated.

Ben

2

I suggest 2 things to do:

  • Upgrade RAM to 8MB or more
  • Consider using InnoDB for your tables. This depends on your application, ie. heavy read or heavy write.
3

safari,
Thanks for the suggestions.

Since this box is a dedicated SQL server, I temporarily bumped the key_buffer_size up to 3G (larger than my MYI and MYD files) and saw the performance jump 100 fold. I assume it is because the large tables are now able to be completely loaded into memory?

At any rate - I’ve got 8G more RAM to put in tomorrow morning, then I should be set.

Thanks for the help!

Ben

4

The key buffer controlled by the key_buffer_size parameter does not cache table data. It only caches indexes.
But one of the big rules for performance with MySQL is that all the indexes should fit entirely into the key buffer.

The reason for this is that index reads are by nature very often random down through the tree and that means that it first has to perform a lot of random reads through the index tree. And then it needs to perform an additional random read to find the actual data in the table.
And random reads are one of the slowest operations that a database server can perform.

Hence recommendation to at least have so much memory so that the index can fit into it.

5

Hmm… well my total index size is around 20G, so loading all index files into memory at the same time isn’t going to happen with our current hardware. Fortunately, the largest single index file is only 2G.

Thanks for the help.
Ben