Hi all, thanks for the feedback. Good information for me to work with :-)
The server in this case is a stand alone with nothing more then CentOS and MySQL
5.1.44 on it. The drives are sas 10K rpm drives. The problem I see is that
when you stress test the server (typically by running loads of reports -
selects, joins) the machine hits 98% cpu and leaves only 800mb of free RAM out
of the 16 GB of which I told it to allocate 12GB for Innodb in my.cnf. Once the
server sucks up all the memory when we are stress testing it, it holds the 12 gb
as hostage and refuses to release it back into the pool, regardless of weather
there is load or not and on box. 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??? If I start to stress it again then it starts to go
into swap. Really weird, thus wanting to split the load onto 3 machines.
Also it doesn't help when your innodb index is larger than physical memory ;-)
The server only reports 50 to 100 slow queries per day out of the hundreds and
thousands of queries it is running.
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?
I KNOW the tables and format and the way they have setup the database including
Indexing is bad, but mgmt says throw hardware as it's cheaper then re-writting
code and re-architecting the db ;-)
Thanks for all of your feedback Gurus :-)
From: Steven Staples <sstaples@stripped>
To: Nunzio Daveri <nunziodaveri@stripped>; mysql@stripped
Sent: Thu, August 5, 2010 7:23:19 AM
Subject: RE: Moving from one MySQL server to three MySQL servers?
Have you double checked the hardware? Are you using 5400rpm drives, or 15k
rpm drives? I/O bottlenecks are common, if you can't read the data fast
enough, then it will definitely be slower, and appear to have more issues
that it really does. If the client can't/won't change/alter the code, then
maybe looking at changing the hardware would be better. Having a smaller
drive size raid array with faster harddrives may solve the I/O bottleneck if
that is the case.
And maybe it is just poorly written queries with crappy indexing? Maybe look
at the slow query log, and ensure that the RIGHT indexes are there
(140gb/21gb index doesn't mean that the indexes are the correct ones)
Going to a replication setup may not be the solution to your problems, and
could just be a bandaid (and prolly cause you many sleepless nights
maintaining data integrity). Find out the cause of the problem, before
adding to it.
> -----Original Message-----
> From: Nunzio Daveri [mailto:nunziodaveri@stripped]
> Sent: August 4, 2010 2:40 PM
> To: mysql@stripped
> Subject: Moving from one MySQL server to three MySQL servers?
> Hello Gurus :-) I was running a simple load generator against our 16GB
> Quad core server and it pretty much came down to it's knees within two
> hours of
> running tests. The customer DOES NOT WANT to change any code, they just
> want to
> throw hardware at it since it took them a year to create all of the code.
> It is
> a 140GB database with 21GB of indexs all using InnoDB - currently doing
> reads and 30% writes.
> My question is what is the best way of distributing the load without
> any of the php / perl code that their web server uses? This is what I am
> thinking but need someone to tell me it is a good idea or bad please?
> 1. Setup a single master and 2 slaves. The question is how to tell the
> servers to get all the read data from the slaves and to only write to the
> 2. Install a MySQL proxy box and let mysql proxy handle the load, problem
> is now
> it is the SPOF!
> 3. Use DNS round robin, BUT how to tell round robin to ONLY go to master
> writes and ONLY use one of the 2 slaves for reads?
> Any links, ideas or suggestions is most appreciated.
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.851 / Virus Database: 271.1.1/3023 - Release Date: 08/04/10