List:General Discussion« Previous MessageNext Message »
From:Mike Spreitzer Date:June 27 2009 2:03pm
Subject:Re: Indexing dynamics in MySQL Community Edition 5.1.34
View as plain text  
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/IBM@IBMUS 
06/27/09 09:48 AM

To
mos <mos99@stripped>
cc
mysql@stripped
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 <mos99@stripped> 
06/25/09 01:05 PM

To
mysql@stripped
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 <mos99@stripped>
>
>06/25/09 02:32 AM
>To
>mysql@stripped
>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=1
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1



Thread
Indexing dynamics in MySQL Community Edition 5.1.34Mike Spreitzer25 Jun
  • Re: Indexing dynamics in MySQL Community Edition 5.1.34Mike Spreitzer25 Jun
    • Re: Indexing dynamics in MySQL Community Edition 5.1.34mos25 Jun
      • Re: Indexing dynamics in MySQL Community Edition 5.1.34Mike Spreitzer25 Jun
      • Re: Indexing dynamics in MySQL Community Edition 5.1.34Simon J Mudd27 Jun
    • RE: Indexing dynamics in MySQL Community Edition 5.1.34Jerry Schwartz25 Jun
Re: Indexing dynamics in MySQL Community Edition 5.1.34mos25 Jun
  • Re: Indexing dynamics in MySQL Community Edition 5.1.34Moon's Father26 Jun
    • RE: Indexing dynamics in MySQL Community Edition 5.1.34Jerry Schwartz26 Jun
      • RE: Indexing dynamics in MySQL Community Edition 5.1.34Jerry Schwartz26 Jun
  • Re: Indexing dynamics in MySQL Community Edition 5.1.34Mike Spreitzer27 Jun
    • Re: Indexing dynamics in MySQL Community Edition 5.1.34Mike Spreitzer27 Jun
      • Re: Indexing dynamics in MySQL Community Edition 5.1.34Todd Lyons27 Jun