On 8/5/2010 9:35 AM, Nunzio Daveri wrote:
> So when I do top-c before I run the reports,
> it says mysql is using 2GB, then I run the stress test (several reports) and it
> hits 12GB then I stop the stress and even 30 mins later the server says there is
> only 800mb of ram free???
That's normal Linux behavior:
I've known this to be true for many years, but just for grins, I tested
it again on a box here. It's a development box, so it hadn't been used
at all over the weekend, and hadn't been used yet today, yet it shows
only 5% of its 6 GB total as free.
> Also it doesn't help when your innodb index is larger than physical memory ;-)
Yes, you should indeed fix that.
> But after all the chatter, I think I will use one of our test/dev servers,
> install fresh OS, install 5.1.49 then import the db without indexing, run a good
> 100mb of sql statements against it from our prod servers logs, then look for
> what fields need to be indexed under slow query logs and then go from there. Is
> this a good idea vs. going straight to splitting the load into 3 servers?
Keep in mind that replication is a sidecar bolted onto DB systems like
MySQL. It's not a core behavior of the relational model, so it has a
lot of penalties. The current hoopla about "NoSQL" systems is one
answer to this, and for a lot of applications, it is a much better way
to get a distributed DB.
> mgmt says throw hardware as it's cheaper then re-writting
> code and re-architecting the db ;-)
They may well be right.
Just one observation: your "16 GB RAM" number means you're not using
DDR3 yet, either because the machine doesn't support it, or you're not
putting memory sticks in it in threes like you should.
Either way, it means RAM accesses could be 50% faster simply by moving
to DDR3, changing nothing else about the system configuration. Couple
that with the fact that the next common step up in RAM size for DDR3
systems from where you are now is 24 GB, just over your current index
size. Those two simple changes may be enough to fix your problem.
If you find a way to optimize the indexes to get it all under 16 GB,
well, so much the better. Upgrade to 24 GB (or 36...?) anyway and be
happy knowing you've bought yourself more time before you need to do the
next upgrade. Meantime, let Linux continue to eat your RAM. :)