WebFaction
Community site: login faq

I tried using my-small.cnf that's provided in the Debian config examples. According to the commenting it's designed for servers with less than 64MB of RAM so this should be keeping my memory usage down. Unfortunately it's not working. After a while my MySQL server still gets up to a couple hundred MB and I have to restart it.

Here's what my modified my.cnf looks like after merging it with the default my.cnf provided for the private MySQL instances:

# Example MySQL config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
port = 27945
socket = /home/aoahosting/webapps/mysql/var/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 27945
socket = /home/aoahosting/webapps/mysql/var/mysql.sock
tmpdir = /home/aoahosting/webapps/mysql/tmp
datadir = /home/aoahosting/webapps/mysql/data
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (using the "enable-named-pipe" option) will render mysqld useless!
# 
skip-networking
server-id   = 1

# Uncomment the following if you want to log updates
#log-bin=mysql-bin

# binary logging format - mixed recommended
#binlog_format=mixed

# Causes updates to non-transactional engines using statement format to be
# written directly to binary log. Before using this option make sure that
# there are no dependencies between transactional and non-transactional
# tables such as in the statement INSERT INTO t_myisam SELECT * FROM
# t_innodb; otherwise, slaves may diverge from the master.
#binlog_direct_non_transactional_updates=TRUE

# Uncomment the following if you are using InnoDB tables
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 4M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
safe-updates

[mysqld_safe]
log-error = /home/aoahosting/logs/user/error_mysql.log
pid-file = /home/aoahosting/webapps/mysql/var/mysqld.pid

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

When I uncomment the innodb lines MySQL fails to start with the following error:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 4194304 bytes!
130912  2:08:49 [ERROR] Plugin 'InnoDB' init function returned error.
130912  2:08:49 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130912  2:08:49 [ERROR] Unknown/unsupported storage engine: InnoDB
130912  2:08:49 [ERROR] Aborting

I'm afraid since my database tables are InnoDB (default in 5.5) that without this InnoDB-specific config entries the server isn't optimized.

How can I modify my MySQL server config to limit its memory usage? I'm running a low traffic XenForo forum so it shouldn't need much.

asked 11 Sep '13, 21:27

HittingSmoke
4822230
accept rate: 8%

edited 11 Sep '13, 23:42


You can fix the InnoDB error by setting innodb_log_file_size to the correct value. Your file is 5242880 bytes (5MB) so use innodb_log_file_size = 5MB.

Regarding the memory issue, the config file provided by Debian states that it is for applications "where MySQL is only used from time to time". There's no guarantee that you're going to see low memory usage with that configuration when you're running it constantly.

If you don't have a pressing need to run a private MySQL instance for your low-traffic forum, then I recommend that you simply use our shared MySQL service instead, since that won't count towards your memory usage.

permanent link

answered 12 Sep '13, 14:01

seanf
12.2k41836
accept rate: 37%

I don't think I'm understanding this file size parameter. I guess I need to read up on it more.

I'm working on a bit of a project here involving intense optimization to get the snappiest forum I can. I plan on benchmarking between the private MySQL instance and the shared when I get all of my configuration in order. Now that the server is starting with the InnoDB setting in effect the memory usage seems much more under control.

(12 Sep '13, 14:18) HittingSmoke

I don't think I'm understanding this file size parameter. I guess I need to read up on it more.

There's not much to understand. The configuration specifies the size of the file. You had an existing file that didn't match that size.

(12 Sep '13, 14:23) seanf

What I don't understand is how the log file has a predetermined size. Wouldn't the log file get bigger and smaller as it's populated and pruned?

(12 Sep '13, 17:40) HittingSmoke

No - the file is created at the size specified in your configuration, and when it needs more space a new file is created at the same size, and named incrementally, eg:

[root@web350 ~]# ls -l /var/lib/mysql/ib_logfile*
-rw-rw---- 1 mysql mysql 5242880 Sep 12 22:52 /var/lib/mysql/ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Sep 12 22:16 /var/lib/mysql/ib_logfile1

As far as I know, it never shrinks.

If you want a database server capable of cleaning up after itself, then you should use PostgreSQL :)

(12 Sep '13, 17:54) seanf

That makes much more sense, thanks.

Unfortunately the products I'm working with don't support PostgreSQL. Trust me when I say I'm never happy about using Orcale anything.

(12 Sep '13, 17:58) HittingSmoke
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×168
×87

question asked: 11 Sep '13, 21:27

question was seen: 3,821 times

last updated: 12 Sep '13, 17:58

WEBFACTION
REACH US
SUPPORT
AFFILIATE PROGRAM
LEGAL
© COPYRIGHT 2003-2019 SWARMA LIMITED - WEBFACTION IS A SERVICE OF SWARMA LIMITED
REGISTERED IN ENGLAND AND WALES 5729350 - VAT REGISTRATION NUMBER 877397162
5TH FLOOR, THE OLD VINYL FACTORY, HAYES, UB3 1HA, UNITED KINGDOM