Re: index creation taking too much time
- Date: Tue, 13 May 2008 12:55:01 +0530
- From: "Ananda Kumar" <anandkl@xxxxxxxxx>
- Subject: Re: index creation taking too much time
| 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 <mos99@xxxxxxxxxxx> 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/mysql?unsub=anandkl@xxxxxxxxx
>
>