| Handler_commit | 25802690 | | Handler_delete | 1000000 | | Handler_discover | 0 | | Handler_prepare | 10370014 | | Handler_read_first | 88920 | | Handler_read_key | 496940874 | | Handler_read_next | 664869434 | | Handler_read_prev | 0 | | Handler_read_rnd | 29330217 | | Handler_read_rnd_next | 3285192105 | | Handler_rollback | 31076 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 343453532 | | Handler_write | 1323617337 | | Innodb_buffer_pool_pages_data | 243487 | | Innodb_buffer_pool_pages_dirty | 47410 | | Innodb_buffer_pool_pages_flushed | 12373875 | | Innodb_buffer_pool_pages_free | 0 | | Innodb_buffer_pool_pages_latched | 8 | | Innodb_buffer_pool_pages_misc | 12513 | | Innodb_buffer_pool_pages_total | 256000 | | Innodb_buffer_pool_read_ahead_rnd | 24087 | | Innodb_buffer_pool_read_ahead_seq | 24761 | | Innodb_buffer_pool_read_requests | 4097964853 | | Innodb_buffer_pool_reads | 673174 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 1522044932 | | Innodb_data_fsyncs | 559537 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 60568031232 | | Innodb_data_reads | 1158787 | | Innodb_data_writes | 5265040 | | Innodb_data_written | 520279266304 | | Innodb_dblwr_pages_written | 12373875 | | Innodb_dblwr_writes | 165315 | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 273756463 | | Innodb_log_writes | 154793 | | Innodb_os_log_fsyncs | 157558 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 114805523968 | | Innodb_page_size | 16384 | | Innodb_pages_created | 4941607 | | Innodb_pages_read | 3696646 | | Innodb_pages_written | 12373875 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 3302 | | Innodb_row_lock_time_avg | 16 | | Innodb_row_lock_time_max | 840 | | Innodb_row_lock_waits | 203 | | Innodb_rows_deleted | 313476 | | Innodb_rows_inserted | 533960321 | | Innodb_rows_read | 2338647213 | | Innodb_rows_updated | 2294055 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 0 | | Key_blocks_used | 1673854 | | Key_read_requests | 10242450469 | | Key_reads | 108256939 | | Key_write_requests | 1907823218 | | Key_writes | 58522089 | | Last_query_cost | 0.000000 | | Max_used_connections | 102 | | Ndb_cluster_node_id | 0 | | Ndb_config_from_host | | | Ndb_config_from_port | 0 | | Ndb_number_of_data_nodes | 0 | | Not_flushed_delayed_rows | 0 | | Open_files | 98 | | Open_streams | 0 | | Open_tables | 223 | | Opened_tables | 314 | | Prepared_stmt_count | 0 | | Qcache_free_blocks | 0 | | Qcache_free_memory | 0 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 0 | | Questions | 64304791 | | Rpl_status | NULL | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 26225 | | Select_range_check | 0 | | Select_scan | 1026415 | | Slave_open_temp_tables | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 77800 | | Sort_merge_passes | 280 | | Sort_range | 2405 | | Sort_rows | 29746589 | | Sort_scan | 79442 | | Table_locks_immediate | 25093922 | | Table_locks_waited | 637731 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 49 | | Threads_connected | 53 | | Threads_created | 102 | | Threads_running | 11 | | Uptime | 335703 | | Uptime_since_flush_status | 335703 | +-----------------------------------+--------------+
Krishna,Myisam_max_sort_file_size is a temporary file used by mysql during index creation. This is not allocated from memory, if i am not wrong. Mike, Also my current setting of key_buffer_size is 2GB. show variables like 'key%'; +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | key_buffer_size | 2097152000 | On 5/13/08, mos <[EMAIL PROTECTED]> wrote: > > At 08:18 AM 5/12/2008, you wrote: > > > Hi All, > > We have a table which is around 100 Million rows. Its a myisam table, > > but > > the db default is innodb. > > CREATE TABLE `dc_data` ( > > `id` decimal(22,0) NOT NULL, > > `words` varchar(255) NOT NULL, > > `lf_id` decimal(22,0) NOT NULL, > > `occurence` bigint(20) NOT NULL, > > `date_modified` timestamp NULL default CURRENT_TIMESTAMP on update > > CURRENT_TIMESTAMP) ENGINE=MyIsam DEFAULT CHARSET=utf8 > > > > > > indexs are as below > > > > PRIMARY KEY (`id`), > > KEY `KI_IDX_0805090456` (`words`,`id`), > > KEY `CI_IDX_0805090456` (`lf_id`) > > > > we have 8 cpu, 8 gb ram. > > We use set below parameters at session level > > > > myisam_sort_buffer_size=300MB > > myisam_max_sort_file_size=10GB > > > > Each index creation is taking 10hrs, is there any way i can speed up > > index > > creation. > > > > regards > > anandkl > > > > Anandkl, > The reason it is taking so long is it is building the index using > the hard drive. You can speed up index creation considerably by allocating > up to 30% of your memory to the key buffer size. This will allow it to build > the index mostly in memory and will be at least 10x faster. > > This is done in your My.Cnf file: > > # Size of the Key Buffer, used to cache index blocks for MyISAM tables. > # Do not set it larger than 30% of your available memory, as some memory > # is also required by the OS to cache rows. Even if you're not using > # MyISAM tables, you should still set it to 8-64M as it will also be > # used for internal temporary disk tables. > key_buffer_size=2666M > > > Once you have made the change, you will need to restart MySQL. > > Mike > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
