From: Moritz Möller
Date: May 25 2006 4:52pm
Subject: RE: mysql performance / ndb 5.1 performance
List-Archive: http://lists.mysql.com/mysql/198307
Message-Id: <00c301c6801b$a3c05100$6c01a8c0@goofy>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi Jay!

Writes make up about 17% of the queries, and we average 4000 =
queries/sec.
Querycache hit ratio is about 45% (QC seems to be a topic worth spending
some time for... 45% does not look so good).

We already tested replication, changing the database logic in the =
scripts to
use the slaves until the first update operation, then use the master =
only
(so the SELECT-after-UPDATE operations succeed).

Main problem was that the slaves got out of sync after large updates =
(LOAD
DATA INFILE for example), which we solved by doing large updates in =
small
chunks (UPDATE LIMIT n while rows_matched < n), but still, every 2-3 =
days
the slaves got completely out of sync ("duplicate key" and other) :(

Moritz

-----Original Message-----
From: Jay Pipes [mailto:jay@stripped]=20
Sent: Thursday, May 25, 2006 6:37 PM
To: Moritz M=F6ller
Cc: 'Dan Trainor'; mysql@stripped
Subject: Re: mysql performance / ndb 5.1 performance

Moritz M=F6ller wrote:
> Hi Dan,
>=20
> there are about 2GB free, so the net size would still be 32 GB.
>=20
> The queries are really optimized, >99.9% of all queries can be =
satisfied
> without table scans.
>=20
> Well, I guess I have to give NDB a chance, I hope it will help. The =
only
> alternative I come to is to cluster the database on application level =
(use
> server userID%numServers), which would be a [insert favourite
non-swear-word
> here] lot of work ;)

Hi Moritz!

There is an alternative solution than NdbCluster, and that would be to=20
set up a replicated environment, and have commodity hardware slaves=20
supply the bulk of the SELECT operations, with the 8-core machine used=20
as the master replication database.

Your application server or web server would have to point SELECTs to the =

slaves for reporting purposes, and do writes to the master only.  This=20
is a cheap way to get scale-out performance from commodity hardware, and =

it is pretty customizable as far as the replicationi layout you would=20
want...

For instance, you could have your application server direct a certain=20
category of queries to one slave, and another category to another slave, =

depending on traffic conditions.

BTW, how many requests/sec are you averaging, and also, what is the=20
percentage reads to writes in your database?  You can get both answers=20
from SHOW STATUS variables.

Cheers,

Jay

>=20
> -----Original Message-----
> From: Dan Trainor [mailto:dan@stripped]=20
> Sent: Thursday, May 25, 2006 1:41 AM
> To: Moritz M=F6ller; mysql@stripped
> Subject: Re: mysql performance
>=20
> Moritz M=F6ller wrote:
>> Hi list,
>>
>> we're running some large high-traffic mysql servers, and are =
currently
>> reaching the limit of our machines.
>>
>> We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. =
Hardware
> is
>> quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor =
is
>> CPU.
>> The queries run very fast (I seldom see a process that's running =
longer
> than
>> a second), but there are too many of them, I guess.
>>
>> As far as I know, NDB keeps the whole database in memory, so with =
indices
>> and some mem as reserve, we'd need ~48GB (3x16 or something) in total =
for
>> NDB :(
>>
>> Does someone know other solutions to this? Is NDB the only storage =
engine
>> supporting clustering?
>>
>> Thanks in advantage,
>>
>> Moritz
>>
>>
>>
>=20
> Hi -
>=20
> That's quite a large database.  I, too, have been dealing with what I=20
> thought was a large database for this new project.  Being 2G, it =
hardly=20
> compares to your database size.
>=20
> Keep in mind, however, that a 36G ibdata file does not necessarily =
mean=20
> that you are using 36G to store data.  InnoDB documents from the MySQL =

> site explain ways to compact these files, possibly shrinking the size =
of=20
> ibdata files.  Another way to get a better idea of how much data =
you're=20
> actually using is to use the 'SHOW TABLE STATUS' query from within=20
> MySQL.  Take the "InnoDB Free:" item under the 'Comment:' column, and=20
> subtract this from the total size of the ibdata file(s).  This will =
give=20
> you a more accurate representation of how much of that ibdata file=20
> you're actually using.  I think.  (Someone mind correcting me if I'm =
way=20
> off here?)
>=20
> NDB may not be your solution.  Even though disk-based storage is=20
> included with NDB in 5.1 and beyond, I'm not too sure how this will=20
> affect the speed of your operations.  I suppose it's worth a try, =
however.
>=20
> Please take this advise with a grain of salt, as InnoDB is still quite =

> new to me, as well.  Other things I've found to speed up large =
databases=20
> are to properly make indexes, and testing them with the EXPLAIN=20
> function.  This alone has let me to speed up our operations as much as =

> 30% in most cases.
>=20
> Thanks
> -dant
>=20
>=20

--=20
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Roaming North America, based in Columbus, Ohio
email: jay@stripped	mob: +1 614 406 1267

Are You MySQL Certified? http://www.mysql.com/certification
Got Cluster? http://www.mysql.com/cluster

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=3Dmm@stripped