1

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
                              |
+---------------------------------------------------+--------------------------------------------------------------------------------
2
  • Binary logging is on maybe?
    – Anonymous
    May 22, 2011 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). May 22, 2011 at 2:16

3 Answers 3

1

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

3
  • @f00 Do you know of a way to check what size the tables become? May 22, 2011 at 6:56
  • @MikeFurlender did you discover it? Im'm necrocommenting but that information belongs here.
    – beppe9000
    Nov 16, 2019 at 4:08
  • @beppe9000 I tried for a while to remember, but I couldn't. This was quite a while ago Nov 19, 2019 at 3:08
1

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

2
  • @Joshua What sort of speed improvement would you imagine I'd get? May 22, 2011 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, 2011 at 20:28
0

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.

5
  • @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. May 22, 2011 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, 2011 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. May 22, 2011 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, 2011 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, 2011 at 7:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .