large file operations exhaust memory
Posted by: Tomas Singliar
Date: July 02, 2012 09:52AM
Date: July 02, 2012 09:52AM
Update: the issue appears to be related to Windows write caching behavior, rather than MySQL itself. It seems either Windows or the device driver or someone like that believes that freshly written files should be kept cached in memory regardless of size. This of course affects large file writes in MySQL.
Hi,
I have some huge and skinny tables, 1-10 billion rows, only 4 fields.
The intended workload is analytical - write once, select various subsets often, so the big ones use MyISAM. Typical table file size is 5GB-50GB.
I am having trouble indexing the files I bulk-loaded, as well as materializing some large queries as tables.
My instance of MySQL exhausts all available memory on large file operations. Total Memory usage as reported by Task manager reaches 24GB (the amount of physical memory), and stays there. System becomes unstable, and errors of the "out of memory" kind appear in the system log. There is nothing useful in the mysql error log.
The memory allocated does NOT appear in mysqld's working set or commit size.
It appears MySQL wants to cache all the data it's written in memory. The memory remains allocated after the query completes, but FLUSH TABLES releases the memory immediately. (More often that completing it crashes the system.) Looks like memory-mapping of some sort is going on, but myisam_use_mmap is OFF.
Any ideas?
Thanks,
Tomas
--------------------------------------------------------------------------
Running server version: 5.5.25 MySQL Community Server (GPL) on Win7 64bit.
6 cores with HT (12 apparent cores), 24GB RAM, striped Win7 soft RAID volume
[client]
# pipe
# socket=mysql
port=3306
[mysql]
default-character-set=utf8
[mysqld]
# The next three options are mutually exclusive to SERVER_PORT below.
# skip-networking
# enable-named-pipe
# The Pipe the MySQL Server will use
# socket=mysql
# The TCP/IP Port the MySQL Server will listen on
port=3306
# Path to installation directory. All paths are usually resolved relative to this.
basedir="C:\Program Files\MySQL\MySQL Server 5.5\"
tmpdir="f:/mysqltemp"
# Path to the database root
# WAS: datadir="C:\ProgramData\MySQL Server 5.5\data\"
datadir="F:/mysqldata"
character-set-server=utf8
# The default storage engine that will be used when create new tables when
default-storage-engine=MyISAM
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=200
query_cache_size=64M
table_cache=256
# tmp_table_size=128M
# max_heap_table_size=128M
max_heap_table_size=8M
tmp_table_size=8M
# myisam_repair_threads=8
thread_cache_size=12
# total =
# innodb_buffer_pool_size +
# key_buffer_size +
# innodb_additional_mem_pool_size +
# innodb_log_buffer_size +
# max_connections *
# (sort_buffer_size +
# read_buffer_size +
# binlog_cache_size +
# maximum_thread_stack_size);
# This was 128G. Let's see whether we can at least successfully index things
# myisam_max_sort_file_size=8G
myisam_max_sort_file_size=128G
myisam_sort_buffer_size=512M
key_buffer_size=2000M
read_buffer_size=2M
read_rnd_buffer_size=4M
myisam_mmap_size=1G
flush=ON
flush_time=60
sort_buffer_size=256M
#*** INNODB Specific options ***
# innodb_data_home_dir=0.0
innodb_additional_mem_pool_size=64M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=4M
innodb_buffer_pool_size=6192M
innodb_log_file_size=512M
innodb_thread_concurrency=4
--------------------------------------------------------------------------
mysql> show variables like '%flush%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| flush | ON |
| flush_time | 0 |
| innodb_adaptive_flushing | ON |
| innodb_flush_log_at_trx_commit | 0 |
| innodb_flush_method | |
+--------------------------------+-------+
5 rows in set (0.00 sec)
mysql> show variables like '%buffer%';
+------------------------------+------------+
| Variable_name | Value |
+------------------------------+------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 6492782592 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 4194304 |
| join_buffer_size | 131072 |
| key_buffer_size | 2097152000 |
| myisam_sort_buffer_size | 536870912 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 2097152 |
| read_rnd_buffer_size | 4194304 |
| sort_buffer_size | 268435456 |
| sql_buffer_result | OFF |
+------------------------------+------------+
14 rows in set (0.00 sec)
mysql> show variables like '%max%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| ft_max_word_len | 84 |
| group_concat_max_len | 1024 |
| innodb_file_format_max | Antelope |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_purge_lag | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_connect_errors | 10 |
| max_connections | 200 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 8388608 |
| 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 |
| myisam_max_sort_file_size | 137438953472 |
| 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 |
| sql_max_join_size | 18446744073709551615 |
+-----------------------------------------+----------------------+
41 rows in set (0.00 sec)
mysql> show variables like '%mmap%';
+------------------+------------+
| Variable_name | Value |
+------------------+------------+
| myisam_mmap_size | 1073741824 |
| myisam_use_mmap | OFF |
+------------------+------------+
2 rows in set (0.00 sec)
Edited 3 time(s). Last edit at 07/04/2012 02:13AM by Tomas Singliar.
Hi,
I have some huge and skinny tables, 1-10 billion rows, only 4 fields.
The intended workload is analytical - write once, select various subsets often, so the big ones use MyISAM. Typical table file size is 5GB-50GB.
I am having trouble indexing the files I bulk-loaded, as well as materializing some large queries as tables.
My instance of MySQL exhausts all available memory on large file operations. Total Memory usage as reported by Task manager reaches 24GB (the amount of physical memory), and stays there. System becomes unstable, and errors of the "out of memory" kind appear in the system log. There is nothing useful in the mysql error log.
The memory allocated does NOT appear in mysqld's working set or commit size.
It appears MySQL wants to cache all the data it's written in memory. The memory remains allocated after the query completes, but FLUSH TABLES releases the memory immediately. (More often that completing it crashes the system.) Looks like memory-mapping of some sort is going on, but myisam_use_mmap is OFF.
Any ideas?
Thanks,
Tomas
--------------------------------------------------------------------------
Running server version: 5.5.25 MySQL Community Server (GPL) on Win7 64bit.
6 cores with HT (12 apparent cores), 24GB RAM, striped Win7 soft RAID volume
[client]
# pipe
# socket=mysql
port=3306
[mysql]
default-character-set=utf8
[mysqld]
# The next three options are mutually exclusive to SERVER_PORT below.
# skip-networking
# enable-named-pipe
# The Pipe the MySQL Server will use
# socket=mysql
# The TCP/IP Port the MySQL Server will listen on
port=3306
# Path to installation directory. All paths are usually resolved relative to this.
basedir="C:\Program Files\MySQL\MySQL Server 5.5\"
tmpdir="f:/mysqltemp"
# Path to the database root
# WAS: datadir="C:\ProgramData\MySQL Server 5.5\data\"
datadir="F:/mysqldata"
character-set-server=utf8
# The default storage engine that will be used when create new tables when
default-storage-engine=MyISAM
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=200
query_cache_size=64M
table_cache=256
# tmp_table_size=128M
# max_heap_table_size=128M
max_heap_table_size=8M
tmp_table_size=8M
# myisam_repair_threads=8
thread_cache_size=12
# total =
# innodb_buffer_pool_size +
# key_buffer_size +
# innodb_additional_mem_pool_size +
# innodb_log_buffer_size +
# max_connections *
# (sort_buffer_size +
# read_buffer_size +
# binlog_cache_size +
# maximum_thread_stack_size);
# This was 128G. Let's see whether we can at least successfully index things
# myisam_max_sort_file_size=8G
myisam_max_sort_file_size=128G
myisam_sort_buffer_size=512M
key_buffer_size=2000M
read_buffer_size=2M
read_rnd_buffer_size=4M
myisam_mmap_size=1G
flush=ON
flush_time=60
sort_buffer_size=256M
#*** INNODB Specific options ***
# innodb_data_home_dir=0.0
innodb_additional_mem_pool_size=64M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=4M
innodb_buffer_pool_size=6192M
innodb_log_file_size=512M
innodb_thread_concurrency=4
--------------------------------------------------------------------------
mysql> show variables like '%flush%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| flush | ON |
| flush_time | 0 |
| innodb_adaptive_flushing | ON |
| innodb_flush_log_at_trx_commit | 0 |
| innodb_flush_method | |
+--------------------------------+-------+
5 rows in set (0.00 sec)
mysql> show variables like '%buffer%';
+------------------------------+------------+
| Variable_name | Value |
+------------------------------+------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 6492782592 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 4194304 |
| join_buffer_size | 131072 |
| key_buffer_size | 2097152000 |
| myisam_sort_buffer_size | 536870912 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 2097152 |
| read_rnd_buffer_size | 4194304 |
| sort_buffer_size | 268435456 |
| sql_buffer_result | OFF |
+------------------------------+------------+
14 rows in set (0.00 sec)
mysql> show variables like '%max%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| ft_max_word_len | 84 |
| group_concat_max_len | 1024 |
| innodb_file_format_max | Antelope |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_purge_lag | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_connect_errors | 10 |
| max_connections | 200 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 8388608 |
| 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 |
| myisam_max_sort_file_size | 137438953472 |
| 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 |
| sql_max_join_size | 18446744073709551615 |
+-----------------------------------------+----------------------+
41 rows in set (0.00 sec)
mysql> show variables like '%mmap%';
+------------------+------------+
| Variable_name | Value |
+------------------+------------+
| myisam_mmap_size | 1073741824 |
| myisam_use_mmap | OFF |
+------------------+------------+
2 rows in set (0.00 sec)
Edited 3 time(s). Last edit at 07/04/2012 02:13AM by Tomas Singliar.
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.