List:General Discussion« Previous MessageNext Message »
From:Moon's Father Date:June 26 2009 8:12am
Subject:Re: Indexing dynamics in MySQL Community Edition 5.1.34
View as plain text  
Who can please tell me what is mean of "The db storage is on fiber
channel."?

On Fri, Jun 26, 2009 at 1:05 AM, mos <mos99@stripped> wrote:

> 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:
>>
>> +-----------------------------------+-----------+
>> | Variable_name                     | Value     |
>> +-----------------------------------+-----------+
>> | Aborted_clients                   | 0         |
>> | Aborted_connects                  | 0         |
>> | Binlog_cache_disk_use             | 0         |
>> | Binlog_cache_use                  | 0         |
>> | Bytes_received                    | 135       |
>> | Bytes_sent                        | 1405      |
>> | 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                | 1         |
>> | 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                       | 9         |
>> | Created_tmp_disk_tables           | 0         |
>> | Created_tmp_files                 | 13        |
>> | Created_tmp_tables                | 1         |
>> | 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             | 34        |
>> | Handler_rollback                  | 0         |
>> | Handler_savepoint                 | 0         |
>> | Handler_savepoint_rollback        | 0         |
>> | Handler_update                    | 0         |
>> | Handler_write                     | 33        |
>> | 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            | 26        |
>> | Key_blocks_unused                 | 6844469   |
>> | Key_blocks_used                   | 676158    |
>> | Key_read_requests                 | 635624989 |
>> | Key_reads                         | 12664     |
>> | Key_write_requests                | 112359740 |
>> | Key_writes                        | 689890    |
>> | Last_query_cost                   | 10.499000 |
>> | Max_used_connections              | 2         |
>> | Not_flushed_delayed_rows          | 0         |
>> | Open_files                        | 70        |
>> | Open_streams                      | 0         |
>> | Open_table_definitions            | 38        |
>> | Open_tables                       | 37        |
>> | Opened_files                      | 312       |
>> | Opened_table_definitions          | 0         |
>> | Opened_tables                     | 0         |
>> | Prepared_stmt_count               | 0         |
>> | Qcache_free_blocks                | 1         |
>> | Qcache_free_memory                | 33536880  |
>> | Qcache_hits                       | 0         |
>> | Qcache_inserts                    | 0         |
>> | Qcache_lowmem_prunes              | 0         |
>> | Qcache_not_cached                 | 12        |
>> | Qcache_queries_in_cache           | 0         |
>> | Qcache_total_blocks               | 1         |
>> | Queries                           | 98        |
>> | Questions                         | 3         |
>> | Rpl_status                        | NULL      |
>> | Select_full_join                  | 0         |
>> | Select_full_range_join            | 0         |
>> | Select_range                      | 0         |
>> | Select_range_check                | 0         |
>> | Select_scan                       | 1         |
>> | 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             | 101       |
>> | 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                 | 2         |
>> | Threads_created                   | 2         |
>> | Threads_running                   | 2         |
>> | Uptime                            | 6952      |
>> | Uptime_since_flush_status         | 6952      |
>> +-----------------------------------+-----------+
>>
>> 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
>
>


-- 
David Yeung,
MySQL Senior Support Engineer,
Sun Gold Partner.
My Blog:http://yueliangdao0608.cublog.cn

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