Since my VPS at slicehost is currently only 256MB my naive rails stack was causing swapping. I’m looking into reducing the memory usage of some of the components running on the slice before starting to pay more.
Here are my working notes:
Some work with free and top quickly showed me that mysql was using a significant portion of the available memory, and kicking a practically idle slice into using some of the swap space.
Before looking at free and top, I had been suspecting that the swap usage was the result of uploading images that were simply very large. This might still kick us over into swap usage. I’ll have to check, but first things first – mysql was crossing the line while idle!
Emergent Properties had a blog entry titled Optimizing Apache and MySQL for Low Memory Usage, Part 2 where mysql configuration settings is well explained. For now, I’m going to start by adjusting only The Key Buffer.
Running show status in mysql after using my sites for a while revealed the following information about use of the key buffer:
Key_blocks_not_flushed 0
Key_blocks_unused 13396
Key_blocks_used 3
Key_read_requests 27
Key_reads 30
Key_write_requests 57
Key_writes 30
3 Key_blocks_used out of 13399 total key blocks would appear to show I can safely lower this number.
For now, I’m going to change the following entries in /etc/mysql/my.conf:
key_buffer = 8M # instead of 16M
max_allowed_packet = 8M # instead of 16M
query_cache_size = 8M # instead of 16M
I also tried uncommenting skip-innodb in the same file, but when I did so and restarted mysqld, this broke my POP passwords that are stored encrypted in the database. Since a comment in the file says: ”# You might want to disable InnoDB to shrink the mysqld process by circa 100MB”, I then tried reducing the InnoDB memory usage as follows:
- Set buffer pool size to 50-80% of your computer’s memory
innodb_buffer_pool_size=35M
innodb_additional_mem_pool_size=5M
These InnoDB changes caused mysqld to use more memory than before, so I backed them out.
Next I looked into mongrel memory usage. I quickly came to think that mongrel memory usage is really your rails app memory usage.
Here are a list of
mephisto memory usage links:
Here’s a slicehost specific article on reducing mysql memory usage.
Following all the changes except for skip-innodb saved just 9 MB. Also turning off innodb saved most of the memory usage.
Again, it looks like InnoDB is the primary culprit.
Next morning – this is what I did:
- uncommented skip-innodb in /etc/mysql/mysql.cnf
- sudo mysqldump—all-databases > ~/mysql.dump
- cp ~/mysql.dump ~/mysql.altered
- vi ~/mysql.altered
- replaced instances of ENGINE=InnoDB with ENGINE=MyISAM. Be careful, there was one line of mysql help content that should not be changed.
- added SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
to the top of the file.
- added SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
to the bottom of the file.
- sudo mysql -u -p < ~/mysql.altered | tee /tmp/x
- sudo /etc/init.d/mysql restart
- I also stepped down to a single mongrel_rails instance serving mephisto
Before reboot, I had been as high as using 106M of swap space.
After reboot, before causing activity, I’m using all by 2M of my regular RAM, but none of the swap space
After causing some mild mephist activity, it’s still the same. No swap space being used!
W00t!
Current free reading:
stephen@bendycodeslice:~$ free
total used free shared buffers cached
Mem: 254 251 3 0 3 66
-/+ buffers/cache: 181 73
Swap: 511 0 511
Current top output:
top - 10:08:17 up 22 min, 2 users, load average: 0.00, 0.01, 0.06
Tasks: 83 total, 2 running, 81 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.2%st
Mem: 260864k total, 258016k used, 2848k free, 4056k buffers
Swap: 524280k total, 0k used, 524280k free, 66664k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2014 root 16 0 157m 56m 3116 S 0 22.2 0:04.62 mongrel_rails
1908 clamav 25 0 92804 44m 572 S 0 17.5 3:20.42 clamd
1801 amavis 18 0 89964 33m 1704 S 0 13.2 0:00.01 amavisd-new
1802 amavis 18 0 89964 33m 1684 S 0 13.2 0:00.00 amavisd-new
1787 amavis 16 0 88980 34m 2256 S 0 13.4 0:00.03 amavisd-new
2194 root 18 0 63620 980 348 S 0 0.4 0:00.00 svnserve
2186 root 16 0 61460 3188 2432 S 0 1.2 0:00.00 dovecot-auth
2193 root 16 0 57284 3312 2568 S 0 1.3 0:00.00 dovecot-auth
1851 mysql 16 0 49112 6592 4752 S 0 2.5 0:00.07 mysqld
2225 root 15 0 48276 2616 2080 S 0 1.0 0:00.01 sshd