List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:June 26 2009 2:22pm
Subject:RE: Indexing dynamics in MySQL Community Edition 5.1.34
View as plain text  

>-----Original Message-----
>From: Moon's Father [mailto:yueliangdao0608@stripped]
>Sent: Friday, June 26, 2009 4:12 AM
>To: mos
>Cc: mysql@stripped
>Subject: Re: Indexing dynamics in MySQL Community Edition 5.1.34
>
>Who can please tell me what is mean of "The db storage is on fiber
>channel."?
>
[JS] Fiber Channel (and that is the official way it is spelled, even in the
USA) is an ultra-high-speed network for disk arrays, computers, and other
devices. Although the physical medium is usual fiber optic, there is a lot
more to it than that. It is more like an intranet in some ways, in that
devices have addresses and there is intelligence in the network itself.

>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