Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are looking to cut some of our server costs and the only place I think it might be possible is our db server.

We're currently running an 8GB rackspace cloud that houses a mysql instance and I believe is running a sphinx searchd instance as well.

Here are the stats from free -m:

       total       used       free     shared    buffers     cached
Mem:    7971       4748       3223          0        773       2773
-/+ buffers/cache: 1201       6769
Swap:  16378        467      15911

Here are the stats from top:

top - 15:23:52 up 349 days, 19:28,  1 user,  load average: 0.39, 0.72, 0.92
Tasks: 141 total,   1 running, 139 sleeping,   0 stopped,   1 zombie
Cpu(s):  0.5%us,  0.1%sy,  0.0%ni, 98.1%id,  1.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   8162332k total,  4861112k used,  3301220k free,   791832k buffers
Swap: 16771852k total,   478512k used, 16293340k free,  2839936k cached

It looks like were using just about half of our available 8Gigs but it would save us almost $200/mo if we could downgrade to a 4GB instance.

Any advice on this situation is much appreciated.

Thank You

share|improve this question

2 Answers 2

up vote 0 down vote accepted

If current memory use is representative of all the application's daily/weekly/monthly activity cycles then you could probably be fine on 4GiB. You are using about 5G most of which is cache/buffers so if that genuinely just basic file blocks then you just not be caching quite as much which may not be an issue.

But the cached count includes memory mapped file content and a few other things so it may not be that simple.

Also the fact that you do have half a GB allocated in swap implies that at some point you were using a lot more memory. It may be that this was due to a one-off event weeks ago of course, you can't tell from this one set of readings. What you should do is collect memory use data regularly for a month or more - there are a number of packages like collectd which will help you here though for once/minute recording a small script called by cron might be sufficient.

The most reliable way to judge this would be to benchmark: automate the database calls for a period of activity (if you application maintains a full audit trail you can use this to replicate a real period's actual activity), speed up that activity several-fold for paranoia (say cram a day of activity into a couple of hours) to allow for growth, replicate the application install in an 8G VM locally, run that automated activity, gauge how responsive the application is to interactive activity (log on and try use the application) while this is going on, then repeat while only allocating 4GB to the VM.

share|improve this answer
    
Good idea. I definitely need to get a big picture look at how hard the db server is getting hit. We used to have it running on the same 4gb server as our apache then we moved it to it's own 8gb. I think we would be okay if we bumped the 8gb to 4gb, but I should handle the logistics before I claim that to be true. –  Eric Uldall Jan 7 at 18:10
    
Using cacti is simple enough to get the memory usage at both system and mysql level. There are some great templates available from percona.com –  eroomydna Jan 8 at 15:17

It's possible. You will potentially lose capacity to serve all data requests from in memory buffers but that depends on how large your data is.

select ROUND(SUM(data_length+index_length/pow(1024,2))) as TotalSizeMB, SUM(table_rows), engine from information_schema.tables where table_schema not in ('mysql','information_schema') group by engine

to see your distribution per engine. You'll be able to judge whether all data and indexes fit inside the innodb bufferpool or indexes within the myisam key buffer. Assuming you set these buffers up yourself in the my.cnf config file and you're not running with the defaults.

share|improve this answer
    
I didn't install mysql on this server, so I'm almost 100% sure it's running on the defaults. –  Eric Uldall Jan 7 at 18:06
    
our largest table has just over 1 million records. currently some of the largest queries to that dataset take around .007 - .024 seconds, I think David Spillett is right in his idea to do more long term benchmarking of the system before deciding. –  Eric Uldall Jan 7 at 18:18

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.