Re: index creation taking too much time
Change the following parameter: myisam_sort_buffer_size=300MB Larger than here. myisam_max_sort_file_size=10GB Reduce this value to 30% of your real memory. On Tue, May 13, 2008 at 7:10 PM, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Krishna, how do i make my index to get more key blocks On 5/13/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi anand, PRIMARY KEY (`id`), KEY `KI_IDX_0805090456` (`words`,`id`), KEY `CI_IDX_0805090456` (`lf_id`) Since id is a primary key. Then why again indexing on id is being created (`words`,`id`). It will be a duplicate index on id. words is a varchar type. So instead of creating fulltext index restrict word(15)). Try. Key buffer seems to be OK. No free key blocks are there. This can be the reason. Key_blocks_unused 0 On Mon, May 12, 2008 at 6:48 PM, Ananda Kumar [EMAIL PROTECTED] 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 -- Krishna Chandra Prajapati -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: index creation taking too much time
Hi anand, PRIMARY KEY (`id`), KEY `KI_IDX_0805090456` (`words`,`id`), KEY `CI_IDX_0805090456` (`lf_id`) Since id is a primary key. Then why again indexing on id is being created (`words`,`id`). It will be a duplicate index on id. words is a varchar type. So instead of creating fulltext index restrict word(15)). Try. Key buffer seems to be OK. No free key blocks are there. This can be the reason. Key_blocks_unused 0 On Mon, May 12, 2008 at 6:48 PM, Ananda Kumar [EMAIL PROTECTED] 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 -- Krishna Chandra Prajapati
Re: index creation taking too much time
Hi Krishna, how do i make my index to get more key blocks On 5/13/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi anand, PRIMARY KEY (`id`), KEY `KI_IDX_0805090456` (`words`,`id`), KEY `CI_IDX_0805090456` (`lf_id`) Since id is a primary key. Then why again indexing on id is being created (`words`,`id`). It will be a duplicate index on id. words is a varchar type. So instead of creating fulltext index restrict word(15)). Try. Key buffer seems to be OK. No free key blocks are there. This can be the reason. Key_blocks_unused 0 On Mon, May 12, 2008 at 6:48 PM, Ananda Kumar [EMAIL PROTECTED] 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 -- Krishna Chandra Prajapati
Re: index creation taking too much time
| Handler_commit| 25802690 | | Handler_delete| 100 | | 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.00 | | 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
Re: index creation taking too much time
myisam_max_sort_file_size=10GB You have alloted 10GB memory to myisam_max_sort_file_size, The system has 8GB of memory. send show global status; On Mon, May 12, 2008 at 6:48 PM, Ananda Kumar [EMAIL PROTECTED] 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 -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED]
Re: index creation taking too much time
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]
