List:General Discussion« Previous MessageNext Message »
From:HMax Date:February 21 2005 3:21pm
Subject:Re: LOAD INDEX INTO CACHE problem
View as plain text  
So this means we cannot combine both FULLTEXT and classical indexes if
we want to use a LOAD INDEX INTO CACHE, and that we won't ever be able
to ?

How about being able to specify the indexes we want to load into the
cache. It's supposed to work this way (but it is told in the doc it
doesn't yet). This would solve the problem I believe, if we specify
what index we want in cache.

What I don't undestand is that when not cached using LOAD INDEX INTO
CACHE, FULLTEXT indexes can be into cache, the ones on VARCHAR too,
and this does not see to cause any trouble. But using LOAD INDEX, it
doesn't work. Is there really no workaround ? We have for about 1.5Go
of fulltext indexes and if they were in cache, this would speed up
things so much !

Thx for your advices
HMax


On Fri, 18 Feb 2005 18:01:29 +0200, Gleb Paharenko
<gleb.paharenko@stripped> wrote:
> Hello.
> 
> Sergei Golubchik said that we can't change the value of the blocksize
> 
> of a key (it is chosen in mi_create.c) and there is no workaround
> 
> with this LOAD INDEX problem.
> 
> 
> HMax <chehax@stripped> wrote:
> 
> > Hello there.
> 
> >
> 
> > OK I'll paste the results of commands you asked right after my reply,
> 
> > because we found out where the problem comes from.
> 
> > The myisamchk command showed that the index on the VarChar has a block
> 
> > size of 2048 instead of 1024. However, when I turn this index to a
> 
> > FULLTEXT one, the block size is back to 1024, and the LOAD INDEX INTO
> 
> > CACHE works.
> 
> >
> 
> > Now this is a problem because our huge table needs both our FULLTEXT
> 
> > indexes and some on VARCHAR fields too. At least we know where it
> 
> > comes from. Now, is there a fast solution ? We were waiting for this
> 
> > bug correction to study a release date for our application :/
> 
> >
> 
> > Thank you, and here is the results :
> 
> >
> 
> >
> 
> > SHOW CREATE TABLE=20
> 
> > `tbltest`;
> 
> >
> 
> >
> 
> > CREATE TABLE `tbltest` (
> 
> >  `testid` int(10) unsigned NOT NULL auto_increment,
> 
> >  `testvalue` varchar(100) NOT NULL default '',
> 
> >  PRIMARY KEY  (`testid`),
> 
> >  KEY `BOB` (`testvalue`)
> 
> > ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8
> 
> >
> 
> >
> 
> > SHOW VARIABLES;
> 
> >
> 
> > +---------------------------------+---------------------------------+
> 
> > | Variable_name                   | Value                           |
> 
> > +---------------------------------+---------------------------------+
> 
> > | back_log                        | 50                              |
> 
> > | basedir                         | D:\mysql\4.1\                   |
> 
> > | binlog_cache_size               | 32768                           |
> 
> > | bulk_insert_buffer_size         | 8388608                         |
> 
> > | character_set_client            | utf8                            |
> 
> > | character_set_connection        | utf8                            |
> 
> > | character_set_database          | utf8                            |
> 
> > | character_set_results           | utf8                            |
> 
> > | character_set_server            | utf8                            |
> 
> > | character_set_system            | utf8                            |
> 
> > | character_sets_dir              | D:\mysql\4.1\share\charsets/    |
> 
> > | collation_connection            | utf8_general_ci                 |
> 
> > | collation_database              | utf8_general_ci                 |
> 
> > | collation_server                | utf8_general_ci                 |
> 
> > | concurrent_insert               | ON                              |
> 
> > | connect_timeout                 | 5                               |
> 
> > | datadir                         | D:\mysql\4.1\Data\              |
> 
> > | date_format                     | %Y-%m-%d                        |
> 
> > | datetime_format                 | %Y-%m-%d %H:%i:%s               |
> 
> > | default_week_format             | 0                               |
> 
> > | delay_key_write                 | ON                              |
> 
> > | delayed_insert_limit            | 100                             |
> 
> > | delayed_insert_timeout          | 300                             |
> 
> > | delayed_queue_size              | 1000                            |
> 
> > | expire_logs_days                | 0                               |
> 
> > | flush                           | OFF                             |
> 
> > | flush_time                      | 1800                            |
> 
> > | ft_boolean_syntax               | + -><()~*:""&|                  |
> 
> > | ft_max_word_len                 | 84                              |
> 
> > | ft_min_word_len                 | 4                               |
> 
> > | ft_query_expansion_limit        | 20                              |
> 
> > | ft_stopword_file                | (built-in)                      |
> 
> > | group_concat_max_len            | 1024                            |
> 
> > | have_archive                    | NO                              |
> 
> > | have_bdb                        | NO                              |
> 
> > | have_compress                   | YES                             |
> 
> > | have_crypt                      | NO                              |
> 
> > | have_csv                        | NO                              |
> 
> > | have_example_engine             | NO                              |
> 
> > | have_geometry                   | YES                             |
> 
> > | have_innodb                     | DISABLED                        |
> 
> > | have_isam                       | NO                              |
> 
> > | have_ndbcluster                 | NO                              |
> 
> > | have_openssl                    | NO                              |
> 
> > | have_query_cache                | YES                             |
> 
> > | have_raid                       | NO                              |
> 
> > | have_rtree_keys                 | YES                             |
> 
> > | have_symlink                    | YES                             |
> 
> > | init_connect                    |                                 |
> 
> > | init_file                       |                                 |
> 
> > | init_slave                      |                                 |
> 
> > | innodb_additional_mem_pool_size | 2097152                         |
> 
> > | innodb_autoextend_increment     | 8                               |
> 
> > | innodb_buffer_pool_awe_mem_mb   | 0                               |
> 
> > | innodb_buffer_pool_size         | 8388608                         |
> 
> > | innodb_data_file_path           |                                 |
> 
> > | innodb_data_home_dir            |                                 |
> 
> > | innodb_fast_shutdown            | ON                              |
> 
> > | innodb_file_io_threads          | 4                               |
> 
> > | innodb_file_per_table           | OFF                             |
> 
> > | innodb_flush_log_at_trx_commit  | 1                               |
> 
> > | innodb_flush_method             |                                 |
> 
> > | innodb_force_recovery           | 0                               |
> 
> > | innodb_lock_wait_timeout        | 50                              |
> 
> > | innodb_locks_unsafe_for_binlog  | OFF                             |
> 
> > | innodb_log_arch_dir             |                                 |
> 
> > | innodb_log_archive              | OFF                             |
> 
> > | innodb_log_buffer_size          | 1048576                         |
> 
> > | innodb_log_file_size            | 10485760                        |
> 
> > | innodb_log_files_in_group       | 2                               |
> 
> > | innodb_log_group_home_dir       |                                 |
> 
> > | innodb_max_dirty_pages_pct      | 90                              |
> 
> > | innodb_max_purge_lag            | 0                               |
> 
> > | innodb_mirrored_log_groups      | 1                               |
> 
> > | innodb_open_files               | 300                             |
> 
> > | innodb_table_locks              | ON                              |
> 
> > | innodb_thread_concurrency       | 8                               |
> 
> > | interactive_timeout             | 28800                           |
> 
> > | join_buffer_size                | 131072                          |
> 
> > | key_buffer_size                 | 10485760                        |
> 
> > | key_cache_age_threshold         | 300                             |
> 
> > | key_cache_block_size            | 1024                            |
> 
> > | key_cache_division_limit        | 100                             |
> 
> > | language                        | D:\mysql\4.1\share\english\     |
> 
> > | large_files_support             | ON                              |
> 
> > | license                         | GPL                             |
> 
> > | local_infile                    | ON                              |
> 
> > | log                             | OFF                             |
> 
> > | log_bin                         | OFF                             |
> 
> > | log_error                       | .\testserver.err                |
> 
> > | log_slave_updates               | OFF                             |
> 
> > | log_slow_queries                | OFF                             |
> 
> > | log_update                      | OFF                             |
> 
> > | log_warnings                    | 1                               |
> 
> > | long_query_time                 | 10                              |
> 
> > | low_priority_updates            | OFF                             |
> 
> > | lower_case_file_system          | OFF                             |
> 
> > | lower_case_table_names          | 1                               |
> 
> > | max_allowed_packet              | 1048576                         |
> 
> > | max_binlog_cache_size           | 4294967295                      |
> 
> > | max_binlog_size                 | 1073741824                      |
> 
> > | max_connect_errors              | 10                              |
> 
> > | max_connections                 | 100                             |
> 
> > | max_delayed_threads             | 20                              |
> 
> > | max_error_count                 | 64                              |
> 
> > | max_heap_table_size             | 16777216                        |
> 
> > | max_insert_delayed_threads      | 20                              |
> 
> > | max_join_size                   | 4294967295                      |
> 
> > | max_length_for_sort_data        | 1024                            |
> 
> > | max_relay_log_size              | 0                               |
> 
> > | max_seeks_for_key               | 4294967295                      |
> 
> > | max_sort_length                 | 1024                            |
> 
> > | max_tmp_tables                  | 32                              |
> 
> > | max_user_connections            | 0                               |
> 
> > | max_write_lock_count            | 4294967295                      |
> 
> > | myisam_data_pointer_size        | 4                               |
> 
> > | myisam_max_extra_sort_file_size | 107374182400                    |
> 
> > | myisam_max_sort_file_size       | 107374182400                    |
> 
> > | myisam_recover_options          | OFF                             |
> 
> > | myisam_repair_threads           | 1                               |
> 
> > | myisam_sort_buffer_size         | 10485760                        |
> 
> > | named_pipe                      | OFF                             |
> 
> > | net_buffer_length               | 16384                           |
> 
> > | net_read_timeout                | 30                              |
> 
> > | net_retry_count                 | 10                              |
> 
> > | net_write_timeout               | 60                              |
> 
> > | new                             | OFF                             |
> 
> > | old_passwords                   | OFF                             |
> 
> > | open_files_limit                | 622                             |
> 
> > | pid_file                        | D:\mysql\4.1\Data\testserver.pid|
> 
> > | port                            | 3307                            |
> 
> > | preload_buffer_size             | 32768                           |
> 
> > | protocol_version                | 10                              |
> 
> > | query_alloc_block_size          | 8192                            |
> 
> > | query_cache_limit               | 1048576                         |
> 
> > | query_cache_min_res_unit        | 4096                            |
> 
> > | query_cache_size                | 8388608                         |
> 
> > | query_cache_type                | ON                              |
> 
> > | query_cache_wlock_invalidate    | OFF                             |
> 
> > | query_prealloc_size             | 8192                            |
> 
> > | range_alloc_block_size          | 2048                            |
> 
> > | read_buffer_size                | 61440                           |
> 
> > | read_only                       | OFF                             |
> 
> > | read_rnd_buffer_size            | 258048                          |
> 
> > | relay_log_purge                 | ON                              |
> 
> > | rpl_recovery_rank               | 0                               |
> 
> > | secure_auth                     | OFF                             |
> 
> > | shared_memory                   | OFF                             |
> 
> > | shared_memory_base_name         | MYSQL                           |
> 
> > | server_id                       | 0                               |
> 
> > | skip_external_locking           | ON                              |
> 
> > | skip_networking                 | OFF                             |
> 
> > | skip_show_database              | OFF                             |
> 
> > | slave_net_timeout               | 3600                            |
> 
> > | slow_launch_time                | 2                               |
> 
> > | sort_buffer_size                | 262136                          |
> 
> > | sql_mode                        |                                 |
> 
> > | storage_engine                  | MyISAM                          |
> 
> > | sync_binlog                     | 0                               |
> 
> > | sync_replication                | 0                               |
> 
> > | sync_replication_slave_id       | 0                               |
> 
> > | sync_replication_timeout        | 0                               |
> 
> > | sync_frm                        | ON                              |
> 
> > | system_time_zone                | Paris, Madrid                   |
> 
> > | table_cache                     | 256                             |
> 
> > | table_type                      | MyISAM                          |
> 
> > | thread_cache_size               | 8                               |
> 
> > | thread_stack                    | 196608                          |
> 
> > | time_format                     | %H:%i:%s                        |
> 
> > | time_zone                       | SYSTEM                          |
> 
> > | tmp_table_size                  | 7340032                         |
> 
> > | tmpdir                          |                                 |
> 
> > | transaction_alloc_block_size    | 8192                            |
> 
> > | transaction_prealloc_size       | 4096                            |
> 
> > | tx_isolation                    | REPEATABLE-READ                 |
> 
> > | version                         | 4.1.10-nt                       |
> 
> > | version_comment                 | MySQL Community Edition (GPL)   |
> 
> > | version_compile_machine         | i32                             |
> 
> > | version_compile_os              | NT                              |
> 
> > | wait_timeout                    | 28800                           |
> 
> > +---------------------------------+---------------------------------+
> 
> >
> 
> > SHOW STATUS;
> 
> >
> 
> > +--------------------------+---------+
> 
> > | Variable_name            | Value   |
> 
> > +--------------------------+---------+
> 
> > | Aborted_clients          | 0       |
> 
> > | Aborted_connects         | 0       |
> 
> > | Binlog_cache_disk_use    | 0       |
> 
> > | Binlog_cache_use         | 0       |
> 
> > | Bytes_received           | 486     |
> 
> > | Bytes_sent               | 3759    |
> 
> > | Com_admin_commands       | 0       |
> 
> > | Com_alter_db             | 0       |
> 
> > | Com_alter_table          | 0       |
> 
> > | Com_analyze              | 0       |
> 
> > | Com_backup_table         | 0       |
> 
> > | Com_begin                | 0       |
> 
> > | Com_change_db            | 2       |
> 
> > | Com_change_master        | 0       |
> 
> > | Com_check                | 0       |
> 
> > | Com_checksum             | 0       |
> 
> > | Com_commit               | 0       |
> 
> > | Com_create_db            | 0       |
> 
> > | Com_create_function      | 0       |
> 
> > | Com_create_index         | 0       |
> 
> > | Com_create_table         | 0       |
> 
> > | Com_dealloc_sql          | 0       |
> 
> > | Com_delete               | 0       |
> 
> > | Com_delete_multi         | 0       |
> 
> > | Com_do                   | 0       |
> 
> > | Com_drop_db              | 0       |
> 
> > | Com_drop_function        | 0       |
> 
> > | Com_drop_index           | 0       |
> 
> > | Com_drop_table           | 0       |
> 
> > | Com_drop_user            | 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_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_rename_table         | 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_savepoint            | 0       |
> 
> > | Com_select               | 0       |
> 
> > | Com_set_option           | 2       |
> 
> > | Com_show_binlog_events   | 0       |
> 
> > | Com_show_binlogs         | 0       |
> 
> > | Com_show_charsets        | 0       |
> 
> > | Com_show_collations      | 0       |
> 
> > | Com_show_column_types    | 0       |
> 
> > | Com_show_create_db       | 0       |
> 
> > | Com_show_create_table    | 1       |
> 
> > | Com_show_databases       | 1       |
> 
> > | Com_show_errors          | 0       |
> 
> > | Com_show_fields          | 2       |
> 
> > | Com_show_grants          | 0       |
> 
> > | Com_show_innodb_status   | 0       |
> 
> > | Com_show_keys            | 0       |
> 
> > | Com_show_logs            | 0       |
> 
> > | Com_show_master_status   | 0       |
> 
> > | Com_show_new_master      | 0       |
> 
> > | Com_show_open_tables     | 0       |
> 
> > | Com_show_privileges      | 0       |
> 
> > | Com_show_processlist     | 0       |
> 
> > | Com_show_slave_hosts     | 0       |
> 
> > | Com_show_slave_status    | 0       |
> 
> > | Com_show_status          | 1       |
> 
> > | Com_show_storage_engines | 0       |
> 
> > | Com_show_tables          | 2       |
> 
> > | Com_show_variables       | 1       |
> 
> > | Com_show_warnings        | 0       |
> 
> > | Com_slave_start          | 0       |
> 
> > | Com_slave_stop           | 0       |
> 
> > | Com_truncate             | 0       |
> 
> > | Com_unlock_tables        | 0       |
> 
> > | Com_update               | 0       |
> 
> > | Com_update_multi         | 0       |
> 
> > | Connections              | 3       |
> 
> > | Created_tmp_disk_tables  | 0       |
> 
> > | Created_tmp_files        | 0       |
> 
> > | Created_tmp_tables       | 0       |
> 
> > | Delayed_errors           | 0       |
> 
> > | Delayed_insert_threads   | 0       |
> 
> > | Delayed_writes           | 0       |
> 
> > | Flush_commands           | 1       |
> 
> > | Handler_commit           | 0       |
> 
> > | Handler_delete           | 0       |
> 
> > | Handler_discover         | 0       |
> 
> > | Handler_read_first       | 2       |
> 
> > | Handler_read_key         | 0       |
> 
> > | Handler_read_next        | 0       |
> 
> > | Handler_read_prev        | 0       |
> 
> > | Handler_read_rnd         | 0       |
> 
> > | Handler_read_rnd_next    | 10      |
> 
> > | Handler_rollback         | 0       |
> 
> > | Handler_update           | 0       |
> 
> > | Handler_write            | 0       |
> 
> > | Key_blocks_not_flushed   | 0       |
> 
> > | Key_blocks_unused        | 8981    |
> 
> > | Key_blocks_used          | 0       |
> 
> > | Key_read_requests        | 0       |
> 
> > | Key_reads                | 0       |
> 
> > | Key_write_requests       | 0       |
> 
> > | Key_writes               | 0       |
> 
> > | Max_used_connections     | 2       |
> 
> > | Not_flushed_delayed_rows | 0       |
> 
> > | Open_files               | 2       |
> 
> > | Open_streams             | 0       |
> 
> > | Open_tables              | 1       |
> 
> > | Opened_tables            | 12      |
> 
> > | Qcache_free_blocks       | 1       |
> 
> > | Qcache_free_memory       | 8379904 |
> 
> > | Qcache_hits              | 0       |
> 
> > | Qcache_inserts           | 0       |
> 
> > | Qcache_lowmem_prunes     | 0       |
> 
> > | Qcache_not_cached        | 0       |
> 
> > | Qcache_queries_in_cache  | 0       |
> 
> > | Qcache_total_blocks      | 1       |
> 
> > | Questions                | 12      |
> 
> > | Rpl_status               | NULL    |
> 
> > | Select_full_join         | 0       |
> 
> > | Select_full_range_join   | 0       |
> 
> > | Select_range             | 0       |
> 
> > | Select_range_check       | 0       |
> 
> > | Select_scan              | 0       |
> 
> > | Slave_open_temp_tables   | 0       |
> 
> > | Slave_running            | OFF     |
> 
> > | Slow_launch_threads      | 0       |
> 
> > | Slow_queries             | 0       |
> 
> > | Sort_merge_passes        | 0       |
> 
> > | Sort_range               | 0       |
> 
> > | Sort_rows                | 0       |
> 
> > | Sort_scan                | 0       |
> 
> > | Table_locks_immediate    | 11      |
> 
> > | Table_locks_waited       | 0       |
> 
> > | Threads_cached           | 0       |
> 
> > | Threads_connected        | 2       |
> 
> > | Threads_created          | 2       |
> 
> > | Threads_running          | 1       |
> 
> > | Uptime                   | 315     |
> 
> > +--------------------------+---------+
> 
> >
> 
> > C:\Documents and Settings\S=E9verine MOREL>D:\mysql\4.1\bin\myisamchk.exe -=
> 
> > d -v D:
> 
> > \mysql\4.1\data\test\tbltest
> 
> >
> 
> > MyISAM file:         D:\mysql\4.1\data\test\tbltest
> 
> > Record format:       Packed
> 
> > Character set:       utf8_general_ci (33)
> 
> > File-version:        1
> 
> > Creation time:       2005-02-16 11:23:24
> 
> > Status:              changed
> 
> > Auto increment key:              1  Last value:                    10
> 
> > Data records:                   10  Deleted blocks:                 0
> 
> > Datafile parts:                 10  Deleted data:                   0
> 
> > Datafile pointer (bytes):        4  Keyfile pointer (bytes):        4
> 
> > Datafile length:               440  Keyfile length:              4096
> 
> > Max datafile length:    4294967294  Max keyfile length: 4398046510079
> 
> > Recordlength:                  305
> 
> >
> 
> > table description:
> 
> > Key Start Len Index   Type                     Rec/key         Root  Blocks=
> 
> > ize
> 
> > 1   1     4   unique  unsigned long                  1         1024       1=
> 
> > 024
> 
> > 2   5     300 multip. char packed stripped           0         2048       2=
> 
> > 048
> 
> >
> 
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
>   __  ___     ___ ____  __
>  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
> / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
>       <___/   www.mysql.com
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
> 


-- 
HMax
Thread
LOAD INDEX INTO CACHE problemHMax16 Feb
  • Re: LOAD INDEX INTO CACHE problemGleb Paharenko17 Feb
    • Re: LOAD INDEX INTO CACHE problemHMax17 Feb
      • Re: LOAD INDEX INTO CACHE problemGleb Paharenko21 Feb
        • Re: LOAD INDEX INTO CACHE problemHMax21 Feb
          • Re: LOAD INDEX INTO CACHE problemSergei Golubchik21 Feb
            • Re: LOAD INDEX INTO CACHE problemHMax21 Feb
              • Re: LOAD INDEX INTO CACHE problemSergei Golubchik21 Feb
                • Re: LOAD INDEX INTO CACHE problemHMax22 Feb
                  • Re: LOAD INDEX INTO CACHE problemHMax22 Feb
          • Re: LOAD INDEX INTO CACHE problemGleb Paharenko21 Feb
Re: LOAD INDEX INTO CACHE problemCheHax28 Feb