Today's instance finished shortly after I sent the email below. BTW, here are some specifics on the table (which uses MyISAM). Thursday's instance has 11 GB of data and 0.78 GB of index. Today's instance has 26 GB of data and 1.8 GB of index.
Thanks, Mike Spreitzer Mike Spreitzer/Watson/i...@ibmus 06/27/09 09:48 AM To mos <mo...@fastmail.fm> cc mysql@lists.mysql.com Subject Re: Indexing dynamics in MySQL Community Edition 5.1.34 Yes, all the indices are added in one "ALTER TABLE" statement. Thursday's incarnation took about 1.5 hours, on a table created from about 8 GB of CSV. Today's has already taken over 8 hours, on a table created from about 22 GB of data. The logarithm of 22 GB is about 24/23 of the logarithm of 8 GB. I seem to have fallen off an additional cliff. As a reminder, here is the situation. I load a table from CSV with zero indices defined. Then I add some indices, and that takes a long time. On Thursday it kept one core busy, but the disk was not very busy for much of that time (about 600 blocks out per sec). Now I am seeing about 2.5 blocks out per second --- still nowhere near capacity --- and one core busy. The MySQL server is running on a 64-bit RHEL 5 machine with 16 Intel cores at 2.4 GHz, and 64 GB RAM. The db storage is on fiber channel. I created my.cnf based on my-huge.cnf, expanding key_buffer[_size] to 8G, myisam_sort_buffer_size to 256M, and putting tmpdir on the fiber channel disk. Here is my current "SHOW STATUS": +-----------------------------------+------------+ | Variable_name | Value | +-----------------------------------+------------+ | Aborted_clients | 8 | | Aborted_connects | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 95 | | Bytes_sent | 180 | | Com_admin_commands | 0 | | Com_assign_to_keycache | 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 | | Com_alter_function | 0 | | Com_alter_procedure | 0 | | Com_alter_server | 0 | | Com_alter_table | 0 | | Com_alter_tablespace | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_binlog | 0 | | Com_call_procedure | 0 | | Com_change_db | 0 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_event | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_procedure | 0 | | Com_create_server | 0 | | Com_create_table | 0 | | Com_create_trigger | 0 | | Com_create_udf | 0 | | Com_create_user | 0 | | Com_create_view | 0 | | Com_dealloc_sql | 0 | | Com_delete | 0 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_event | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_procedure | 0 | | Com_drop_server | 0 | | Com_drop_table | 0 | | Com_drop_trigger | 0 | | Com_drop_user | 0 | | Com_drop_view | 0 | | Com_empty_query | 0 | | Com_execute_sql | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 0 | | Com_insert_select | 0 | | Com_install_plugin | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 0 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_release_savepoint | 0 | | Com_rename_table | 0 | | Com_rename_user | 0 | | Com_repair | 0 | | Com_replace | 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_rollback_to_savepoint | 0 | | Com_savepoint | 0 | | Com_select | 1 | | Com_set_option | 0 | | Com_show_authors | 0 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets | 0 | | Com_show_collations | 0 | | Com_show_column_types | 0 | | Com_show_contributors | 0 | | Com_show_create_db | 0 | | Com_show_create_event | 0 | | Com_show_create_func | 0 | | Com_show_create_proc | 0 | | Com_show_create_table | 0 | | Com_show_create_trigger | 0 | | Com_show_databases | 0 | | Com_show_engine_logs | 0 | | Com_show_engine_mutex | 0 | | Com_show_engine_status | 0 | | Com_show_events | 0 | | Com_show_errors | 0 | | Com_show_fields | 0 | | Com_show_function_status | 0 | | Com_show_grants | 0 | | Com_show_keys | 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_plugins | 0 | | Com_show_privileges | 0 | | Com_show_procedure_status | 0 | | Com_show_processlist | 0 | | Com_show_profile | 0 | | Com_show_profiles | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 1 | | Com_show_storage_engines | 0 | | Com_show_table_status | 0 | | Com_show_tables | 0 | | Com_show_triggers | 0 | | Com_show_variables | 0 | | Com_show_warnings | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_stmt_close | 0 | | Com_stmt_execute | 0 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 0 | | Com_stmt_reprepare | 0 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | | Com_truncate | 0 | | Com_uninstall_plugin | 0 | | Com_unlock_tables | 0 | | Com_update | 0 | | Com_update_multi | 0 | | Com_xa_commit | 0 | | Com_xa_end | 0 | | Com_xa_prepare | 0 | | Com_xa_recover | 0 | | Com_xa_rollback | 0 | | Com_xa_start | 0 | | Compression | OFF | | Connections | 87 | | Created_tmp_disk_tables | 0 | | Created_tmp_files | 87 | | Created_tmp_tables | 0 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 1 | | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_next | 0 | | 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 | | Innodb_buffer_pool_pages_data | 19 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 0 | | Innodb_buffer_pool_pages_free | 493 | | Innodb_buffer_pool_pages_misc | 0 | | Innodb_buffer_pool_pages_total | 512 | | Innodb_buffer_pool_read_ahead_rnd | 1 | | Innodb_buffer_pool_read_ahead_seq | 0 | | Innodb_buffer_pool_read_requests | 77 | | Innodb_buffer_pool_reads | 12 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 0 | | Innodb_data_fsyncs | 3 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 2494464 | | Innodb_data_reads | 25 | | Innodb_data_writes | 3 | | Innodb_data_written | 1536 | | Innodb_dblwr_pages_written | 0 | | Innodb_dblwr_writes | 0 | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 0 | | Innodb_log_writes | 1 | | Innodb_os_log_fsyncs | 3 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 512 | | Innodb_page_size | 16384 | | Innodb_pages_created | 0 | | Innodb_pages_read | 19 | | Innodb_pages_written | 0 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 0 | | Innodb_row_lock_time_avg | 0 | | Innodb_row_lock_time_max | 0 | | Innodb_row_lock_waits | 0 | | Innodb_rows_deleted | 0 | | Innodb_rows_inserted | 0 | | Innodb_rows_read | 0 | | Innodb_rows_updated | 0 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 4852117 | | Key_blocks_used | 2006827 | | Key_read_requests | 5758452015 | | Key_reads | 447924 | | Key_write_requests | 417359004 | | Key_writes | 2623617 | | Last_query_cost | 0.000000 | | Max_used_connections | 4 | | Not_flushed_delayed_rows | 0 | | Open_files | 112 | | Open_streams | 0 | | Open_table_definitions | 54 | | Open_tables | 60 | | Opened_files | 10060 | | Opened_table_definitions | 0 | | Opened_tables | 0 | | Prepared_stmt_count | 0 | | Qcache_free_blocks | 3 | | Qcache_free_memory | 33502088 | | Qcache_hits | 3 | | Qcache_inserts | 24 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 236 | | Qcache_queries_in_cache | 8 | | Qcache_total_blocks | 23 | | Queries | 86450 | | Questions | 2 | | Rpl_status | NULL | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 0 | | Slave_open_temp_tables | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 0 | | Sort_merge_passes | 0 | | Sort_range | 0 | | Sort_rows | 0 | | Sort_scan | 0 | | Ssl_accept_renegotiates | 0 | | Ssl_accepts | 0 | | Ssl_callback_cache_hits | 0 | | Ssl_cipher | | | Ssl_cipher_list | | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_ctx_verify_depth | 0 | | Ssl_ctx_verify_mode | 0 | | Ssl_default_timeout | 0 | | Ssl_finished_accepts | 0 | | Ssl_finished_connects | 0 | | Ssl_session_cache_hits | 0 | | Ssl_session_cache_misses | 0 | | Ssl_session_cache_mode | NONE | | Ssl_session_cache_overflows | 0 | | Ssl_session_cache_size | 0 | | Ssl_session_cache_timeouts | 0 | | Ssl_sessions_reused | 0 | | Ssl_used_session_cache_entries | 0 | | Ssl_verify_depth | 0 | | Ssl_verify_mode | 0 | | Ssl_version | | | Table_locks_immediate | 27591 | | Table_locks_waited | 0 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 0 | | Threads_connected | 4 | | Threads_created | 4 | | Threads_running | 2 | | Uptime | 202522 | | Uptime_since_flush_status | 202522 | +-----------------------------------+------------+ Thanks, Mike Spreitzer mos <mo...@fastmail.fm> 06/25/09 01:05 PM To mysql@lists.mysql.com cc Subject Re: Indexing dynamics in MySQL Community Edition 5.1.34 Mike, I re-posted your Show Status to the group to see if anyone can offer a way to speed up the indexing for you. BTW, you are adding ALL of the indexes to the table using ONE sql statement right? And not a separate SQL statement to build each index? Mike At 02:01 AM 6/25/2009, you wrote: >Like I said in the subject line, I am using 5.1.34. I started with >my-huge.cnf, which says "key_buffer" rather than "key_buffer_size"; SHOW >GLOBAL VARIABLES confirms, however, that my key_buffer_size is 8GB. > >That indexing operation finally finished after about 1.5 hours; that was >about 0.5 hours ago. Now I am on to other things. Here is the status you >suggested: > > [SNIP] > >Thanks, >Mike Spreitzer > > > >mos <mo...@fastmail.fm> > >06/25/09 02:32 AM >To >mysql@lists.mysql.com >cc >Subject >Re: Indexing dynamics in MySQL Community Edition 5.1.34 > > > > >At 12:37 AM 6/25/2009, you wrote: > >Actually, my characterization of the current state is wrong. It appears > >that one core is completely busy, I suppose MySQL does this indexing work > >in a single thread. Is it reasonable for indexing to be CPU bound? > > > > > >my.cnf based on my-huge.cnf, expanding key_buffer to 8G, > >myisam_sort_buffer_size to 256M, and putting tmpdir on the fiber channel > >disk. > >Mike, > You mean "key_buffer_size" don't you and not "key_buffer"? If you are >using MySQL 5.0 then the upper limit is 4gb but 5.1 allows for more than 4gb. >Also try increasing "sort_buffer_size". > >Posting your "Show Status" will help people see where the bottle neck is. > >Mike > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/mysql?unsub=mspre...@us.ibm.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mspre...@us.ibm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mspre...@us.ibm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org