Como posso evitar que o MYSQL grave no disco?

1

Estou usando o MEMORY Engine para todas as tabelas associadas a uma consulta MYSQL em particular, porque a velocidade de acesso é fundamental para o meu projeto.

Por algum motivo, notei que uma grande quantidade de E / S de disco ainda ocorre!

IssoocorreporqueoWindowsestátrocandoaRAMpelodisco?Comopossoevitarqueissoaconteça?

Edit:aquiestãominhasvariáveisglobais:

mysql>showglobalvariables;+---------------------------------------------------+--------------------------------------------------------------------------------------------------------------+|Variable_name|Value|+---------------------------------------------------+--------------------------------------------------------------------------------------------------------------+|auto_increment_increment|1||auto_increment_offset|1||autocommit|ON||automatic_sp_privileges|ON||back_log|50||basedir|C:/ProgramFiles/MySQL/MySQLServer5.5/||big_tables|OFF||binlog_cache_size|32768||binlog_direct_non_transactional_updates|OFF||binlog_format|STATEMENT||binlog_stmt_cache_size|4096||bulk_insert_buffer_size|8388608||character_set_client|latin1||character_set_connection|latin1||character_set_database|latin1||character_set_filesystem|binary||character_set_results|latin1||character_set_server|latin1||character_set_system|utf8||character_sets_dir|C:\ProgramFiles\MySQL\MySQLServer5.5\share\charsets\||collation_connection|latin1_swedish_ci||collation_database|latin1_swedish_ci||collation_server|latin1_swedish_ci||completion_type|NO_CHAIN||concurrent_insert|AUTO||connect_timeout|10||datadir|C:\ProgramData\MySQL\MySQLServer5.5\Data\||date_format|%Y-%m-%d||datetime_format|%Y-%m-%d%H:%i:%s||default_storage_engine|MyISAM||default_week_format|0||delay_key_write|OFF||delayed_insert_limit|100||delayed_insert_timeout|300||delayed_queue_size|1000||div_precision_increment|4||engine_condition_pushdown|ON||event_scheduler|OFF||expire_logs_days|0||flush|OFF||flush_time|1800||foreign_key_checks|ON||ft_boolean_syntax|+-><()~*:""&|
                              |
| ft_max_word_len                                   | 84
                              |
| ft_min_word_len                                   | 4
                              |
| ft_query_expansion_limit                          | 20
                              |
| ft_stopword_file                                  | (built-in)
                              |
| general_log                                       | OFF
                              |
| general_log_file                                  | C:\ProgramData\MySQL\MySQL Server 5.5\Data\FXMachine.log
                              |
| group_concat_max_len                              | 1024
                              |
| have_compress                                     | YES
                              |
| have_crypt                                        | NO
                              |
| have_csv                                          | YES
                              |
| have_dynamic_loading                              | YES
                              |
| have_geometry                                     | YES
                              |
| have_innodb                                       | DISABLED
                              |
| have_ndbcluster                                   | NO
                              |
| have_openssl                                      | DISABLED
                              |
| have_partitioning                                 | YES
                              |
| have_profiling                                    | YES
                              |
| have_query_cache                                  | YES
                              |
| have_rtree_keys                                   | YES
                              |
| have_ssl                                          | DISABLED
                              |
| have_symlink                                      | YES
                              |
| hostname                                          | FXMachine
                              |
| ignore_builtin_innodb                             | OFF
                              |
| init_connect                                      |
                              |
| init_file                                         |
                              |
| init_slave                                        |
                              |
| interactive_timeout                               | 28800
                              |
| join_buffer_size                                  | 25600
                              |
| keep_files_on_create                              | OFF
                              |
| key_buffer_size                                   | 25165824
                              |
| key_cache_age_threshold                           | 300
                              |
| key_cache_block_size                              | 1024
                              |
| key_cache_division_limit                          | 100
                              |
| large_files_support                               | ON
                              |
| large_page_size                                   | 0
                              |
| large_pages                                       | OFF
                              |
| lc_messages                                       | en_US
                              |
| lc_messages_dir                                   | C:\Program Files\MySQL\MySQL Server 5.5\share\
                              |
| lc_time_names                                     | en_US
                              |
| license                                           | GPL
                              |
| local_infile                                      | ON
                              |
| lock_wait_timeout                                 | 31536000
                              |
| log                                               | OFF
                              |
| log_bin                                           | OFF
                              |
| log_bin_trust_function_creators                   | OFF
                              |
| log_error                                         | C:\ProgramData\MySQL\MySQL Server 5.5\Data\FXMachine.err
                              |
| log_output                                        | FILE
                              |
| log_queries_not_using_indexes                     | OFF
                              |
