关于MYSQL内存配置.

kimsung 2014-08-15 06:41:44

今天新配置一个mysql服务器. 但是服务器内存很小, 还有一个其他java程序在跑....
总之... 我安装mysql改了点my.cnf但是内存使用量还是900多M. 能不能再少点呢..
下面是my.cnf的内容

default_storage_engine=InnoDB

skip_name_resolve

federated



max_connections = 3000

max_connect_errors = 999999

max_allowed_packet = 1G

max_heap_table_size = 1G

tmp_table_size = 16M

binlog_cache_size = 1M

thread_cache_size = 128

group_concat_max_len=102400

# wait_timeout = 30

lock_wait_timeout = 60

max_prepared_stmt_count = 1048576



table_open_cache = 2000

table_open_cache_instances = 16



sort_buffer_size = 1M

join_buffer_size = 8M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

query_cache_type = OFF

# query_cache_size = 64M

# query_cache_limit = 2M



# MyISAM options

key_buffer_size = 16M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

ft_min_word_len = 4



# INNODB options

innodb_buffer_pool_size = 128M         # 50 ~ 70% of main memory

innodb_page_size = 16384

innodb_buffer_pool_instances = 1

innodb_flush_neighbors = 1

innodb_purge_threads = 1

innodb_log_buffer_size = 8M

innodb_data_file_path = ibdata1:512M:autoextend

innodb_io_capacity = 800

innodb_read_io_thread = 4

innodb_write_io_thread = 4

innodb_log_file_size = 2G

innodb_log_files_in_group = 2

innodb_support_xa=1

innodb_thread_concurrency = 0

innodb_lock_wait_timeout = 60

innodb_flush_log_at_trx_commit = 1

innodb_flush_method = O_DIRECT

innodb_max_dirty_pages_pct = 75

innodb_file_format = barracuda

innodb_file_per_table

innodb_strict_mode=1

innodb_stats_on_metadata = OFF

innodb_adaptive_hash_index = OFF

innodb_buffer_pool_dump_at_shutdown = ON

innodb_buffer_pool_load_at_startup = ON

启动完之后内存信息..

[xxxx@test.ncl mysql]$ free -m

             total       used       free     shared    buffers     cached

Mem:          1869       1753        116          0         60         46

-/+ buffers/cache:       1645        223

Swap:            0          0          0

下面是top信息



27183 xxxx    20   0 1560m 936m 6440 S  0.3 50.1   0:01.67 mysqld

24660 xxxx    20   0 2312m 615m  15m S  0.7 32.9   1:47.49 java

 1253 momo    20   0  621m  16m  948 S  0.0  0.9  12:49.47 noms_nsight

 4209 root      20   0  361m 6952 1300 S  1.3  0.4 123:19.06 box

  721 root      20   0  249m 3496  380 S  0.0  0.2   0:04.46 rsyslogd

 4192 root      20   0 23868 2596    4 S  0.0  0.1   0:00.01 box

看看mysql的内存使用量还能减少点吗... mysql总是莫名其妙的挂掉.. 在mysql的log上挂掉之前也没有留下任何log. ..

...全文

299 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

zhu19774279 2014-09-01

打赏
举报

对了，MySQL给的默认配置文件中，不管是5.5还是5.1，默认的引擎都是myisam，注意把default-storage-engine改成INNODB

引用 6 楼 zhu19774279 的回复:

/usr/share/mysql下面有my-small.cnf，my-large.cnf什么的，打开以后文件上面写了这个配置文件适合多大内存，改好后把文件名修改成my.cnf，放到/etc下即可 [quote=引用 5 楼 kimsung 的回复:] [quote=引用 4 楼 zhu19774279 的回复:] MySQL装好以后，自带了几个my.cnf文件，里面从64MB-4GB内存的配置都有，推荐试试看。

哪里有啊？？[/quote][/quote]

zhu19774279 2014-08-28