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:[EMAIL PROTECTED]
Sent: Thursday, May 25, 2006 6:37 PM
To: Moritz Möller
Cc: 'Dan Trainor'; mysql@lists.mysql.com
Subject: Re: mysql performance / ndb 5.1 performance
Moritz Möller wrote:
> Hi Dan,
>
> there are about 2GB free, so the net size would still be 32 GB.
>
> The queries are really optimized, >99.9% of all queries can be satisfied
> without table scans.
>
> 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
set up a replicated environment, and have commodity hardware slaves
supply the bulk of the SELECT operations, with the 8-core machine used
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
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
want...
For instance, you could have your application server direct a certain
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
percentage reads to writes in your database? You can get both answers
from SHOW STATUS variables.
Cheers,
Jay
>
> -----Original Message-----
> From: Dan Trainor [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 25, 2006 1:41 AM
> To: Moritz Möller; mysql@lists.mysql.com
> Subject: Re: mysql performance
>
> Moritz Möller 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
>>
>>
>>
>
> Hi -
>
> That's quite a large database. I, too, have been dealing with what I
> thought was a large database for this new project. Being 2G, it hardly
> compares to your database size.
>
> Keep in mind, however, that a 36G ibdata file does not necessarily mean
> 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
> ibdata files. Another way to get a better idea of how much data you're
> actually using is to use the 'SHOW TABLE STATUS' query from within
> MySQL. Take the "InnoDB Free:" item under the 'Comment:' column, and
> subtract this from the total size of the ibdata file(s). This will give
> you a more accurate representation of how much of that ibdata file
> you're actually using. I think. (Someone mind correcting me if I'm way
> off here?)
>
> NDB may not be your solution. Even though disk-based storage is
> included with NDB in 5.1 and beyond, I'm not too sure how this will
> affect the speed of your operations. I suppose it's worth a try, however.
>
> 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
> are to properly make indexes, and testing them with the EXPLAIN
> function. This alone has let me to speed up our operations as much as
> 30% in most cases.
>
> Thanks
> -dant
>
>
--
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Roaming North America, based in Columbus, Ohio
email: [EMAIL PROTECTED] mob: +1 614 406 1267
Are You MySQL Certified? http://www.mysql.com/certification
Got Cluster? http://www.mysql.com/cluster
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