| log_slave_updates                                 | OFF
                              |
| log_slow_queries                                  | OFF
                              |
| log_warnings                                      | 1
                              |
| long_query_time                                   | 10.000000
                              |
| low_priority_updates                              | OFF
                              |
| lower_case_file_system                            | ON
                              |
| lower_case_table_names                            | 1
                              |
| max_allowed_packet                                | 1048576
                              |
| max_binlog_cache_size                             | 4096
                              |
| max_binlog_size                                   | 4096
                              |
| max_binlog_stmt_cache_size                        | 4096
                              |
| max_connect_errors                                | 10
                              |
| max_connections                                   | 10
                              |
| max_delayed_threads                               | 20
                              |
| max_error_count                                   | 64
                              |
| max_heap_table_size                               | 134217728
                              |
| max_insert_delayed_threads                        | 20
                              |
| max_join_size                                     | 18446744073709551615
                              |
| max_length_for_sort_data                          | 1024
                              |
| max_long_data_size                                | 1048576
                              |
| max_prepared_stmt_count                           | 16382
                              |
| max_relay_log_size                                | 0
                              |
| max_seeks_for_key                                 | 4294967295
                              |
| max_sort_length                                   | 1024
                              |
| max_sp_recursion_depth                            | 0
                              |
| max_tmp_tables                                    | 32
                              |
| max_user_connections                              | 0
                              |
| max_write_lock_count                              | 4294967295
                              |
| min_examined_row_limit                            | 0
                              |
| multi_range_count                                 | 256
                              |
| myisam_data_pointer_size                          | 6
                              |
| myisam_max_sort_file_size                         | 2146435072
                              |
| myisam_mmap_size                                  | 18446744073709551615
                              |
| myisam_recover_options                            | OFF
                              |
| myisam_repair_threads                             | 1
                              |
| myisam_sort_buffer_size                           | 8388608
                              |
| myisam_stats_method                               | nulls_unequal
                              |
| myisam_use_mmap                                   | OFF
                              |
| named_pipe                                        | ON
                              |
| net_buffer_length                                 | 16384
                              |
| net_read_timeout                                  | 30
                              |
| net_retry_count                                   | 10
                              |
| net_write_timeout                                 | 60
                              |
| new                                               | OFF
                              |
| old                                               | OFF
                              |
| old_alter_table                                   | OFF
                              |
| old_passwords                                     | OFF
                              |
| open_files_limit                                  | 2324
                              |
| optimizer_prune_level                             | 1
                              |
| optimizer_search_depth                            | 62
                              |
| optimizer_switch                                  | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_inter
,engine_condition_pushdown=on |
| performance_schema                                | OFF
                              |
| performance_schema_events_waits_history_long_size | 10000
                              |
| performance_schema_events_waits_history_size      | 10
                              |
| performance_schema_max_cond_classes               | 80
                              |
| performance_schema_max_cond_instances             | 1000
                              |
| performance_schema_max_file_classes               | 50
                              |
| performance_schema_max_file_handles               | 32768
                              |
| performance_schema_max_file_instances             | 10000
                              |
| performance_schema_max_mutex_classes              | 200
                              |
| performance_schema_max_mutex_instances            | 1000000
                              |
| performance_schema_max_rwlock_classes             | 30
                              |
| performance_schema_max_rwlock_instances           | 1000000
                              |
| performance_schema_max_table_handles              | 100000
                              |
| performance_schema_max_table_instances            | 50000
                              |
| performance_schema_max_thread_classes             | 50
                              |
| performance_schema_max_thread_instances           | 1000
                              |
| pid_file                                          | C:\ProgramData\MySQL\MySQL Server 5.5\Data\FXMachine.pid
                              |
| plugin_dir                                        | C:\Program Files\MySQL\MySQL Server 5.5\lib/plugin
                              |
| port                                              | 0
                              |
| preload_buffer_size                               | 32768
                              |
| profiling                                         | OFF
                              |
| profiling_history_size                            | 15
                              |
| protocol_version                                  | 10
                              |
| query_alloc_block_size                            | 8192
                              |
| query_cache_limit                                 | 1048576
                              |
| query_cache_min_res_unit                          | 4096
                              |
| query_cache_size                                  | 0
                              |
| query_cache_type                                  | OFF
                              |
| query_cache_wlock_invalidate                      | OFF
                              |
| query_prealloc_size                               | 8192
                              |
| range_alloc_block_size                            | 4096
                              |
| read_buffer_size                                  | 67108864
                              |
| read_only                                         | OFF
                              |
| read_rnd_buffer_size                              | 4194304
                              |
| relay_log                                         |
                              |
| relay_log_index                                   |
                              |
