開発環境のWindows XP Pro + MySQL 5.4.1betaでmy-small.ini使っても起動時にメモリを1GBくらい使われるのでmy.iniを編集してみた。
本番環境がMySQL 5.4.1-betaだけど、起動するとメモリが多い。
Process Explorer のvirtual memoryで見てみた。
java 1.6.0_11b3 - -
eclipse 3.3.1.1 - 800MB
tomcat 5.5.26 - 1GB
mysql 5.4.1-beta - 1GB
firefox 3.5.7 - 500MB
> set MYSQL_HOME=C:\mysql PATH=%PATH%;%MYSQL_HOME%\bin;
eclipseとjavaのパッケージとtomcatはめんどくさいのでとりあえずmysqlの利用メモリを削りたいので色々見てみた。場合によってcolinux環境が混ざってるのでコマンド名は適当に読み替えて。
my.iniって何が読み込まれてるの?コマンドでは出ないみたいだけど。
MySQL :: MySQL 5.1 リファレンスマニュアル :: 3.3.2 オプションファイルの使用 http://dev.mysql.com/doc/refman/5.1/ja/option-files.html ファイル名 目的 WINDIR\my.ini グローバルオプション C:\my.cnf グローバルオプション INSTALLDIR\my.ini グローバルオプション defaults-extra-file --defaults-extra-file=pathで指定されたファイル(あれば)
どんな値を変更したら良いかわからないので現状の値をだす。
$ mysqladmin variables -hhostname -uuser -p mysql> show variables; #以下はデフォルト値?
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- +
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Variable_name | Value |
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- +
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
auto_increment_increment | 1 | |
auto_increment_offset | 1 | |
autocommit | ON | |
automatic_sp_privileges | ON | |
back_log | 50 | |
basedir | C:\mysql\ | |
big_tables | OFF | |
binlog_cache_size | 32768 | |
binlog_format | STATEMENT | |
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:\mysql\share\charsets\ | |
collation_connection | latin1_swedish_ci | |
collation_database | latin1_swedish_ci | |
collation_server | latin1_swedish_ci | |
completion_type | 0 | |
concurrent_insert | 1 | |
connect_timeout | 10 | |
datadir | C:\mysql\data\ | |
date_format | %Y-%m-%d | |
datetime_format | %Y-%m-%d %H:%i:%s | |
default_week_format | 0 | |
delay_key_write | ON | |
delayed_insert_limit | 100 | |
delayed_insert_timeout | 300 | |
delayed_queue_size | 1000 | |
div_precision_increment | 4 | |
engine_condition_pushdown | ON | |
error_count | 0 | |
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:\mysql\data\HOSTNAME.log | |
group_concat_max_len | 1024 | |
have_community_features | YES | |
have_compress | YES | |
have_crypt | NO | |
have_csv | YES | |
have_dynamic_loading | YES | |
have_geometry | YES | |
have_innodb | YES | |
have_ndbcluster | NO | |
have_openssl | DISABLED | |
have_partitioning | YES | |
have_query_cache | YES | |
have_rtree_keys | YES | |
have_ssl | DISABLED | |
have_symlink | YES | |
hostname | HOSTNAME | |
identity | 0 | |
ignore_builtin_innodb | OFF | |
init_connect | ||
init_file | ||
init_slave | ||
innodb_adaptive_hash_index | ON | |
innodb_additional_mem_pool_size | 8388608 | |
innodb_autoextend_increment | 64 | |
innodb_autoinc_lock_mode | 1 | |
innodb_buffer_pool_size | 1073741824 | |
innodb_checksums | ON | |
innodb_commit_concurrency | 0 | |
innodb_concurrency_tickets | 500 | |
innodb_data_file_path | ibdata1:10M:autoextend | |
innodb_data_home_dir | ||
innodb_doublewrite | ON | |
innodb_extra_dirty_writes | ON | |
innodb_fast_shutdown | 1 | |
innodb_file_per_table | OFF | |
innodb_flush_log_at_trx_commit | 1 | |
innodb_flush_method | ||
innodb_force_recovery | 0 | |
innodb_io_capacity | 200 | |
innodb_lock_wait_timeout | 50 | |
innodb_locks_unsafe_for_binlog | OFF | |
innodb_log_buffer_size | 16777216 | |
innodb_log_file_size | 134217728 | |
innodb_log_files_in_group | 3 | |
innodb_log_group_home_dir | .\ | |
innodb_max_dirty_pages_pct | 75 | |
innodb_max_merged_io | 64 | |
innodb_max_purge_lag | 0 | |
innodb_mirrored_log_groups | 1 | |
innodb_open_files | 300 | |
innodb_read_io_threads | 8 | |
innodb_rollback_on_timeout | OFF | |
innodb_stats_on_metadata | ON | |
innodb_support_xa | ON | |
innodb_sync_spin_loops | 20 | |
innodb_table_locks | ON | |
innodb_thread_concurrency | 0 | |
innodb_thread_concurrency_timer_based | ON | |
innodb_thread_sleep_delay | 10000 | |
innodb_write_io_threads | 8 | |
insert_id | 0 | |
interactive_timeout | 28800 | |
join_buffer_size | 131072 | |
keep_files_on_create | OFF | |
key_buffer_size | 16384 | |
key_cache_age_threshold | 300 | |
key_cache_block_size | 1024 | |
key_cache_division_limit | 100 | |
language | C:\mysql\share\english\ | |
large_files_support | ON | |
large_page_size | 0 | |
large_pages | OFF | |
last_insert_id | 0 | |
lc_time_names | en_US | |
license | GPL | |
local_infile | ON | |
log | OFF | |
log_bin | OFF | |
log_bin_trust_function_creators | OFF | |
log_bin_trust_routine_creators | OFF | |
log_error | C:\mysql\data\HOSTNAME.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 | 4294963200 | |
max_binlog_size | 1073741824 | |
max_connect_errors | 10 | |
max_connections | 151 | |
max_delayed_threads | 20 | |
max_error_count | 64 | |
max_heap_table_size | 16777216 | |
max_insert_delayed_threads | 20 | |
max_join_size | 18446744073709551615 | |
max_length_for_sort_data | 1024 | |
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_recover_options | OFF | |
myisam_repair_threads | 1 | |
myisam_sort_buffer_size | 8388608 | |
myisam_stats_method | nulls_unequal | |
myisam_use_mmap | OFF | |
named_pipe | OFF | |
net_buffer_length | 2048 | |
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 | 755 | |
optimizer_prune_level | 1 | |
optimizer_search_depth | 62 | |
pid_file | C:\mysql\data\HOSTNAME.pid | |
plugin_dir | C:\mysql\lib/plugin | |
port | 3306 | |
preload_buffer_size | 32768 | |
profiling | OFF | |
profiling_history_size | 15 | |
protocol_version | 10 | |
pseudo_thread_id | 2 | |
query_alloc_block_size | 8192 | |
query_cache_limit | 1048576 | |
query_cache_min_res_unit | 4096 | |
query_cache_size | 0 | |
query_cache_type | ON | |
query_cache_wlock_invalidate | OFF | |
query_prealloc_size | 8192 | |
rand_seed1 | ||
rand_seed2 | ||
range_alloc_block_size | 4096 | |
read_buffer_size | 262144 | |
read_only | OFF | |
read_rnd_buffer_size | 262144 | |
relay_log | ||
relay_log_index | ||
relay_log_info_file | relay-log.info | |
relay_log_purge | ON | |
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 | 1 | |
shared_memory | OFF | |
shared_memory_base_name | MYSQL | |
skip_external_locking | ON | |
skip_networking | OFF | |
skip_show_database | OFF | |
slave_compressed_protocol | OFF | |
slave_exec_mode | STRICT | |
slave_load_tmpdir | C:\DOCUME~1\USR\LOCALS~1\Temp | |
slave_net_timeout | 3600 | |
slave_skip_errors | ||
slave_transaction_retries | 10 | |
slow_launch_time | 2 | |
slow_query_log | OFF | |
slow_query_log_file | C:\mysql\data\HOSTNAME-slow.log | |
sort_buffer_size | 65536 | |
sql_auto_is_null | ON | |
sql_big_selects | ON | |
sql_big_tables | OFF | |
sql_buffer_result | OFF | |
sql_log_bin | ON | |
sql_log_off | OFF | |
sql_log_update | ON | |
sql_low_priority_updates | OFF | |
sql_max_join_size | 18446744073709551615 | |
sql_mode | ||
sql_notes | ON | |
sql_quote_show_create | ON | |
sql_safe_updates | OFF | |
sql_select_limit | 18446744073709551615 | |
sql_slave_skip_counter | ||
sql_warnings | OFF | |
ssl_ca | ||
ssl_capath | ||
ssl_cert | ||
ssl_cipher | ||
ssl_key | ||
storage_engine | MyISAM | |
sync_binlog | 0 | |
sync_frm | ON | |
system_time_zone | ||
table_definition_cache | 400 | |
table_lock_wait_timeout | 50 | |
table_open_cache | 4 | |
table_type | MyISAM | |
thread_cache_size | 0 | |
thread_handling | one-thread-per-connection | |
thread_stack | 131072 | |
time_format | %H:%i:%s | |
time_zone | SYSTEM | |
timed_mutexes | OFF | |
timestamp | 1264665148 | |
tmp_table_size | 16777216 | |
tmpdir | C:\DOCUME~1\USR\LOCALS~1\Temp | |
transaction_alloc_block_size | 8192 | |
transaction_prealloc_size | 4096 | |
tx_isolation | REPEATABLE-READ | |
unique_checks | ON | |
updatable_views_with_limit | YES | |
version | 5.4.1-beta-community | |
version_comment | MySQL Community Server (GPL) | |
version_compile_machine | ia32 | |
version_compile_os | Win32 | |
wait_timeout | 28800 | |
warning_count | 1 |
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- +
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
my.iniに数字でかそうなところを小さくしたりとか、connectionを削除してみるとか、latin1をutf8にしたりして再起動、で失敗するのでエラーログを見てみる。
$ cat hostname.err InnoDB: The InnoDB memory heap has been disabled. InnoDB: Mutex and rw_lock use atomics. InnoDB: ios_per_array 32 read threads 1 write threads 1 InnoDB: The first specified data file .\ibdata1 did not exist: InnoDB: a new database to be created! 100128 16:06:43 InnoDB: Setting file .\ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 100128 16:06:43 InnoDB: Log file .\ib_logfile0 did not exist: new to be created InnoDB: Setting log file .\ib_logfile0 size to 128 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 100128 16:06:46 InnoDB: Log file .\ib_logfile1 did not exist: new to be created InnoDB: Setting log file .\ib_logfile1 size to 128 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 100128 16:06:49 InnoDB: Log file .\ib_logfile2 did not exist: new to be created InnoDB: Setting log file .\ib_logfile2 size to 128 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 100128 16:06:53 InnoDB: Started; log sequence number 0 0 InnoDB master thread running with io_capacity 200 100128 16:06:53 [Note] Event Scheduler: Loaded 0 events 100128 16:06:53 [Note] mysqld: ready for connections. Version: '5.4.1-beta-community' socket: '' port: 3306 MySQL Community Server (GPL) 100128 16:07:41 [Note] mysqld: Normal shutdown 100128 16:07:41 [Note] Event Scheduler: Purging the queue. 0 events 100128 16:07:41 InnoDB: Starting shutdown... 100128 16:07:43 InnoDB: Shutdown completed; log sequence number 0 46409 100128 16:07:43 [Warning] Forcing shutdown of 1 plugins 100128 16:07:43 [Note] mysqld: Shutdown complete InnoDB: The InnoDB memory heap has been disabled. InnoDB: Mutex and rw_lock use atomics. InnoDB: ios_per_array 32 read threads 1 write threads 1 100128 16:22:18 InnoDB: Started; log sequence number 0 46409 InnoDB master thread running with io_capacity 200 100128 16:22:18 [Note] Event Scheduler: Loaded 0 events 100128 16:22:18 [Note] mysqld: ready for connections. Version: '5.4.1-beta-community' socket: '' port: 3306 MySQL Community Server (GPL) 100128 16:37:27 [Note] mysqld: Normal shutdown 100128 16:37:27 [Note] Event Scheduler: Purging the queue. 0 events 100128 16:37:27 InnoDB: Starting shutdown... 100128 16:37:29 InnoDB: Shutdown completed; log sequence number 0 46409 100128 16:37:29 [Warning] Forcing shutdown of 1 plugins 100128 16:37:29 [Note] mysqld: Shutdown complete InnoDB: The InnoDB memory heap has been disabled. InnoDB: Mutex and rw_lock use atomics. InnoDB: ios_per_array 32 read threads 1 write threads 1 100128 16:49:37InnoDB master thread running with io_capacity 200 InnoDB: Started; log sequence number 0 46409 100128 16:49:37 [Note] Event Scheduler: Loaded 0 events 100128 16:49:37 [Note] mysqld: ready for connections. Version: '5.4.1-beta-community' socket: '' port: 3306 MySQL Community Server (GPL) 100128 17:52:11 [Note] mysqld: Normal shutdown 100128 17:52:11 [Note] Event Scheduler: Purging the queue. 0 events 100128 17:52:11 InnoDB: Starting shutdown... 100128 17:52:13 InnoDB: Shutdown completed; log sequence number 0 46409 100128 17:52:13 [Warning] Forcing shutdown of 1 plugins 100128 17:52:13 [Note] mysqld: Shutdown complete 100128 17:52:24 [Warning] option 'innodb-buffer-pool-size': signed value 4194304 adjusted to 67108864 100128 17:52:24 [Warning] option 'innodb-buffer-pool-size': signed value 1048576 adjusted to 67108864 100128 17:52:24 [Warning] option 'innodb-log-file-size': signed value 4194304 adjusted to 33554432 InnoDB: The InnoDB memory heap has been disabled. InnoDB: Mutex and rw_lock use atomics. InnoDB: ios_per_array 32 read threads 1 write threads 1 InnoDB: Error: log file C:\mysql\data\ib_logfile0 is of different size 0 134217728 bytes InnoDB: than specified in the .cnf file 0 33554432 bytes! 100128 17:52:24 [ERROR] Plugin 'InnoDB' init function returned error. 100128 17:52:24 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 100128 17:52:24 [ERROR] mysqld: unknown variable 'innodb_log_arch_dir=C:\mysql\data/' 100128 17:52:24 [ERROR] Aborting 100128 17:52:24 [Warning] Forcing shutdown of 1 plugins 100128 17:52:24 [Note] mysqld: Shutdown complete 100129 9:27:22 [Warning] option 'innodb-buffer-pool-size': signed value 4194304 adjusted to 67108864 100129 9:27:22 [Warning] option 'innodb-buffer-pool-size': signed value 1048576 adjusted to 67108864 100129 9:27:22 [Warning] option 'innodb-log-file-size': signed value 4194304 adjusted to 33554432 InnoDB: The InnoDB memory heap has been disabled. InnoDB: Mutex and rw_lock use atomics. InnoDB: ios_per_array 32 read threads 1 write threads 1 InnoDB: The first specified data file C:\mysql\data\ibdata1 did not exist: InnoDB: a new database to be created! 100129 9:27:22 InnoDB: Setting file C:\mysql\data\ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 100129 9:27:23 InnoDB: Log file C:\mysql\data\ib_logfile0 did not exist: new to be created InnoDB: Setting log file C:\mysql\data\ib_logfile0 size to 32 MB InnoDB: Database physically writes the file full: wait... 100129 9:27:23 InnoDB: Log file C:\mysql\data\ib_logfile1 did not exist: new to be created InnoDB: Setting log file C:\mysql\data\ib_logfile1 size to 32 MB InnoDB: Database physically writes the file full: wait... 100129 9:27:24 InnoDB: Log file C:\mysql\data\ib_logfile2 did not exist: new to be created InnoDB: Setting log file C:\mysql\data\ib_logfile2 size to 32 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 100129 9:27:25 InnoDB: Started; log sequence number 0 0 InnoDB master thread running with io_capacity 200 100129 9:27:25 [ERROR] mysqld: unknown variable 'innodb_log_arch_dir=C:\mysql\data/' 100129 9:27:25 [ERROR] Aborting 100129 9:27:25 InnoDB: Starting shutdown... 100129 9:27:27 InnoDB: Shutdown completed; log sequence number 0 46409 100129 9:27:27 [Warning] Forcing shutdown of 1 plugins 100129 9:27:27 [Note] mysqld: Shutdown complete 100129 9:38:39 [Warning] option 'innodb-buffer-pool-size': signed value 1048576 adjusted to 67108864 100129 9:38:39 [Warning] option 'innodb-log-file-size': signed value 4194304 adjusted to 33554432 InnoDB: The InnoDB memory heap has been disabled. InnoDB: Mutex and rw_lock use atomics. InnoDB: ios_per_array 32 read threads 1 write threads 1 100129 9:38:39InnoDB master thread running with io_capacity 200 InnoDB: Started; log sequence number 0 46409 100129 9:38:39 [ERROR] mysqld: unknown variable 'sql_max_join_size=8M' 100129 9:38:39 [ERROR] Aborting 100129 9:38:39 InnoDB: Starting shutdown... 100129 9:38:41 InnoDB: Shutdown completed; log sequence number 0 46409 100129 9:38:41 [Warning] Forcing shutdown of 1 plugins 100129 9:38:41 [Note] mysqld: Shutdown complete 100129 9:39:10 [Warning] option 'innodb-buffer-pool-size': signed value 1048576 adjusted to 67108864 100129 9:39:10 [Warning] option 'innodb-log-file-size': signed value 4194304 adjusted to 33554432 InnoDB: The InnoDB memory heap has been disabled. InnoDB: Mutex and rw_lock use atomics. InnoDB: ios_per_array 32 read threads 1 write threads 1 100129 9:39:10InnoDB master thread running with io_capacity 200 InnoDB: Started; log sequence number 0 46409 100129 9:39:10 [ERROR] mysqld: unknown variable 'sql_select_limit=8M' 100129 9:39:10 [ERROR] Aborting 100129 9:39:10 InnoDB: Starting shutdown... 100129 9:39:12 InnoDB: Shutdown completed; log sequence number 0 46409 100129 9:39:12 [Warning] Forcing shutdown of 1 plugins 100129 9:39:12 [Note] mysqld: Shutdown complete 100129 9:39:37 [Warning] option 'innodb-buffer-pool-size': signed value 1048576 adjusted to 67108864 100129 9:39:37 [Warning] option 'innodb-log-file-size': signed value 4194304 adjusted to 33554432 InnoDB: The InnoDB memory heap has been disabled. InnoDB: Mutex and rw_lock use atomics. InnoDB: ios_per_array 32 read threads 1 write threads 1 100129 9:39:37InnoDB master thread running with io_capacity 200 InnoDB: Started; log sequence number 0 46409 100129 9:39:37 [Note] Event Scheduler: Loaded 0 events 100129 9:39:37 [Note] mysqld: ready for connections. Version: '5.4.1-beta-community' socket: '' port: 3306 MySQL Community Server (GPL)
エラーログを元に修正した結果のmy.iniが以下
$ grep -v "^#" my.ini [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16K max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 128K default-character-set=utf8 skip-character-set-client-handshake back_log = 1 server-id = 1 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 1M innodb_data_home_dir = C:\mysql\data/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = C:\mysql\data/ innodb_additional_mem_pool_size = 2M innodb_log_file_size = 4M innodb_log_buffer_size = 2M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 max_binlog_cache_size = 2M max_binlog_size = 1M max_connections = 10 max_join_size = 16M [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout
[hogehoge]による文節の違いについて調べてないからそこらへんはなんともいえないけど、メモリ利用率がかなり下がった。
mysql> show variables;
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- +
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Variable_name | Value |
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- +
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
auto_increment_increment | 1 | |
auto_increment_offset | 1 | |
autocommit | ON | |
automatic_sp_privileges | ON | |
back_log | 1 | |
basedir | C:\mysql\ | |
big_tables | OFF | |
binlog_cache_size | 32768 | |
binlog_format | STATEMENT | |
bulk_insert_buffer_size | 8388608 | |
character_set_client | utf8 | |
character_set_connection | utf8 | |
character_set_database | utf8 | |
character_set_filesystem | binary | |
character_set_results | utf8 | |
character_set_server | utf8 | |
character_set_system | utf8 | |
character_sets_dir | C:\mysql\share\charsets\ | |
collation_connection | utf8_general_ci | |
collation_database | utf8_general_ci | |
collation_server | utf8_general_ci | |
completion_type | 0 | |
concurrent_insert | 1 | |
connect_timeout | 10 | |
datadir | C:\mysql\data\ | |
date_format | %Y-%m-%d | |
datetime_format | %Y-%m-%d %H:%i:%s | |
default_week_format | 0 | |
delay_key_write | ON | |
delayed_insert_limit | 100 | |
delayed_insert_timeout | 300 | |
delayed_queue_size | 1000 | |
div_precision_increment | 4 | |
engine_condition_pushdown | ON | |
error_count | 0 | |
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:\mysql\data\HOSTNAME.log | |
group_concat_max_len | 1024 | |
have_community_features | YES | |
have_compress | YES | |
have_crypt | NO | |
have_csv | YES | |
have_dynamic_loading | YES | |
have_geometry | YES | |
have_innodb | YES | |
have_ndbcluster | NO | |
have_openssl | DISABLED | |
have_partitioning | YES | |
have_query_cache | YES | |
have_rtree_keys | YES | |
have_ssl | DISABLED | |
have_symlink | YES | |
hostname | HOSTNAME | |
identity | 0 | |
ignore_builtin_innodb | OFF | |
init_connect | ||
init_file | ||
init_slave | ||
innodb_adaptive_hash_index | ON | |
innodb_additional_mem_pool_size | 2097152 | |
innodb_autoextend_increment | 64 | |
innodb_autoinc_lock_mode | 1 | |
innodb_buffer_pool_size | 67108864 | |
innodb_checksums | ON | |
innodb_commit_concurrency | 0 | |
innodb_concurrency_tickets | 500 | |
innodb_data_file_path | ibdata1:10M:autoextend | |
innodb_data_home_dir | C:\mysql\data\ | |
innodb_doublewrite | ON | |
innodb_extra_dirty_writes | ON | |
innodb_fast_shutdown | 1 | |
innodb_file_per_table | OFF | |
innodb_flush_log_at_trx_commit | 1 | |
innodb_flush_method | ||
innodb_force_recovery | 0 | |
innodb_io_capacity | 200 | |
innodb_lock_wait_timeout | 50 | |
innodb_locks_unsafe_for_binlog | OFF | |
innodb_log_buffer_size | 2097152 | |
innodb_log_file_size | 33554432 | |
innodb_log_files_in_group | 3 | |
innodb_log_group_home_dir | C:\mysql\data\ | |
innodb_max_dirty_pages_pct | 75 | |
innodb_max_merged_io | 64 | |
innodb_max_purge_lag | 0 | |
innodb_mirrored_log_groups | 1 | |
innodb_open_files | 300 | |
innodb_read_io_threads | 8 | |
innodb_rollback_on_timeout | OFF | |
innodb_stats_on_metadata | ON | |
innodb_support_xa | ON | |
innodb_sync_spin_loops | 20 | |
innodb_table_locks | ON | |
innodb_thread_concurrency | 0 | |
innodb_thread_concurrency_timer_based | ON | |
innodb_thread_sleep_delay | 10000 | |
innodb_write_io_threads | 8 | |
insert_id | 0 | |
interactive_timeout | 28800 | |
join_buffer_size | 131072 | |
keep_files_on_create | OFF | |
key_buffer_size | 16384 | |
key_cache_age_threshold | 300 | |
key_cache_block_size | 1024 | |
key_cache_division_limit | 100 | |
language | C:\mysql\share\english\ | |
large_files_support | ON | |
large_page_size | 0 | |
large_pages | OFF | |
last_insert_id | 0 | |
lc_time_names | en_US | |
license | GPL | |
local_infile | ON | |
log | OFF | |
log_bin | OFF | |
log_bin_trust_function_creators | OFF | |
log_bin_trust_routine_creators | OFF | |
log_error | C:\mysql\data\HOSTNAME.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 | 2097152 | |
max_binlog_size | 1048576 | |
max_connect_errors | 10 | |
max_connections | 10 | |
max_delayed_threads | 20 | |
max_error_count | 64 | |
max_heap_table_size | 16777216 | |
max_insert_delayed_threads | 20 | |
max_join_size | 16777216 | |
max_length_for_sort_data | 1024 | |
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_recover_options | OFF | |
myisam_repair_threads | 1 | |
myisam_sort_buffer_size | 8388608 | |
myisam_stats_method | nulls_unequal | |
myisam_use_mmap | OFF | |
named_pipe | OFF | |
net_buffer_length | 2048 | |
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 | 50 | |
optimizer_prune_level | 1 | |
optimizer_search_depth | 62 | |
pid_file | C:\mysql\data\HOSTNAME.pid | |
plugin_dir | C:\mysql\lib/plugin | |
port | 3306 | |
preload_buffer_size | 32768 | |
profiling | OFF | |
profiling_history_size | 15 | |
protocol_version | 10 | |
pseudo_thread_id | 1 | |
query_alloc_block_size | 8192 | |
query_cache_limit | 1048576 | |
query_cache_min_res_unit | 4096 | |
query_cache_size | 0 | |
query_cache_type | ON | |
query_cache_wlock_invalidate | OFF | |
query_prealloc_size | 8192 | |
rand_seed1 | ||
rand_seed2 | ||
range_alloc_block_size | 4096 | |
read_buffer_size | 262144 | |
read_only | OFF | |
read_rnd_buffer_size | 262144 | |
relay_log | ||
relay_log_index | ||
relay_log_info_file | relay-log.info | |
relay_log_purge | ON | |
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 | 1 | |
shared_memory | OFF | |
shared_memory_base_name | MYSQL | |
skip_external_locking | ON | |
skip_networking | OFF | |
skip_show_database | OFF | |
slave_compressed_protocol | OFF | |
slave_exec_mode | STRICT | |
slave_load_tmpdir | C:\DOCUME~1\USR\LOCALS~1\Temp | |
slave_net_timeout | 3600 | |
slave_skip_errors | ||
slave_transaction_retries | 10 | |
slow_launch_time | 2 | |
slow_query_log | OFF | |
slow_query_log_file | C:\mysql\data\HOSTNAME-slow.log | |
sort_buffer_size | 65536 | |
sql_auto_is_null | ON | |
sql_big_selects | OFF | |
sql_big_tables | OFF | |
sql_buffer_result | OFF | |
sql_log_bin | ON | |
sql_log_off | OFF | |
sql_log_update | ON | |
sql_low_priority_updates | OFF | |
sql_max_join_size | 16777216 | |
sql_mode | ||
sql_notes | ON | |
sql_quote_show_create | ON | |
sql_safe_updates | OFF | |
sql_select_limit | 18446744073709551615 | |
sql_slave_skip_counter | ||
sql_warnings | OFF | |
ssl_ca | ||
ssl_capath | ||
ssl_cert | ||
ssl_cipher | ||
ssl_key | ||
storage_engine | MyISAM | |
sync_binlog | 0 | |
sync_frm | ON | |
system_time_zone | ||
table_definition_cache | 400 | |
table_lock_wait_timeout | 50 | |
table_open_cache | 4 | |
table_type | MyISAM | |
thread_cache_size | 0 | |
thread_handling | one-thread-per-connection | |
thread_stack | 131072 | |
time_format | %H:%i:%s | |
time_zone | SYSTEM | |
timed_mutexes | OFF | |
timestamp | 1264727085 | |
tmp_table_size | 16777216 | |
tmpdir | C:\DOCUME~1\USR\LOCALS~1\Temp | |
transaction_alloc_block_size | 8192 | |
transaction_prealloc_size | 4096 | |
tx_isolation | REPEATABLE-READ | |
unique_checks | ON | |
updatable_views_with_limit | YES | |
version | 5.4.1-beta-community | |
version_comment | MySQL Community Server (GPL) | |
version_compile_machine | ia32 | |
version_compile_os | Win32 | |
wait_timeout | 28800 | |
warning_count | 1 |
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- +
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
ib_logfileがまだ多いのでどうにかしたいけど、それはまた必要になったときに。
New! javaのパッケージ規模でかすぎ