Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. 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 write still occurs.

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
5  
How much memory is in the machine, and how much data are you putting into the memory tables? –  David Spillett May 22 '11 at 11:10
add comment

3 Answers 3

up vote 4 down vote accepted

You may want to watch how many MEMORY tables you are using. Even with a MEMORY table, there must be an open file handle to the .frm of the MEMORY table. Check your open_files_limit. You may need to increase that as well.

You also have a great disparity between read_buffer_size and read_rnd_buffer_size.

You have read_buffer_size at 64M and read_rnd_buffer_size at 4M.

I have never seen a config with read_buffer_size much bigger than read_rnd_buffer_size

You should make it as follows:

read_buffer_size = 16M
read_rnd_buffer_size = 64M

These things contribute to read I/O, not write I/O.

Here is something to consider:

Do you have other tables using MyISAM ???

Do you run JOIN queries that mix MEMORY tables and MyISAM tables ???

Do any temp tables exceed 128M ??

share|improve this answer
    
Thanks for your tips! The "file handle to the .frm" is exactly what the problem was. The write I/O was a result of repeated "DROP TABLE/CREATE TABLE" statements. I replaced those with "TRUNCATE TABLE" and the problem is gone. –  Mike May 23 '11 at 2:05
add comment

The write I/O was a result of repeated DROP TABLE/CREATE TABLE statements. I replaced those with TRUNCATE TABLE and the problem is gone.

share|improve this answer
1  
I will give you +1 on this one because you learned something from your own implementation. I just gave you a hint !!! –  RolandoMySQLDBA May 23 '11 at 2:20
add comment

I think it's time to do some math. What's your system ram vs. the size of table your trying to stuff into it? What is the size of the struct (data storage requirements of each row) you're storing in your memory table?

Have you multiplied that * qty rows to see if that exceeds your values?

http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

MEMORY tables are never converted to disk tables. If an internal temporary table becomes too large, the server automatically converts it to on-disk storage, as described in Section 7.8.4, “How MySQL Uses Internal Temporary Tables”.

Other thoughts: Your limits for tmp_table_size and max_heap_table_size are 128M, but your key_buffer is 24M? Odd. Also, your join and sort buffers appear to be quite small. I would experiment with upping your key_buffer to 128M.

share|improve this answer
    
the key_buffer_size only affects MyISAM (key buffer caches index pages from MYI files). –  RolandoMySQLDBA May 22 '11 at 16:58
    
It just so happens that @Mike asked the exact same question in ServerFault (serverfault.com/questions/272337/…) I suggested the same thing regarding tmp_table_size. At least, you found a doc verifying this possibility. Your other thoughts seem reasonable to me. +1 for you !!! –  RolandoMySQLDBA May 22 '11 at 17:05
add comment

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.