| relay_log_info_file                               | relay-log.info
                              |
| relay_log_purge                                   | ON
                              |
| relay_log_recovery                                | OFF
                              |
| relay_log_space_limit                             | 0
                              |
| report_host                                       |
                              |
| report_password                                   |
                              |
| report_port                                       | 3306
                              |
| report_user                                       |
                              |
| rpl_recovery_rank                                 | 0
                              |
| secure_auth                                       | OFF
                              |
| secure_file_priv                                  |
                              |
| server_id                                         | 0
                              |
| shared_memory                                     | OFF
                              |
| shared_memory_base_name                           | MYSQL
                              |
| skip_external_locking                             | ON
                              |
| skip_name_resolve                                 | OFF
                              |
| skip_networking                                   | ON
                              |
| skip_show_database                                | OFF
                              |
| slave_compressed_protocol                         | OFF
                              |
| slave_exec_mode                                   | STRICT
                              |
| slave_load_tmpdir                                 | C:\Windows\TEMP
                              |
| slave_net_timeout                                 | 3600
                              |
| slave_skip_errors                                 | OFF
                              |
| slave_transaction_retries                         | 10
                              |
| slave_type_conversions                            |
                              |
| slow_launch_time                                  | 2
                              |
| slow_query_log                                    | OFF
                              |
| slow_query_log_file                               | C:\ProgramData\MySQL\MySQL Server 5.5\Data\FXMachine-slow.log
                              |
| socket                                            | mysql
                              |
| sort_buffer_size                                  | 32768
                              |
| sql_auto_is_null                                  | OFF
                              |
| sql_big_selects                                   | ON
                              |
| sql_big_tables                                    | OFF
                              |
| sql_buffer_result                                 | OFF
                              |
| sql_log_bin                                       | OFF
                              |
| sql_log_off                                       | OFF
                              |
| sql_low_priority_updates                          | OFF
                              |
| sql_max_join_size                                 | 18446744073709551615
                              |
| sql_mode                                          | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                              |
| sql_notes                                         | ON
                              |
| sql_quote_show_create                             | ON
                              |
| sql_safe_updates                                  | OFF
                              |
| sql_select_limit                                  | 18446744073709551615
                              |
| sql_slave_skip_counter                            | 0
                              |
| sql_warnings                                      | OFF
                              |
| ssl_ca                                            |
                              |
| ssl_capath                                        |
                              |
| ssl_cert                                          |
                              |
| ssl_cipher                                        |
                              |
| ssl_key                                           |
                              |
| storage_engine                                    | MyISAM
                              |
| sync_binlog                                       | 0
                              |
| sync_frm                                          | ON
                              |
| sync_master_info                                  | 0
                              |
| sync_relay_log                                    | 0
                              |
| sync_relay_log_info                               | 0
                              |
| system_time_zone                                  | Eastern Daylight Time
                              |
| table_definition_cache                            | 400
                              |
| table_open_cache                                  | 128
                              |
| thread_cache_size                                 | 0
                              |
| thread_concurrency                                | 10
                              |
| thread_handling                                   | one-thread-per-connection
                              |
| thread_stack                                      | 262144
                              |
| time_format                                       | %H:%i:%s
                              |
| time_zone                                         | SYSTEM
                              |
| timed_mutexes                                     | OFF
                              |
| tmp_table_size                                    | 134217728
                              |
| tmpdir                                            | C:\Windows\TEMP
                              |
| transaction_alloc_block_size                      | 8192
                              |
| transaction_prealloc_size                         | 4096
                              |
| tx_isolation                                      | REPEATABLE-READ
                              |
| unique_checks                                     | ON
                              |
| updatable_views_with_limit                        | YES
                              |
| version                                           | 5.5.11
                              |
| version_comment                                   | MySQL Community Server (GPL)
                              |
| version_compile_machine                           | x86
                              |
| version_compile_os                                | Win64
                              |
| wait_timeout                                      | 28800
                              |
+---------------------------------------------------+--------------------------------------------------------------------------------
    
por Mike Furlender 22.05.2011 / 02:26

3 respostas

1

Se você for armazenar todos os seus dados na memória, você também pode considerar usar um "banco de dados" de memória como Redis ou VoltDB.

    
por 22.05.2011 / 06:08
0

O link a seguir pode lançar alguma luz sobre o seu problema: link

If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values....

    
por 22.05.2011 / 05:47
0

Se você precisar de um armazenamento de memória, suponho que não seja necessário persistir os valores (caso contrário, não seria sensato usar uma tabela de memória). Se esse for o caso, você pode usar um dos "bancos de dados" sugeridos por Joshua ou usar o memcached , que é bastante rápido.

    
por 22.05.2011 / 06:17