Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Conclusion:

  • if you really want to use little memory out-of-the-box drop MySQL and use PostgreSQL: it has a ~30Mo memory footprint, i.e. more than 10x less than MySQL; the migration was quite easy be it the server side or application side (using NHibernate).

  • in real-life of course you will not be changing your database for saving 300Mo but as noticed in the comments you will instead buy some RAM or sign up for a bigger VM if you are hosted.


I have a new MySQL setup on a small Windows Server 2008 machine (less than 1GB RAM).

The mysqld process is consuming between 300MB and 400MB of memory causing the system to swap!

I've read this could help :

  • disabling InnoDB but I need it,
  • tuning InnoDB but my setup seems to use already low values (e.g. for innodb_buffer_pool_size).

For information here is the my.ini configuration file:

[client]
no-beep
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="C:\Program Files\MySQL\MySQL Server 5.6\"
datadir="C:\ProgramData\MySQL\MySQL Server 5.6\data\"
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="my-pc.log"
slow-query-log=1
slow_query_log_file="my-pc.log"
long_query_time=10
log-error="my-pc.err"
max_connections=100
query_cache_size=1M
table_open_cache=2000
tmp_table_size=3M
thread_cache_size=9
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=5M
key_buffer_size=8M
read_buffer_size=0
read_rnd_buffer_size=0
sort_buffer_size=0
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=48M
innodb_thread_concurrency=8
innodb_autoextend_increment=64M
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=70
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4110
query_cache_type=1
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

This MySQL server could be really small: max 20 connections at the same time, no more than 2GB of data to store, no need for high performance...

Is there any way of reducing the memory footprint of this MySQL instance or is it already at the bare minimum?

Thanks in advance for any help.

share|improve this question
2  
Your memory usage is below the bare minimum for a 2GB InnoDB database. I would expect poor performance unless you gave it more memory to work with. –  Michael Hampton Mar 16 '13 at 18:21
    
Thanks for your quick and clear answer :) To be completely honest I'm not really sure I "need" it: what important features will I lose by disabling it? e.g. data consistency is important in this case. –  Pragmateek Mar 16 '13 at 18:27
4  
RAM is cheap. Go buy some. –  Michael Hampton Mar 16 '13 at 18:44
    
Yes I totally agree with you on this point (+1) :), but this is an hosted virtual server: more RAM means the superior VM with more fees and this is not cheap anymore ;) I'll try to disable InnoDB and see if I get in troubles, or better I'll look for a less avid DBMS. –  Pragmateek Mar 16 '13 at 19:11
1  
GET MORE MEMORY! (i.e. look at the tradeoff in the cost of low performance ... bad Customer Service, poor user response, bad sales to that of the increase in memory). Have you thought of going to another provider that has more favorable cloud rates? –  mdpc Mar 16 '13 at 20:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.