Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I am using the MEMORY Engine for all tables associated with a particular MYSQL query because speed of access is paramount to my project.

For some reason, I have noticed that a large amount of disk I/O still occurs! enter image description here

Is this because of Windows swapping the RAM to disk? How can I prevent this from happening?

Edit: Here are my global variables:

mysql> show global variables;
+---------------------------------------------------+--------------------------------------------------------------------------------
------------------------------+
| Variable_name                                     | Value
                              |
+---------------------------------------------------+--------------------------------------------------------------------------------
------------------------------+
| auto_increment_increment                          | 1
                              |
| auto_increment_offset                             | 1
                              |
| autocommit                                        | ON
                              |
| automatic_sp_privileges                           | ON
                              |
| back_log                                          | 50
                              |
| basedir                                           | C:/Program Files/MySQL/MySQL Server 5.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:\Program Files\MySQL\MySQL Server 5.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\MySQL Server 5.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
                              |
+---------------------------------------------------+--------------------------------------------------------------------------------
share|improve this question

migrated from stackoverflow.com May 26 '11 at 0:41

This question came from our site for professional and enthusiast programmers.

    
Binary logging is on maybe? –  Anonymous May 22 '11 at 1:01
    
@N.B No I don't think its the binary log... I posted my system variables above (and I am going to cross-post this to serverfault). –  Mike Furlender May 22 '11 at 2:16

3 Answers 3

If you're going to store all your data in memory, you might also consider using a memory "database" like Redis or VoltDB.

share|improve this answer
    
@Joshua What sort of speed improvement would you imagine I'd get? –  Mike Furlender May 22 '11 at 6:56
    
@Mike-Furlender, you'll really need to try some things with your actual load pattern to know for sure. –  Anonymous May 22 '11 at 20:28

The following link might shed some light on your issue : http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html

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....

share|improve this answer
    
@f00 Do you know of a way to check what size the tables become? –  Mike Furlender May 22 '11 at 6:56

If you need a memory store, I suppose you don't need to persist the values (otherwise it would be not wise to use a memory table). If that is the case you can use one of the "databases" suggested by Joshua or use memcached, which is pretty fast.

share|improve this answer
    
@stivlo I have no need for the results of my queries to persist. I do, however, need my DB to persist... As of right now I am simply duplicating my permanent MyISAM tables to MEMORY. –  Mike Furlender May 22 '11 at 6:55
    
Normally when you use a cache, you save results of your queries, or even better objects, with a name, then when you need to retrieve an object you ask the cache first, if it's there it will be fast to retrieve, otherwise you execute the query and save results in the cache. Cached values expires after a timeout or you could invalidate them programmatically if you need to do so. How do you do in your case? Are you copying all tables periodically to memory tables? –  stivlo May 22 '11 at 7:05
    
@stivlo I have about 500k rows of numerical data that represents 5 years of financial data. When I query this data it is with unique parameters each time; my queries never repeat. Also the results simply 1 row of information, on which I act immediately and never require again. So a results cache would only slow things down... What I do instead is transfer the static portion (500k rows) of my database to the memory to avoid I/O overhead from the disk. –  Mike Furlender May 22 '11 at 7:22
    
@Mike, I understand, caching works well with websites that tends to extract the same information multiple times. I understand you reasons, but since you're trying to optimize I/O overhead, maybe you should look at the execution plan of your queries and add indexes if needed. It may reduce your I/O a lot (of course I can't quantify a lot without knowing your db, but it could be orders of magnitude). –  stivlo May 22 '11 at 7:29
    
Also if you're using MyIsam enlarge your key cache, it's really small: | key_buffer_size | 25165824 So first add indexes, then enlarge your key buffer. 500k rows is not a lot at all! I've tables with million rows and they can perform well if optimized. –  stivlo May 22 '11 at 7:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.