List:General Discussion« Previous MessageNext Message »
From:Warren Young Date:August 9 2010 8:21pm
Subject:Re: Moving from one MySQL server to three MySQL servers?
View as plain text  
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:

	http://www.linuxatemyram.com/

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?

Yes.

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. :)
Thread
Moving from one MySQL server to three MySQL servers?Nunzio Daveri4 Aug
  • Re: Moving from one MySQL server to three MySQL servers?Warren Young4 Aug
  • Re: Moving from one MySQL server to three MySQL servers?Wm Mussatto4 Aug
    • Re: Moving from one MySQL server to three MySQL servers?Prabhat Kumar4 Aug
      • Re: Moving from one MySQL server to three MySQL servers?Andrés Tello5 Aug
  • Re: Moving from one MySQL server to three MySQL servers?Joerg Bruehe5 Aug
  • RE: Moving from one MySQL server to three MySQL servers?Steven Staples5 Aug
    • Re: Moving from one MySQL server to three MySQL servers?Nunzio Daveri5 Aug
      • Re: Moving from one MySQL server to three MySQL servers?Andrés Tello6 Aug
      • Re: Moving from one MySQL server to three MySQL servers?Warren Young9 Aug
      • Re: Moving from one MySQL server to three MySQL servers?Todd Lyons18 Aug